JDBC知识点汇总（MyBatis的底层）

0 绪论

技术体系：

1. JDBC概述

数据的持久化

• 持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以”固化”，而持久化的实现过程大多通过各种关系数据库来完成。

• 持久化的主要应用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中。

java中的数据存储技术

在Java中，数据库存取技术可分为如下几类：

• JDBC直接访问数据库
• JDO (Java Data Object )技术
• 第三方O/R工具，如Hibernate, Mybatis 等

JDBC是java访问数据库的基石**，JDO、Hibernate、MyBatis等只是更好的封装了JDBC**。

JDBC介绍

• JDBC(Java Database Connectivity)是一个独立于特定数据库管理系统、通用的SQL数据库存取和操作的公共接口（一组API），定义了用来访问数据库的标准Java类库，（java.sql,javax.sql）使用这些类库可以以一种标准的方法、方便地访问数据库资源。

• JDBC为访问不同的数据库提供了一种统一的途径，为开发者屏蔽了一些细节问题。

• JDBC的目标是使Java程序员使用JDBC可以连接任何提供了JDBC驱动程序的数据库系统，这样就使得程序员无需对特定的数据库系统的特点有过多的了解，从而大大简化和加快了开发过程。

• 如果没有JDBC，那么Java程序访问数据库时是这样的：

  

• 有了JDBC，Java程序访问数据库时是这样的：

  

  总结如下：

  

JDBC体系结构

• JDBC接口（API）包括两个层次：
  • 面向应用的API：Java API，抽象接口，供应用程序开发人员使用（连接数据库，执行SQL语句，获得结果）。
  • 面向数据库的API：Java Driver API，供开发商开发数据库驱动程序用。

JDBC是sun公司提供一套用于数据库操作的接口，java程序员只需要面向这套接口编程即可。

不同的数据库厂商，需要针对这套接口，提供不同实现。不同的实现的集合，即为不同数据库的驱动。 ————面向接口编程

JDBC程序编写步骤

2. 获取数据库连接

Driver接口实现类

介绍

• java.sql.Driver 接口是所有 JDBC 驱动程序需要实现的接口。这个接口是提供给数据库厂商使用的，不同数据库厂商提供不同的实现。

• 在程序中不需要直接去访问实现了 Driver 接口的类，而是由驱动程序管理器类(java.sql.DriverManager)去调用这些Driver实现。

  • Oracle的驱动：oracle.jdbc.driver.OracleDriver
  • mySql的驱动： com.mysql.jdbc.Driver

URL

• JDBC URL 用于标识一个被注册的驱动程序，驱动程序管理器通过这个 URL 选择正确的驱动程序，从而建立到数据库的连接。

• JDBC URL的标准由三部分组成，各部分间用冒号分隔。

  • jdbc:子协议:子名称
  • 协议：JDBC URL中的协议总是jdbc
  • 子协议：子协议用于标识一个数据库驱动程序
  • 子名称：一种标识数据库的方法。子名称可以依不同的子协议而变化，用子名称的目的是为了定位数据库提供足够的信息。包含主机名(对应服务端的ip地址)，端口号，数据库名

• 举例：

  

数据库连接方式

导入Mysql驱动jar包

Driver是一个接口，mysql.jdbc.Driver 这个jar包来实现这个接口，开发人员导入jar包并调用。

连接方式一

// 方式一：
@Test
public void testConnection01() throws SQLException {
    Driver driver = new com.mysql.jdbc.Driver();
    //jdbc:mysql 协议
    //localhost: ip
    //3306 端口
    //test test数据库
    String url = "jdbc:mysql://localhost:3306/test";
    // 将用户名和密码封装在Properties中
    Properties info = new Properties();
    info.setProperty("user", "root");
    info.setProperty("password", "123456");
    Connection connect = driver.connect(url, info);
    S
        ystem.out.println(connect);
}

连接方式二

@Test
// 方式二：对方式一的迭代:使程序不出现第三方api，具有更好的移植性
public void testConnection02() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
    // 1.获取Driver实现类对象，使用发射
    Class clazz = Class.forName("com.mysql.jdbc.Driver");
    Driver driver = (Driver) clazz.newInstance();
    String url = "jdbc:mysql://localhost:3306/test";
    Properties info = new Properties();
    info.setProperty("user", "root");
    info.setProperty("password", "123456");

    Connection connect = driver.connect(url, info);
    System.out.println(connect);
}

连接方式三

//方式三：使用DriverManager替换Driver
@Test
public void testConnection03() throws Exception{

    Class clazz = Class.forName("com.mysql.jdbc.Driver");
    Driver driver = (Driver) clazz.newInstance();

    String url = "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "123456";

    // 注册驱动
    DriverManager.registerDriver(driver);

    //获取连接
    Connection connect = DriverManager.getConnection(url, user, password);
    System.out.println(connect);
}

连接方式四

//方式四：无需手动注册driver
@Test
public void testConnection04() throws Exception{

    String url = "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "123456";

    //加载Driver类的时候,存在static语句，使用DriverManager注册了驱动
    Class.forName("com.mysql.jdbc.Driver");
    /*
    源码
    Driver类：
    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
    */


    // 注册驱动
    //        DriverManager.registerDriver(driver);

    //获取连接
    Connection connect = DriverManager.getConnection(url, user, password);
    System.out.println(connect);
}

连接方式五

jdbc.properties:

user=root
password=123456
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver

// // 方式五: 将数据库连接需要的4个基本信息声明在配置文件中，通过读取配置文件的方式获取连接
/**
     * 好处：
     * 1. 实现了数据与代码的分离，实现了解耦
     * 2. 如果需要修改配置信息，可以避免程序重新打包
     *
     */
@Test
public void getConnection5() throws Exception{
    Properties properties = new Properties();

    //读取配置文件的4个基本信息
    properties.load(new FileReader("src\\jdbc.properties"));
    properties.list(System.out);
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    String url = properties.getProperty("url");
    String driverClass = properties.getProperty("driverClass");


    // 加载驱动
    Class.forName(driverClass);

    // 获取连接
    Connection connection = DriverManager.getConnection(url, user, password);
    System.out.println(connection);
}

3. PreparedStatement

操作和访问数据库

• 数据库连接被用于向数据库服务器发送命令和 SQL 语句，并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接。

• 在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：

  • Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
  • PrepatedStatement：它是Statement的子接口，SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。
  • CallableStatement：用于执行 SQL 存储过程

使用Statement的弊端

• 问题一：存在拼串操作，繁琐
• 问题二：存在SQL注入问题

int excuteUpdate(String sql)：执行更新操作INSERT、UPDATE、DELETE
ResultSet executeQuery(String sql)：执行查询操作SELECT

• SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令(如：SELECT user, password FROM user_table WHERE user=‘a’ OR 1 = ’ AND password = ’ OR ‘1’ = ‘1’) ，从而利用系统的 SQL 引擎完成恶意行为的做法。

• 对于 Java 而言，要防范 SQL 注入，只要用 PreparedStatement(从Statement扩展而来) 取代 Statement 就可以了。

PreparedStatement介绍

介绍

• 可以通过调用 Connection 对象的 preparedStatement(String sql) 方法获取 PreparedStatement 对象

• PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句

• PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始)，第二个是设置的 SQL 语句中的参数的值

优点

• 代码的可读性和可维护性。

• PreparedStatement 能最大可能提高性能：

  • DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。
  • 在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。
  • (语法检查，语义检查，翻译成二进制命令，缓存)

• PreparedStatement 可以防止 SQL 注入

Java与SQL数据类型转换表

Java类型	SQL类型
boolean	BIT
byte	TINYINT
short	SMALLINT
int	INTEGER
long	BIGINT
String	CHAR,VARCHAR,LONGVARCHAR
byte array	BINARY , VAR BINARY
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP

PreparedStatement实现增删改操作

避免重复编写创建连接，关闭资源代码，编写一个JDBCUtils工具类：

// JDBCUtils类
/**
     * 获取数据库的连接
     * @return
     * @throws Exception
     */
public static Connection getConnection() throws Exception{
    // 加载配置信息
    Properties properties = new Properties();
    properties.load(new FileReader("src\\jdbc.properties"));
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    String url = properties.getProperty("url");
    String driverClass = properties.getProperty("driverClass");

    // 加载驱动
    Class.forName(driverClass);

    // 获取连接
    Connection conn = DriverManager.getConnection(url, user, password);

    return conn;
}

/**
     * 关闭连接Statement的操作
     * @param conn
     * @param ps
     */
public static void closeResource(Connection conn, Statement ps) {
    try {
        if(ps != null)
            ps.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    try {
        if(conn != null)
            conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public static void closeResource(Connection conn, Statement ps, ResultSet rs) {
    try {
        if(ps != null)
            ps.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    try {
        if(conn != null)
            conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    try {
        if(rs != null)
            rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

举例1

// 修改customers表的记录
    @Test
    public void testUpdate() throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "update customers set name = ? where id = ?";
            ps = conn.prepareStatement(sql);
            // 通用方法setObject
            ps.setObject(1, "czk");
            ps.setObject(2, 18);
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
        JDBCUtils.closeResource(conn, ps);
    }

举例2

通用的增加改操作：

@Test
public void testCommonUpdate() {
    //        String sql = "delete from customers where id = ?";
    //        update(sql, 3);

    String sql = "update `order` set order_name = ? where order_id = ?";
    update(sql, "DD", "2");
}

// 通用的增删改操作
public void update(String sql,  Object ...args){
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = JDBCUtils.getConnection();
        ps = conn.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    }

    JDBCUtils.closeResource(conn, ps);
}

PreparedStatement实现查询操作

ResultSet

• 查询需要调用PreparedStatement 的 executeQuery() 方法，查询结果是一个ResultSet 对象

• ResultSet 对象以逻辑表格的形式封装了执行数据库操作的结果集，ResultSet 接口由数据库厂商提供实现

• ResultSet 返回的实际上就是一张数据表。有一个指针指向数据表的第一条记录的前面。

• ResultSet 对象维护了一个指向当前数据行的游标，初始的时候，游标在第一行之前，可以通过 ResultSet 对象的 next() 方法移动到下一行。调用 next()方法检测下一行是否有效。若有效，该方法返回 true，且指针下移。相当于Iterator对象的 hasNext() 和 next() 方法的结合体。

• 当指针指向一行时, 可以通过调用 getXxx(int index) 或 getXxx(int columnName) 获取每一列的值。

  • 例如: getInt(1), getString(“name”)
  • 注意：Java与数据库交互涉及到的相关Java API中的索引都从1开始。

• ResultSet 接口的常用方法：

  • boolean next()
  • getString()
  • …

ResultSetMetaData

• 获取关于 ResultSet 对象中列的类型和属性信息的对象

• ResultSetMetaData meta = resultSet.getMetaData();

  • getColumnName(int column)：获取指定列的名称
  • getColumnLabel(int column)：获取指定列的别名
  • getColumnCount()：返回当前 ResultSet 对象中的列数。
  • getColumnTypeName(int column)：检索指定列的数据库特定的类型名称。
  • getColumnDisplaySize(int column)：指示指定列的最大标准宽度，以字符为单位。
  • isNullable(int column)：指示指定列中的值是否可以为 null。
  • isAutoIncrement(int column)：指示是否自动为指定列进行编号，这样这些列仍然是只读的。

资源的释放

• 释放ResultSet, Statement,Connection。
• 数据库连接（Connection）是非常稀有的资源，用完后必须马上释放，如果Connection不能及时正确的关闭将导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。
• 可以在finally中关闭，保证及时其他代码出现异常，资源也一定能被关闭。

举例1

@Test
public void testQuery1() {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet resultSet = null;
    try {
        conn = JDBCUtils.getConnection();
        String sql = "select id, name, email, birth from customers where id = ?";
        ps = conn.prepareStatement(sql);
        ps.setObject(1, 1);
        resultSet = ps.executeQuery();
        // 处理结果集
        if(resultSet.next()) { // 判断下一条是否有数据，如果有返回true，并指针下移，若没有，则返回false，结束
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            String email = resultSet.getString(3);
            Date birth = resultSet.getDate(4);
            Customer customer = new Customer(id, name, email, birth); //创建javaBean对象，来接收结果集
            System.out.println(customer);
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    }

    JDBCUtils.closeResource(conn, ps, resultSet);
}

通用查询操作举例

@Test
public void test1() throws Exception{
    String sql = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id = ?";
    Order order = getOrder(sql, 1);
    System.out.println(order);
}

public Order getOrder(String sql, Object ...args){
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet res = null;
    try {
        conn = JDBCUtils.getConnection();
        ps = conn.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        res = ps.executeQuery();
        // 获取结果集元数据
        ResultSetMetaData rsmd = res.getMetaData();
        // 通过ResultSetMetaData列数
        int columnCount = rsmd.getColumnCount();
        if(res.next()) {
            Order order = new Order();
            // 处理结果集一行数据的每一个列
            for (int i = 0; i < columnCount; i++) {
                // 获取列值
                Object colValue = res.getObject(i + 1);
                // 获取列的列名
                // String columnName = rsmd.getColumnName(i + 1); 不推荐使用
                // 获取列的别名
                String columnLabel = rsmd.getColumnLabel(i + 1);
                // 给customer对象指定属性赋值，通过发射
                Field field = Order.class.getDeclaredField(columnLabel);
                field.setAccessible(true); // 暴力破解private类型
                field.set(order, colValue);
            }
            return order;
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        JDBCUtils.closeResource(conn, ps, res);
    }
    return null;
}

注意：为什么不能使用rsmd.getColumnName(i + 1)呢？

因为数据库中字段名字为order_id，order_name…，而不是Order对象中的orderId，orderName,。所以应该对sql语句的查询的结果集取别名，然后rsmd.getColumnLabel(i + 1)拿到别名，再给Order对象赋值。

4. 操作Blob类型字段

MySQL BLOB类型

• MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。

• 插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。

• MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)

• 实际使用中根据需要存入的数据大小定义不同的BLOB类型。
• 需要注意的是：如果存储的文件过大，数据库的性能会下降。
• 如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数： max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务。

向数据表插入大数据类型

//获取连接
Connection conn = JDBCUtils.getConnection();

String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);

// 填充占位符
ps.setString(1, "徐海强");
ps.setString(2, "xhq@126.com");
ps.setDate(3, new Date(new java.util.Date().getTime()));
// 操作Blob类型的变量
FileInputStream fis = new FileInputStream("xhq.png");
ps.setBlob(4, fis);
//执行
ps.execute();

fis.close();
JDBCUtils.closeResource(conn, ps);

5. 批量插入

批量执行SQL语句

当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率

JDBC的批量处理语句包括下面三个方法：

• addBatch(String)：添加需要批量处理的SQL语句或是参数；
• executeBatch()：执行批量处理语句；
• clearBatch():清空缓存的数据

通常我们会遇到两种批量执行SQL语句的情况：

• 多条SQL语句的批量处理；
• 一个SQL语句的批量传参；

高效地批量插入

举例：向数据表中插入20000条数据

• 数据库中提供一个goods表。创建如下：

CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);

实现层次一：使用PreparedStatement

long start = System.currentTimeMillis();
		
Connection conn = JDBCUtils.getConnection();
		
String sql = "insert into goods(name)values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
for(int i = 1;i <= 20000;i++){
	ps.setString(1, "name_" + i);
	ps.executeUpdate();
}
		
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));//82340
		
		
JDBCUtils.closeResource(conn, ps);

实现层次二：batch操作

/*
 * 修改1： 使用 addBatch() / executeBatch() / clearBatch()
 * 修改2：mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
 * 		 ?rewriteBatchedStatements=true 写在配置文件的url后面
 * 修改3：使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar
 * 
 */
@Test
public void testInsert1() throws Exception{
    long start = System.currentTimeMillis();

    Connection conn = JDBCUtils.getConnection();

    String sql = "insert into goods(name)values(?)";
    PreparedStatement ps = conn.prepareStatement(sql);

    for(int i = 1;i <= 1000000;i++){
        ps.setString(1, "name_" + i);

        //1.“攒”sql
        ps.addBatch();
        if(i % 500 == 0){
            //2.执行
            ps.executeBatch();
            //3.清空
            ps.clearBatch();
        }
    }

    long end = System.currentTimeMillis();
    System.out.println("花费的时间为：" + (end - start));//20000条：625                                                                         //1000000条:14733  

    JDBCUtils.closeResource(conn, ps);
}

实现层次三：commit手动提交

/*
* 使用Connection 的 setAutoCommit(false)  /  commit()
*/
@Test
public void testInsert2() throws Exception{
    long start = System.currentTimeMillis();

    Connection conn = JDBCUtils.getConnection();

    //1.设置为不自动提交数据
    conn.setAutoCommit(false);

    String sql = "insert into goods(name)values(?)";
    PreparedStatement ps = conn.prepareStatement(sql);

    for(int i = 1;i <= 1000000;i++){
        ps.setString(1, "name_" + i);

        ps.addBatch();

        if(i % 500 == 0){
            ps.executeBatch();
            ps.clearBatch();
        }
    }

    //2.提交数据
    conn.commit();

    long end = System.currentTimeMillis();
    System.out.println("花费的时间为：" + (end - start));//1000000条:4978 

    JDBCUtils.closeResource(conn, ps);
}

6. 数据库事务

6.1 数据库事务介绍

• 事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。

• 事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务**回滚(rollback)**到最初状态。

• 为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

6.2 JDBC事务处理

• 数据一旦提交，就不可回滚。

• 数据什么时候意味着提交？

  • 当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
  • **关闭数据库连接，数据就会自动的提交。**如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同一个连接下。

• JDBC程序中为了让多个 SQL 语句作为一个事务执行：

  • 调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务
  • 在所有的 SQL 语句都成功执行后，调用 commit(); 方法提交事务
  • 在出现异常时，调用 rollback(); 方法回滚事务

  若此时 Connection 没有被关闭，还可能被重复使用，则需要恢复其自动提交状态 setAutoCommit(true)。尤其是在使用数据库连接池技术时，执行close()方法前，建议恢复自动提交状态。

【案例：用户AA向用户BB转账100】

public void testJDBCTransaction() {
    Connection conn = null;
    try {
        // 1.获取数据库连接
        conn = JDBCUtils.getConnection();
        // 2.开启事务
        conn.setAutoCommit(false);
        // 3.进行数据库操作
        String sql1 = "update user_table set balance = balance - 100 where user = ?";
        update(conn, sql1, "AA"); // 这里的update方法里面没有 关闭conn连接操作，最后再关闭

        // 模拟网络异常
        System.out.println(10 / 0);

        String sql2 = "update user_table set balance = balance + 100 where user = ?";
        update(conn, sql2, "BB");
        // 4.若没有异常，则提交事务
        conn.commit();
    } catch (Exception e) {
        e.printStackTrace();
        // 5.若有异常，则回滚事务
        try {
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    } finally {
        try {
            //6.恢复每次DML操作的自动提交功能
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //7.最后关闭连接
        JDBCUtils.closeResource(conn, null, null); 
    }  
}

7. DAO及相关实现类

• DAO：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、Delete），而不包含任何业务相关的信息。有时也称作：BaseDAO
• 作用：为了实现功能的模块化，更有利于代码的维护和升级。

BaseDAO.java

所有DAO的基类，可继承该BaseDAO类，体现了继承与封装的特性。

package com.czk.dao;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.czk.utils.JDBCUtils;

/*
 * DAO: data(base) access object
 * 封装了针对于数据表的通用的操作 （version 2.0：考虑上事务）
 */
public abstract class BaseDAO<T> {

    private Class<T> clazz = null; // 将Custom.class抽离出来
    
    
	// 获取当前BaseDAO的子类继承的父类中的泛型
    {
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType paramType = (ParameterizedType) genericSuperclass;
        Type[] typeArguments = paramType.getActualTypeArguments();//获取了父类的泛型参数
        clazz = (Class<T>) typeArguments[0];//泛型的第一个参数
    }

    // 通用的增删改操作（version 2.0  考虑上事务）
    // conn，首先考虑到事务性，即写完了一大段调用sql函数，然后可以手动关闭连接。 也体现了一个连接可以用于多次sql操作，即方便连接池的使用。
    public int update(Connection conn, String sql, Object... args) {// sql中占位符的个数与可变形参的长度相同！
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps);
        }
        return 0;

    }

    // 通用的查询操作，用于返回数据表中的一条记录（version 2.0：考虑上事务）
    public T getInstance(Connection conn, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {

            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();

            if (rs.next()) {
                T t = clazz.newInstance();

                for (int i = 0; i < columnCount; i++) {
                    Object columValue = rs.getObject(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);

        }

        return null;
    }
    // 通用的查询操作，用于返回数据表中的多条记录构成的集合（version 2.0：考虑上事务）
    public List<T> getForList(Connection conn, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {

            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            ArrayList<T> list = new ArrayList<T>();
            while (rs.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    Object columValue = rs.getObject(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columValue);
                }
                list.add(t);
            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);

        }

        return null;
    }
    
    //用于查询特殊值的通用的方法
    public <E> E getValue(Connection conn,String sql,Object...args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i = 0;i < args.length;i++){
                ps.setObject(i + 1, args[i]);

            }

            rs = ps.executeQuery();
            if(rs.next()){
                return (E) rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.closeResource(null, ps, rs);

        }
        return null;
    }	
}

CustomerDAO.java(接口)

package com.czk.dao;

import com.czk.bean.Customer;

import java.sql.Connection;
import java.util.Date;
import java.util.List;

/**
 * @author czk
 * @date: 2023/12/13
 * @version: 1.0
 */
public interface CustomerDAO {
    /**
     * 将cust对象添加到数据库中
     * @param conn
     * @param cust
     */
    void insert(Connection conn, Customer cust);

    /**
     * 根据指定id，删除表中的一条记录
     * @param conn
     * @param id
     */
    void deleteById(Connection conn, int id);

    /**
     * 针对内存中的cust对象，取修改数据表中指定的记录
     * @param conn
     * @param cust
     */
    void update(Connection conn, Customer cust);

    /**
     * 根据指定的id查询到对应的Customer对象
     * @param conn
     * @param id
     */
    Customer getCustomerById(Connection conn, int id);

    /**
     * 查询表中的所有记录构成的集合
     * @param conn
     * @return
     */
    List<Customer> getAll(Connection conn);

    /**
     * 返回数据表中的数据的条目数
     * @param conn
     * @return
     */
    Long getCount(Connection conn);
}

CustomerDAOImpl.java

package com.czk.dao;

import com.czk.bean.Customer;

import java.sql.Connection;
import java.util.Date;
import java.util.List;

/**
 * @author czk
 * @date: 2023/12/13
 * @version: 1.0
 */
public class CustomerDAOImpl extends BaseDAO<Customer> implements CustomerDAO{
    @Override
    public void insert(Connection conn, Customer cust) {
        String sql = "insert into customers(name, email, birth) values(?, ?, ?)";
        update(conn, sql, cust.getName(), cust.getEmail(), cust.getBirth());
    }

    @Override
    public void deleteById(Connection conn, int id) {
        String sql = "delete from customers where id = ?";
        update(conn, sql, id);
    }

    @Override
    public void update(Connection conn, Customer cust) {
        String sql = "update customers set name = ?, email = ?, birth = ? where id = ?";
        update(conn, sql, cust.getName(), cust.getEmail(), cust.getBirth(), cust.getId());
    }

    @Override
    public Customer getCustomerById(Connection conn, int id) {
        String sql = "select id, name, email, birth from customers where id = ?";
        return getInstance(conn, sql, id);
    }

    @Override
    public List<Customer> getAll(Connection conn) {
        String sql = "select id, name, email, birth from customers";
        return getForList(conn, sql);
    }

    @Override
    public Long getCount(Connection conn) {
        String sql = "select count(*) from customers";
        return getValue(conn, sql);
    }

    @Override
    public Date getMaxBirth(Connection conn) {
        String sql = "select max(birth) from customers";
        return getValue(conn, sql);
    }
}

CustomerDAOImplTest.java(测试类)

package com.czk.dao.junit;

import com.czk.bean.Customer;
import com.czk.dao.CustomerDAOImpl;
import com.czk.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.util.Date;
import java.util.List;

import static org.junit.Assert.*;

/**
 * @author czk
 * @date: 2023/12/13
 * @version: 1.0
 */
public class CustomerDAOImplTest {

    private CustomerDAOImpl dao = new CustomerDAOImpl();

    @Test
    public void testInsert(){
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Customer cust = new Customer(1, "BOb", "xiaofei@qq.com", new Date(234234234L));
            dao.insert(conn, cust);
            System.out.println("添加成功");
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    @Test
    public void testDeleteById() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            dao.deleteById(conn, 13);
            System.out.println("删除成功");
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    @Test
    public void testUpdate() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Customer cust = new Customer(21, "BoBOb", "xiaofei@qq.com", new Date(234234234L));
            dao.update(conn, cust);
            System.out.println("修改成功");
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    @Test
    public void testGetCustomerById() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Customer cust = dao.getCustomerById(conn, 7);
            System.out.println(cust);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    @Test
    public void testGetAll() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            List<Customer> list = dao.getAll(conn);
            System.out.println(list);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    @Test
    public void testGetCount() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Long count = dao.getCount(conn);
            System.out.println("表中记录数为：" + count);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }
}

8. 数据库连接池

JDBC数据库连接池的必要性

• 在使用开发基于数据库的web程序时，传统的模式基本是按以下步骤：

  • 在主程序（如servlet、beans）中建立数据库连接
  • 进行sql操作
  • 断开数据库连接

• 这种模式开发，存在的问题:

  • 普通的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection 加载到内存中，再验证用户名和密码(得花费0.05s～1s的时间)。需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。**数据库的连接资源并没有得到很好的重复利用。**若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。
  • **对于每一次数据库连接，使用完后都得断开。**否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。（回忆：何为Java的内存泄漏？）
  • 这种开发不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃。

数据库连接池技术

• 为解决传统开发中的数据库连接问题，可以采用数据库连接池技术。

• 数据库连接池的基本思想：就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。

• 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。

• 数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。

工作原理

数据库连接池技术的优点

1. 资源重用

由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。

2. 更快的系统反应速度

数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间

3. 新的资源分配手段

对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源

4. 统一的连接管理，避免数据库连接泄漏

在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露

多种开源的数据库连接池

• JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：
  • DBCP 是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持。
  • C3P0 是一个开源组织提供的一个数据库连接池，**速度相对较慢，稳定性还可以。**hibernate官方推荐使用
  • Proxool 是sourceforge下的一个开源项目数据库连接池，有监控连接池状态的功能，稳定性较c3p0差一点
  • BoneCP 是一个开源组织提供的数据库连接池，速度快
  • Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不确定是否有BoneCP快
• DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池
• DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度。
• 特别注意：
  • 数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。
  • 当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但conn.close()并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

Druid数据库连接池操作

Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的连接池之一。

将Druid连接池jar包加载到jar包中。

简单测试Druid连接：

@Test
public void getConnection() throws Exception {
    Properties properties = new Properties();
    properties.load(new FileReader("src/druid.properties"));
    properties.list(System.out);
    DataSource ds = DruidDataSourceFactory.createDataSource(properties);
    Connection conn = ds.getConnection();
    System.out.println(conn);
}

// druid.properties
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=123456
driverClassName=com.mysql.cj.jdbc.Driver
    
initialSize=10
maxActive=20
maxWait=1000
filters=wall

将Druid获取连接加入到JDBCUtils中：

/**
     * 使用Druid数据库连接池返回connection连接
     * @return
     * @throws Exception
     */
    private static DataSource source1; 
	// 静态语句功能：只进行一次创建数据库连接池，而不是每次调用都创建连接池
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileReader("src/druid.properties"));
            source1 = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    public static Connection getConnectionByDruid() throws Exception{
        return source1.getConnection();
    }

9.Apache-DBUtils实现CRUD操作

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。

QueryRunner类

• 该类简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。

• QueryRunner类提供了两个构造器：

  • 默认的构造器
  • 需要一个 javax.sql.DataSource 来作参数的构造器

• QueryRunner类的主要方法：

  • 更新
    • public int update(Connection conn, String sql, Object… params) throws SQLException:用来执行一个更新（插入、更新或删除）操作。
    • …
  • 插入
    • public T insert(Connection conn,String sql,ResultSetHandler rsh, Object… params) throws SQLException：只支持INSERT语句，其中 rsh - The handler used to create the result object from the ResultSet of auto-generated keys. 返回值: An object generated by the handler.即自动生成的键值
    • …
  • 批处理
    • public int[] batch(Connection conn,String sql,Object[][] params)throws SQLException： INSERT, UPDATE, or DELETE语句
    • public T insertBatch(Connection conn,String sql,ResultSetHandler rsh,Object[][] params)throws SQLException：只支持INSERT语句
    • …
  • 查询
    • public Object query(Connection conn, String sql, ResultSetHandler rsh,Object… params) throws SQLException：执行一个查询操作，在这个查询中，对象数组中的每个元素值被用来作为查询语句的置换参数。该方法会自行处理 PreparedStatement 和 ResultSet 的创建和关闭。
    • …

Apache-DBUtils的使用

先导入jar包。

案例：

// 测试插入
    @Test
    public void testInsert() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();

            conn  = JDBCUtils.getConnectionByDruid();
            String sql = "insert into customers (name, email, birth) values (?, ?, ?)";
            int insertCount = runner.update(conn, sql, "lqy", "wrwer@1 23.com", "2001-2-22");
            System.out.println("添加了" + insertCount + "条记录");
            JDBCUtils.closeResource(conn, null);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

ResultSerHandler接口及其实现类

• 该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式。

• ResultSetHandler 接口提供了一个单独的方法：Object handle (java.sql.ResultSet .rs)。

• 接口的主要实现类：

  • ArrayHandler：把结果集中的第一行数据转成对象数组。
  • ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。
  • **BeanHandler：**将结果集中的第一行数据封装到一个对应的JavaBean实例中。
  • **BeanListHandler：**将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
  • ColumnListHandler：将结果集中某一列的数据存放到List中。
  • KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。
  • **MapHandler：**将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
  • **MapListHandler：**将结果集中的每一行数据都封装到一个Map里，然后再存放到List
  • **ScalarHandler：**查询单个值对象

/**
     * 测试查询
     * BeanHandler: 是ResultSetHandler接口的实现类，用于封装表中的一条记录
     * @throws Exception
     */
    @Test
    public void testQuery1(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnectionByDruid();
            String sql = "select id, name, email, birth from customers where id = ?";
            BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
            Customer customer = runner.query(conn, sql, handler, 10);
            System.out.println(customer);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    @Test
    public void testQuery2(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnectionByDruid();
            String sql = "select id, name, email, birth from customers where id < ?";
            BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
            List<Customer> customerList = runner.query(conn, sql, handler, 10);
            System.out.println(customerList);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }