数据库连接池，queryRunner

JDBC（数据库连接池，queryRunner）

一，数据库连接池

Connection是一个稀有资源，一个连接就创造了一个资源

同时在线人很多，服务器可能崩溃

1.设置只有自己

2.服务器人数限制，设置排队

 

JDBC使用连接池的必要性：

在使用基于wed程序的数据库连接，

1.在主程序中建立连接

2.执行sql

3.断开连接

 

所有的JDBC连接通过DriverManager.getConnection

用完的连接不要被垃圾回收，能够重复使用

“池化思想”

 

每次初始化一个连接池，连接池中会有很多个连接等待被使用

每次用完连接后，不需要关闭连接，只需要把连接还给连接池

还给连接池的操作不需要我们手动控制

 

1.C3P0，2代数据库连接池

2.DBCP，2代数据库连接池

3.Druid（德鲁伊）数据库连接池，最好用的连接池

  阿里巴巴开源平台上的数据库连接池实现，整合了C3P0和DBCP各自的优点

  加入了日志监控，可以监控sql语句的执行情况

4.Hikari（光），目前最快的连接池，springboot默认的连接池

 

    /**
     * 德鲁伊
     * .properties
     * 约定 > 配置 > 编码
     */
    public static void main(String[] args) throws IOException, SQLException {
        Properties properties = new Properties();
        properties.load(TestSqlChi.class.getClassLoader().getResourceAsStream("druid.properties"));

        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.configFromPropety(properties);

        System.out.println(druidDataSource.getConnection());
        System.out.println(druidDataSource.getCreateCount());

    }


    /**
     * Hikari
     */
    @Test
    public void test() throws IOException, SQLException {
        Properties properties = new Properties();
        properties.load(TestSqlChi.class.getClassLoader().getResourceAsStream("hikari.properties"));

        HikariConfig hikariConfig = new HikariConfig(properties);
        HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);

        System.out.println(hikariDataSource.getConnection());

    }
druid.url=jdbc:mysql://127.0.0.1:3306/jiruan?useUnicode=true&characterEncoding=utf8
druid.username=root
druid.password=123
druid.driverName=com.mysql.jdbc.Driver

druid.initialSize=10
druid.maxActive=20
druid.maxWait=20
jdbcUrl=jdbc:mysql://127.0.0.1:3306/jiruan?useUnicode=true&characterEncoding=utf8
username=root
password=123
driverClassName=com.mysql.jdbc.Driver

 

二，简易框架

1.定义接口

查看代码 
 package jdbc.jianyikuangjia;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;

/**
 * 约束
 * 约定
 */
public interface IBaseDao<T> {

    /**
     * 获取连接的方法
     */
    Connection getConnection();

    /**
     * 关闭资源
     */
    void closeAll(Statement statement, ResultSet resultSet);

    /**
     * 通用的保存
     */
    void save(Object object);

    /**
     * 通用的查询所有
     */
    List<T> findAll(Class clazz);

    /**
     * 通用的更新的方法
     */
    void update(Object obj,String fieldName,Object fieldValue);

    /**
     * 通用的删除
     */
    void delete(Class clazz,String fieldName,Object fieldValue);


    /**
     * 查询单条数据
     */
    T findOne(Class clazz,String fieldName,Object fieldValue);
}

 

2.接口实现类

查看代码 
 package jdbc.jianyikuangjia;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.*;

/**
 * 约定：
 * 1、表名和类名必须相同
 * 2、表的字段名和类的属性名必须相同
 *
 * @param <T> 泛型是要操作的类
 */

public class BaseDaoImpl<T> implements IBaseDao<T> {

    private static final DataSource DATA_SOURCE;

    static {
        Properties properties = new Properties();
        try {
            properties.load(BaseDaoImpl.class.getClassLoader().getResourceAsStream("druid.properties"));
            // 创建德鲁伊的数据源
            DATA_SOURCE = DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Connection getConnection() {
        try {
            return DATA_SOURCE.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void closeAll(Statement stmt, ResultSet rs) {
        if(Objects.nonNull(stmt)) {
            try {
                stmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(Objects.nonNull(rs)){
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 通用的保存方法
     * @param object 传入一个要保存的对象
     */
    @Override
    public void save(Object object) {
        // insert into user(id,username,password) values (?,?,?)
        Class clazz = object.getClass();
        Field[] fields = clazz.getDeclaredFields();

        // 拼接出一个insert语句
        StringBuilder strb = new StringBuilder("insert into ");
        // insert into user
        String[] split = clazz.getName().split("\\.");
        strb.append(split[split.length - 1]);
        strb.append(" (");
        for (Field field : fields) {
            strb.append(field.getName().toLowerCase()).append(",");
        }
        // insert into user (id,username,password
        strb.deleteCharAt(strb.length() - 1);
        strb.append(") values (");
        for (Field field : fields) {
            strb.append("?,");
        }
        strb.deleteCharAt(strb.length() - 1);
        strb.append(")");

        PreparedStatement pstmt = null;

        try {
            Connection conn = DATA_SOURCE.getConnection();
            pstmt = conn.prepareStatement(strb.toString());

            // 给?赋值
            for (int i = 0; i < fields.length; i++) {
                fields[i].setAccessible(true);
                pstmt.setObject(i+1,fields[i].get(object));
            }

            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(pstmt,null);
        }
    }

    /**
     * 通用的查询所有的方法
     * @param clazz 要操作的对象.class类型
     * @return
     */
    @Override
    public List<T> findAll(Class clazz) {
        // 拼sql
        // select id,username,password from user
        // 其中id,username,password可变的他们都是一个类的属性
        List<T> list = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        // 利用反射获取属性名
        Field[] fields = clazz.getDeclaredFields();
        // 拼装sql语句，拼字符串
        StringBuilder fieldStr = new StringBuilder();
        fieldStr.append("select ");
        for (Field field : fields) {
            // id,username,password,
            fieldStr.append(field.getName().toLowerCase()).append(",");
        }
        // select id,username,password
        fieldStr.deleteCharAt(fieldStr.length() - 1);
        fieldStr.append(" from ");
        // select id,username,password from
        //
        String clazzName = clazz.getName().toLowerCase();
        System.out.println(clazzName + "--------------------");
        String[] split = clazzName.split("\\.");
        fieldStr.append(split[split.length - 1]);
        // select id,username,password from user
        Connection conn = getConnection();
        try {
            pstmt = conn.prepareStatement(fieldStr.toString());
            rs = pstmt.executeQuery();
            while(rs.next()){
                // 1. 创建对象
                Object obj = clazz.getDeclaredConstructor().newInstance();
                for (Field field : fields) {
                    Object value = rs.getObject(field.getName());
                    // 访问私有化的结构
                    field.setAccessible(true);
                    // 利用反射给属性赋值，赋不上值
                    // 因为属性一定是private
                    field.set(obj,value);
                }
                list.add((T) obj);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (InvocationTargetException e) {
            throw new RuntimeException(e);
        } catch (InstantiationException e) {
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        } catch (NoSuchMethodException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(pstmt,rs);
        }

        return list;
    }

    /**
     * 通用的修改
     * @param obj 要修改的对象
     * @param fieldName 根据什么去修改数据 id
     * @param fieldValue 根据条件的值 1
     */
    @Override
    public void update(Object obj, String fieldName, Object fieldValue) {
        PreparedStatement pstmt = null;

        Class clazz = obj.getClass();

        // 拼接出一个update语句
        // update user set
        StringBuilder strb = new StringBuilder("update " + clazz.getName().toLowerCase().substring(clazz.getName().lastIndexOf(".") + 1) + " set ");
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            field.setAccessible(true);
            // update user set username = ?,password = ?,
            strb.append(field.getName()).append(" = ").append("?").append(",");
        }
        strb.deleteCharAt(strb.length() - 1);
        // update user set username = ?,password = ?
        strb.append(" where ").append(fieldName).append("=").append(fieldValue);
//        System.out.println(strb.toString());
        try {
            Connection conn = DATA_SOURCE.getConnection();
            pstmt = conn.prepareStatement(strb.toString());
            for (int i = 0; i < fields.length; i++) {
                fields[i].setAccessible(true);
                pstmt.setObject(i+1,fields[i].get(obj));
            }
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(pstmt,null);
        }
    }

    /**
     * 通用的删除
     * @param clazz 要删除的类.class
     * @param fieldName 根据什么去删除 id
     * @param fieldValue 根据的条件的值 1
     */
    @Override
    public void delete(Class clazz, String fieldName, Object fieldValue) {
        // 拼接一个delete语句
        PreparedStatement pstmt = null;
        StringBuilder sql = new StringBuilder("delete from ");
        // delete from user
        sql.append(clazz.getName().toLowerCase().substring(clazz.getName().lastIndexOf(".") + 1));
        sql.append(" where ").append(fieldName).append(" = ?");
        try {
            Connection conn = DATA_SOURCE.getConnection();
            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setObject(1,fieldValue);

            pstmt.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(pstmt,null);
        }
    }

    /**
     * 查询某一条记录
     * @param clazz 要查询的类.class
     * @param fieldName 根据什么去查询 id
     * @param fieldValue 查询的条件的值 1
     * @return
     */
    @Override
    public T findOne(Class clazz, String fieldName, Object fieldValue) {
        T t = null;

        PreparedStatement pstmt = null;
        ResultSet rs = null;

        // 拼接一个select语句
        Field[] fields = clazz.getDeclaredFields();
        StringBuilder strb = new StringBuilder();
        strb.append("select ");
        for (Field field : fields) {
            strb.append(field.getName().toLowerCase()).append(",");
        }
        strb.deleteCharAt(strb.length() - 1);
        // select id,username,password
        strb.append(" from ");
        strb.append(clazz.getName().toLowerCase().substring(clazz.getName().lastIndexOf(".") + 1));
        // select id,username,password from user
        strb.append(" where ");
        strb.append(fieldName).append("= ?");
        // select id,username,password from user where id = ?
        try {
            Connection conn = DATA_SOURCE.getConnection();
            pstmt = conn.prepareStatement(strb.toString());
            pstmt.setObject(1,fieldValue);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                Object o = clazz.getDeclaredConstructor().newInstance();
                for (Field field : fields) {
                    Object value = rs.getObject(field.getName());
                    field.setAccessible(true);
                    field.set(o,value);
                }
                t = (T) o;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (InvocationTargetException e) {
            throw new RuntimeException(e);
        } catch (InstantiationException e) {
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        } catch (NoSuchMethodException e) {
            throw new RuntimeException(e);
        }

        return t;
    }
}

 

 3.继承BaseDaoImpl以确定泛型类型

public class TeacherDao extends BaseDaoImpl<Teacher> {
}

 

4.Teacher类

查看代码 
 package jdbc.jianyikuangjia;

public class Teacher {

    private Integer id;
    private String name;

    public Teacher() {
    }

    public Teacher(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

 

 5.实现类

package jdbc.jianyikuangjia;

public class Demo {

    public static void main(String[] args) {
        TeacherDao teacherDao = new TeacherDao();
//        System.out.println(teacherDao.findAll(Teacher.class));
//        teacherDao.save(new Teacher(10,"HH"));
//        teacherDao.update(new Teacher(10,"zzz"),"id",10);
//        teacherDao.delete(Teacher.class,"id",10);
        System.out.println(teacherDao.findOne(Teacher.class, "id", 5));
    }
}

 

6，德鲁伊工具类

查看代码 
 package jdbc.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Objects;
import java.util.Properties;

public class BaseDao {

    public static final DataSource DATASOURCE;

    static {
        Properties properties = new Properties();
        try {
            properties.load(BaseDao.class.getClassLoader().getResourceAsStream("druid.properties"));
            // 创建德鲁伊的数据源
            DATASOURCE = DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static void release(Connection conn, Statement stmt, ResultSet rs) {
        if(Objects.nonNull(stmt)) {
            try {
                stmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(Objects.nonNull(conn)){
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(Objects.nonNull(rs)){
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static void release(Connection conn, Statement stmt) {
        if(Objects.nonNull(stmt)) {
            try {
                stmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(Objects.nonNull(conn)){
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

}

 

三，QueryRunner工具

package jdbc.queryRunners;

import jdbc.util.BaseDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.testng.annotations.Test;

import java.sql.SQLException;
import java.util.List;

/**
 * 需要引入一个依赖jar包
 * commons-dbutil-1.7.jar
 * DBUtils
 */
public class QueryRunnerTest {
    @Test
    public void test03() throws SQLException, SQLException {

        QueryRunner runner = new QueryRunner(BaseDao.DATASOURCE);
        int i = runner.update("update teacher set name = ? where id = ?", "mmm", 6);
        System.out.println(i);

    }

    /**
     * 查询一个记录
     */
    @Test
    public void test02() throws SQLException {
        QueryRunner runner = new QueryRunner(BaseDao.DATASOURCE);
        Teacher teacher = runner.query("select * from teacher where id = ?", new BeanHandler<>(Teacher.class), 1);
        System.out.println(teacher);
    }

    /**
     * 查询多个记录
     * @throws SQLException
     */
    @Test
    public void test01() throws SQLException {
        // 要使用DBUtils使用的是一个类
        // 传入的是一个数据源DataSource，不是一个Connection
        QueryRunner runner = new QueryRunner(BaseDao.DATASOURCE);
        // 查询多个记录
        List<Teacher> teachers = runner.query("select * from teacher", new BeanListHandler<>(Teacher.class));
        System.out.println(teachers);
    }
}
查看代码 
 package jdbc.queryRunners;

public class Teacher {
    private int id;
    private String name;

    public Teacher(int id, String name) {
        this.id = id;
        this.name = name;
    }

    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;
    }
}

 

封装QueryRunner（泛化，反射）

1.定义接口

查看代码 
 package jdbc.queryRunners.tongyongkuangjia;

import java.sql.SQLException;
import java.util.List;

public interface DAO<T> {

    /**
     * 更新
     * @return
     */
    int update(String sql,Object ... args) throws Exception;

    /**
     * 通用的查询所有
     */
    List<T> getForList(String sql,Object... args) throws Exception;

    /**
     * 通用的查询单个
     */
    T get(String sql,Object...args) throws Exception;

    /**
     * 查询某一个列的值，统计
     */
    <E> E getForValue(String sql,Object ... args) throws SQLException;

}

 

2.接口实现类

查看代码 
 package jdbc.queryRunners.tongyongkuangjia;

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 java.lang.reflect.ParameterizedType;
import java.sql.SQLException;
import java.util.List;

public class DAOImpl<T> implements DAO<T> {

    private QueryRunner runner = null;
    private Class<T> type;

    /**
     * 这个构造器中在做的事：
     *  为了获取Class<T> type = Teacher.class
     */
    public DAOImpl() {
        runner = new QueryRunner(JDBCUtil.getDataSource());
        // 获得当前类的带有泛型类型的父类（运行期this其实是DAOImpl的某个子类）
        ParameterizedType ptClass = (ParameterizedType) this.getClass().getGenericSuperclass();
        type = (Class<T>) ptClass.getActualTypeArguments()[0];
    }

    @Override
    public int update(String sql, Object... args) throws Exception {
        return runner.update(sql,args);
    }

    @Override
    public List<T> getForList(String sql, Object... args) throws Exception {
        return runner.query(sql,new BeanListHandler<>(type),args);
    }

    @Override
    public T get(String sql, Object... args) throws Exception {
        return runner.query(sql,new BeanHandler<>(type),args);
    }

    @Override
    public <E> E getForValue(String sql, Object... args) throws SQLException {

        return (E) runner.query(sql,new ScalarHandler<>(),args);
    }
}

 

3.JDBC工具类

查看代码 
 package jdbc.queryRunners.tongyongkuangjia;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.util.Properties;

public class JDBCUtil {

    private static final DataSource DATA_SOURCE;

    static {
        Properties properties = new Properties();
        try {
            properties.load(JDBCUtil.class.getClassLoader().getResourceAsStream("druid.properties"));
            DATA_SOURCE = DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static DataSource getDataSource() {
        return DATA_SOURCE;
    }
    
}

 

 4.继承，确定泛型的类型

public class TeacherDao extends DAOImpl<Teacher> {
}

 

 5.Teacher类

查看代码 
 package jdbc.queryRunners.tongyongkuangjia;

public class Teacher {

    private Integer id;
    private String name;

    public Teacher() {
    }

    public Teacher(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

 

 6.实现类

查看代码 
 package jdbc.queryRunners.tongyongkuangjia;


import org.testng.annotations.Test;

import java.util.List;

public class TeacherDaoTest {
    TeacherDao teacherDao = new TeacherDao();
    @Test
    public void test02(){

        try {
            List<Teacher> teachers = teacherDao.getForList("select * from teacher");
//            System.out.println(teachers);
            Teacher teacher = teacherDao.get("select id,name from teacher where id = 1");
//            System.out.println(teacher);
            long r = teacherDao.getForValue("select count(*) from teacher where name = ?", "Rose");
            System.out.println(r);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }


    @Test
    public void test01() {

        try {
            int i = teacherDao.update("insert into teacher(name) values (?)", "李四");
            System.out.println(i);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }
}

 

7.druid.properties

druid.url=jdbc:mysql://127.0.0.1:3306/jiruan?useUnicode=true&characterEncoding=utf8
druid.username=root
druid.password=123
druid.driverName=com.mysql.jdbc.Driver

druid.initialSize=10
druid.maxActive=20
druid.maxWait=20

 

posted @   一只神秘的猫  阅读(118)  评论(0)  编辑  收藏  举报
相关博文：
·  数据库查询
·  JDBC预编译，数据库事务
·  学习笔记——查询方法工具类&&连接池
·  2022-08-20 第二小组 张鑫 学习笔记
·  2022-8-20 第一组 (≥▽≤) 学习笔记
阅读排行：
· 使用C#创建一个MCP客户端
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· ollama系列1：轻松3步本地部署deepseek，普通电脑可用
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 按钮权限的设计及实现
点击右上角即可分享
微信分享提示
AI IDE Trae