1.常用开源连接池
- Apache DBCP
- C3P0
1.1 DBCP
使用DBCP 数据源,应用程序需要在系统中增加两个jar文件
1.手动配置
package ConnectionPool;
import JDBC.JDBCUtil;
import org.apache.commons.dbcp.BasicDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBCPPool {
public static void main(String[] args) {
BasicDataSource basicDataSource = new BasicDataSource();
//手动设置连接需要的参数
basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
basicDataSource.setUrl("jdbc:mysql://localhost:3306/mybd");
basicDataSource.setUsername("root");
basicDataSource.setPassword("root");
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
//获取连接
try {
connection = basicDataSource.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery("select * from user");
while (resultSet.next()) {
System.out.println(resultSet.getString("username"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtil.release(connection, statement, resultSet);
}
}
}
2.文件配置
创建 dbcp.properties 文件
Key是固定的,不要随意改变
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybd
username=root
password=root
package ConnectionPool;
import JDBC.JDBCUtil;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DBCPPool {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
//获取连接
try {
//dbcp数据库连接池配置文件的方式
//获取dbcp的配置文件
Properties properties = new Properties();
properties.load(new FileInputStream("src/ConnectionPool/dbcp.properties"));
//创建dbcp数据库连接池
DataSource basicDataSource = BasicDataSourceFactory.createDataSource(properties);
connection = basicDataSource.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery("select * from user");
while(resultSet.next()){
System.out.println(resultSet.getString("username"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtil.release(connection,statement, resultSet);
}
}
}
1.2 C3P0
C3P0 是一个开源的JDBC连接池,目前他的开源项目有spring和hibernate。
spring 和 hibernate对C3P0进行支持。
使用c3p0数据库连接池之前,首先需要在资料中找到如下的jar包,加载到项目中。
手动配置:
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
//获取连接
try {
//首先创建c3p0数据库连接池对象
ComboPooledDataSource basicDataSource = new ComboPooledDataSource();
//手动设置参数
basicDataSource.setDriverClass("com.mysql.jdbc.Driver");
basicDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/12day01");
basicDataSource.setUser("root");
basicDataSource.setPassword("123");
connection = basicDataSource.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery("select * from dept");
while(resultSet.next()){
System.out.println(resultSet.getString(2));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtils04.release(connection,statement, resultSet);
}
}
文件方式配置:
当然了,我们提供了c3p0数据库连接池的配置方式,配置文件如下,注意,这是一个xml配置文件。而我们之前的dbcp数据库连接池需要获取配置文件交给dbcp,然后才能去创建数据库连接池。而我们的c3p0,我们只需要将信息配置好,取好固定的名字:c3p0-config.xml然后将文件放在src目录下,我们的c3p0数据库连接池自己去获取配置文件,自己解析配置文件。非常方便
注意:
1.c3p0的xml配置文件的名称必须叫做c3p0-config.xml
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://localhost:3306/mybd</property>
<property name="user">root</property>
<property name="password">root</property>
<!-- 如果连接池中连接不够时一次性增长多少连接 -->
<property name="acquireIncrement">5</property>
<!-- 初始化连接池时池子中有多少个连接 -->
<property name="initialPoolSize">20</property>
<!-- 池子中最小连接数 -->
<property name="minPoolSize">10</property>
<!-- 池子中最大连接数 -->
<property name="maxPoolSize">40</property>
<!-- 每次最多可以执行多少个批处理语句 -->
<property name="maxStatements">50</property>
<!-- 连接池内单个连接所拥有的最大缓存statements数 -->
<property name="maxStatementsPerConnection">5</property>
</default-config>
</c3p0-config>
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
//获取连接
try {
ComboPooledDataSource basicDataSource = new ComboPooledDataSource();
connection = basicDataSource.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery("select * from user");
while (resultSet.next()) {
System.out.println(resultSet.getString(2));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtil.release(connection, statement, resultSet);
}
}
2.DBUtils
package ConnectionPool;
public class User {
private String id;
private String username;
private int age;
private String password;
public User() {
super();
}
public User(String id, String username, int age, String password) {
this.id = id;
this.username = username;
this.age = age;
this.password = password;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
之前的做法
2.1 使用DBUtils改写上述代码
使用DBUtils之前需要导入DBUtils的jar包
DBUtils概述
DBUtils是java编程中的数据库操作实用工具,小巧简单实用。
DBUtils封装了对JDBC的操作,简化了JDBC操作,可以少写代码。
Dbutils三个核心功能介绍
- QueryRunner中提供对sql语句操作的API.
- ResultSetHandler接口,用于定义select操作后,怎样封装结果集.
- DbUtils类,它就是一个工具类,定义了关闭资源与事务处理的方法
package ConnectionPool;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class C3P0Utils {
//空参,自动到classpath目录下面加载“c3p0-config.xml”配置文件---配置文件的存储位置和名称必须是这样,且使用“默认配置”
public static ComboPooledDataSource dataSource = new ComboPooledDataSource();
public static DataSource getDataSource(){
return dataSource;
}
public static Connection getConnection(){
Connection con = null;
//con从数据库连接池去拿
try {
con = dataSource.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
public static void release(Connection con, Statement st, ResultSet rs){
//6.释放资源:先开后关,后开先关,最好放在finally代码块中
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
c3p0-config.xml需要放在src目录下
<?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/mybd</property>
<property name="user">root</property>
<property name="password">root</property>
<!-- 如果连接池中连接不够时一次性增长多少连接 -->
<property name="acquireIncrement">5</property>
<!-- 初始化连接池时池子中有多少个连接 -->
<property name="initialPoolSize">20</property>
<!-- 池子中最小连接数 -->
<property name="minPoolSize">10</property>
<!-- 池子中最大连接数 -->
<property name="maxPoolSize">40</property>
<!-- 每次最多可以执行多少个批处理语句 -->
<property name="maxStatements">50</property>
<!-- 连接池内单个连接所拥有的最大缓存statements数 -->
<property name="maxStatementsPerConnection">5</property>
</default-config>
</c3p0-config>
2.2 QueryRunner核心类介绍
- QueryRunner(DataSource) 创建核心类,并提供数据源,内部自己维护Connection
- update(String sql , Object … params) 执行DML语句
- query(String sql , ResultSetHandler , Object … params) 执行DQL语句,并将查询结果封装到对象中。
或者是自己提供Connection
- QueryRunner() 创建核心类,没有提供数据源,在进行具体操作时,需要手动提供Connection
- update(Connection conn, String sql , Object … params) 执行DML语句
- query(Connection conn, String sql , ResultSetHandler , Object … params) 执行DQL语句,并将查询结果封装到对象中。
2.3 QueryRunner实现添加、更新、删除操作
添加
// 提供con
public void add() throws Exception{
//首先创建queryRunner对象
QueryRunner queryRunner = new QueryRunner();
String sql = "insert into product values(?,?,?,?)";
//注意,此处必须要传递connection对象才能执行。
Connection con = JdbcUtils.getConnection();
int update = queryRunner.update(con, sql, 20,"空调",2000,"c001");
//自己需要关闭连接
con.close();
}
// 不提供con
public void add() throws SQLException{
//首先创建queryRunner对象,提供数据库连接池
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "insert into product values(?,?,?,?)";
//注意,此处必须要传递connection对象才能执行。
int update = queryRunner.update(sql, 21,"立式空调",3000,"c001");
}
更新
public void update(){
//创建QueryRunner对象
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
//
try {
int update = queryRunner.update("update product set pname=? where pid=? ", "cccc",14);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
删除
public void delete(){
//创建QueryRunner对象
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
try {
int update = queryRunner.update("delete from product where pid=?", 14);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
2.4 QueryRunner实现查询操作(重点)
- query(String sql, ResultSetHandler rsh, Object… params) ,用来完成表数据的查询操作
ResultSetHandler结果集处理类
JavaBean
package ConnectionPool;
public class Product {
private int pid;
private String pname;
private double price;
private String category_id;
public Product() {
super();
}
public Product(int pid, String pname, double price, String category_id) {
this.pid = pid;
this.pname = pname;
this.price = price;
this.category_id = category_id;
}
public int getPid() {
return pid;
}
public void setPid(int pid) {
this.pid = pid;
}
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public String getCategory_id() {
return category_id;
}
public void setCategory_id(String category_id) {
this.category_id = category_id;
}
@Override
public String toString() {
return "Product{" +
"pid=" + pid +
", pname='" + pname + '\'' +
", price=" + price +
", category_id='" + category_id + '\'' +
'}';
}
}
1.BeanHandler
/**
* BeanHandler:结果集处理的一种方式
* 将查询的结果的第一条数据封装到javaBean中。
* 格式:new BeanHandler<泛型>(javaBean的类型)
*
* @throws SQLException
*/
@Test
public void beanHandlerTest() throws SQLException{
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from product";
//执行sql语句
Product product = queryRunner.query(sql, new BeanHandler<Product>(Product.class));
System.out.println(product);
}
2.BeanListHandler
/**
* BeanListHandler:
* 将查询的所有的结果封装到javaBean的集合中。
*
* @throws SQLException
*/
@Test
public void beanListHandlerTest() throws SQLException{
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from product";
//执行sql语句
List<Product> productList = queryRunner.query(sql, new BeanListHandler<Product>(Product.class));
for (Product product : productList) {
System.out.println(product);
}
}
3.ScalarHander
/**
* ScalarHandler:
* 将一行一列的数据封装起来。一般用于聚集函数
* 必须是Long类型
* @throws SQLException
*
*/
@Test
public void scalarHandlerTest() throws SQLException{
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select count(*) from product";
//执行sql语句
Long count = queryRunner.query(sql, new ScalarHandler<Long>());
System.out.println(count);
}
4.MapHandler
/**
* mapHandler:
* 将第一条数据封装成map集合。
* key就是列名,value就是列对应的值。
* @throws SQLException
*/
@Test
public void mapHandlerTest() throws SQLException{
//首先创建一个queryRunner对象
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql ="select * from product";
Map<String, Object> query = queryRunner.query(sql, new MapHandler());
System.out.println(query);
}
5.MapListHandler
/**
* mapListHandlerTest:
* 将所有的商品封装成list集合,list集合中每一个都是map集合。
* map集合的key就是列名,值就是列对应的值。
* @throws SQLException
*/
@Test
public void mapListHandlerTest() throws SQLException{
//首先创建一个queryRunner对象
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql ="select * from product";
List<Map<String,Object>> query = queryRunner.query(sql, new MapListHandler());
for (Map<String, Object> map : query) {
System.out.println(map);
}
}
6.ArrayHandler
/**
* arrayHandler:
* 将第一条数据封装成数组,数组中的每个值都是列中的值。
* @throws SQLException
*/
@Test
public void arrayHandlerTest() throws SQLException{
//首先创建一个queryRunner对象
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql ="select * from product";
Object[] query = queryRunner.query(sql, new ArrayHandler());
System.out.println(Arrays.toString(query));
}
7.ArrayListHandler
/**
* arrayListHandler:
* 将所有的数据封装成一个list集合,集合中的每一个对象都是数组。每一个数组对应了一条数据。
* @throws SQLException
*/
@Test
public void arrayListHandlerTest() throws SQLException{
//首先创建一个queryRunner对象
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql ="select * from product";
List<Object[]> query = queryRunner.query(sql, new ArrayListHandler());
for (Object[] objects : query) {
System.out.println(Arrays.toString(objects));
}
}
8.KeyedHandler
/**
* keyedHandler:
* 将所有的结果封装成一个map集合,
* key就是制定的列的值,
* value就是每一条数据被封装成的map集合,key是列名,值就是列 对应的值。
* new KeyedHandler<T>(columName)
* T:指的就是列对应的数据类型
* columName:制定的列的名称,如果不指定列,那么就默认的取第一列。
* @throws SQLException
*/
@Test
public void keyedHandlerTest() throws SQLException{
//首先创建一个queryRunner对象
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql ="select * from product";
Map<String, Map<String, Object>> query = queryRunner.query(sql, new KeyedHandler<String>("category_id"));
for (Map.Entry<String, Map<String, Object>> e : query.entrySet()) {
System.out.println(e.getKey());
System.out.println(e.getValue());
}
}
9.ColumnListHandler
/**
* columListHandler:
* 将制定列中的所有值封装成list集合。
* new ColumnListHandler<T>(columName)
* T:列的类型对应了java中的数据类型。
* columName:指定的表中的列名,如果不指定列名,那么默认的就是第一列。
* @throws SQLException
*/
@Test
public void columListHandlerTest() throws SQLException{
//首先创建一个queryRunner对象
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql ="select * from product";
List<String> query = queryRunner.query(sql, new ColumnListHandler<String>("pname"));
for (String integer : query) {
System.out.println(integer);
}
}