Jdbc 封裝, 利用反射, 加入連線池

隨著技術的發展, JDBC離我們越來越遠, 上傳一篇以前好玩封裝的jdbc, 建立連線池 獲取連線, 利用反射

package com.xx.loopdata;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
/**
 * DBUtil
 * @author Administrator
 *
 */
public final class DBUtil {
    private final static String DriverName = "com.mysql.jdbc.Driver"; 
    private final static String URL = "jdbc:mysql://127.0.0.1:3306/atom";
    private final static String USER = "root";
    private final static String PASSWORD = "123456";
    //定義一個連結串列代表連線池
    private static LinkedList<Connection> listConn = new LinkedList<>();
    private static Connection conn;
    private static PreparedStatement ps;
    private static ResultSet rs;
    static {
        //初始化驅動
        try {
            Class.forName(DriverName);
            //初始化十個連結
            for (int i = 0; i < 10; i++) {
                listConn.add(DriverManager.getConnection(URL, USER, PASSWORD));
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    /**
     * 獲取得到一客戶端的連線
     * @return
     * @throws SQLException 
     */
    private static Connection getConnection() throws SQLException {
        //返回一個連結給客戶端
        if (listConn.size() > 0) {
            //存在連結
            //真正的物件
            final Connection conn = listConn.removeFirst();
            //生成真正的物件代理，重寫close方法，還給連線池
            Connection  proxyConn = (Connection) Proxy.newProxyInstance(DBUtil.class.getClassLoader(), conn.getClass().getInterfaces(),new InvocationHandler() {
                @Override
                public Object invoke(Object proxy, Method method, Object[] obj) throws Throwable {
                    //重寫close方法
                    if (method.getName().equals("close")) {
                        //將資料庫連線返回給連結串列
                        listConn.add(conn);
                        return null;
                    }
                    else {
                        return method.invoke(conn, obj);
                    }
                }
            });
            return proxyConn;
        }
        else {
            throw new SQLException("系統繁忙，請稍後再試");
        }
    }
    /**
     * 建立一個 PrepareStatement 物件
     * @param sql
     * @param objects
     * @return
     */
    private static PreparedStatement createStatement(String sql,Object[] objects) {
        //先獲取連線
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            //迴圈獲取引數
            for (int i = 0; i < objects.length; i++) {
                ps.setObject(i+1, objects[i]);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            close();
        }
        return ps;
    }
    /**
     * 修改語句
     * @param sql
     * @param objects 
     * @return
     */
    public static int executeUpdate(String sql,Object...objects) {
        ps=createStatement(sql, objects);
        int result = 0;
        try {
            result = ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            close();
        }
        return result;
    }
    /**
     * 查詢語句
     * @param sql
     * @param objects
     * @return
     */
    public static ResultSet executeQuery(String sql,Object...objects) {
        try {
            rs = createStatement(sql, objects).executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            close();
        }
        return rs;
    }
    /**
     * 關閉連線
     * @param conn
     * @param sm
     * @param rs
     */
    private static void close() {
        try {
            rs.close();
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        try {
            ps.close();
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        try {
            conn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}
複製程式碼