statment和preparedStatement
Statement使用的注意事项
statement的作用是执行一段静态的sql语句，并返回处理的结果集对象。但是statement存在一定的弊端：
①sql语句需要传递参数时，我们需要对sql语句进行拼接，这样会很麻烦，影响我们的开发效率。
②使用statement执行sql语句时会存在sql注入问题，简单来说就是利用没有对用户输入的数据进行检查，利用非法的sql语句完成恶意行为的做法
下面写了一个简单的登录例子，用来测试statement存在的sql注入问题。
正常访问数据库时：
@Test public void queryDataByStatement() { /* 简单的登录模块测试statement的弊端 */ Scanner scanner = new Scanner(System.in); System.out.print("请输入用户账号："); String userNum = scanner.nextLine(); System.out.print("请输入用户密码："); String password = scanner.nextLine(); Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { //1.获取数据库的连接:使用自定义工具类 connection = MyJDBCUtils.getConnection(); //2.创建一个statement实例 statement = connection.createStatement(); //3.创建sql语句：此处需要对sql语句进行拼串操作，略微麻烦 String sql="select user,password from user_table where user='"+userNum+"' and password='"+password+"'"; //4.执行sql语句 resultSet = statement.executeQuery(sql); //5.对返回结果进行简单处理 if (resultSet.next()) System.out.println("登录成功！！！"); else System.out.println("登录失败！！！"); //6.关闭数据库的连接,此时statement和结果集也需要被关闭：使用自定义工具类 } catch (Exception e) { e.printStackTrace(); } finally { MyJDBCUtils.closeConnection(connection,statement,resultSet); } }
返回的结果是正常的：
当恶意访问数据库时：
@Test
public void queryDataByStatement() {
/*
简单的登录模块测试statement的弊端
*/
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户账号：");
String userNum = scanner.nextLine();
System.out.print("请输入用户密码：");
String password = scanner.nextLine();
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//1.获取数据库的连接:使用自定义工具类
connection = MyJDBCUtils.getConnection();
//2.创建一个statement实例
statement = connection.createStatement();
//3.创建sql语句：此处需要对sql语句进行拼串操作，略微麻烦
String sql="select user,password from user_table where user = '"+userNum+" 'and password = '"+password+"'";
//4.执行sql语句
resultSet = statement.executeQuery(sql);
//5.对返回结果进行简单处理
if (resultSet.next())
System.out.println("登录成功！！！");
else
System.out.println("登录失败！！！");
//6.关闭数据库的连接,此时statement和结果集也需要被关闭：使用自定义工具类
} catch (Exception e) {
e.printStackTrace();
} finally {
MyJDBCUtils.closeConnection(connection,statement,resultSet);
}
执行结果为：此时账号和密码明显不对，但是却能登陆成功。
之所以出现这种情况，是因为statement没有对sql语句进行事先的编译，我们传入什么，它就会向数据库发送什么数据，当账号和密码是上图中的情况时，sql语句实际为下图的情况，这个就叫做sql注入
PreparedStatement使用注意事项
为了解决statement中sql注入的问题，我们需要使用preparedStatement来替换原有的statement。
preparedStatement是statement的一个子接口，它的好处是可以对sql语句进行预编译，在创建preparedStatement实例时已经知道了自己要执行的sql语句是什么
使用preparedStatement完成对数据库的增删改查操作
1.使用prepareStatement向user表中添加一条数据
@Test public void addUserByPre() { Connection connection = null; PreparedStatement preparedStatement = null; try { //1.获取数据库的连接 connection = MyJDBCUtils.getConnection(); //2.创建sql语句:此时数据库中设计的主键id是自增的，我们可以不用主动添加 //?此时代表占位符，表明你将要传递的参数，有几个？代表需要传递几个参数 String sql="insert into `user`(name,password,address,phone) values(?,?,?,?)"; //3.创建preparedStatement对象 preparedStatement = connection.prepareStatement(sql); //4.注入占位符（ // 两个参数，第一个为需要注入的占位符的下标，第二个参数为具体注入的内容.这里需要注意的是下标是从1开始的而不是0）。 preparedStatement.setString(1,"王宝强"); preparedStatement.setString(2,"123456"); preparedStatement.setString(3,"河北省秦皇岛市"); preparedStatement.setString(4,"12345678910"); //5.执行相关操作 preparedStatement.execute(); } catch (Exception e) { e.printStackTrace(); } finally { //6.关闭相应连接 MyJDBCUtils.closeConnection(connection,preparedStatement); } }
2. 使用preparedStatement修改user表中的某条数据
@Test public void updateUserByPre() { Connection connection = null; PreparedStatement preparedStatement = null; try { //1.获取数据库连接 connection = MyJDBCUtils.getConnection(); //2.创建sql语句 String sql="update user set name = ? where id = ?"; //3.创建preparedStatement对象 preparedStatement = connection.prepareStatement(sql); //4.填充占位符 preparedStatement.setString(1,"许三多"); preparedStatement.setInt(2,8); //5.执行操作 int i = preparedStatement.executeUpdate(); if (i != 0) System.out.println("修改成功"); else System.out.println("修改失败"); } catch (Exception e) { e.printStackTrace(); } finally { //6.关闭资源 MyJDBCUtils.closeConnection(connection,preparedStatement); } }
3.使用preparedStatement删除user表中的一条数据
@Test public void deleteUserByPre(){ Connection connection = null; PreparedStatement preparedStatement = null; try { //1.获取数据库连接 connection = MyJDBCUtils.getConnection(); //2.创建sql语句 String sql="delete from user where id = ?"; //3.创建preparedStatement对象 preparedStatement = connection.prepareStatement(sql); //4.填充占位符 preparedStatement.setInt(1,6); //5.执行操作 int i = preparedStatement.executeUpdate(); if (i != 0) System.out.println("删除成功"); else System.out.println("删除失败"); } catch (Exception e) { e.printStackTrace(); } finally { //6.关闭资源 MyJDBCUtils.closeConnection(connection,preparedStatement); } }
4.观察代码可以看出来，增删改三种方法的代码是具有一定的重复性的，唯一的区别无非就是sql语句和占位符的不同，因此我们可以考虑将三种方法封装为同一个方法，调用的时候只需要传递sql语句和占位符即可。代码如下（可自行测试，这里就不再写测试代码了）
public static void updateDataBase(String sql,Object ...args) { Connection connection = null; PreparedStatement preparedStatement = null; try { //1.获取数据库的连接 connection = getConnection(); //2.创建sql语句,此步骤可直接使用传递进来的sql语句 //3.创建preparedStatement对象 preparedStatement = connection.prepareStatement(sql); //4.填充占位符 /* 1.首先要获取占位符的个数，因为可变形参的个数就是占位符的个数，所以只需要获取args的长度即可 2.填充占位符，使用for循环来做，需要注意的是下标的问题 */ for (int i = 0; i < args.length; i++) { preparedStatement.setObject(i+1,args[i]); } //5.执行操作 int i = preparedStatement.executeUpdate(); if (i != 0) System.out.println("此次操作成功！"); else System.out.println("此次操作失败！"); } catch (Exception e) { e.printStackTrace(); } finally { //6.关闭资源 closeConnection(connection,preparedStatement); } }
5.使用preparedStatement查询user表中的一条记录（查询和增删改是不同的，因为查询需要有返回的结果集）
public User queryUser(String sql,Object ...args) { Connection connection = null; PreparedStatement prepareStatement = null; ResultSet resultSet = null; try { //1.获取数据库连接 connection = MyJDBCUtils.getConnection(); //2.创建sql语句 //3.创建preparedStatement对象 prepareStatement = connection.prepareStatement(sql); //4.填充占位符 for (int i = 0; i < args.length; i++) { prepareStatement.setObject(i+1,args[i]); } //5.执行操作 resultSet = prepareStatement.executeQuery(); //6.将查询出来的数据封装成为一个对象 //1.获取一个元数据对象 ResultSetMetaData metaData = resultSet.getMetaData(); //2.通过元数据对象来获取该条数据中一共有多少列 int columnCount = metaData.getColumnCount(); if (resultSet.next()){ /* resultSet.next()有些类似与迭代器中的hashNext()和next()的结合体 在迭代器中，hasNext()的作用是判断下一个位置是否为空，next()如果下一个位置不为空，指针下移并且返回当前对象，如果为空，则结束操作 而resultSet.next()的作用是判断下一个位置是否为空，并且指针下移，返回的是Boolean值 */ //3.创建一个对象实体 User user = new User(); //如何将数据封装进一个JavaBean中呢？此时并不知道取出的元素具体是什么类型的！ //在resultSet中提供了一个方法用来获取查询到的元数据（元数据：修饰查询出来数据的数据，可以参考元注解的概念）， // 4.使用元数据来获取当前这一条数据的每一列的列名和对应的列值 for (int i = 0; i < columnCount; i++) { String columnName = metaData.getColumnName(i + 1); Object columnValue = resultSet.getObject(i + 1); //5.使用反射技术动态的为bean对象中的属性赋值 Field declaredField = User.class.getDeclaredField(columnName); declaredField.setAccessible(true); declaredField.set(user,columnValue); } //System.out.println(user); return user; } } catch (Exception e) { e.printStackTrace(); } finally { //6.关闭资源 MyJDBCUtils.closeConnection(connection,prepareStatement,resultSet); } return null; }
这个时候需要提供一个对应的JavaBean实例
6.同样的，我们也可以封装一个函数用来获取不同的表中的单条数据
public static <T> T getBeanByPre(Class<T> clazz,String sql,Object ...args) { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { //1.获取到数据库的连接 connection = getConnection(); //2.创建一个preparedStatement实例 preparedStatement = connection.prepareStatement(sql); //3.填充占位符 for (int i = 0; i < args.length; i++) { preparedStatement.setObject(i+1,args[i]); } //4.执行操作 resultSet = preparedStatement.executeQuery(); //5.获取查询记录的元数据 ResultSetMetaData metaData = resultSet.getMetaData(); //6.获取查询记录中的列数 int columnCount = metaData.getColumnCount(); if (resultSet.next()){ //7.使用反射创建一个bean是咧 T t = clazz.newInstance(); for (int i = 0; i < columnCount; i++) { //8.获取到每一列的别名 String columnLabel = metaData.getColumnLabel(i + 1); //9.获取到每一列的值 Object columnValue = resultSet.getObject(i + 1); //10.使用反射为bean中的属性赋值 Field field = clazz.getDeclaredField(columnLabel); field.setAccessible(true); field.set(t,columnValue); } return t; } } catch (Exception e) { e.printStackTrace(); } finally { //11.关闭资源 closeConnection(connection,preparedStatement,resultSet); } return null; }
7.我们也可以封装一个函数用来获取不同的表中的多条数据
public static <T>List<T> getBeanListByPre(Class<T> clazz, String sql, Object ...args) { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { //1.获取到数据库的连接 connection = getConnection(); //2.创建一个preparedStatement实例 preparedStatement = connection.prepareStatement(sql); //3.填充占位符 for (int i = 0; i < args.length; i++) { preparedStatement.setObject(i+1,args[i]); } //4.执行操作 resultSet = preparedStatement.executeQuery(); //5.获取查询记录的元数据 ResultSetMetaData metaData = resultSet.getMetaData(); //6.获取查询记录中的列数 int columnCount = metaData.getColumnCount(); //7.创建list集合 ArrayList<T> list = new ArrayList<>(); while (resultSet.next()){ //7.使用反射创建一个bean实例 T t = clazz.newInstance(); for (int i = 0; i < columnCount; i++) { //8.获取到每一列的别名 String columnLabel = metaData.getColumnLabel(i + 1); //9.获取到每一列的值 Object columnValue = resultSet.getObject(i + 1); //10.使用反射为bean中的属性赋值 Field field = clazz.getDeclaredField(columnLabel); field.setAccessible(true); field.set(t,columnValue); } list.add(t); } return list; } catch (Exception e) { e.printStackTrace(); } finally { //11.关闭资源 closeConnection(connection,preparedStatement,resultSet); } return null; }
之所以preparedStatement可以解决sql注入问题，是因为它的预编译sql语句的功能，在我们生成preparedStatement对象时，已经使用过了sql语句。在占位符还没有填充之前，它就已经对sql语句进行了解析。对于刚开始的登录测试来说，创建preparedStatement对象时，sql语句表示的就是user = ？and password = ？，无论传入什么值，它都会认为是user和password的值。以此避免了sql注入问题
preparedStatement同时还可以操作Blob类型的数据，可以更高效的实现批量操作
preparedStatement和statement的对比
1.代码的可读性和可维护性：
在statement中，sql语句需要我们主动的去拼接，这样在可读性的体验上是非常差的，同时维护起来也是十分的不方便。在preparedStatement中，对于参数的传递我们使用占位符来替代，这样大大的提高了代码的可读性，同时维护起来也不容易出错。
2.preparedStatement能最大可能地提高性能：
（1）DBServer会对预编译的语句提供性能的优化。因为预编译语句有可能会被重复的调用，所以语句在被DBServer的编译器编译之后的执行代码会被缓存下来，那么下次调用时，只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的预编译语句中就会得到执行。
（2）在statement语句中，即使是相同操作但是因为数据内容不一样，所以整个语句本身不能匹配，不存在缓存语句的意义，这样每执行一次都要对传入的语句编译执行一次。
3.preparestatement还可以有效的防止sql注入问题。
批量插入的对比
使用statement进行批量插入
@Test public void statementInsert() { Connection connection = null; Statement statement = null; try { //1.获取数据库连接 connection = MyJDBCUtils.getConnection(); //2.创建statement对象 statement = connection.createStatement(); //3.模拟演示批量插入操作 long startTime = System.currentTimeMillis(); for (int i = 0; i < 2000; i++) { //4.提供sql语句 String sql = "insert into goods(name) values('name-"+i+"')"; statement.executeUpdate(sql); } long endTime = System.currentTimeMillis(); System.out.println("statement批量插入所需要的时间为："+(endTime-startTime)); } catch (Exception e) { e.printStackTrace(); } finally { MyJDBCUtils.closeConnection(connection,statement); } }
所花费的时间为：
使用prepareStatement实现批量插入操作
@Test public void preparedStatementInsert() { Connection connection = null; PreparedStatement preparedStatement = null; try { //1.获取数据库连接 connection = MyJDBCUtils.getConnection(); //2.提供sql语句 String sql = "insert into goods(name) values(?)"; //3.创建preparedStatement对象 preparedStatement = connection.prepareStatement(sql); //4.批量插入 long startTime = System.currentTimeMillis(); for (int i = 0; i < 2000; i++) { //5.填充占位符 preparedStatement.setString(1,"name"+i); preparedStatement.executeUpdate(); } long endTime = System.currentTimeMillis(); System.out.println("preparedStatement执行批量插入所需要的时间为："+(endTime-startTime)); } catch (Exception e) { e.printStackTrace(); } finally { //6.关闭操作 MyJDBCUtils.closeConnection(connection,preparedStatement); } }
所花费的时间为
使用batch相关操作提高批量插入的效率：mysql默认是关闭批处理的，我们需要在url中添加参数来开启批处理的操作。?rewriteBatchedStatements=true
@Test public void preparedStatementInsert1() { Connection connection = null; PreparedStatement preparedStatement = null; try { //1.获取数据库连接 connection = MyJDBCUtils.getConnection(); //2.提供sql语句 String sql = "insert into goods(name) values(?)"; //3.创建preparedStatement对象 preparedStatement = connection.prepareStatement(sql); //4.批量插入 long startTime = System.currentTimeMillis(); for (int i = 0; i <= 2000; i++) { //5.填充占位符 preparedStatement.setString(1,"name"+i); //6.在填充占位符之后不立刻提交，而是积累到一定数量之后一起提交 //1.攒sql preparedStatement.addBatch(); if (i % 500 == 0) { //2.提交积攒的500条sql语句 preparedStatement.executeBatch(); //3.清空batch preparedStatement.clearBatch(); } } long endTime = System.currentTimeMillis(); System.out.println("preparedStatement中使用batch执行批量插入所需要的时间为："+(endTime-startTime)); } catch (Exception e) { e.printStackTrace(); } finally { //6.关闭操作 MyJDBCUtils.closeConnection(connection,preparedStatement); } }
所花费的时间为：
批量提交的方式四：默认情况下，数据库中的DML操作每执行一次就会自动提交，也就是说在上述的第三种方式中，每500次都会重新执行一次insert，意味着每500次都会提交一次数据然后将数据写入到数据库，写入操作也是会花费一定的时间的，如果数据量小可能看不出来，但是如果是百万条数据的话，时间方面的差异就体现出出来了。对此，我们可以关闭数据库的默认提交操作，等把数据全部读入之后在进行统一的提交
@Test public void preparedStatementInsert2() { Connection connection = null; PreparedStatement preparedStatement = null; try { //1.获取数据库连接 connection = MyJDBCUtils.getConnection(); //关闭数据库的自动提交功能 connection.setAutoCommit(false); //2.提供sql语句 String sql = "insert into goods(name) values(?)"; //3.创建preparedStatement对象 preparedStatement = connection.prepareStatement(sql); //4.批量插入 long startTime = System.currentTimeMillis(); for (int i = 0; i <= 2000; i++) { //5.填充占位符 preparedStatement.setString(1,"name"+i); //6.在填充占位符之后不立刻提交，而是积累到一定数量之后一起提交 //1.攒sql preparedStatement.addBatch(); if (i % 500 == 0) { //2.提交积攒的500条sql语句 preparedStatement.executeBatch(); //3.清空batch preparedStatement.clearBatch(); } } //对数据进行统一的提交 connection.commit(); long endTime = System.currentTimeMillis(); System.out.println("preparedStatement中使用batch执行批量插入所需要的时间为："+(endTime-startTime)); } catch (Exception e) { e.printStackTrace(); } finally { //6.关闭操作 MyJDBCUtils.closeConnection(connection,preparedStatement); } }
所花费的时间为：
