标签：20 String 08 sql 张晟源 id import new public

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

标签：20,String,08,sql,张晟源,id,import,new,public
From： https://www.cnblogs.com/shenmimao/p/16607465.html

2022-08-20 第二小组张晟源（数据库连接池，queryRunner）

JDBC（数据库连接池，queryRunner）

一，数据库连接池

二，简易框架

三，QueryRunner工具

封装QueryRunner（泛化，反射）

相关文章

赞助商

阅读排行

2022-08-20 第二小组 张晟源（数据库连接池，queryRunner）

JDBC（数据库连接池，queryRunner）

一，数据库连接池

二，简易框架

三，QueryRunner工具

封装QueryRunner（泛化，反射）

相关文章

赞助商

阅读排行

2022-08-20 第二小组张晟源（数据库连接池，queryRunner）