JDBC的C3P0连接池和DBUtils工具类
一、C3P0连接池
1、导入jar包
mysql-connector-java-5.0.4-bin.jar
c3p0-0.9.1.2.jar
2、创建C3P0配置文件
C3P0配置文件命名必须是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://localhost:3306/jdbcdb</property>
<property name=”user”>root</property>
<property name=”password”>123456</property>
<property name=”initialPoolSize”>5</property>
<property name=”maxPoolSize”>20</property>
</default-config>
<named-config name=”MyConfig”>
<property name=”driverClass”>com.mysql.jdbc.Driver</property>
<property name=”jdbcUrl”>jdbc:mysql://localhost:3306/jdbcdb</property>
<property name=”user”>root</property>
<property name=”password”>123456</property>
</named-config>
</c3p0-config>
3、编写C3P0工具类
package com.utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Util {
//方法一、不需要传参，会找c3p0配置文件中<default-config>默认配置
private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
//方法二、需要传参，会找c3p0配置文件中<named-config>中名称对应的配置
//private static ComboPooledDataSource dataSource = new ComboPooledDataSource(“MyConfig”);
public static DataSource getDataSource(){
return dataSource;
}
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException();
}
}
public static void release(Connection conn, PreparedStatement ps, ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
4、测试类
@Test
public void dbOpt(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = C3P0Util.getConnection();
String sql = “select * from tbl_user”;
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
System.out.println(rs.getString(2)+”:”+rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
C3P0Util.release(conn, ps, rs);
}
}
注意：连接池对数据库操作完成后必须 con.close() 掉，执行 con.close() 并不会关闭与数据库的连接，而是将连接还回到连接池中，如果不 close 掉的话，这个连接将会一直被占用，直到连接池中的连接耗尽为止。
二、DBUtils工具类
1、导入jar包
commons-dbutils-1.4.jar
2、编写DBUtils工具类的增删改方法
package com.utils;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
/**
* 测试DBUtils工具类的增删改操作
*/
public class TestDBUtils1 {
/**
* 添加所有用户方法
*/
@Test
public void testAddUser() {
try {
// 1.创建核心类QueryRunner
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
// 2.编写SQL语句
String sql = “insert into tbl_user values(null,?,?)”;
// 3.为站位符设置值
Object[] params = { “余淮”, “耿耿” };
// 4.执行添加操作
int rows = qr.update(sql, params);
if (rows > 0) {
System.out.println(“添加成功!”);
} else {
System.out.println(“添加失败!”);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 根据id修改用户方法
*
*/
@Test
public void testUpdateUserById() {
try {
// 1.创建核心类QueryRunner
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
// 2.编写SQL语句
String sql = “update tbl_user set upassword=? where id=?”;
// 3.为站位符设置值
Object[] params = { “xxx”, 2 };
// 4.执行添加操作
int rows = qr.update(sql, params);
if (rows > 0) {
System.out.println(“修改成功!”);
} else {
System.out.println(“修改失败!”);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 根据id删除用户方法
*/
@Test
public void testDeleteUserById() {
try {
// 1.创建核心类QueryRunner
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
// 2.编写SQL语句
String sql = “delete from tbl_user where id=?”;
// 3.为站位符设置值
Object[] params = {2};
// 4.执行添加操作
int rows = qr.update(sql, params);
if (rows > 0) {
System.out.println(“删除成功!”);
} else {
System.out.println(“删除失败!”);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3、创建javabean类，给查询操作使用
package com.utils;
public class User {
private int id;
private String uName;
private String uPassword;
public User() {}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getuName() {
return uName;
}
public void setuName(String uName) {
this.uName = uName;
}
public String getuPassword() {
return uPassword;
}
public void setuPassword(String uPassword) {
this.uPassword = uPassword;
}
}
4、编写DBUtils工具类的查询操作
package com.utils;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
/**
* 测试DBUtils查询操作
*/
public class TestDBUtils2 {
/*
* 查询所有用户方法
* JavaBean获取
*/
@Test
public void testQueryAll() {
try {
// 1.获取核心类queryRunner
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
// 2.编写sql语句
String sql = “select * from tbl_user”;
// 3.执行查询操作
List<User> users = qr.query(sql, new BeanListHandler<User>(User.class));
// 4.对结果集集合进行遍历
for (User user : users) {
System.out.println(user.getuName() + ” : ” + user.getuPassword());
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/*
* 根据id查询用户方法
* JavaBean获取
*/
@Test
public void testQueryUserById() {
try {
// 1.获取核心类queryRunner
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
// 2.编写sql语句
String sql = “select * from tbl_user where id=?”;
//3.为占位符设置值
Object[] params = {1};
// 4.执行查询操作
User user = qr.query(sql, new BeanHandler<User>(User.class), params);
System.out.println(user.getuName() + ” : ” + user.getuPassword());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/*
* 根据所有用户的总个数
*/
@Test
public void testQueryCount() {
try {
// 1.获取核心类queryRunner
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
// 2.编写sql语句
String sql = “select count(*) from tbl_user”;
// 4.执行查询操作
Long count = (Long) qr.query(sql, new ScalarHandler());
System.out.println(count);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/*
* 查询所有用户方法
* Map获取
*/
@Test
public void testQueryAll1() {
try {
// 1.获取核心类queryRunner
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
// 2.编写sql语句
String sql = “select * from tbl_user”;
// 3.执行查询操作
List<Map<String, Object>> list = qr.query(sql, new MapListHandler());
// 4.对结果集集合进行遍历
for (Map<String, Object> map : list) {
System.out.println(map);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/*
* 查询所有用户的uname字段的信息
*/
@Test
public void testQueryAll2() {
try {
// 1.获取核心类queryRunner
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
// 2.编写sql语句
String sql = “select * from tbl_user”;
// 3.执行查询操作
List<Object> list = qr.query(sql, new ColumnListHandler(“uname”));
// 4.对结果集集合进行遍历
for (Object object : list) {
System.out.println(object);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
注意：使用DBUtils对数据库操作完成后不需要手动 close() 。
———————
作者：冰零0o
来源：CSDN
原文：https://blog.csdn.net/weixin_42072596/article/details/80659002
版权声明：本文为博主原创文章，转载请附上博文链接！