下面简要说明一下他们的区别与联系：
联系：
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();
}
