模仿Common DBUtils的功能实现自定义的DBUtils

①导入需要的jar包和配置文件

②辅助类

package com.yxfyg.util;

import java.sql.Connection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JDBCUtil {
    
    private static ComboPooledDataSource dataSource = null;
    
    static {
        dataSource = new ComboPooledDataSource();
    }
    
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    public static ComboPooledDataSource getDataSource() {
        return dataSource;
    }
    
    public static void release(ResultSet rs,Statement st,Connection conn) {
        closeRs(rs);
        closeSt(st);
        closeConn(conn);
    }
    
    public static void release(Statement st,Connection conn) {
        closeSt(st);
        closeConn(conn);
    }
    
    private static void closeRs(ResultSet rs) {
        try {
            if(rs != null) {
                rs.close();
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            rs = null;
        }
    }
    
    private static void closeSt(Statement st) {
        try {
            if(st != null) {
                st.close();
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            st = null;
        }
    }
    
    private static void closeConn(Connection conn) {
        try {
            if(conn != null) {
                conn.close();
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            conn = null;
        }
    }
}

package com.yxfyg.bean;

public class Account {
    
    private int id;
    
    private String name;
    
    private int money;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getMoney() {
        return money;
    }

    public void setMoney(int money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account [id=" + id + ", name=" + name + ", money=" + money + "]";
    }
        
}

③自定义的DBUtils和需要使用者进行实现的接口

package com.yxfyg.dbutils;

import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.yxfyg.util.JDBCUtil;

public class MyDBUtils {

    private DataSource dataSource;
    
    public MyDBUtils(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    // 增、删、改
    public int update(String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        int update = 0;
        try {
            // 获取连接
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            // 确定sql语句中问号的个数
            ParameterMetaData data = ps.getParameterMetaData();
            int count = data.getParameterCount();
            for (int i = 0; i < count; i++) {
                ps.setObject(i + 1, args[i]);
            }
            update = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(ps, conn);
        }
        return update;
    }

    public <T> T query(String sql, ResultSetHandler<T> handler, Object... args) {

        // 获取连接
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        T t = null;
        try {
            conn = dataSource.getConnection();
            ps = conn.prepareStatement(sql);
            // 确定sql语句中问号的个数
            ParameterMetaData data = ps.getParameterMetaData();
            int count = data.getParameterCount();
            for (int i = 0; i < count; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            
            //将结果集交给由使用者实现的类ResultSetHandler的对象处理
            t = handler.handle(rs);
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs , ps, conn);
        }
        return t;
    }

}

package com.yxfyg.dbutils;

import java.sql.ResultSet;

public interface ResultSetHandler<T> {
    
    T handle(ResultSet rs);
    
}

④使用

package com.yxfyg.test;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import com.yxfyg.bean.Account;
import com.yxfyg.dbutils.MyDBUtils;
import com.yxfyg.dbutils.ResultSetHandler;
import com.yxfyg.util.JDBCUtil;

public class MainTest {

    @Test
    public void testUpdate() {

        MyDBUtils myUtils = new MyDBUtils(JDBCUtil.getDataSource());

        // 增、删、改都使用update方法，查使用query方法
        // 插入一条数据
        myUtils.update("insert into account values(null,?,?)","Lucy",2000);

        // 修改一条数据
        myUtils.update("update account set money = money - ? where name = ?", 300, "Lucy");

        // 删除一条数据
        myUtils.update("delete from account where name = ?","Lucy");

    }

    @Test
    public void testQuery() {
        
        MyDBUtils myUtils = new MyDBUtils(JDBCUtil.getDataSource());
        
        // 查询一条数据(使用匿名内部类，自己实现方法)
        Account account = myUtils.query("select * from account where name = ?", new ResultSetHandler<Account>() {

            @Override
            public Account handle(ResultSet rs) {
                Account account = new Account();
                try {
                    while (rs.next()) {
                        account.setId(rs.getInt("id"));
                        account.setName(rs.getString("name"));
                        account.setMoney(rs.getInt("money"));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return account;
            }
        }, "Jack");

        System.out.println(account.toString());
    }

}