Java使用DBCP连接池操作MySQL

摘要：DBCP，BasicDataSource，MySQL，Statement，PreparedStatement

DBCP简介

DBCP(DataBase connection pool)数据库连接池是 apache 上的一个Java连接池项目。实际开发中获得连接或释放资源是非常消耗系统资源的两个过程。DBCP通过连接池预先同数据库建立一些连接放在内存中(即连接池中)，应用程序需要建立数据库连接时直接到从接池中申请一个连接使用，用完后由连接池回收该连接，从而达到连接复用，减少资源消耗的目的。

DBCP依赖

<dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.36</version>
        </dependency>

DBCP连接池参数说明

• setDriverClassName: 数据库驱动名称，一般是com.mysql.jdbc.Driver。
• setUrl: 数据库url地址，jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&connectTimeout=30000&maxReconnects=100&autoReconnect=true&socketTimeout=60000&rewriteBatchedStatements=true，分别指定host，port，数据库名和其他配偶之参数。
• setMaxActive: 连接池的最大数据库连接数，设置成并发量。
• setMaxIdle： 最大空闲连接数。
• setMinIdle： 最小空闲连接数。
• setInitialSize： 初始化连接数。
• setMaxWait： 最大建立连接等待时间。
• setValidationQuery： 验证数据库连接的有效性，mysql是select 1。
• setValidationQueryTimeout：验证超时时间。

创建DBCP连接池单例对象

创建连接池单例对象，如果应用需要调用多个不同的mysql服务器的连接池，创建一个Map用name得到连接池。

import org.apache.commons.dbcp.BasicDataSource;

import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class MySQLUtils {
    private static BasicDataSource mysqlConn = null;
    private static final Map<String, BasicDataSource> mysqlConnMap = new HashMap<>();

    public static BasicDataSource getConnPool(Properties prop) {
        if (mysqlConn == null) {
            synchronized (MySQLUtils.class) {
                if (mysqlConn == null) {
                    mysqlConn = new BasicDataSource();
                    mysqlConn.setDriverClassName(prop.getProperty("jdbc.driver"));
                    mysqlConn.setUrl(prop.getProperty("jdbc.url"));
                    mysqlConn.setUsername(prop.getProperty("jdbc.user"));
                    mysqlConn.setPassword(prop.getProperty("jdbc.passwd"));
                    mysqlConn.setMaxActive(4);
                    mysqlConn.setMaxIdle(4);
                    mysqlConn.setMinIdle(2);
                    mysqlConn.setInitialSize(4);
                    mysqlConn.setMaxWait(10000);
                    mysqlConn.setTestWhileIdle(true);
                    mysqlConn.setValidationQuery("select 1");
                    mysqlConn.setValidationQueryTimeout(10000);
                    mysqlConn.setTimeBetweenEvictionRunsMillis(10000);
                }
            }
        }
        return mysqlConn;
    }

    public static BasicDataSource getConnPool(Properties prop, String name) {
        if (!mysqlConnMap.containsKey(name)) {
            synchronized (MySQLUtils.class) {
                if (!mysqlConnMap.containsKey(name)) {
                    BasicDataSource tmpConn = new BasicDataSource();
                    tmpConn.setDriverClassName(prop.getProperty("jdbc.driver"));
                    tmpConn.setUrl(prop.getProperty("jdbc." + name + ".url"));
                    tmpConn.setUsername(prop.getProperty("jdbc." + name + ".user"));
                    tmpConn.setPassword(prop.getProperty("jdbc." + name + ".passwd"));
                    tmpConn.setMaxActive(4);
                    tmpConn.setMaxIdle(4);
                    tmpConn.setMinIdle(2);
                    tmpConn.setInitialSize(4);
                    tmpConn.setMaxWait(10000);
                    tmpConn.setTestWhileIdle(true);
                    tmpConn.setValidationQuery("select 1");
                    tmpConn.setValidationQueryTimeout(10000);
                    tmpConn.setTimeBetweenEvictionRunsMillis(10000);
                    mysqlConnMap.put(name, tmpConn);
                }
            }
        }
        return mysqlConnMap.get(name);
    }
}

DBCP连接池操作MySQL

从连接池单例中拿到BasicDataSource对象，如果BasicDataSource不为空，初始化Connection和查询对象，包括Statement，PreparedStatement，区别如下：

• PreparedStatement是预编译的,对于批量处理可以大大提高效率。
• Statement在对数据库只执行一次性存取的时侯，效率比PreparedStatement高，PreparedStatement对象的开销比Statement大，对于一次性操作PreparedStatement并不会带来额外的好处。
• statement每次执行sql语句，相关数据库都要执行sql语句的重新编译。
  使用完毕关闭查询对象和连接对象给连接池回收。

使用Statement进行简单查询

public static void easySearch(Properties prop) {
        BasicDataSource connPool = MySQLUtils.getConnPool(prop);
        if (connPool != null) {
            Connection conn = null;
            Statement statement = null;
            try {
                conn = connPool.getConnection();
                statement = conn.createStatement();
                ResultSet res = statement.executeQuery("select name, score from student_info");
                while (res.next()) {
                    System.out.println("name => " + res.getString("name"));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (statement != null) {
                        statement.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                }catch (Exception e) {
                    System.out.println();
                    e.printStackTrace();
                }
            }
        }
    }

in条件查询

public static void inSearch(Properties prop) {
        BasicDataSource connPool = MySQLUtils.getConnPool(prop);
        if (connPool != null) {
            Connection conn = null;
            Statement statement = null;
            try {
                conn = connPool.getConnection();
                statement = conn.createStatement();
                List<String> param = Arrays.asList("gp", "wf");
                String param2 = param.stream().map(s -> "\"" + s + "\"").collect(Collectors.joining(","));
                ResultSet res = statement.executeQuery(String.format("select name, score from student_info where name in (%s)", param2));
                while (res.next()) {
                    System.out.println("name:" + res.getString("name"));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (statement != null) {
                        statement.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

使用PreparedStatement将语句预编译再查询，将固定部分先编译，再使用？将传参部分作为占位符，使用setString传参，默认位置从1开始。

public static void prepareStatementTest(Properties prop, String query) {
        BasicDataSource connPool = MySQLUtils.getConnPool(prop);
        if (connPool != null) {
            Connection conn = null;
            PreparedStatement preparedStatement = null;
            try {
                conn = connPool.getConnection();
                preparedStatement = conn.prepareStatement("select score from student_info where name = ?");
                preparedStatement.setString(1, query);
                ResultSet res = preparedStatement.executeQuery();
                while (res.next()) {
                    System.out.println(res.getString("score"));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

在in查询中使用PreparedStatement，有多少个参数就传多少个占位符？。

public static void prepareStatementTest2(Properties prop, Collection<String> collections) {
        BasicDataSource connPool = MySQLUtils.getConnPool(prop);
        if (connPool != null) {
            Connection conn = null;
            PreparedStatement preparedStatement = null;
            try {
                conn = connPool.getConnection();
                String param = collections.stream().map(s -> "?").collect(Collectors.joining(","));
                preparedStatement = conn.prepareStatement(String.format("select score from student_info where name in (%s)", param));
                int index = 1;
                for (String s : collections) {
                    preparedStatement.setString(index, s);
                    index += 1;
                }
                ResultSet res = preparedStatement.executeQuery();
                while (res.next()) {
                    System.out.println(res.getString("score"));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

使用PreparedStatement预编译插入语句，调用addBatch和executeBatch进行批量插入

    public static void batchInsertTest(Properties prop) {
        BasicDataSource connPool = MySQLUtils.getConnPool(prop);
        if (connPool != null) {
            Connection conn = null;
            PreparedStatement preparedStatement = null;
            try {
                conn = connPool.getConnection();
                preparedStatement = conn.prepareStatement("insert into student_info (name,score)  values (?,?)");
                conn.setAutoCommit(false);
                for (int i = 1; i <= 10; i++) {
                    preparedStatement.setString(1, "gp" + i);
                    preparedStatement.setString(2, String.valueOf(i));
                    preparedStatement.addBatch();
                    if(i % 5 == 0){
                        preparedStatement.executeBatch();
                        conn.commit();
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

删除数据，使用execute执行sql语句。

public static void deleteData(Properties prop, String string) {
        BasicDataSource connPool = MySQLUtils.getConnPool(prop);
        if (connPool != null) {
            Connection conn = null;
            Statement statement = null;
            try {
                conn = connPool.getConnection();
                statement = conn.createStatement();
                statement.execute(String.format("delete from student_info where name = '%s'", string));
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (statement != null) {
                        statement.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

update修改数据，使用execute语句

public static void alterData(Properties prop) {
        BasicDataSource connPool = MySQLUtils.getConnPool(prop);
        if (connPool != null) {
            Connection conn = null;
            Statement statement = null;
            try {
                conn = connPool.getConnection();
                statement = conn.createStatement();
                statement.execute("update student_info set score = 5 where name = \"zzb\"");
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (statement != null) {
                        statement.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

测试多个连接池对象，使用Map获得对应的连接池。

public static void mapPoolTest(Properties prop, String poolName) {
        BasicDataSource connPool = MySQLUtils.getConnPool(prop, poolName);
        if (connPool != null) {
            Connection conn = null;
            Statement statement = null;
            try {
                conn = connPool.getConnection();
                statement = conn.createStatement();
                ResultSet res = statement.executeQuery("select name, score from student_info");
                while (res.next()) {
                    System.out.println("name => " + res.getString("name"));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (statement != null) {
                        statement.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                }catch (Exception e) {
                    System.out.println();
                    e.printStackTrace();
                }
            }
        }
    }