Statement和PreparedStatement的区别与联系

下面简要说明一下他们的区别与联系：

联系：

1.PreParedStatement是Statement接口的子接口
2.PreParedStatement和Statement都可以实现对数据表的CRUD操作：增删改查

区别：

1.PreparedStatement 可以规避 Statement弊端：①拼串 ②sql注入问题
2.PreparedStatement 可以实现操作Blob类型的数据
3.PreparedStatement 可以实现相对于Statement的高效的批量插入

Statement的弊端

①拼串②sql注入问题

public static void testLogin() {Scanner scan = new Scanner(System.in);System.out.print("用户名：");String userName = scan.nextLine();System.out.print("密   码：");String password = scan.nextLine();String sql = "select user, password from user_table where user = '" + userName + "' and password = '" + password + "'";User user = get(sql, User.class);if(user != null){System.out.println("登陆成功!");}else{System.out.println("用户名或密码错误！");}}

get()为获取查询到的用户。

在写代码的过程中，拼串也是很麻烦的，需要添加很多" "，也正是因为拼串这个问题，从而导致出现了sql注入的问题。

就像这样：

可以看出这并不是一个合理的用户名和密码，将图片中的用户名和密码的字符串填入到Statement的sql语句中会变成：
select user, password from user_table where user = ’ 1 ’ or ’ 1 ’ and password =’ 1 ’ or ’ 1 ’ = ’ 1 ’
完全曲解了我们的本意

通过PreparedStatement来解决以上弊端

public static void testLogin() {Connection connection = null;PreparedStatement ps = null;ResultSet resultSet = null;try {Scanner scan = new Scanner(System.in);System.out.print("用户名：");String userName = scan.nextLine();System.out.print("密   码：");String password = scan.nextLine();connection = JDBCUtils.getConnection();String sql = "select user, password from user_table where user = ? and password = ? ";//预编译sql语句ps = connection.prepareStatement(sql);//填充占位符ps.setString(1,"");ps.setString(2,"");//执行查询,获取结果集resultSet = ps.executeQuery();User user = null;if(resultSet.next()){String username = resultSet.getString(1);String passwd = resultSet.getString(2);user = new User(username,passwd);}if(user != null){System.out.println("登录成功");} else {System.out.println("登录失败");}}catch (Exception e){e.printStackTrace();}finally{JDBCUtils.close(connection,ps,resultSet);}}

通过PreparedStatement 实现操作Blob类型的数据

    public static void insertBlob(){Connection connection = null;PreparedStatement preparedStatement = null;FileInputStream inputStream = null;try{connection = JDBCUtils.getConnection();String sql = "insert into customers(name,email,birth,photo) value(?,?,?,?)";preparedStatement = connection.prepareStatement(sql);preparedStatement.setObject(1,"啦啦");preparedStatement.setObject(2,"lala@163.com");preparedStatement.setObject(3,"2019-07-14");inputStream = new FileInputStream("MAC1.png");preparedStatement.setBlob(4,inputStream);preparedStatement.execute();}catch (Exception e){e.printStackTrace();}finally{JDBCUtils.close(connection,preparedStatement,null);try{if(inputStream != null){inputStream.close();}}catch (IOException e){e.printStackTrace();}}}

    public static void downloadBlob(){Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;InputStream binaryStream = null;FileOutputStream fos = null;try{connection = JDBCUtils.getConnection();String sql = "select id,name,email,birth,photo from customers where id = ?";preparedStatement = connection.prepareStatement(sql);preparedStatement.setInt(1,20);resultSet = preparedStatement.executeQuery();if(resultSet.next()){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);System.out.println(customer);Blob photo = resultSet.getBlob("photo");//通过Blob类型的photo获取一个二进制流binaryStream = photo.getBinaryStream();fos = new FileOutputStream("mac.png");byte[] buffer = new byte[1024];int length;while((length = binaryStream.read(buffer)) != -1){fos.write(buffer,0,length);}}}catch (Exception e){e.printStackTrace();}finally{JDBCUtils.close(connection,preparedStatement,resultSet);try{if(binaryStream != null){binaryStream.close();}if(fos != null){fos.close();}}catch (IOException e){e.printStackTrace();}}}

通过PreparedStatement 实现高效的批量插入

public static void test()throws Exception{Connection connection = JDBCUtils.getConnection();/** 将提交方式设置为不自动提交* 因为每次执行一条sql语句时,数据库会帮我们自动提交* 当批量插入的时候,这个操作会非常浪费时间,特别是提交的数据量特别大的时候*/connection.setAutoCommit(false);String sql = "insert into goods(name) value(?)";PreparedStatement ps = connection.prepareStatement(sql);for(int i = 1;i <= 20000;i++){ps.setString(1, "name_"+i);//由于每次传来一条sql语句都一一执行比较浪费时间//下面这个操作会为我们攒一些sql语句,攒够一定数量的时候再提交,会节省一些时间ps.addBatch();if(i % 500 == 0){//当攒够500条数据时,提交ps.executeBatch();//因为之前攒的数据已经提交了,现在要清除缓存,清空之前攒的数据ps.clearBatch();}}connection.commit();}