基于DBUtils的数据库工具类实现

来源：互联网发布：淘宝客服绩效是什么编辑：程序博客网时间：2024/05/22 09:40

Commonsdbutils是什么？

commons-dbutils 是 Apache 组织提供的一个开源 JDBC 工具类库，对传统操作数据库的类进行二次封装，可以把结果集转化成List。并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。因此dbutils成为很多不喜欢hibernate的公司的首选。

准备

由于使用Oracle数据库和c3p0数据源我们要

导入以下jar包
c3p0-0.9.5.1.jar
commons-dbutils-1.3.jar
ojdbc6.jar
c3p0配置文件
c3p0.jdbcUrl=jdbc:oracle:thin:test/test@localhost:1521:orcl c3p0.driverClass=oracle.jdbc.OracleDriver c3p0.initialPoolSize=6 c3p0.acquireIncrement=6 c3p0.minPoolSize=6
数据库连接工具类JdbcUtils

 #### package com.mav.db;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import javax.sql.DataSource;import com.mchange.v2.c3p0.ComboPooledDataSource;public class JdbcUtils {    /**     * dbUtils 不需要关resultset     * 释放 Connection 连接     * @param connection     */    public static void releaseConnection(Connection connection){        try {            if(connection != null){                connection.close();            }        } catch (Exception e) {            e.printStackTrace();        }    }    private static DataSource ds = new ComboPooledDataSource();    public static Connection getConnection() throws SQLException{           return ds.getConnection();    }    public static void free(ResultSet rs, Statement stmt, Connection conn) {        if (rs != null) {            try {                rs.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (stmt != null) {            try {                stmt.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (conn != null) {            try {                conn.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

####接下来就是编写使用dbutils来进行CURD操作的工具类了

其中dbutils的一些方法的使用大家可以看我写的模仿dbutils的jdbc框架这篇文章
该类使用了泛型 T 代表传入的实体类类型

package com.mav.dao;import java.lang.reflect.ParameterizedType;import java.lang.reflect.Type;import java.sql.Connection;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 com.mav.db.JdbcUtils;/** * 封装了基本的 CRUD 的方法, 以供子类继承使用 * 当前 DAO 直接在方法中获取数据库连接.  * 整个 DAO 采取 DBUtils 解决方案.  * @param <T>: 当前 DAO 处理的实体类的类型是什么 */public class Dao<T> {    private QueryRunner queryRunner = new QueryRunner();    private Class<T> clazz;    public Dao() {        Type superClass = this.getClass().getGenericSuperclass();        if(superClass instanceof ParameterizedType){            ParameterizedType parameterizedType = (ParameterizedType) superClass;            Type[] typeArgs = parameterizedType.getActualTypeArguments();            if(typeArgs != null && typeArgs.length>0){                if(typeArgs[0] instanceof Class){                    clazz = (Class<T>) typeArgs[0];                    System.out.println("dao类中的构造方法泛型类型："+clazz.getName());                }            }        }    }    /**     * 返回某一个字段的值：例如返回某一条记录的 customerName, 或返回数据表中有多少条记录等.      * @param sql     * @param args     * @return     */    public <E> E getForValue(String sql,Object ...args){        Connection conn = null;        try {            conn = JdbcUtils.getConnection();            return (E) queryRunner.query(conn, sql,new ScalarHandler(), args);        } catch (Exception e) {            e.printStackTrace();        }finally{            JdbcUtils.releaseConnection(conn);        }        return null;    }    /**     * 返回 T 所对应的 List      * @param sql     * @param args     * @return     */    public List<T> getForList(String sql,Object ...args){        Connection conn = null;        try {            conn = JdbcUtils.getConnection();            return queryRunner.query(conn, sql,new BeanListHandler<>(clazz), args);        } catch (Exception e) {            e.printStackTrace();        }finally{            JdbcUtils.releaseConnection(conn);        }        return null;    }    /**     * 返回对应的 T 的一个实例类的对象.      * @param sql     * @param args     * @return     */    public T get(String sql ,Object ...args){        Connection conn = null;        try {            conn = JdbcUtils.getConnection();            return queryRunner.query(conn, sql,new BeanHandler<>(clazz), args);        } catch (Exception e) {            e.printStackTrace();        }finally{            JdbcUtils.releaseConnection(conn);        }        return null;    }    /**     * 该方法封装了 INSERT、DELETE、UPDATE 操作.     * @param sql: SQL 语句     * @param args: 填充 SQL 语句的占位符.     */    public void Update(String sql,Object ...args){        Connection conn = null;        try {            conn = JdbcUtils.getConnection();            queryRunner.update(conn, sql, args);        } catch (Exception e) {            e.printStackTrace();        }finally{            JdbcUtils.releaseConnection(conn);        }    }}

以上的代码时可以重复利用的

具体进行数据库操作的接口

package com.mav.dao;import java.util.List;import com.mav.domain.CriteriaCustomer;import com.mav.domain.Customer;public interface CustomerDao {    /**     * 返回满足查询条件的 List     * @param cc: 封装了查询条件     * @return     */    public List<Customer> getForListByCriteriaCustomer(CriteriaCustomer cc);    public List<Customer> getALl();    public void save(Customer customer);    public Customer get(Integer id);    public void delete(Integer id);    /**     * 返回和 name 相等的记录数.      * @param name     * @return     */    public Long getCountWithName(String name);    public void update(Customer customer);}

#### 与数据表对应的实体类

package com.mav.domain;public class Customer {    private Integer id;    private String name;    private String address;    private String phone;    public Customer() {        // TODO Auto-generated constructor stub    }    public Customer(Integer id, String name, String address, String phone) {        super();        this.id = id;        this.name = name;        this.address = address;        this.phone = phone;    }    public Integer getId() {        return id;    }    public String getName() {        return name;    }    public String getAddress() {        return address;    }    public String getPhone() {        return phone;    }    public void setId(Integer id) {        this.id = id;    }    public void setName(String name) {        this.name = name;    }    public void setAddress(String address) {        this.address = address;    }    public void setPhone(String phone) {        this.phone = phone;    }    @Override    public String toString() {        return "Customer [id=" + id + ", name=" + name + ", address=" + address + ", phone=" + phone + "]";    }}

#### CustomerDao 的实现类
实现类CustomerDao 并继承了Dao<>类

package com.mav.dao.impl;import java.math.BigDecimal;import java.util.List;import com.mav.dao.CustomerDao;import com.mav.dao.Dao;import com.mav.domain.CriteriaCustomer;import com.mav.domain.Customer;public class CustomerDaoJdbcImpl extends Dao<Customer> implements CustomerDao {    public List<Customer> getForListByCriteriaCustomer(CriteriaCustomer cc) {        String sql = "select id,name,address,phone from customers where name like ? and address like ? and phone like ?";        //这样写太麻烦了   我们可以修改CriteriaCustomer 类中的get方法来获得          //return getForList(sql,"%"+ cc.getName()+"%","%"+ cc.getAddress()+"%","%"+ cc.getPhone()+"%");        return getForList(sql, cc.getName(),cc.getAddress(),cc.getPhone());    }    @Override    public List<Customer> getALl() {        String sql = "select id,name,address,phone from customers";        return getForList(sql);    }    @Override    public void save(Customer customer) {        String sql = "insert into customers values(null,?,?,?)";        Update(sql,customer.getName(),customer.getAddress(),customer.getPhone());    }    @Override    public Customer get(Integer id) {        String sql ="select id,name,address,phone from customers where id = ?";        return get(sql, id);    }    @Override    public void delete(Integer id) {        String sql = "delete from customers where id = ?";        Update(sql, id);    }    @Override    public Long getCountWithName(String name) {        String sql = "select count(id) from customers where name = ?";        BigDecimal b = getForValue(sql, name);        //BigDecimal 从数据库返回的类型为此 须强转       也可以返回值为 number 类型        return b.longValue();    }    @Override    public void update(Customer customer) {        String sql ="update customers set name = ? , address = ? , phone = ? where id = ?";        Update(sql, customer.getName(),customer.getAddress(),customer.getPhone(),customer.getId());    }}

## ##测试 ##

package com.mav.test;import java.util.List;import org.junit.Test;import com.mav.dao.CustomerDao;import com.mav.dao.impl.CustomerDaoJdbcImpl;import com.mav.domain.Customer;import junit.framework.TestCase;public class CustomerDaoJdbcImplTest extends TestCase {    private  CustomerDao customerDao = new CustomerDaoJdbcImpl();    @Test    public void testAdd(){        Customer customer = new Customer();        customer.setAddress("china");        customer.setName("liming");        customer.setPhone("15088789250");        customerDao.save(customer);    }    @Test    public void testGetList(){        List<Customer> list = customerDao.getALl();        for(Customer c : list){            System.out.println(c);        }    }    /* 输出结果            Customer [id=18, name=update, address=qwewqweq, phone=121343121]            Customer [id=9, name=里面, address=南阳, phone=12121212]            Customer [id=12, name=行云, address=西安, phone=123456]            Customer [id=19, name=zli123, address=beijing, phone=110120119454]            Customer [id=17, name=zhognfd, address=sdfsd, phone=123]            Customer [id=11, name=重视, address=北京, phone=12121212]            */    @Test    public void testGetbyId(){        Customer customer = customerDao.get(3);        System.out.println(customer);    }    @Test    public void testDeletebyId(){     customerDao.delete(3);    }    @Test    public void testgetCountWithName(){        Long count =  customerDao.getCountWithName("222");        System.out.println(count);    }}

关于dbutils的介绍就到这了，有什么错误的地方还请包含并指出

0 0