本文主要是介绍2022-08-20 第二小组 张鑫 学习笔记，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

实训四十二天

1.学习重点

1.数据库连接池

2.基础框架

3.QueryRunner类

2.学习内容

数据库连接池

connection是一种稀有资源，一个连接建立就创造了一个资源

JDBC使用数据库连接的必要性

在使用基于web程序的数据库连接
1、在主程序中建立连接
2、执行SQL
3、断开连接
所有的JDBC连接通过DriverManager.getConnection。
用完的连接不要被垃圾回收，能够重复使用

“池化思想”

每次去初始化一个连接池，连接池中会有很多个连接等待被使用。
使用完连接之后，不需要关闭连接，只需要把连接还回到连接池，
还回到连接池的操作不需要我们手动控制。

设置一些属性：最大等待时间。

（1）C3P0，2代数据库连接池，太老了，不学
（2）DBCP，2代数据库连接池，太老了，不学
（3）Druid（德鲁伊）数据库连接池，最好用的连接池。
     阿里巴巴开源平台上的一个数据库连接池实现，整合了C3P0和DBCP各自的优点
     加入了日志监控，可以监控sql语句的执行情况。
（4）Hikari（光），目前最快的连接池。springboot默认的连接池。

必须有对应的属性文件
.properties

约定 > 配置 > 编码

德鲁伊

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

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

public void test01() throws IOException, SQLException {
        Properties properties = new Properties();
        properties.load(Ch01.class.getClassLoader().getResourceAsStream("druid.properties"));

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

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

    }

Hikari

jdbcUrl=jdbc:mysql://127.0.0.1:3306/jsoft?useUnicode=true&characterEncoding=utf8
username=root
password=root
driverClassName=com.mysql.jdbc.Driver

public void test01() throws IOException, SQLException {
     Properties properties = new Properties();
     properties.load(Ch01.class.getClassLoader().getResourceAsStream("hikari.properties"));
     HikariConfig hikariConfig = new HikariConfig(properties);
     HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);
     System.out.println(hikariDataSource.getConnection());
 }

德鲁伊工具包

public class BaseDao {

    public static final DataSource DATA_SOURCE;

    static {
        Properties properties = new Properties();
        try {
            properties.load(Ch01.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 void release(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);
            }
        }
    }

}

定义接口

/**
 * 约束
 * 约定
 */
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);
}

接口实现类

/**
 * 约定：
 * 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(Ch01.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;
    }
}

QueryRunner

/**
 * 需要引入一个依赖jar包
 * DBUtils
 */
public class Ch01 {

    @Test
    public void test03() throws SQLException {

        QueryRunner runner = new QueryRunner(BaseDao.DATA_SOURCE);
        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.DATA_SOURCE);
        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.DATA_SOURCE);
        // 查询多个记录
        List<Teacher> teachers = runner.query("select * from teacher", new BeanListHandler<>(Teacher.class));
        System.out.println(teachers);
    }

}

定义接口

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;

}

接口实现类

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

JDBC工具类

public class JDBCUtil {

    private static final DataSource DATA_SOURCE;

    static {
        Properties properties = new Properties();
        try {
            properties.load(Ch01.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;
    }

}

这篇关于2022-08-20 第二小组 张鑫 学习笔记的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！