dbcp:
1.
需要导入的jar包:
commons-dbcp-1.4.jar,
commons-logging-1.2.jar,commons-pool-1.5.6.jar,
mysql-connector-java-5.1.39-bin.jar
2.配置文件
配置文件名称:*.properties(例如db.properties)
配置文件位置:任意,我放在了src下面
注意:配置文件中不可以书写中文,否则为乱码
配置文件db.properties:
#基本的设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/demo01
username=root
password=root
#初始化时连接池中connection数量
initialSize=10
#最大连接数量
maxActive=50
#最大的空闲连接数量
maxIdle=20
#最小的空闲链接数量
minIdle=5
#最大的等待时间,单位是毫秒
maxWait=60000
#建立连接时的附加参数,如果指定的编码不一致数据库中会出现乱码
connectionProperties=useUnicode=true;characterEncoding=utf8
#是否开启自动提交,跟事务的控制有关
defaultAutoCommit=true
#指定由连接池所创建的连接的事务隔离级别(TransactionIsolation)。
defaultTransactionIsolation=REPEATABLE_READ
DbcpUtil.java:
public class DbcpUtil {
private static DataSource dataSource;
static {
try {
//加载配置文件,获得文件流
InputStream in = DbcpUtil.class.getClassLoader().getResourceAsStream("db.properties");
//使用properties处理配置文件
Properties p = new Properties();
p.load(in);
//使用工具类创建连接池
dataSource=BasicDataSourceFactory.createDataSource(p);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException(e);
}
}
public static DataSource getDataSource() {
return dataSource;
}
public static Connection getConnection() {
Connection conn = null;
try {
conn = dataSource.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void closeAll(ResultSet rs,PreparedStatement ptmt,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ptmt!=null){
try {
ptmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();//关闭
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
c3p0:
1.
需要导入的jar包:
c3p0-0.9.1.2.jar,
mchange-commons-java-0.2.3.jar,
mysql-connector-java-5.1.39-bin.jar
2.配置文件
配置文件名称:c3p0-config.xml
配置文件位置:任意,我放在了src下面
c3p0-config.xml:
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
//默认配置,不指定时使用默认配置
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///webdemo</property>
<property name="user">root</property>
<property name="password">0118</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
//其他配置
<named-config name="config_1">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///webdemo</property>
<property name="user">root</property>
<property name="password">0118</property>
</named-config>
</c3p0-config>
C3p0Util.java:
public class C3p0Util {
//使用默认配置(c3p0-config.xml--default-config)
private static ComboPooledDataSource dataSource=new ComboPooledDataSource();
//使用指定配置
//private static ComboPooledDataSource dataSource=new ComboPooledDataSource("config_1");
/**
* 获得数据源
* @return
*/
public static DataSource getDateSource() {
return dataSource;
}
public static Connection getConnection() {
Connection conn=null;
try {
conn=dataSource.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
}
使用DBUtils工具和Junit5进行测试:
准备工作:引入commons-dbutils-1.4.jar
测试代码:
class DbcpUtilTest {
@Test
void test() {
String sql="select *from t_user limit ?,?";
try {
Connection conn = DbcpUtil.getConnection();
PreparedStatement ptmt = conn.prepareStatement(sql);
ptmt.setInt(1, 0);
ptmt.setInt(2, 10);
ResultSet rs=ptmt.executeQuery();
while(rs.next()) {
System.out.println(rs.getInt("id")+" : "+rs.getString("name"));
}
} catch (Exception e) {
// TODO: handle exception
}
}
/**
* 使用DBUtil查---查询
*/
@Test
public void DBUtilTest1() {
String sql="select *from t_user limit ?,?";
try {
QueryRunner qr = new QueryRunner(DbcpUtil.getDataSource());
Object[] params = { 0, 10 };
List<Student> students = qr.query(sql, new BeanListHandler<Student>(Student.class), params);
for(Student student:students) {
System.out.println(student.getId()+" : "+student.getName());
}
} catch (Exception e) {
// TODO: handle exception
}
}
/**
* 使用DBUtil查---添加
*/
@Test
public void DBUtilTest2() {
String sql="INSERT INTO t_user(name,pwd) VALUES(?,?)";
try {
QueryRunner qr = new QueryRunner(DbcpUtil.getDataSource());
Object[] params = { "admin", "123" };
qr.update(sql, params);
} catch (Exception e) {
// TODO: handle exception
}
}
/**
* 使用DBUtil查---删除
*/
@Test
public void DBUtilTest3() {
String sql="DELETE FROM t_user WHERE id=?";
try {
QueryRunner qr = new QueryRunner(DbcpUtil.getDataSource());
Object[] params = { 2 };
qr.update(sql, params);
} catch (Exception e) {
// TODO: handle exception
}
}
/**
* 使用DBUtil查---根据id查询
*/
@Test
public void DBUtilTest4() {
String sql="SELECT *FROM t_user WHERE id=?";
try {
QueryRunner qr = new QueryRunner(DbcpUtil.getDataSource());
Object[] params = { 3 };
User user=qr.query(sql,new BeanHandler<User>(User.class), params);
System.out.println(user.getName());
} catch (Exception e) {
// TODO: handle exception
}
}
/**
* 使用DBUtil查---查询总记录数
*/
@Test
public void DBUtilTest5() {
String sql="SELECT COUNT(id) FROM t_user";
try {
QueryRunner qr = new QueryRunner(DbcpUtil.getDataSource());
Object[] params = {};
long num = (long) qr.query(sql, new ScalarHandler(),params);
System.out.println(num);
} catch (Exception e) {
// TODO: handle exception
}
}
}