MySqlHelper

package utils;

import java.io.IOException;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Types;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Properties;

import org.apache.tomcat.jdbc.pool.DataSource;

import org.apache.tomcat.jdbc.pool.PoolProperties;

/**

 * edit by lxj 2016-10-19

 * 采用tomcat7的最新数据库连接池技术进行处理参考以下网址

 * https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Introduction

 * 需要引入tomcat-juli.jar和tomcat-jdbc.jar两个包

 * @author Administrator

 *

 */

public class MySqlHelper {

    private static DataSource datasource;

    public MySqlHelper()

    {

        if(datasource==null)

        {

            Properties pro=new Properties();

            try {

                pro.load(this.getClass().getResourceAsStream("/dbinfo.properties"));

            } catch (IOException e) {

                e.printStackTrace();

            }

            String jdbcDriver=pro.getProperty("driver");

            String dbUrl=pro.getProperty("url");

            String dbUsername=pro.getProperty("userName");

            String dbPassword=pro.getProperty("password");

            PoolProperties p = new PoolProperties();

            p.setUrl(dbUrl);

            p.setDriverClassName(jdbcDriver);

            p.setUsername(dbUsername);

            p.setPassword(dbPassword);

            p.setJmxEnabled(true);

            p.setTestWhileIdle(false);

            p.setTestOnBorrow(true);

            p.setValidationQuery("SELECT 1");

            p.setTestOnReturn(false);

            p.setValidationInterval(30000);

            p.setTimeBetweenEvictionRunsMillis(30000);

            p.setMaxActive(100);

            p.setInitialSize(3);

            p.setMaxWait(10000);

            p.setRemoveAbandonedTimeout(60);

            p.setMinEvictableIdleTimeMillis(30000);

            p.setMinIdle(10);

            p.setLogAbandoned(true);

            p.setRemoveAbandoned(true);

            p.setJdbcInterceptors(

              "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+

              "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");

            datasource = new DataSource();

            datasource.setPoolProperties(p);

        }

    }

    /**

     * 查询

     * @param sql

     * @param lo

     * @return

     */

    public List<Map<String, Object>> search(String sql, Object[] lo) {

        List<Map<String, Object>> lm = new ArrayList<Map<String, Object>>();

        Connection con = null;

        PreparedStatement ps =null;

        ResultSet rs=null;

        try {

            con = datasource.getConnection();

            ps = con.prepareStatement(sql);

            if (lo != null) {

                for (int i = 1; i <= lo.length; i++) {

                    ps.setObject(i, lo[i - 1]);

                }

            }

            rs = ps.executeQuery();

            ResultSetMetaData rm = rs.getMetaData();

            int cont = rm.getColumnCount();

            while (rs.next()) {

                Map<String, Object> mp = new HashMap<String, Object>();

                for (int c = 1; c <= cont; c++) {

                    Object o = rs.getObject(c);

                    mp.put(rm.getColumnName(c), o == null ? "" : o);

                }

                lm.add(mp);

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

        finally {

            if (rs!=null) try {rs.close();}catch (Exception ex) {}

            if (ps!=null) try {ps.close();}catch (Exception ex) {}

            if (con!=null) try {con.close();}catch (Exception ex) {}

        }

        return lm;

    }

    /**

     * 保存和修改和删除

     * @param sql

     * @param lo

     * @return

     */

    public int saveorupdate(String sql, Object[] lo) {

        int p = 0;

        Connection con = null;

        PreparedStatement ps =null;

        try {

            con = datasource.getConnection();

            ps = con.prepareStatement(sql);

            if (lo != null) {

                for (int i = 1; i <= lo.length; i++) {

                    ps.setObject(i, lo[i - 1]);

                }

            }

            p = ps.executeUpdate();

        } catch (SQLException e) {

            e.printStackTrace();

        }

        finally {

            if (ps!=null) try {ps.close();}catch (Exception ex) {}

            if (con!=null) try {con.close();}catch (Exception ex) {}

        }

        return p;

    }

    /**

     * 获取分页数据

     * @param sqlmodel 查询模型

     * @return

     */

    public static List<Map<String, Object>> GetPageList(Model.Sql_PageList sqlmodel) {

        List<Map<String, Object>> lm = new ArrayList<Map<String, Object>>();

        Connection con = null;

        ResultSet rs = null;

        CallableStatement Callstmt=null;

        try {

            con = datasource.getConnection();

            Callstmt = con.prepareCall("{call PageList(?,?,?,?,?,?,?,?)}");

            Callstmt.setString("_fields", sqlmodel.getStrFieldShow());

            Callstmt.setString("_tables", sqlmodel.getStrTableName());

            Callstmt.setString("_where", sqlmodel.getStrWhere());

            Callstmt.setString("_orderby", sqlmodel.getStrFieldOrder());

            Callstmt.setInt("_pageindex", sqlmodel.getPageCurrent());

            Callstmt.setInt("_pagesize", sqlmodel.getPageSize());

            Callstmt.registerOutParameter("_totalcount",java.sql.Types.INTEGER);

            Callstmt.registerOutParameter("_pagecount",java.sql.Types.INTEGER);

            Callstmt.executeQuery();

            rs = Callstmt.executeQuery();

            int totalcount = Callstmt.getInt("_totalcount");

            int pagecount = Callstmt.getInt("_pagecount");

            sqlmodel.setInfoCount(totalcount);

            sqlmodel.setPageCount(pagecount);

            ResultSetMetaData rm = rs.getMetaData();

            int cont = rm.getColumnCount();

            while (rs.next()) {

                Map<String, Object> mp = new HashMap<String, Object>();

                for (int c = 1; c <= cont; c++) {

                    Object o = rs.getObject(c);

                    mp.put(rm.getColumnName(c), o == null ? "" : o);

                }

                lm.add(mp);

            }

        } catch (Exception e) {

            e.printStackTrace();

        } finally {

            if (rs!=null) try {rs.close();}catch (Exception ex) {}

            if (Callstmt!=null) try {Callstmt.close();}catch (Exception ex) {}

            if (con!=null) try {con.close();}catch (Exception ex) {}

        }

        return lm;

    }

}