数据库(源)操作工具类（JDBC、C3P0、DBCP）简单封装

一、JDBC工具类

包：

db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/xiaohu_test
jdbc.username=root
jdbc.password=123

JDBCUtils.java

package cn.xiyoucloud.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ResourceBundle;

public class JDBCUtils {
	private static String driver;
	private static String url;
	private static String username;
	private static String password;
	
	static{
		ResourceBundle bundle = ResourceBundle.getBundle("db");
		
		driver = bundle.getString("jdbc.driver");
		url = bundle.getString("jdbc.url");
		username = bundle.getString("jdbc.username");
		password = bundle.getString("jdbc.password");
	
	}

	/*
	public static void main(String[] args) throws Exception {
		Connection con = getConnection();
		
		String sql = "select * from product where pid = ? or pname = ?";
		PreparedStatement st = con.prepareStatement(sql);
		st.setString(1, "p006");
		st.setString(2, "联想");
		ResultSet rs = st.executeQuery();
		
		while(rs.next()){
			System.out.println(rs.getString(2));
			//System.out.println(rs.getString(1));
			//System.out.println(rs.getLong(1));
		}
		
		release(rs, st, con);
	
	}*/
	
	
	/**
	 * 获取连接
	 * @return
	 */
	public static Connection getConnection() {

		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, username, password);
			
		} catch (Exception e) {
			e.printStackTrace();
		}

		return conn;
	}

	/**
	 * 释放资源
	 * @param rs
	 * @param st
	 * @param conn
	 */
	public static void release(ResultSet rs, PreparedStatement st, Connection conn) {

		try {
			if (rs != null) {
				rs.close();
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if (st != null) {
					st.close();
				}
			} catch (Exception e) {
				throw new RuntimeException(e);
			} finally {
				try {
					if (conn != null) {
						conn.close();
					}
				} catch (Exception e) {
					throw new RuntimeException(e);
				}
			}
		}

	}

}

---

二、C3P0工具类

包：

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>

<c3p0-config>

	<!-- 命名的配置 -->
	<named-config name="xiyoucloud">
	
		<!-- 连接数据库的4项基本参数 -->
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/xiaohu_test</property>
		<property name="user">root</property>
		<property name="password">123</property>
		
		<!-- 初始化连接数 -->
		<property name="initialPoolSize">10</property>
		<!-- 最小连接数 -->
		<property name="minPoolSize">2</property>
		<!-- 最大连接数 -->
		<property name="MaxPoolSize">20</property>
		
		<!-- 如果池中数据连接不够时，一次增长多少个 -->
		<property name="acquireIncrement">3</property>
		
		<!-- 连接池为数据源缓存的PreparedStatement数量 -->
		<property name="maxStatements">0</property>
		
		<!-- 连接池为数据源单个Connection缓存的PreparedStatement数量 -->
		<property name="maxStatementsPerConnection">5</property>
		
		
	</named-config>
</c3p0-config>

C3P0Utils.java

package cn.xiyoucloud.c3p0utils;

import java.sql.Connection;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {

	private static ComboPooledDataSource dataSource = new ComboPooledDataSource("xiyoucloud");//"xiyoucloud"与xml配置文件中的named-config>标签的name属性值对应

	/**
	 * 获取连接池（数据源）
	 * 
	 * @return
	 */
	public static ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	/**
	 * 获取连接
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

}

测试

package cn.xiyoucloud.dbutils;

import java.sql.SQLException;
import java.util.List;

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.ScalarHandler;
import org.junit.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import cn.xiyoucloud.c3p0utils.C3P0Utils;
import cn.xiyoucloud.category.Category;

public class DBUtilsTest {

	public static void main(String[] args) throws Exception {
		// delete();
		// add();
		// update();
		//findById();
		//findAll();
		findColumn();
	}

	/**
	 * 添加数据
	 * @throws SQLException
	 */
	public static void add() throws SQLException {
		ComboPooledDataSource ds = C3P0Utils.getDataSource();
		QueryRunner queryRunner = new QueryRunner(ds);
		String sql = "insert into category(cid,cname) values(?,?)";
		Object[] params = { "c006", "玩具" };
		queryRunner.update(sql, params);

	}

	/**
	 * 删数据
	 * @throws SQLException
	 */
	public static void delete() throws SQLException {
		ComboPooledDataSource ds = C3P0Utils.getDataSource();
		QueryRunner queryRunner = new QueryRunner(ds);
		String sql = "delete from category where cid = ?";
		// Object[] params = {"c006"};//或下面一行
		Object params = "c006";

		queryRunner.update(sql, params);

	}

	/**
	 * 改数据
	 * @throws SQLException
	 */
	public static void update() throws SQLException {
		ComboPooledDataSource ds = C3P0Utils.getDataSource();
		QueryRunner queryRunner = new QueryRunner(ds);
		String sql = "update category set cname = ? where cid = ? ";
		Object[] params = { "不知道", "c006" };
		queryRunner.update(sql, params);

	}

	/**
	 * BeanHandler
	 * 
	 * 注意声明Category是由自己建立的实体类
	 * @throws SQLException
	 */
	public static void findById() throws SQLException {
		ComboPooledDataSource ds = C3P0Utils.getDataSource();
		QueryRunner queryRunner = new QueryRunner(ds);
		String sql = "select * from category where cid = ?";
		Object[] params = { "c006" };
		Category category = queryRunner.query(sql, new BeanHandler<Category>(Category.class), params);
		System.out.println(category);

	}
	
	/**
	 * BeanListHandler
	 * @throws SQLException
	 */
	@Test
	public static void findAll() throws SQLException{
		ComboPooledDataSource ds = C3P0Utils.getDataSource();
		QueryRunner queryRunner = new QueryRunner(ds);
		String sql = "select * from category";
		Object[] params = {};
		List<Category> lists = queryRunner.query(sql, new BeanListHandler<>(Category.class));
	
		for (Category category : lists) {
			System.out.println(category);
		}
	
	}
	

	/**
	 * ScalarHandler
	 */
	public static void findColumn(){
		
		try {
			ComboPooledDataSource ds = C3P0Utils.getDataSource();
			QueryRunner queryRunner = new QueryRunner(ds);
			String sql = "select count(*) from category";
			
			Long col = queryRunner.query(sql, new ScalarHandler<>());
			
			System.out.println(col);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
	}
	
}

---

三、DBCP工具类

包：

dbcp.properties

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/xiaohu_test
username=root
password=123

#初始化连接
initialSize=10

#最大连接数
maxActive=50

#最大空闲连接数
maxIdle=20

#超时等待时间以毫秒为单位60000毫秒/1000等于60秒
maxWait=60000

#JDBC驱动连接时附带的连接属性，格式为：[属性名=properties;]
#注意："user"与"password"两个属性会被明确地传递，因此这里不需要包含它们
connectionProperties=useUnicode=true;characterEncoding=gbk

#指定由连接池所创建的连接的自动提交（auto-commit）状态
defaultAutoCommit=true

#driver default指定由连接池所创建的连接的事务级别（TransactionIsolation）
#可用值：NONE, READ_UNCOMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

DBCPUtils.java

package cn.xiyoucloud.dbcputils;

import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

public class DBCPUtils {

	private static DataSource dataSource;
	
	static{
		try {
			//加载配置文件
			InputStream is = DBCPUtils.class.getClassLoader().getResourceAsStream("dbcp.properties");
			Properties props = new Properties();
			props.load(is);
			//创建连接池（数据源）
			dataSource = BasicDataSourceFactory.createDataSource(props);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	
	public static DataSource getDataSource(){
		return dataSource;
	}
	
	
	public static Connection getConnection(){
		try {
			return dataSource.getConnection();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}	
	}
	
}