连接池及Druid(德鲁伊) 数据库连接池

傷城~ 2024-04-20 11:24 17阅读 0赞

## 连接池及Druid(德鲁伊) 数据库连接池 ##

### 一、数据库连接池 ###

> 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高了数据库操作的性能。
> 
> 连接：创建 close

#### 1.连接池规范 ####

> 连接池各有各的实现方式，所以sun公司定义了一个标准，DataSource

#### 2.使用数据库连接池的原因 ####

> 1.因为创建连接对象和销毁连接对象都必须耗时，数据库连接池优化并调整了创建连接和销毁连接对象
> 
> 2.提高了连接对象的复用率
> 
> 3.控制整个项目的连接对象的个数

import org.junit.Test;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class Test01 {
        
        @Test
        public void test01(){
        
    
            //创建连接池对象
            FastConnectionPool pool = new FastConnectionPool();
    
            //设置参数
            pool.setDriverName("com.mysql.jdbc.Driver");
            pool.setUrl("jdbc:mysql://localhost:3306/2211javaee?useUnicode=true&characterEncoding=utf8");
            pool.setUsername("root");
            pool.setPassword("123456");
            pool.setMaxCapacity(20);
    
            Connection connection = null;
            Statement statement = null;
            ResultSet resultSet = null;
            try {
        
                //获取连接对象
                connection = pool.getConnection();
                //获取发送指令对象
                statement = connection.createStatement();
    
                //发送SQL指令，并返回结果集对象
                String sql = "SELECT * FROM student";
                resultSet = statement.executeQuery(sql);
    
                //遍历结果集
                while (resultSet.next()){
        //判断是否遍历的数据行
    
                    //获取指定字段上的数据
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    String sex = resultSet.getString("sex");
                    int age = resultSet.getInt("age");
                    float salary = resultSet.getFloat("salary");
                    String course = resultSet.getString("course");
    
                    System.out.println(id + "\t" + name + "\t" + sex + "\t" + age + "\t" + salary + "\t" + course);
                }
            } catch (SQLException e) {
        
                e.printStackTrace();
            }finally {
        
                if(resultSet != null){
        
                    try {
        
                        resultSet.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
    
                if(statement != null){
        
                    try {
        
                        statement.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
    
                if(connection != null){
        
                    try {
        
                        connection.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
            }
    
            pool.recovery(connection);
        }
    }

import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.LinkedList;
    
    //自定义连接池
    public class FastConnectionPool {
        
    
        //连接容器
        private LinkedList<Connection> list;
        //驱动地址
        private String driverName;
        //数据库驱动url
        private String url;
        //账号
        private String username;
        //密码
        private String password;
        //设置最大连接数
        private int maxCapacity;
    
    
        public void setDriverName(String driverName) {
        
            this.driverName = driverName;
        }
    
        public void setUrl(String url) {
        
            this.url = url;
        }
    
        public void setUsername(String username) {
        
            this.username = username;
        }
    
        public void setPassword(String password) {
        
            this.password = password;
        }
    
        public void setMaxCapacity(int maxCapacity) {
        
            this.maxCapacity = maxCapacity;
        }
    
        //初始化数据库连接池
        private  void init() throws SQLException {
        
    
            //初始化数据库连接池的容器
            list = new LinkedList<>();
    
            try {
        
                //导入驱动包
                Class.forName(driverName);
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            }
    
            //循环创建数据库连接对象，并将对象存入list容器中
            for (int i = 0; i < maxCapacity ; i++) {
        
                Connection connection = DriverManager.getConnection(url, username, password);
                list.add(connection);
            }
        }
        public Connection getConnection() throws SQLException {
        
            if(list == null){
        
                init();
            }
            if(list.isEmpty()){
        //连接容器中没有对象
                //解决方案1：返回null
    //            return null;
                //解决方法2：等待
                try {
        
                   Thread.sleep(100);
               } catch (InterruptedException e) {
        
                   e.printStackTrace();
              }
            }
            Connection connection = list.removeFirst();
            return connection;
        }
    
        //回收连接对象
        public void recovery(Connection connection){
        
            list.add(connection);
        }
    }

#连接文本地址
        
    driverName=com.mysql.jdbc.Driver
    url=jdbc:mysql://localhost:3306/2211javaee?useUnicode=true&characterEncoding=utf8
    username=root
    password=123456
    maxCapacity = 20

### 二、自定义连接池 – 符合JDBC的规范（DataSourse） ###

import org.junit.Test;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class Test01 {
        
        @Test
        public void test01(){
        
    
            //创建连接池对象
            FastConnectionPool pool = new FastConnectionPool();
    
            //设置参数
            pool.setDriverName("com.mysql.jdbc.Driver");
            pool.setUrl("jdbc:mysql://localhost:3306/2211javaee?useUnicode=true&characterEncoding=utf8");
            pool.setUsername("root");
            pool.setPassword("123456");
            pool.setMaxCapacity(20);
    
            Connection connection = null;
            Statement statement = null;
            ResultSet resultSet = null;
            try {
        
                //获取连接对象
                connection = pool.getConnection();
                //获取发送指令对象
                statement = connection.createStatement();
    
                //发送SQL指令，并返回结果集对象
                String sql = "SELECT * FROM student";
                resultSet = statement.executeQuery(sql);
    
                //遍历结果集
                while (resultSet.next()){
        //判断是否遍历的数据行
    
                    //获取指定字段上的数据
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    String sex = resultSet.getString("sex");
                    int age = resultSet.getInt("age");
                    float salary = resultSet.getFloat("salary");
                    String course = resultSet.getString("course");
    
                    System.out.println(id + "\t" + name + "\t" + sex + "\t" + age + "\t" + salary + "\t" + course);
                }
            } catch (SQLException e) {
        
                e.printStackTrace();
            }finally {
        
                if (resultSet != null) {
        
                    try {
        
                        resultSet.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
    
                if (statement != null) {
        
                    try {
        
                        statement.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
    
                if (connection != null) {
        
                    try {
        
                        connection.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
    
                if (resultSet != null){
        
                    try {
        
                        resultSet.close();
                    } catch (SQLException e) {
        
                        e.printStackTrace();
                    }
                }
            }
        }
    }

import javax.sql.DataSource;
    import java.io.PrintWriter;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.SQLFeatureNotSupportedException;
    import java.util.LinkedList;
    import java.util.logging.Logger;
    
    //自定义连接池
    public class FastConnectionPool implements DataSource {
        
    
        //连接容器
        private LinkedList<ConnectionWapper> list;
        //驱动地址
        private String driverName;
        //数据库驱动url
        private String url;
        //账号
        private String username;
        //密码
        private String password;
        //设置最大连接数
        private int maxCapacity;
    
    
        public void setDriverName(String driverName) {
        
            this.driverName = driverName;
        }
    
        public void setUrl(String url) {
        
            this.url = url;
        }
    
        public void setUsername(String username) {
        
            this.username = username;
        }
    
        public void setPassword(String password) {
        
            this.password = password;
        }
    
        public void setMaxCapacity(int maxCapacity) {
        
            this.maxCapacity = maxCapacity;
        }
    
        //初始化数据库连接池
        private  void init() throws SQLException {
        
    
            //初始化数据库连接池的容器
            list = new LinkedList<>();
    
            try {
        
                //导入驱动包
                Class.forName(driverName);
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            }
    
            //循环创建数据库连接对象，并将对象存入list容器中
            for (int i = 0; i < maxCapacity ; i++) {
        
                Connection connection = DriverManager.getConnection(url, username, password);
                ConnectionWapper connectionWapper = new ConnectionWapper(connection,list);
                list.add(connectionWapper);
            }
        }
    
        @Override
        public Connection getConnection() throws SQLException {
        
            if(list == null){
        
                init();
            }
            Connection connectionWapper = list.removeFirst();
            return connectionWapper;
        }
    
        @Override
        public Connection getConnection(String username, String password) throws SQLException {
        
            return null;
        }
    
        @Override
        public <T> T unwrap(Class<T> iface) throws SQLException {
        
            return null;
        }
    
        @Override
        public boolean isWrapperFor(Class<?> iface) throws SQLException {
        
            return false;
        }
    
        @Override
        public PrintWriter getLogWriter() throws SQLException {
        
            return null;
        }
    
        @Override
        public void setLogWriter(PrintWriter out) throws SQLException {
        
    
        }
    
        @Override
        public void setLoginTimeout(int seconds) throws SQLException {
        
    
        }
    
        @Override
        public int getLoginTimeout() throws SQLException {
        
            return 0;
        }
    
        @Override
        public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        
            return null;
        }
    }

import java.sql.*;
    import java.util.LinkedList;
    import java.util.Map;
    import java.util.Properties;
    import java.util.concurrent.Executor;
    
    public class ConnectionWapper implements Connection {
        
    
       private Connection connection;
       private LinkedList<ConnectionWapper> list;
    
        public ConnectionWapper(Connection connection, LinkedList<ConnectionWapper> list) {
        
            this.connection = connection;
            this.list = list;
        }
    
        @Override
        public Statement createStatement() throws SQLException {
        
            return connection.createStatement();
        }
    
        @Override
        public PreparedStatement prepareStatement(String sql) throws SQLException {
        
            return connection.prepareStatement(sql);
        }
    
        @Override
        public CallableStatement prepareCall(String sql) throws SQLException {
        
            return connection.prepareCall(sql);
        }
    
        @Override
        public String nativeSQL(String sql) throws SQLException {
        
            return connection.nativeSQL(sql);
        }
    
        @Override
        public void setAutoCommit(boolean autoCommit) throws SQLException {
        
            connection.setAutoCommit(autoCommit);
        }
    
        @Override
        public boolean getAutoCommit() throws SQLException {
        
            return connection.getAutoCommit();
        }
    
        @Override
        public void commit() throws SQLException {
        
                connection.commit();
        }
    
        @Override
        public void rollback() throws SQLException {
        
            connection.rollback();
        }
    
        @Override
        public void close() throws SQLException {
        
            System.out.println("回收Connedtion");
            list.add(this);
        }
    
        @Override
        public boolean isClosed() throws SQLException {
        
            return connection.isClosed();
        }
    
        @Override
        public DatabaseMetaData getMetaData() throws SQLException {
        
            return connection.getMetaData();
        }
    
        @Override
        public void setReadOnly(boolean readOnly) throws SQLException {
        
            connection.setReadOnly(readOnly);
        }
    
        @Override
        public boolean isReadOnly() throws SQLException {
        
            return false;
        }
    
        @Override
        public void setCatalog(String catalog) throws SQLException {
        
    
        }
    
        @Override
        public String getCatalog() throws SQLException {
        
            return null;
        }
    
        @Override
        public void setTransactionIsolation(int level) throws SQLException {
        
    
        }
    
        @Override
        public int getTransactionIsolation() throws SQLException {
        
            return 0;
        }
    
        @Override
        public SQLWarning getWarnings() throws SQLException {
        
            return null;
        }
    
        @Override
        public void clearWarnings() throws SQLException {
        
    
        }
    
        @Override
        public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
        
            return null;
        }
    
        @Override
        public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        
            return null;
        }
    
        @Override
        public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        
            return null;
        }
    
        @Override
        public Map<String, Class<?>> getTypeMap() throws SQLException {
        
            return null;
        }
    
        @Override
        public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
        
    
        }
    
        @Override
        public void setHoldability(int holdability) throws SQLException {
        
    
        }
    
        @Override
        public int getHoldability() throws SQLException {
        
            return 0;
        }
    
        @Override
        public Savepoint setSavepoint() throws SQLException {
        
            return null;
        }
    
        @Override
        public Savepoint setSavepoint(String name) throws SQLException {
        
            return null;
        }
    
        @Override
        public void rollback(Savepoint savepoint) throws SQLException {
        
    
        }
    
        @Override
        public void releaseSavepoint(Savepoint savepoint) throws SQLException {
        
    
        }
    
        @Override
        public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        
            return null;
        }
    
        @Override
        public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        
            return null;
        }
    
        @Override
        public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        
            return null;
        }
    
        @Override
        public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
        
            return null;
        }
    
        @Override
        public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
        
            return null;
        }
    
        @Override
        public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
        
            return null;
        }
    
        @Override
        public Clob createClob() throws SQLException {
        
            return null;
        }
    
        @Override
        public Blob createBlob() throws SQLException {
        
            return null;
        }
    
        @Override
        public NClob createNClob() throws SQLException {
        
            return null;
        }
    
        @Override
        public SQLXML createSQLXML() throws SQLException {
        
            return null;
        }
    
        @Override
        public boolean isValid(int timeout) throws SQLException {
        
            return false;
        }
    
        @Override
        public void setClientInfo(String name, String value) throws SQLClientInfoException {
        
    
        }
    
        @Override
        public void setClientInfo(Properties properties) throws SQLClientInfoException {
        
    
        }
    
        @Override
        public String getClientInfo(String name) throws SQLException {
        
            return null;
        }
    
        @Override
        public Properties getClientInfo() throws SQLException {
        
            return null;
        }
    
        @Override
        public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
        
            return null;
        }
    
        @Override
        public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
        
            return null;
        }
    
        @Override
        public void setSchema(String schema) throws SQLException {
        
    
        }
    
        @Override
        public String getSchema() throws SQLException {
        
            return null;
        }
    
        @Override
        public void abort(Executor executor) throws SQLException {
        
    
        }
    
        @Override
        public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
        
    
        }
    
        @Override
        public int getNetworkTimeout() throws SQLException {
        
            return 0;
        }
    
        @Override
        public <T> T unwrap(Class<T> iface) throws SQLException {
        
            return null;
        }
    
        @Override
        public boolean isWrapperFor(Class<?> iface) throws SQLException {
        
            return false;
        }
    }

### 三、自定义连接池 – 结合DBUtil去使用 ###

import org.junit.Test;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.List;
    
    public class Test01 {
        
        @Test
        public void test01(){
        
    
                String sql = "SELECT * FROM student";
    
            List<Student> stuList = DBUtil.commonQuery(Student.class, sql);
            for (Student stu: stuList) {
        
                System.out.println(stu);
            }
        }
    }

import java.io.IOException;
    import java.lang.reflect.Field;
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Properties;
    
    //数据库工具类
    public class DBUtil {
        
    
        private static FastConnectionPool pool;
    
        static{
        
            //获取配置文件对象
            Properties properties = new Properties();
            try {
        
                //加载配置文件
                properties.load(DBUtil.class.getClassLoader().getResourceAsStream("DBConfig.properties"));
            } catch (IOException e) {
        
                e.printStackTrace();
            }
            String driverName = properties.getProperty("driverName");
            String url = properties.getProperty("url");
            String username = properties.getProperty("username");
            String password = properties.getProperty("password");
            int maxCapacity = Integer.parseInt(properties.getProperty("maxCapacity"));
    
            //创建连接池对象
            pool = new FastConnectionPool();
            //设置参数
            pool.setDriverName(driverName);
            pool.setUrl(url);
            pool.setUsername(username);
            pool.setPassword(password);
            pool.setMaxCapacity(maxCapacity);
    
        }
    
        private static ThreadLocal<Connection> local = new ThreadLocal<>();
    
        //开启事务
        public static void startTransaction() throws SQLException {
        
            Connection connection = getConnection();
            connection.setAutoCommit(false);
        }
    
        //提交事务
        public static void commit() throws SQLException {
        
            Connection connection = local.get();
            if(connection != null){
        
                connection.commit();
                connection.close();
                local.set(null);
            }
        }
    
        //回滚事务
        public static void rollback() throws SQLException {
        
            Connection connection = local.get();
            if(connection != null){
        
                connection.rollback();
                connection.close();
                local.set(null);
            }
        }
    
        //获取连接对象
        public static Connection getConnection() throws SQLException {
        
            Connection connection = local.get();
            if (connection == null) {
        
                //获取连接池中的连接对象
                connection = pool.getConnection();
    
                //设置隔离级别
                connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
    
                local.set(connection);
            }
            return connection;
        }
    
        //关闭资源
        public static void close(Connection connection, Statement statement, ResultSet resultSet){
        
            if(resultSet != null){
        
                try {
        
                    resultSet.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                  }
            }
            if(statement != null){
        
                try {
        
                    statement.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if(connection != null){
        
                try {
        
                    if(connection.getAutoCommit()){
        //没有开启事务，MySQL自动管理提交
                        connection.close();
                        local.set(null);
                    }
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
        }
    
        //sql -- insert into xxx(username,name,password,salary,age) values(?,?,?,?,?)
        //参数
    
        //更新数据 -- （添加、删除、修改）
        public static int commonUpdate(String sql,Object... params){
        
    
            Connection connection = null;
            PreparedStatement statement = null;
            try {
        
                connection = getConnection();
                statement = connection.prepareStatement(sql);
                setParameter(statement,params);
                int num = statement.executeUpdate();
                return num;
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
               close(connection,statement,null);
            }
            return -1;
        }
    
        //主键回填
        public static int commonInsert(String sql,Object... params){
        
            Connection connection = null;
            PreparedStatement statement = null;
            try {
        
                connection = getConnection();
                statement = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
                setParameter(statement,params);
                statement.executeUpdate();
    
                //获取主键
                ResultSet resultSet = statement.getGeneratedKeys();
                if(resultSet.next()){
        
                    int primaryKey = resultSet.getInt(1);
                    return primaryKey;
                }
    
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
                close(connection,statement,null);
            }
            return -1;
        }
    
        //select * from user where id < 4;
    
        //查询数据
        public static <T> List<T> commonQuery(Class<T> clazz,String sql,Object... params){
        
    
            Connection connection = null;
            PreparedStatement statement = null;
            ResultSet resultSet = null;
            try {
        
                connection = getConnection();
                statement = connection.prepareStatement(sql);
                setParameter(statement,params);
    
                //获取表信息
                ResultSetMetaData metaData = statement.getMetaData();
                //获取表中字段的个数
                int columnCount = metaData.getColumnCount();
    
                //创建集合
                List<T> list = new ArrayList<>();
    
                resultSet = statement.executeQuery();
                while(resultSet.next()){
        //1 叶涛 男 22 12000 java 00000000
    
                    //利用反射创建实体类对象
                    T obj = clazz.newInstance();
    
                    //循环获取字段名，并利用反射机制添加到实体类对象中
                    for (int i = 1; i <= columnCount; i++) {
        
                        //字段名
                        String columnName = metaData.getColumnName(i);
                        //获取字段名对应的值
                        Object columnValue = resultSet.getObject(columnName);
    
                        //获取属性对象
                        Field field = getField(clazz, columnName);
                        if(field != null){
        
                            field.setAccessible(true);
                            field.set(obj,columnValue);
                        }
                    }
    
                    list.add(obj);
                }
                return list;
            } catch (SQLException e) {
        
                e.printStackTrace();
            } catch (IllegalAccessException e) {
        
                e.printStackTrace();
            } catch (InstantiationException e) {
        
                e.printStackTrace();
            } finally {
        
                close(connection,statement,resultSet);
            }
            return null;
        }
    
        //设置sql参数的方法
        private static void setParameter(PreparedStatement statement,Object... params) throws SQLException {
        
            for (int i = 0; i < params.length; i++) {
        
                statement.setObject(i+1,params[i]);
            }
        }
    
        //利用反射获取实体类中的属性对象
        private static Field getField(Class<?> clazz,String fieldName){
        
    
            for(Class<?> c = clazz;c != null;c=c.getSuperclass()){
        
                try {
        
                    Field field = c.getDeclaredField(fieldName);
                    return field;
                } catch (NoSuchFieldException e) {
        
                }
            }
            return null;
        }
    }

public class Student {
        
    
        private int id;
        private String name;
        private String sex;
        private  int age;
        private float salary;
        private String course;
        private String password;
    
        public Student() {
        
        }
    
        public Student(int id, String name, String sex, int age, float salary, String course, String password) {
        
            this.id = id;
            this.name = name;
            this.sex = sex;
            this.age = age;
            this.salary = salary;
            this.course = course;
            this.password = password;
        }
    
        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 String getSex() {
        
            return sex;
        }
    
        public void setSex(String sex) {
        
            this.sex = sex;
        }
    
        public int getAge() {
        
            return age;
        }
    
        public void setAge(int age) {
        
            this.age = age;
        }
    
        public float getSalary() {
        
            return salary;
        }
    
        public void setSalary(float salary) {
        
            this.salary = salary;
        }
    
        public String getCourse() {
        
            return course;
        }
    
        public void setCourse(String course) {
        
            this.course = course;
        }
    
        public String getPassword() {
        
            return password;
        }
    
        public void setPassword(String password) {
        
            this.password = password;
        }
    
        @Override
        public String toString() {
        
            return "Student{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", sex='" + sex + '\'' +
                    ", age=" + age +
                    ", salary=" + salary +
                    ", course='" + course + '\'' +
                    ", password='" + password + '\'' +
                    '}';
        }
    }

### 四、Druid连接池 ###

#### 1.开源数据库连接池 ####

> 通常我们把DataSource的实现，按其英文含义称之为数据源，数据源中都包含了数据库连接池的实现。也有一些开源组织提供了数据源的独立实现：  
> DBCP 数据库连接池  
> C3P0 数据库连接池  
> Druid(德鲁伊) 数据库连接池  
> 在使用了数据库连接池之后，在项目的实际开发中就不需要编写连接数据库的代码了，直接从数据源获得数据库的连接。

#### 2.Druid(德鲁伊) 数据库连接池 ####

> Druid 是目前比较流行的高性能的，它有如下几个特点：  
> 一. 亚秒级查询  
> druid提供了快速的聚合能力以及亚秒级的查询能力，多租户的设计，是面向用户分析应用的理想方式。
> 
> 二.实时数据注入  
> druid支持流数据的注入，并提供了数据的事件驱动，保证在实时和离线环境下事件的实效性和统一性
> 
> 三.可扩展的PB级存储  
> druid集群可以很方便的扩容到PB的数据量，每秒百万级别的数据注入。即便在加大数据规模的情况下，也能保证时其效性
> 
> 四.多环境部署  
> druid既可以运行在商业的硬件上，也可以运行在云上。它可以从多种数据系统中注入数据，包括hadoop，spark，kafka，storm和samza等
> 
> 五.丰富的社区  
> druid拥有丰富的社区，供大家学习

#### 3.Druid连接池的使用 ####

import org.junit.Test;
    
    import java.util.List;
    
    public class Test01 {
        
    
        @Test
        public void test01(){
        
    
            String sql = "SELECT * FROM student";
            List<Student> stuList = DBUtil.commonQuery(Student.class,sql);
            for (Student stu: stuList) {
        
                System.out.println(stu);
            }
        }
    }

import com.alibaba.druid.pool.DruidDataSource;
    
    import java.io.IOException;
    import java.lang.reflect.Field;
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Properties;
    
    //数据库工具类
    public class DBUtil {
        
    
        private static DruidDataSource dataSource;
    
        static{
        
            //获取配置文件对象
            Properties properties = new Properties();
            try {
        
                //加载配置文件
                properties.load(DBUtil.class.getClassLoader().getResourceAsStream("DBConfig.properties"));
            } catch (IOException e) {
        
                e.printStackTrace();
            }
            String driverName = properties.getProperty("driverName");
            String url = properties.getProperty("url");
            String username = properties.getProperty("username");
            String password = properties.getProperty("password");
            int maxCapacity = Integer.parseInt(properties.getProperty("maxCapacity"));
    
            //创建连接池对象
            dataSource = new DruidDataSource();
            //设置参数
            dataSource.setDriverClassName(driverName);
            dataSource.setUrl(url);
            dataSource.setUsername(username);
            dataSource.setPassword(password);
            dataSource.setMaxActive(maxCapacity);
    
        }
    
        private static ThreadLocal<Connection> local = new ThreadLocal<>();
    
        //开启事务
        public static void startTransaction() throws SQLException {
        
            Connection connection = getConnection();
            connection.setAutoCommit(false);
        }
    
        //提交事务
        public static void commit() throws SQLException {
        
            Connection connection = local.get();
            if(connection != null){
        
                connection.commit();
                connection.close();
                local.set(null);
            }
        }
    
        //回滚事务
        public static void rollback() throws SQLException {
        
            Connection connection = local.get();
            if(connection != null){
        
                connection.rollback();
                connection.close();
                local.set(null);
            }
        }
    
        //获取连接对象
        public static Connection getConnection() throws SQLException {
        
            Connection connection = local.get();
            if (connection == null) {
        
                //获取连接池中的连接对象
                connection = dataSource.getConnection();
    
                //设置隔离级别
                connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
    
                local.set(connection);
            }
            return connection;
        }
    
        //关闭资源
        public static void close(Connection connection, Statement statement, ResultSet resultSet){
        
            if(resultSet != null){
        
                try {
        
                    resultSet.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if(statement != null){
        
                try {
        
                    statement.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if(connection != null){
        
                try {
        
                    if(connection.getAutoCommit()){
        //没有开启事务，MySQL自动管理提交
                        connection.close();
                        local.set(null);
                    }
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
        }
    
        //sql -- insert into xxx(username,name,password,salary,age) values(?,?,?,?,?)
        //参数
    
        //更新数据 -- （添加、删除、修改）
        public static int commonUpdate(String sql,Object... params){
        
    
            Connection connection = null;
            PreparedStatement statement = null;
            try {
        
                connection = getConnection();
                statement = connection.prepareStatement(sql);
                setParameter(statement,params);
                int num = statement.executeUpdate();
                return num;
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
               close(connection,statement,null);
            }
            return -1;
        }
        
        //设置sql参数的方法
        private static void setParameter(PreparedStatement statement,Object... params) throws SQLException {
        
            for (int i = 0; i < params.length; i++) {
        
                statement.setObject(i+1,params[i]);
            }
        }
        
        //主键回填
        public static int commonInsert(String sql,Object... params){
        
            Connection connection = null;
            PreparedStatement statement = null;
            try {
        
                connection = getConnection();
                statement = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
                setParameter(statement,params);
                statement.executeUpdate();
    
                //获取主键
                ResultSet resultSet = statement.getGeneratedKeys();
                if(resultSet.next()){
        
                    int primaryKey = resultSet.getInt(1);
                    return primaryKey;
                }
    
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
                close(connection,statement,null);
            }
            return -1;
        }
    
        //select * from user where id < 4;
    
        //查询数据
        public static <T> List<T> commonQuery(Class<T> clazz,String sql,Object... params){
        
    
            Connection connection = null;
            PreparedStatement statement = null;
            ResultSet resultSet = null;
            try {
        
                connection = getConnection();
                statement = connection.prepareStatement(sql);
                setParameter(statement,params);
    
                //获取表信息
                ResultSetMetaData metaData = statement.getMetaData();
                //获取表中字段的个数
                int columnCount = metaData.getColumnCount();
    
                //创建集合
                List<T> list = new ArrayList<>();
    
                resultSet = statement.executeQuery();
                while(resultSet.next()){
        
    
                    //利用反射创建实体类对象
                    T obj = clazz.newInstance();
    
                    //循环获取字段名，并利用反射机制添加到实体类对象中
                    for (int i = 1; i <= columnCount; i++) {
        
                        //字段名
                        String columnName = metaData.getColumnName(i);
                        //获取字段名对应的值
                        Object columnValue = resultSet.getObject(columnName);
    
                        //获取属性对象
                        Field field = getField(clazz, columnName);
                        if(field != null){
        
                            field.setAccessible(true);
                            field.set(obj,columnValue);
                        }
                    }
    
                    list.add(obj);
                }
                return list;
            } catch (SQLException e) {
        
                e.printStackTrace();
            } catch (IllegalAccessException e) {
        
                e.printStackTrace();
            } catch (InstantiationException e) {
        
                e.printStackTrace();
            } finally {
        
                close(connection,statement,resultSet);
            }
            return null;
        }
        
        //利用反射获取实体类中的属性对象
        private static Field getField(Class<?> clazz,String fieldName){
        
    
            for(Class<?> c = clazz;c != null;c=c.getSuperclass()){
        
                try {
        
                    Field field = c.getDeclaredField(fieldName);
                    return field;
                } catch (NoSuchFieldException e) {
        
                }
            }
            return null;
        }
    }