JDBC的C3P0连接池和DBUtils工具类

最新推荐文章于 2021-03-19 13:01:30 发布

冰零0o

最新推荐文章于 2021-03-19 13:01:30 发布

阅读量815

点赞数

分类专栏： JDBC

本文链接：https://blog.csdn.net/weixin_42072596/article/details/80659002

版权

JDBC 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、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

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
JDBC的C3P0连接池和DBUtils工具类

JDBC连接池使用的是C3P0连接池。1、导入jar包mysql-connector-java-5.0.4-bin.jarc3p0-0.9.1.2.jar2、创建C3P0配置文件C3P0配置文件命名必须是c3p0-config.xml&lt;?xml version="1.0" encoding="UTF-8"?&gt;&lt;c3p0-config&gt; &lt;default-con...
复制链接

扫一扫

专栏目录