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();
	}