摘要:DBCPBasicDataSourceMySQLStatementPreparedStatement

DBCP简介

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

DBCP依赖


            commons-dbcp
            commons-dbcp
            1.4
        

        
            mysql
            mysql-connector-java
            5.1.36
        

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服务器的连接池,创建一个Mapname得到连接池。

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 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和查询对象,包括StatementPreparedStatement,区别如下:

  • 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 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 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预编译插入语句,调用addBatchexecuteBatch进行批量插入

    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 

删除数据,使用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();
                }
            }
        }
    }

文章来源于互联网:Java使用DBCP连接池操作MySQL

发表评论