JDBC-数据库连接池实现&BaseDao封装

连接池

连接池原理连接池代码实现baseDao封装

①某次从集合中获取Connection对象时，集合为空，那么就创建一个新的连接对象,并返回；
②为了避免无限制的创建Connection对象，降低内存消耗，可以设置集合中能够存储的连接数上限值；
③如果超过连接池规定的Connection数量上限，就释放掉多余的连接对象。
※ 在框架阶段的实际开发中，可以使用例如：C3P0、Druid、DBCP等第三方数据库连接池框架实现连接池操作。

package com.xwd.dao;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.linkedList;


public class ConnectionPool {
    //properties
    private static final String driver="com.mysql.cj.jdbc.Driver";
    private static final String url="jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT&useServerPrepStmts=true&cachePrepStmts=true&rewriteBatchedStatements=true";
    private static final String user="root";
    private static final String password="root";
    //存储线程池对象
    private static linkedList pools;
    private static int initSize = 5;
    private static int maxSize = 10;

    //load Driver
    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println(driver+" load failed!");
        }
        //初始化pools
        pools=new linkedList<>();
        //创建initSize个初始Connection对象
        for (int i = 0; i < initSize; i++) {
            Connection connection = initConnection();
            pools.addFirst(connection);
        }
    }

    //methods

    
    private static Connection initConnection(){
        try {
            Connection connection = DriverManager.getConnection(url, user, password);
            System.out.println("init Connection - "+connection.hashCode()+" which was pushed into pools");
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    
    public static Connection getConnection(){
        Connection connection=null;
        if (pools.size()>0)
            connection = pools.removeLast();//移除最后一个元素
        else
            connection = initConnection();//如果为空，则创建新的Connection对象
        return connection;
    }

    
    public static void returnConnection(Connection connection){
        if (connection!=null) {
            try{
                //若Connection连接对象未被close
                if (!connection.isClosed()){
                    try {
                        //修改事务处理模式为——自动提交模式
                        connection.setAutoCommit(true);
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    //归还Connection对象
                    if (pools.size() 
baseDao封装 
package com.xwd.dao;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class baseDao {
    //methods

    
    public List baseQuery(Class clazz,String sql,Object... args){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        List list=null;
        //数据库操作
        try {
            //通过数据库连接池-获取Connectio连接
            connection = ConnectionPool.getConnection();
            //获取Statement
            statement = connection.prepareStatement(sql);
            //参数设置
            for (int i = 0; i < args.length; i++) {
                statement.setObject(i+1,args[i]);
            }
            //执行CURD操作，获取ResultSet
            resultSet = statement.executeQuery();
            //实例化List
            list=new ArrayList<>();
            //利用反射获取字段
            Field[] declaredFields = clazz.getDeclaredFields();
            //解析ResultSet
            while (resultSet.next()) {
                Object object=clazz.newInstance();
                for (Field declaredField : declaredFields) {
                    if (Modifier.isStatic(declaredField.getModifiers()))
                        continue;
                    declaredField.setAccessible(true);
                    String fieldName = declaredField.getName();
                    //通过反射获取setter方法
                    String setMethodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Method setMethod = clazz.getDeclaredMethod(setMethodName, declaredField.getType());
                    //调用setter方法
                    setMethod.invoke(object,resultSet.getObject(fieldName));
                    declaredField.setAccessible(false);
                }
                //添加到列表
                list.add(object);
            }
            return list;
        } catch (SQLException | InvocationTargetException | NoSuchMethodException | IllegalAccessException | InstantiationException e) {
            e.printStackTrace();
            return list;
        } finally {
            if (statement!=null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            //归还数据库连接对象
            ConnectionPool.returnConnection(connection);
        }
    }


    
    public int baseUpdateBook(String sql,Object ...args){
        Connection connection=null;
        PreparedStatement statement=null;
        int rows=0;
        try{
            connection = ConnectionPool.getConnection();
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                statement.setObject(i+1,args[i]);
            }
            rows = statement.executeUpdate();
            return rows;
        } catch (SQLException e) {
            e.printStackTrace();
            return rows;
        } finally {
            if (statement!=null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            ConnectionPool.returnConnection(connection);
        }
    }
}