30-JDBC(2)

1. 操作和访问数据库

数据库连接被用于向数据库服务器发送命令和 SQL 语句，并接受数据库服务器返回的结果。其实一个数据库连接就是一个 Socket 连接。
在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：
- Statement：用于执行静态 SQL 语句并返回它所生成结果的对象
- PrepatedStatement：SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句
- CallableStatement：用于执行 SQL 存储过程

2. 使用 Statement 操作数据表的弊端

通过调用 Connection 对象的 createStatement() 方法创建该对象。该对象用于发送 SQL 语句到数据库，并且返回执行结果。
Statement 接口中定义了下列方法用于执行 SQL 语句：
- int excuteUpdate(String sql)：执行更新操作 INSERT、UPDATE、DELETE
- ResultSet executeQuery(String sql)：执行查询操作 SELECT
但是使用Statement操作数据表存在弊端：
- Q1：存在拼串操作，繁琐
- Q2：存在SQL注入问题
SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令(如：SELECT user, password FROM user_table WHERE user='a' OR 1 = ' AND password = ' OR '1' = '1') ，从而利用系统的 SQL 引擎完成恶意行为的做法。
对于 Java 而言，要防范 SQL 注入，只要用 PreparedStatement(从 Statement 扩展而来) 取代 Statement 就可以了。

3. PreparedStatement 简述

表示预编译的 SQL 语句的对象
可以通过调用 Connection 对象的 preparedStatement(String sql) 获取 PreparedStatement 对象
PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句
PreparedStatement 对象所代表的 SQL 语句中的参数用 [占位符(?)] 来表示，调用 PreparedStatement 对象的 setXxx() 来设置这些参数
- 第 1 个参数是要设置的 SQL 语句中的参数的索引（从 1 开始）
- 第 2 个是设置的 SQL 语句中的参数的值（setString("value") 底层会在该值赋值给通配符的时候在参数两边自动补上单引号，即 'value'）
PreparedStatement VS Statement
- 预编译SQL的代码的可读性和可维护性要比 Statement 要好
- PreparedStatement 可以防止 SQL 注入
- PreparedStatement 能最大可能提高批量操作的性能
  - DBServer 会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被 DBServer 的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。
  - 在 Statement 语句中，即使是相同操作但因为数据内容不一样，所以整个语句本身不能匹配,没有缓存语句的意义。事实是没有数据库会对普通语句编译后的执行代码缓存。这样的话，每执行一次都要对传入的语句进行编译(语法检查，语义检查，翻译成二进制命令，缓存)。
- PreparedStatement 能操作 Blob 的数据，而 Statement 做不到

4. Java 与 SQL 对应数据类型转换表

5. ResultSet 与 ResultSetMetaData

5.1 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 接口的常用获取数据的方法(均提供index，columnName两种方式获取)：
- boolean next()
- int nextInt()
- …
- String getString()
- Java.sql.Date getDate()

5.2 ResultSetMetaData

可用于获取关于 ResultSet 对象中列的类型和属性信息的对象
ResultSetMetaData meta = rs.getMetaData();
常用方法
- getColumnName(int column)：获取指定列的名称
- getColumnLabel(int column)：获取指定列的别名
- getColumnCount()：返回当前 ResultSet 对象中的列数
- getColumnTypeName(int column)：检索指定列的数据库特定的类型名称
- getColumnDisplaySize(int column)：指示指定列的最大标准宽度，以字符为单位
- isNullable(int column)：指示指定列中的值是否可以为 null
- isAutoIncrement(int column)：指示是否自动为指定列进行编号，这样这些列仍然是只读的

Q1：得到结果集后, 如何知道该结果集中有哪些列？列名是什么？
需要使用一个描述 ResultSet 的对象，即 ResultSetMetaData。

Q2：关于ResultSetMetaData：

如何获取 ResultSetMetaData 对象：调用 ResultSet 的 getMetaData() 即可
获取 ResultSet 中有多少列：调用 ResultSetMetaData 的 getColumnCount()
获取 ResultSet 每一列的列的别名是什么：调用 ResultSetMetaData 的 getColumnLabel()

6. 使用 PreparedStat 实现 CRUD

说明：使用 PreparedStatement 实现的查询操作可以替换 Statement 实现的查询操作，解决 Statement 拼串和 SQL 注入问题。

6.1 前置准备

ORM(Object Relational Mapping) 思想：1. 表 ~ 类；2. 记录 ~ 对象；3. 字段 ~ 属性

6.1.1 JavaBean

getConnection() 和 close() 操作由于经常使用，故封装成工具类静态方法，方便直接调用。

6.1.2 资源的释放

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

6.1.3 封装成工具类

public class JDBCUtils {
    private static Properties prop = null;
    static {
        prop = new Properties();
        try {
            // 1. 读取配置文件
            prop.load(ClassLoader.getSystemClassLoader()
                    .getResourceAsStream("jdbc.properties"));
            // 2. 加载驱动
            Class.forName(prop.getProperty("driverClass"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws Exception {
        Connection connection = null;
        // 3. 获取连接
        connection = DriverManager.getConnection(prop.getProperty("url")
                        , prop.getProperty("user"), prop.getProperty("password"));
        return connection;
    }

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

6.2 使用 PreparedStat 实现增、删、改操作

/**
 * 通用的增删改操作
 * @param sql 要执行的SQL语句
 * @param args SQL中的参数
 * @return SQL影响到的数据库中的记录条数
 */
public int update(String sql, Object... args) {
    // 1. 获取连接
    Connection conn = null;
    // 2. 获取PreparedStatement的实例 (或：预编译SQL语句)
    PreparedStatement stat = null;
    int line = 0;
    try {
        conn = JDBCUtils.getConnection();
        stat = conn.prepareStatement(sql);
        int count = args.length;
        // 3. 填充占位符
        for(int i = 0; i < count; i++)
            stat.setObject(i+1, args[i]);
        // 4. 执行SQL语句
        // excute() 执行的若是查询则return true，增删改return false)
        // stat.execute(); // 如果只是单纯执行SQL，不需要返回值则使用这个方法
        // 返回值int类型：表示此条SQL语句影响的记录条数
        line = stat.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 5. 关闭资源
        JDBCUtils.close(conn, stat, null);
    }
    return line;
}

6.3 使用 PreparedStat 实现查询操作

针对 table 的字段名和 class 的属性名不一致：

使用类的属性名为 SQL 语句中的各查询字段取别名
使用 ResultSetMetaData 时，getColumnLable() 替代 getColumnName() 来获取列的别名

注：如果未指定 SQL AS，则从 getColumnLabel() 返回的值将和 getColumnName() 返回的值相同。

public static void main(String[] args) {
    System.out.println(query("SELECT order_id orderId, order_name orderName"
            + ", order_date orderDate FROM `order` WHERE order_id = ?", 4));
}

// 针对Order表的通用查询方法
public static Order query(String sql, Object... args) {
    Connection conn = null;
    PreparedStatement stat = null;
    ResultSet rs = null;
    Order order = null;
    try {
        conn = JDBCUtils.getConnection();
        stat = conn.prepareStatement(sql);
        for(int i = 0; i < args.length; i++)
        stat.setObject(i+1, args[i]);
        rs = stat.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        if(rs.next()) {
            order = new Order();
            for(int i = 0; i < columnCount; i++) {
                // 获取第 i 列的列值
                Object columnValue = rs.getObject(i+1);
                // 获取第 i 列的列名(万万不可啊~)
                // String columnName = rsmd.getColumnName(i+1);
                // 获取第 i 列的别名(如果没起别名，返回列名)
                String columnLable = rsmd.getColumnLabel(i+1);
                Field field = Order.class.getDeclaredField(columnLable);
                field.setAccessible(true);
                field.set(order, columnValue);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.close(conn, stat, rs);
    }
    return order;
}

通用的表查询方法：

// 使用PreparedStatement实现不同表的通用查询多条记录操作
public static<T> List<T> queryRecords(Class<T> clazz, String sql, Object... args) {
    Connection conn = null;
    PreparedStatement stat = null;
    ResultSet rs = null;
    List<T> list = null;
    try {
        conn = JDBCUtils.getConnection();
        stat = conn.prepareStatement(sql);
        for (int i = 0; i < args.length; i++)
            stat.setObject(i+1, args[i]);
        rs = stat.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        list = new ArrayList<>();
        T instance = null;
        while (rs.next()) {
            instance = clazz.newInstance();
            for (int i = 0; i < columnCount; i++) {
                // 获取第i列的列值
                Object columnValue = rs.getObject(i+1);
                // 获取第i列的别名
                String columnLable = rsmd.getColumnLabel(i+1);
                // 反射方式赋值
                Field field = clazz.getDeclaredField(columnLable);
                field.setAccessible(true);
                field.set(instance, columnValue);
            }
            list.add(instance);
        }
        return list;
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.close(conn, stat, rs);
    }
    return null;
}

// 使用PreparedStatement实现不同表的通用查询单条记录操作
public static <T> T queryRecord(Class<T> clazz, String sql, Object... args) {
    Connection conn = null;
    PreparedStatement stat = null;
    ResultSet rs = null;
    T instance = null;
    try {
        conn = JDBCUtils.getConnection();
        stat = conn.prepareStatement(sql);
        for (int i = 0; i < args.length; i++)
            stat.setObject(i+1, args[i]);
        rs = stat.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        if (rs.next()) {
            instance = clazz.newInstance();
            for (int i = 0; i < columnCount; i++) {
                // 获取第i列的列值
                Object columnValue = rs.getObject(i+1);
                // 获取第i列的别名
                String columnLable = rsmd.getColumnLabel(i+1);
                // 反射方法赋值
                Field field = clazz.getDeclaredField(columnLable);
                field.setAccessible(true);
                field.set(instance, columnValue);
            }
        }
    } catch (Exception e) {
    e.printStackTrace();
    } finally {
    JDBCUtils.close(conn, stat, rs);
    }
    return instance;
}

7. 操作 BLOB 类型字段

7.1 MySQL BLOB 类型

MySQL 中，BLOB 是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据
插入 BLOB 类型的数据必须使用 PreparedStatement，因为 BLOB 类型的数据无法使用字符串拼接写的（即Statement）
MySQL 的四种 BLOB 类型(除了在存储的最大信息量上不同外，他们是等同的)，实际使用中根据需要存入的数据大小定义不同的 BLOB 类型
需要注意的是：如果存储的文件过大，数据库的性能会下降
如果在指定了相关的 Blob 类型以后，插入报错：PacketTooBigException: Packet for query is too large，需在 MySQL 的安装目录下，找到 my.ini 文件加上如下的配置参数： max_allowed_packet=16M。同时注意：修改了 my.ini 文件之后，需要重新启动 MySQL 服务

7.2 向数据表中插入大数据类型

@Test
public void testInsert() {
    Connection conn = null;
    PreparedStatement stat = null;
    FileInputStream is = null;
    String sql = "insert into customers(name, email, birth, photo) values(?, ?, ?, ?)";
    try {
        conn = JDBCUtils.getConnection();
        stat = conn.prepareStatement(sql);
        stat.setObject(1, "燃烧女子的画像");
        stat.setObject(2, "shaonv@gmail.com");
        stat.setObject(3, "2020-10-10");
        // com.mysql.jdbc.PacketTooBigException: Packet for query is too large
        // change this value on the server by setting the 'max_allowed_packet' variable
        is = new FileInputStream(new File("p28.png"));
        stat.setBlob(4, is);
        stat.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if(is != null)
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        JDBCUtils.close(conn, stat, null);
    }
}

7.3 从数据表中读取大数据类型

@Test
public void testQuery() {
    Connection conn = null;
    PreparedStatement stat = null;
    ResultSet rs = null;
    FileOutputStream os = null;
    InputStream is = null;
    String sql = "select id, name, email, birth, photo from customers where id = ?";
    try {
        conn = JDBCUtils.getConnection();
        stat = conn.prepareStatement(sql);
        stat.setObject(1, 25);
        rs = stat.executeQuery();
        if(rs.next()) {
            Customer cust = new Customer();
            cust.setId(rs.getInt("id"));
            cust.setName(rs.getString("name"));
            cust.setEmail(rs.getString("email"));
            cust.setBirth(rs.getDate("birth"));
            Blob photo = rs.getBlob("photo");
            is = photo.getBinaryStream();
            os = new FileOutputStream(new File("伞哥牛逼.jpeg"));
            byte[] buf = new byte[1024];
            int len;
            while((len = is.read(buf)) != -1)
                os.write(buf, 0, len);
            System.out.println(cust);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if(is != null)
            try {
                is.close();
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        if(os != null)
            try {
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        JDBCUtils.close(conn, stat, null);
    }
}

8. 批量处理

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

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

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

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

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

执行如下演示代码前要做 2 处修改：

MySQL 服务器默认是关闭批处理的，需要通过追加参数 ?rewriteBatchedStatements=true 在配置文件的url 后，让 MySQL 开启批处理的支持。
使用更新的 MySQL 驱动：mysql-connector-java-5.1.37-bin.jar

这里的批量操作，主要指的是批量插入，使用预编译语句实现更高效的插入：

@Test
public void batchInsert() {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        long start = System.currentTimeMillis();
        conn = JDBCUtils.getConnection();
        // 设置不允许自动提交数据
        conn.setAutoCommit(false);
        String sql = "insert into goods(name) values(?)";
        ps = conn.prepareStatement(sql);
        for (int i = 1; i <= 20000; i++) {
            ps.setObject(1, "name_" + i);
            // 1. "攒" SQL
            ps.addBatch();
            if (i % 500 == 0) {
                // 2. 执行 batch
                ps.executeBatch();
                // 3. 清空 batch
                ps.clearBatch();
            }
        }
        // 提交数据
        conn.commit();
        long end = System.currentTimeMillis();
        System.out.println("花费的时间为：" + (end - start)); // 2130, 662
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.close(conn, ps, null);
    }
}

9. 小结

两种思想
- 面向接口编程思想(Interface-Oriented)
- ORM 思想(Object Relational Mapping)
  - 一个数据表对应一个 Java 类
  - 表中的一条记录对应 Java 类的一个对象
  - 表中的一个字段对应 Java 类的一个属性(SQL 是需要结合列名和表的属性名来写，注意起别名)
两种技术
- JDBC结果集的元数据：ResultSetMetaData
  - 获取列数：getColumnCount()
  - 获取列的别名：getColumnLabel()
- 通过反射，创建指定类的对象，获取指定的属性并赋值