Statement接口和它的子接口PreparedStatement

Statement接口实现CRUD操作（了解）

增、删、改；查询

/*
 * 实现对数据表中数据的增删改操作
 */
public void update(String sql) {
    Connection connection = null;
    Statement statement = null;
    try {
        //1.获取数据库的连接
        connection = JDBCUtils.getConnection();
        //2.创建一个Statement的实例
        statement = connection.createStatement();
        //3.根据提供的sql语句，执行
        statement.execute(sql);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //4.资源的关闭
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
/*
 * 实现对数据表的查询操作。需要使用结果集：ResultSet
 *
 */
public <T> T get(String sql, Class<T> clazz) {// (sql, Customer.class)
    T t = null;
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        connection = JDBCUtils.getConnection();
        statement = connection.createStatement();
        resultSet = statement.executeQuery(sql);
        // 获取结果集的元数据
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        // 获取结果集的列数
        int columnCount = resultSetMetaData.getColumnCount();
        if (resultSet.next()) {
            t = clazz.newInstance();
            for (int i = 0; i < columnCount; i++) {
                // 1. 获取列的别名
                // 注意：获取结果集中（相当于数据表）列的名称（别名）
                String columnName = resultSetMetaData.getColumnLabel(i + 1);
                // 2. 根据列名获取对应数据表中的数据
                Object columnVal = resultSet.getObject(columnName);
                // 3. 将数据表中得到的数据，封装进对象
                // 注意：反射根据Java中类的属性获取Field对象
                Field field = clazz.getDeclaredField(columnName);
                field.setAccessible(true);
                field.set(t, columnVal);
            }
            return t;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 关闭资源
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    return null;
}

Statement使用的弊端

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

• SQL注入问题是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的SQL语句端或命令，从而利用系统的SQL引擎完成恶意行为的做法

拼串

String sql = "insert into customers(name,email,birth)values('" + name +"','"+email+"','"+birth+"')";

SQL注入：

String sql = "SELECT user,password FROM user_table WHERE user = '"+ user + "' AND password = '" + password + "'"
//原意：SELECT user,password FROM user_table WHERE user = '？' AND password = '？'
String user = "1' or ";
String password = "=1 or '1' = '1";
//执行：SELECT user,password FROM user_table WHERE user = '1' or ' AND password = '=1 or '1' = '1'

其他问题：
Statement没办法操作Blob类型变量
Statement实现批量插入时，效率较低

PreparedStatement替换Statement实现CRUD操作

1.PreparedStatement的理解：

① PreparedStatement 是Statement的子接口
② An object that represents a precompiled SQL statement.
③ 可以解决Statement的sql注入问题，拼串问题

2.使用PreparedStatement实现通用的增、删、改的方法：

通用的增删改操作

public void update(String sql,Object ...args){//sql中占位符的个数与可变形参的长度相同！
	Connection connection = null;
	PreparedStatement preparedStatement = null;
	try {
		//1.获取数据库的连接
		connection = JDBCUtils.getConnection();
		//2.预编译sql语句，返回PreparedStatement的实例
		preparedStatement = connection.prepareStatement(sql);
		//3.填充占位符
		for(int i = 0;i < args.length;i++){
			preparedStatement.setObject(i + 1, args[i]);//小心参数声明错误！！
		}
		//4.执行
		preparedStatement.execute();
	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		//5.资源的关闭
		JDBCUtils.closeResource(connection, preparedStatement);
	}
}

3.使用PreparedStatement实现通用的查询操作：version 1.0

public <T> T getInstance(Class<T> clazz,String sql,Object ...args){
	Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        connection = JDBCUtils.getConnection();
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            preparedStatement.setObject(i + 1,args[i]);
        }
        resultSet = preparedStatement.executeQuery();
 		// 获取结果集的元数据 :ResultSetMetaData
        ResultSetMetaData metaData = resultSet.getMetaData();
        // 通过ResultSetMetaData获取结果集中的列数
        int columnCount = metaData.getColumnCount();
 
        if (resultSet.next()){
            T t = clazz.newInstance();
            // 处理结果集一行数据中的每一个列
            for (int i = 0; i < columnCount; i++) {
            	// 获取列值
                Object columnValue = resultSet.getObject(i + 1);
                // 获取每个列的列名
                String columnLabel = metaData.getColumnLabel(i + 1);
                // 给t对象指定的columnName属性，赋值为columValue：通过反射
                Field field = clazz.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t,columnValue);
            }
            return t;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
       JDBCUtils.closeResource(connection,preparedStatement,resultSet);
    }
    return null;
}
public <T> List<T> getForList(Class<T> clazz, String sql, Object ...args){
	Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        connection = JDBCUtils.getConnection();
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            preparedStatement.setObject(i + 1,args[i]);
        }
        resultSet = preparedStatement.executeQuery();
		// 获取结果集的元数据 :ResultSetMetaData
        ResultSetMetaData metaData = resultSet.getMetaData();
        // 通过ResultSetMetaData获取结果集中的列数
        int columnCount = metaData.getColumnCount();
        //创建集合对象
        ArrayList<T> list = new ArrayList<>();
        while (resultSet.next()){
            T t = clazz.newInstance();
            // 处理结果集一行数据中的每一个列:给t对象指定的属性赋值
            for (int i = 0; i < columnCount; i++) {
            	// 获取列值
                Object columnValue = resultSet.getObject(i + 1);
                // 获取每个列的列名
                String columnLabel = metaData.getColumnLabel(i + 1);
                // 给t对象指定的columnName属性，赋值为columValue：通过反射
                Field field = clazz.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t,columnValue);
            }
            list.add(t);
        }
        return list;
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
       JDBCUtils.closeResource(connection,preparedStatement,resultSet);
	}
	return null;
}

【总结】
两种思想：
1.面向接口编程的思想
2.ORM编程思想：（object relational mapping）

• 一个数据表对应一个java类
• 表中的一条记录对应java类的一个对象
• 表中的一个字段对应java类的一个属性

两种技术：

1.使用结果集的元数据：ResultSetMetaData

getColumnCount():获取列数
getColumnLabel():获取列的别名
说明：如果sql中没给字段其别名，getColumnLabel()获取的就是列名

2.反射的使用（①创建对应的运行时类的对象 ② 在运行时，动态的调用指定的运行时类的属性、方法）

PreparedStatement操作Blob类型的变量

PreparedStatement可以操作Blob类型的变量。
写入操作的方法：setBlob(InputStream is);
读取操作的方法：

Blob blob = getBlob(int index);
InputStream is = blob.getBinaryStream();

具体的insert:

public void testInsert(){
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        connection = JDBCUtils.getConnection();
        String sql = "insert into test(name,email,birth,photo)values(?,?,?,?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,"张三");
        preparedStatement.setObject(2,"zangsan@**.com");
        preparedStatement.setObject(3,"1000-01-01");
        FileInputStream is = new FileInputStream(new File("张三.jpg"));
        preparedStatement.setBlob(4,is);
        preparedStatement.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(connection,preparedStatement);
    }
}

具体的query:

public void testQuery(){
    InputStream is = null;
    FileOutputStream fos = null;
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        connection = JDBCUtils.getConnection();
        String sql = "select id,name,email,birth,photo from customers where id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,22);
        resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String email = resultSet.getString("email");
            Date birth = resultSet.getDate("birth");
            Custormer custormer = new Custormer(id, name, email, birth);
            System.out.println(custormer);

            Blob photo = resultSet.getBlob("photo");

            is = photo.getBinaryStream();
            fos = new FileOutputStream("nice.jpg");
            byte[] buffer = new byte[1024];
            int len;
            while ((len = is.read(buffer)) != -1){
                fos.write(buffer,0,len);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (is != null){
                is.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            if (fos != null){
                fos.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        JDBCUtils.closeResource(connection,preparedStatement,resultSet);
    }
}

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

PreparedStatement实现高效的批量插入

基础版
插入20000条数据耗时21202毫秒

@Test
public void testInsert1(){
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        long start = System.currentTimeMillis();
        connection = JDBCUtils.getConnection();
        String sql = "insert into goods(name)values(?)";
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 1; i <= 20000; i++) {
            preparedStatement.setString(1,"name_" + i);
            preparedStatement.execute();
        }
        long end = System.currentTimeMillis();
        System.out.println("花费的时间为"+ (end - start));//20000:21202
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(connection,preparedStatement);
    }
}

进阶版
插入20000条数据耗时391毫秒，插入1000000条数据耗时4734毫秒
1.addBatch()、executeBatch()、clearBatch()
2.mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。?rewriteBatchedStatements=true 写在配置文件的url后面
3.使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar

@Test
public void testInsert2(){
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        long start = System.currentTimeMillis();
        connection = JDBCUtils.getConnection();
        String sql = "insert into goods(name)values(?)";
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 1; i <= 1000000; i++) {
            preparedStatement.setString(1,"name_" + i);
            preparedStatement.addBatch();
            if (i % 500 == 0){
                preparedStatement.execute();
                preparedStatement.clearBatch();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("花费的时间为"+ (end - start));//20000:391//1000000：4734
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(connection,preparedStatement);
    }
}

最终版
插入20000条数据耗时309毫秒，插入1000000条数据耗时988毫秒
设置连接不允许自动提交数据

@Test
public void testInsert3(){
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        long start = System.currentTimeMillis();
        connection = JDBCUtils.getConnection();
        connection.setAutoCommit(false);
        String sql = "insert into goods(name)values(?)";
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 1; i <= 1000000; i++) {
            preparedStatement.setString(1,"name_" + i);
            preparedStatement.addBatch();
            if (i % 500 == 0){
                preparedStatement.execute();
                preparedStatement.clearBatch();
            }
        }
        connection.commit();
        long end = System.currentTimeMillis();
        System.out.println("花费的时间为"+ (end - start));//20000:309//1000000：988
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeResource(connection,preparedStatement);
    }
}

总结：PreparedStatement与Statement的异同？
① 指出二者的关系？ 接口 与 子接口的关系
② 开发中，PreparedStatement替换Statement
③ An object that represents a precompiled SQL statement.

• PreparedStatement能最大可能提高性能

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

• PreparedStatement可以防止SQL注入