java dbutils工具类,JDBC的C3P0连接池和DBUtils工具类

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

com.mysql.jdbc.Driver

jdbc:mysql://localhost:3306/jdbcdb

root

123456

5

20

com.mysql.jdbc.Driver

jdbc:mysql://localhost:3306/jdbcdb

root

123456

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配置文件中默认配置

private static ComboPooledDataSource dataSource = new ComboPooledDataSource();

//方法二、需要传参，会找c3p0配置文件中中名称对应的配置

//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 users = qr.query(sql, new BeanListHandler(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.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> list = qr.query(sql, new MapListHandler());

// 4.对结果集集合进行遍历

for (Map 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 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

版权声明：本文为博主原创文章，转载请附上博文链接！