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

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的介绍就到这了，有什么错误的地方还请包含并指出