🌊 PreparedStatement 实现数据的修改

// 修改customers表中的一条数据
    @Test
    public void testUpdate() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            // 获取数据库的连接
            connection = JDBCUtils.getConnection();
            // 预编译sql语句，返回PreparedStatement实例对象
            String sql = "update customers set name=? where id=?";
            ps = connection.prepareStatement(sql);
            // 填充占位符
            ps.setObject(1, "莫扎特");
            ps.setObject(2, 18);
            // 执行sql
            Boolean res = ps.execute();
        } catch (Exception e) {
            e.fillInStackTrace();
        } finally {
            // 资源关闭
            JDBCUtils.closeResource(connection, ps);
        }
    }

🌊 PreparedStatement 实现通用的增删改操作

// 通用的增删改操作
    public void update(String sql, Object ...args) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            // 获取数据库的连接
            connection = JDBCUtils.getConnection();
            // 预编译sql，获取PreparedStatement实例对象
            ps = connection.prepareStatement(sql);
            // 填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1, args[i]); // 由于不知道填充占位的数据是什么类型所以使用setObject
            }
            // 执行sql
            ps.execute();
        } catch (Exception e) {
            e.fillInStackTrace();
        } finally {
            // 关闭资源
            JDBCUtils.closeResource(connection, ps);
        }
    }

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

🌊 PreparedStatement 实现数据的查询

JDBCUtils中新增关闭资源的方法：

/**
     * 关闭资源操作
     * @param connection
     * @param ps
     * @param rs
     */
    public static void closeResource(Connection connection, Statement ps, ResultSet rs) {
        // 如果存在Statement或Statement的子类对象，进行关闭操作
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        // 进行数据库的关闭
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        // 进行结果集的关闭
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

@Test
    public void testQuery1() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            // 获取数据库的连接
            connection = JDBCUtils.getConnection();
            // 预编译sql，获取PreparedStatement实例对象
            String sql = "select id, name, email from customers where id=?";
            ps = connection.prepareStatement(sql);
            // 填充占位符
            ps.setObject(1, 1);
            // 执行sql获取结果集
            resultSet = ps.executeQuery();
            // 处理结果集
            while (resultSet.next()) { // 判断结果集的下一条是否有数据，有数据返回true并指针下移，如果返回false指针不会下移
                // 获取当前这条数据的各个字段值
                int id = resultSet.getInt(1); // 获取第一个字段的值(从1开始)
                String name = resultSet.getString(2);
                String email = resultSet.getString(3);
                System.out.println(id + ": " + name + " " + email);
            }
        } catch (Exception e) {
            e.fillInStackTrace();
        } finally {
            // 关闭资源
            JDBCUtils.closeResource(connection, ps, resultSet);
        }
    }

🌊 实现通用的查询操作

💦 ORM编程思想

一个数据表对应一个Java类；
表中的一条记录对应Java类的一个对象；
表中的一个字段对应Java类的一个属性。

💦 Java与SQL对应数据类型

💦 针对customers表的通用查询操作

customers表对应的Java类

package preparedstatement;
import java.sql.Date;
public class Customer {
    private int id;
    private String name;
    private String email;
    private Date birth;
    @Override
    public String toString() {
        return "Customer{" + "id=" + id + ", name='" + name + '\'' + ", email='" + email + '\'' + ", birth=" + birth + '}';
    }
    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;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Date getBirth() {
        return birth;
    }
    public void setBirth(Date birth) {
        this.birth = birth;
    }
    public Customer() {
    }
    public Customer(int id, String name, String email, Date birth) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }
}

针对customers表的通用查询操作的方法

public ArrayList<Customer> queryForCustomers(String sql, Object ...args) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<Customer> customers = new ArrayList<>(); // 返回的数据对象
        try {
            // 获取数据库连接
            connection = JDBCUtils.getConnection();
            // 预编译sql获取PreparedStatement实例对象
            ps = connection.prepareStatement(sql);
            // 填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1, args[i]);
            }
            // 执行sql获取结果集
            rs = ps.executeQuery();
            // 获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            // 获取结果集的列数
            int columnCount = rsmd.getColumnCount();
            // 处理结果集
            while (rs.next()) { // 如果还有下一条数据
                // 创建表行数据对应的对象
                Customer customer = new Customer();
                // 获取当前这条数据的每个字段
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = rs.getObject(i + 1);
                    // 获取当前列的列名
                    String columnName = rsmd.getColumnName(i + 1);
                    // 给Customer对象指定的columnName属性赋值 通过反射
                    Field field = Customer.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(customer, columnValue); // 把customer对象的属性赋值为columnValue
                }
                customers.add(customer);
            }
        } catch (Exception e) {
            e.fillInStackTrace();
        } finally {
            // 关闭资源
            JDBCUtils.closeResource(connection, ps, rs);
        }
        return customers;
    }

@Test
    public void testQueryForCustomers() {
        String sql = "select id, name, email from customers where id=?";
        ArrayList<Customer> customers = queryForCustomers(sql, 13);
        for (int i = 0; i < customers.size(); i++) {
            System.out.println(customers.get(i));
        }
    }

💦 针对order表的通用查询操作

order表对应的Java类

package preparedstatement;
import java.sql.Date;
public class Order {
    private int orderId;
    private String orderName;
    private Date orderDate;
    public int getOrderId() {
        return orderId;
    }
    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }
    public String getOrderName() {
        return orderName;
    }
    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }
    public Date getOrderDate() {
        return orderDate;
    }
    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }
    public Order() {
    }
  @Override
    public String toString() {
        return "Order{" + "orderId=" + orderId + ", orderName='" + orderName + '\'' + ", orderDate=" + orderDate + '}';
    }
}

针对order表的通用查询操作的方法

// 针对与表的字段名和类的属性名不一致的情况，
  // 声明sql时使用类的属性名作为字段的别名，
  // 使用getColumnLabel获取列的别名
  // 如果没有取别名getColumnLabel获取的是列名
    public ArrayList<Order> queryForOrder(String sql, Object ...args) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<Order> orders = new ArrayList<>();
        try {
            connection = JDBCUtils.getConnection();
            ps = connection.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();
            while (rs.next()) {
                Order order = new Order();
                for (int i = 0; i < columnCount; i++) {
                    // 获取每个列的列值
                    Object columnValue = rs.getObject(i + 1);
                    // 获取列名
                    // String columnName = rsmd.getColumnName(i + 1);
                    // 获取列的别名
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    // 通过反射将对象指定列名columnName的属性赋值为columnValue
                    Field field = Order.class.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(order, columnValue); // 赋值
                }
                orders.add(order);
            }
        } catch (Exception e) {
            e.fillInStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, ps, rs);
        }
        return orders;
    }

@Test
    public void testQueryForOrder() {
        String sql = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id=?";
        ArrayList<Order> orders = queryForOrder(sql, 1);
        for (int i = 0; i < orders.size(); i++) {
            System.out.println(orders.get(i));
        }
    }

💦 查询过程图解

💦 针对不同表的通用查询操作

/**
     * 泛型方法
     * @param tClass 指定返回的数据对应的Class
     * @param sql
     * @param args 填充sql语句的参数
     * @return 返回查询结果
     * @param <T> 指定返回数据对应的类
     */
    public <T> ArrayList<T> query(Class<T> tClass, String sql, Object ...args) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<T> ts = new ArrayList<>(); // 用于存储查询结果数据
        try {
            connection = JDBCUtils.getConnection(); // 获取连接对象
            ps = connection.prepareStatement(sql); // 预编译sql获取prepareStatement对象
            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();
            while (rs.next()) {
                T t = tClass.newInstance(); // 实例化对应的对象
                for (int i = 0; i < columnCount; i++) {
                    // 获取每个列的列值
                    Object columnValue = rs.getObject(i + 1);
                    // 获取列的别名，没有别名获取列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    // 通过反射将对象指定列名columnName的属性赋值为columnValue
                    Field field = tClass.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue); // 赋值
                }
                ts.add(t);
            }
        } catch (Exception e) {
            e.fillInStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, ps, rs);
        }
        return ts;
    }
    @Test
    public void test() {
        String sql = "select id, name, email from customers where id < ?";
        ArrayList<Customer> customers = query(Customer.class, sql, 3);
        // System.out.println(customers);
        // for (Customer customer: customers) {
        //     System.out.println(customer);
        // }
        customers.forEach(System.out::println);
    }

🌊 PreparedStatement 解决SQL注入问题

@Test
    public void testLogin() {
        // SELECT user,password FROM user_table WHERE user = '1' or ' AND password = '=1 or '1' = '1'
        // String sql = "SELECT user,password FROM user_table WHERE user = '"+ user +"' AND password = '"+ password +"'";
        String sql = "SELECT user,password FROM user_table WHERE user = ? AND password = ?";
        // ArrayList<User> users = query(User.class, sql, "AA", "123456"); // 登录成功
        ArrayList<User> users = query(User.class, sql, "1' or ", "=1 or '1' = '1"); // 用户名不存在或密码错误
        if(users.size() > 0){
            System.out.println("登录成功");
        }else{
            System.out.println("用户名不存在或密码错误");
        }
    }
    /**
     * 泛型方法
     * @param tClass 指定返回的数据对应的Class
     * @param sql
     * @param args 填充sql语句的参数
     * @return 返回查询结果
     * @param <T> 指定返回数据对应的类
     */
    public <T> ArrayList<T> query(Class<T> tClass, String sql, Object ...args) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<T> ts = new ArrayList<>(); // 用于存储查询结果数据
        try {
            connection = JDBCUtils.getConnection(); // 获取连接对象
            ps = connection.prepareStatement(sql); // 预编译sql获取prepareStatement对象
            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();
            while (rs.next()) {
                T t = tClass.newInstance(); // 实例化对应的对象
                for (int i = 0; i < columnCount; i++) {
                    // 获取每个列的列值
                    Object columnValue = rs.getObject(i + 1);
                    // 获取列的别名，没有别名获取列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    // 通过反射将对象指定列名columnName的属性赋值为columnValue
                    Field field = tClass.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue); // 赋值
                }
                ts.add(t);
            }
        } catch (Exception e) {
            e.fillInStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, ps, rs);
        }
        return ts;
    }

由于PreparedStatement会先对sql语句进行预编译，上述登录的sql语句的条件子句为且关系，PreparedStatement对其预编译之后会认定其就为且关系，不会因为填充的数据而改变其逻辑关系，所以不会引发SQL注入问题。对于Statement，会根据拼接字符串的不同而发生条件子句逻辑关系的改变，所以会导致SQL注入。

PreparedStatement能够解决Statement的问题：

Statement的拼串
Statement的SQL注入问题
PreparedStatement可以操作BLob类型的数据，而Statement不行
PreparedStatement可以实现更高效的批量操作，Statement每次执行sql都需要进行sql校验，而PreparedStatement在预编译时进行sql校验，后续多次执行sql可以直接填充数据执行，不用再次进行校验。

🥽 PreparedStatement操作Blob类型的数据

🌊 MySQL BLOB类型

MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。

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

实际使用中根据需要存入的数据大小定义不同的BLOB类型。需要注意的是：如果存储的文件过大，数据库的性能会下降，所以实际情况中，一般将文件存储在专门的文件服务器，在数据库中存储的为文件对应的地址。

🌊 插入Blob类型的数据

@Test
    public void testInster() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into customers(name, email, birth, photo) values (?, ?, ?, ?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1, "ZS");
        ps.setObject(2, "ZS@qq.com");
        ps.setObject(3, "2001-11-11");
        // 使用文件输入流获取文件
        FileInputStream fis = new FileInputStream(new File("src\\preparedstatement\\1.jpg"));
        ps.setObject(4, fis);
        ps.execute();
        JDBCUtils.closeResource(connection, ps);
    }

如果在数据库表中指定了字段相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数：max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务。

Mysql8.0的my.ini文件在如下路径：C:\ProgramData\MySQL\MySQL Server 8.0

🌊 读取Blob类型的数据

@Test
    public void testQuery() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select id, name, email, birth, photo from customers where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1, 21);
        // 执行获取结果集
        ResultSet rs = ps.executeQuery();
        InputStream is = null;
        FileOutputStream fos = null;
        if (rs.next()) {
            // 方式1：
            //int id = rs.getInt(1);
            //String name = rs.getString(2);
            //String email = rs.getString(3);
            //Date birth = rs.getDate(3);
            // 方式2（建议）：
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String email = rs.getString("email");
            Date birth = rs.getDate("birth");
            System.out.println(id);
            System.out.println(name);
            System.out.println(email);
            System.out.println(birth);
            // 将blob类型的数据保存本地
            Blob photo = rs.getBlob("photo");
            is = photo.getBinaryStream(); // 读取blob数据到内存
            fos = new FileOutputStream("ZS.jpg");// 输出流
            // 写入文件
            byte[] buffer = new byte[1024];
            int len;
            while ((len = is.read(buffer)) != -1) {
                fos.write(buffer, 0, len);
            }
        }
        fos.close();
        is.close();
        JDBCUtils.closeResource(connection, ps, rs);
    }

🥽 PreparedStatement批量插入数据

goods的建表语句：

CREATE TABLE goods(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(25)
);

向goods表中插入20000条数据

🌊 使用PreparedStatement

@Test
    public void testInsert1() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into goods(name) values(?);";
        PreparedStatement ps = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i=0; i<20000; i++) {
            ps.setObject(1, "name_" + i);
            ps.execute();
        }
        long end = System.currentTimeMillis();
        System.out.println(end - start);
        JDBCUtils.closeResource(connection, ps);
    }

[Java]JDBC学习笔记(尚硅谷康师傅JDBC) （二）