数据库连接池
DBCP
DataBase Connection Pool：数据库连接池，由Apache开发。
1、导入jar包
commons-dbcp-1.4.jar、commons-pool-1.6.jar
下载jar包在 https://mvnrepository.com/ 中搜索下载即可
2、导入dbcp.properties配置文件
#连接基本设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc?useSSL=false
username=root
password=xxxxxx
#<!--扩展配置 了解-->
#初始化连接
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=utf8
#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
#defaultReadOnly=
#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
#defaultTransactionIsolation=READ_UNCOMMITTED
3、编写DBCPUtil工具类获取连接
package org.westos.util;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author lwj
* @date 2020/8/13 9:10
*/
public class DBCPUtil {
private static DataSource dataSource;
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream(new File("dbcp.properties")));
//读取项目根目录下的配置文件
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void close(ResultSet rs, PreparedStatement ps, Connection conn) throws SQLException {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
}
}
4、测试连接
package org.westos.demo;
import org.westos.util.DBCPUtil;
import java.sql.*;
/**
* @author lwj
* @date 2020/8/13 9:23
*/
public class DbcpTest {
public static void main(String[] args) throws SQLException {
Connection connection = DBCPUtil.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("select * from users where id = ?");
preparedStatement.setInt(1, 2);
System.out.println(preparedStatement);
//com.mysql.jdbc.JDBC42PreparedStatement@4141d797: select * from users where id = 2
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int id = resultSet.getInt(1);
String username = resultSet.getString(2);
String password = resultSet.getString(3);
Date date = resultSet.getDate(4);
System.out.println(id + ":" + username + ":" + password + ":" + date);
//2:aaa2:123456:2020-08-11
}
DBCPUtil.close(resultSet, preparedStatement, connection);
}
}
C3P0
C3P0是一个开源的JDBC连接池。
与DBCP的区别：
- DBCP没有自动回收空闲连接的功能；
- C3P0有自动回收空闲连接的功能。
1、导入jar包
c3p0-0.9.5.5.jar、mchange-commons-java-0.2.20.jar
2、导入c3p0.properties或者c3p0-confog.xml
c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql:///jdbc?useSSL=false
c3p0.user=root
c3p0.password=xxxxxx
<c3p0-config>
<!-- 默认配置，如果没有指定则使用这个配置 -->
<default-config>
<!-- 基本配置 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/jdbc?useSSL=false</property>
<property name="user">root</property>
<property name="password">xxxxxx</property>
<!--扩展配置-->
<property name="checkoutTimeout">30000</property>
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
<!-- 命名的配置 -->
<named-config name="otherDB">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/xxxx</property>
<property name="user">root</property>
<property name="password">1234</property>
<!-- 如果池中数据连接不够时一次增长多少个 -->
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">20</property>
<property name="minPoolSize">10</property>
<property name="maxPoolSize">40</property>
<property name="maxStatements">20</property>
<property name="maxStatementsPerConnection">5</property>
</named-config>
</c3p0-config>
3、编写C3P0工具类获取连接
package org.westos.util;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.SQLException;
/**
* @author lwj
* @date 2020/8/13 9:34
*/
public class C3P0Util {
private static ComboPooledDataSource dataSource;
static {
dataSource = new ComboPooledDataSource();
//ComboPooledDataSource otherDB = new ComboPooledDataSource("otherDB");
//当使用c3p0-config.xml配置时，可以有第二配置，连接其他数据库产品使用一个配置文件，无参构造连接xml中的default-config
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
//close
}
4、测试类
package org.westos.demo;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.westos.util.C3P0Util;
import java.sql.Connection;
import java.sql.SQLException;
/**
* @author lwj
* @date 2020/8/13 9:53
*/
public class C3p0Test {
public static void main(String[] args) throws SQLException {
//c3p0数据库连接池的配置文件有两种，.properties和xml类型，必须放在src目录下，而且名称为c3p0.properties/c3p0-config.xml
Connection connection = C3P0Util.getConnection();
System.out.println(connection);
//com.mchange.v2.c3p0.impl.NewProxyConnection@483bf400 [wrapping: com.mysql.jdbc.JDBC4Connection@21a06946]
}
}
Druid
DRUID是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、PROXOOL等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池。
1、导入jar包
druid-1.1.23.jar
2、导入druid.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///jdbc?useSSL=false
username=root
password=xxxxxx
filters=stat
initialSize=2
maxActive=300
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
poolPreparedStatements=false
maxPoolPreparedStatementPerConnectionSize=200
3、编写Druid工具类获取连接
package org.westos.util;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author lwj
* @date 2020/8/13 10:04
*/
public class DruidUtil {
private static DataSource dataSource;
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream(new File("src\\druid.properties")));
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
}
4、测试类
package org.westos.demo;
import org.westos.util.DruidUtil;
import java.sql.Connection;
import java.sql.SQLException;
/**
* @author lwj
* @date 2020/8/13 10:04
*/
public class DruidTest {
public static void main(String[] args) throws SQLException {
Connection connection = DruidUtil.getConnection();
System.out.println(connection);
//com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@629f0666
}
}
DBUtils
Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发。
1、导入jar包
commons-dbutils-1.7.jar
2、数据库环境
-- DBUtils
TRUNCATE TABLE users;
3、创建Bean对象
package org.bean;
import java.util.Date;
/**
* @author lwj
* @date 2020/8/13 10:25
*/
public class User {
private int id;
private String username;
private String password;
private Date birthday;
public User() {
}
public User(int id, String username, String password, Date birthday) {
this.id = id;
this.username = username;
this.password = password;
this.birthday = birthday;
}
//getter/setter/toString
}
4、测试DBUtils
package org.westos.demo2;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.bean.User;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* @author lwj
* @date 2020/8/13 10:11
*/
public class DBUtilsTest {
public static void main(String[] args) throws SQLException {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
//获取数据源
QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
//创建一个QueryRunner对象
//增
queryRunner.update("insert into users values (?, ?, ?, ?)", null, "张三", "123456", new Date(new java.util.Date().getTime()));
queryRunner.update("insert into users values (?, ?, ?, ?)", null, "李四", "123456", new Date(new java.util.Date().getTime()));
//查
List<User> userList = queryRunner.query("select * from users where id > ?", new BeanListHandler<>(User.class), 0);
//把查询到的每一条结果封装成实体类，再最终把每一个实体类放到list集合中---BeanListHandler是ResultSetHandler接口的实现类
System.out.println(userList);
//[User{id=1, username='张三', password='123456', birthday=2020-08-13}, User{id=2, username='李四', password='123456', birthday=2020-08-13}]
//改
queryRunner.update("update users set username = ? where id = ?", "王五", 1);
User user = queryRunner.query("select * from users where id = ?", new BeanHandler<>(User.class), 1);
//把查询到的一条结果封装成实体类
System.out.println(user);
//User{id=1, username='王五', password='123456', birthday=2020-08-13}
//删
queryRunner.update("delete from users where id = ?", 2);
List<Map<String, Object>> maps = queryRunner.query("select * from users", new MapListHandler());
//键为字段名，值为value，然后封装到List集合中(可以不用构建Bean对象)
System.out.println(maps);
//[{id=1, username=王五, password=123456, birthday=2020-08-13}]
}
}
项目结构：
扫一扫
176
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
