使用c3p0技术实现对数据库crud操作的自定义封装

一：什么是c3p0？

C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。

二：c3p0 与dbcp 的区别？

c3p0有自动回收空闲连接功能

dbcp没有自动回收空闲连接的功能

三： 使用c3p0技术自定义框架实现对数据库crud 操作的封装

一：准备所依赖 的jar 包

http://sourceforge.net/projects/c3p0/ 在这里可以下载所依赖的jar 包

二：书写代码---实现所需功能

一： 实体bean：

 此处的实体命名一定要和数据库统一

package bean;public class User {private Integer id;private String name;private String sex;private Integer age;public User() {super();// TODO Auto-generated constructor stub}public User(Integer id, String name, String sex, Integer age) {super();this.id = id;this.name = name;this.sex = sex;this.age = age;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}@Overridepublic String toString() {return "User [id=" + id + ", name=" + name + ", sex=" + sex + ", age="+ age + "]";}}

二：接口方法：

package dao;import java.util.List;public interface BaseDao<T,PK> {T findById(PK id);void deleteById(PK id)throws Exception;void delete(T entity)throws Exception;void deletes(String ids[])throws Exception;void update(T entity)throws Exception;void insert(T entity)throws Exception;List<T> findAll();}

package dao;import bean.User;public interface UserDao extends BaseDao<User, Integer> {}

三： 接口的实现类

package dao.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.List;import com.mchange.v2.c3p0.impl.DbAuth;import util.BeanHanlder;import util.BeanListHanlder;import util.DBConn;import bean.User;import dao.UserDao;public class UserDaoImpl_c3p0 implements UserDao{private Connection conn;private PreparedStatement pstmt;private ResultSet rs;@Overridepublic User findById(Integer id) {String sql="select id,name,sex,age from user where id=?";return (User) DBConn.query(sql, new Object[]{id}, new BeanHanlder(User.class));}@Overridepublic void deleteById(Integer id) throws Exception {String sql="delete from user where id=?";DBConn.update(sql, new Object[]{id});}@Overridepublic void delete(User entity) throws Exception {deleteById(entity.getId());}@Overridepublic void deletes(String[] ids) throws Exception {// TODO Auto-generated method stub}@Overridepublic void update(User entity) throws Exception {String sql="updata user set name=?,sex=?,age=? where id=?";DBConn.update(sql, new Object[]{entity.getName(),entity.getSex(),entity.getAge(),entity.getId()});}@Overridepublic void insert(User entity) throws Exception {String sql="insert into user (name,sex,age) values(?,?,?)";DBConn.update(sql, new Object[]{entity.getName(),entity.getSex(),entity.getAge()});}@Overridepublic List<User> findAll() {        String sql = "select id,name,sex,age from user limit ?,?";return (List<User>) DBConn.query(sql, new Object[]{1,2}, new BeanListHanlder(User.class));/*  此处 可以 测试 查询所有 和  简单的分页 查询 * String sql = "select id,name,sex,age from user";return (List<User>) DBConn.query(sql, null, new BeanListHanlder(User.class));*/}}

四：封装的连接 数据库，crd 的---update  方法 以及 释放资源的 方法

package dao.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.List;import com.mchange.v2.c3p0.impl.DbAuth;import util.BeanHanlder;import util.BeanListHanlder;import util.DBConn;import bean.User;import dao.UserDao;public class UserDaoImpl_c3p0 implements UserDao{private Connection conn;private PreparedStatement pstmt;private ResultSet rs;@Overridepublic User findById(Integer id) {String sql="select id,name,sex,age from user where id=?";return (User) DBConn.query(sql, new Object[]{id}, new BeanHanlder(User.class));}@Overridepublic void deleteById(Integer id) throws Exception {String sql="delete from user where id=?";DBConn.update(sql, new Object[]{id});}@Overridepublic void delete(User entity) throws Exception {deleteById(entity.getId());}@Overridepublic void deletes(String[] ids) throws Exception {// TODO Auto-generated method stub}@Overridepublic void update(User entity) throws Exception {String sql="updata user set name=?,sex=?,age=? where id=?";DBConn.update(sql, new Object[]{entity.getName(),entity.getSex(),entity.getAge(),entity.getId()});}@Overridepublic void insert(User entity) throws Exception {String sql="insert into user (name,sex,age) values(?,?,?)";DBConn.update(sql, new Object[]{entity.getName(),entity.getSex(),entity.getAge()});}@Overridepublic List<User> findAll() {        String sql = "select id,name,sex,age from user limit ?,?";return (List<User>) DBConn.query(sql, new Object[]{1,2}, new BeanListHanlder(User.class));/*  此处 可以 测试 查询所有 和  简单的分页 查询 * String sql = "select id,name,sex,age from user";return (List<User>) DBConn.query(sql, null, new BeanListHanlder(User.class));*/}}

五： 所依赖的 查询的封装类

 结果集的接口----------------

package util;import java.sql.ResultSet;public interface ResultSetHanlder {// 声明一个方法用来处理查询的结果集public Object handler(ResultSet rs)throws Exception;}

单个的封装类

package util;import java.lang.reflect.Field;import java.sql.ResultSet;import java.sql.ResultSetMetaData;public class BeanHanlder implements ResultSetHanlder {// 声明 这是处理的bean 的class  对象private Class clazz;// 构造器初始化  clazz 的对象public BeanHanlder(Class clazz) {super();this.clazz = clazz;}@Overridepublic Object handler(ResultSet rs) throws Exception {if(rs.next()){Object bean=clazz.newInstance();// 反射// 获得代表resulset 元素数据的ResultSetMetaData 对象ResultSetMetaData metaData=rs.getMetaData();// 遍历for(int i=1;i<=metaData.getColumnCount();i++){// 获取表中的 字段名称String columnName=metaData.getColumnName(i);Object columnValue=rs.getObject(columnName);// 通过反射 获取 bean 的name 对应的Field 对象Field f=bean.getClass().getDeclaredField(columnName);f.setAccessible(true);// 暴力f.set(bean, columnValue);// 设值}return bean;}else{return null;}}}

多个结果集的处理类

package util;import java.lang.reflect.Field;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.util.ArrayList;import java.util.List;public class BeanListHanlder implements ResultSetHanlder {  // 这是 处理bean de class 对象private Class clazz;public BeanListHanlder(Class clazz) {super();this.clazz = clazz;}@Overridepublic Object handler(ResultSet rs) throws Exception {List list=new ArrayList();while(rs.next()){Object bean=clazz.newInstance();// 反射// 获得代表resulset 元素数据的ResultSetMetaData 对象ResultSetMetaData metaData=rs.getMetaData();// 遍历for(int i=1;i<=metaData.getColumnCount();i++){// 获取表中的 字段名称String columnName=metaData.getColumnName(i);Object columnValue=rs.getObject(columnName);// 通过反射 获取 bean 的name 对应的Field 对象Field f=bean.getClass().getDeclaredField(columnName);f.setAccessible(true);// 暴力f.set(bean, columnValue);// 设值}list.add(bean);}return list;}}

数据库的xml 配置文件

命名必须规范-----c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?><c3p0-config>  <named-config name="xx"><property name="jdbcUrl">jdbc:mysql://localhost:3306/hiber</property><property name="driverClass">com.mysql.jdbc.Driver</property><property name="user">root</property><property name="password">199358</property><property name="initialPoolSize">10</property>    <property name="maxPoolSize">30</property>    <property name="minPoolSize">10</property><property name="acquireIncrement">5</property>  </named-config>    <named-config name="mysql"><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql://localhost:3306/hiber</property><property name="user">root</property><property name="password">199358</property><property name="acquireIncrement">5</property><property name="initialPoolSize">10</property><property name="minPoolSize">5</property><property name="maxPoolSize">30</property></named-config></c3p0-config>

测试类

package junit;import java.util.List;import org.junit.Test;import bean.User;import dao.UserDao;import dao.impl.UserDaoImpl_c3p0;public class TestDemo {UserDao userDao=new UserDaoImpl_c3p0();@Testpublic void findById(){User u=userDao.findById(1);System.out.println(u.toString());}@Testpublic void insert() throws Exception{User user=new User(null, "wangchong", "M", 20);userDao.insert(user);}@Testpublic void findAll(){List<User> entityes=userDao.findAll();for (User user : entityes) {System.out.println(user.toString());}}}

查询所有测试成功效果