PreparedStatement 和 Statement 的区别（推荐使用PreparedStatement）

PreparedStatement与Statement在使用时的区别：

1.Statement:

String sql=" ";

executeUpdate(sql)

2.

PreparedStatement:

String sql(可能存在占位符?)

在创建PreparedStatement 对象时，将sql预编译 prepareStatement(sql)

executeUpdate()

setXxx()替换占位符？

 

推荐使用PreparedStatement：原因如下：

　　1. 编码更加简便（避免了字符串的拼接）

String name = "zs" ;
int age = 23 ;

stmt:
String sql =" insert into student(stuno,stuname) values('"+name+"', "+age+" ) " ;
stmt.executeUpdate(sql);

pstmt:
String sql =" insert into student(stuno,stuname) values(?,?) " ;
pstmt = connection.prepareStatement(sql);//预编译SQL
pstmt.setString(1,name);
pstmt.setInt(2,age);

 

　　2. 提高性能(因为 有预编译操作，预编译只需要执行一次)

需要重复增加100条数

stmt://编译100次，执行100次
String sql =" insert into student(stuno,stuname) values('"+name+"', "+age+" ) " ;
for(100)
stmt.executeUpdate(sql);

pstmt://编译1次，执行100次
String sql =" insert into student(stuno,stuname) values(?,?) " ;
pstmt = connection.prepareStatement(sql);//预编译SQL
pstmt.setString(1,name);
pstmt.setInt(2,age);
for( 100){
pstmt.executeUpdate();
}

 

3.安全（可以有效防止 sql 注入）

sql 注入： 将客户输入的内容 和 开发人员的SQL语句 混为一体

stmt:存在被sql注入的风险

(例如输入 用户名：任意值 ' or 1=1 --

密码：任意值)

分析：

select count(*) from login where uname='任意值 ' or 1=1 --' and upwd ='任意值' ;

select count(*) from login where uname='任意值 ' or 1=1 ;

select count(*) from login ;


select count(*) from login where uname='"+name+"' and upwd ='"+pwd+"'

pstmt:有效防止sql注入

 

推荐使用PreparedStatement

 

代码对比

package jdbcproject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStatementDemo {
    private static final String URL = "jdbc:mysql://localhost:3306/mydatabase?serverTimezone=GMT%2B8";
    private static final String USERNAME = "root";
    private static final String PWD = "password";

    public static void update() throws ClassNotFoundException, SQLException {// 增删改
        // a. 导入驱动，加载具体的驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
        // b.与数据库建立连接
        Connection connection = DriverManager.getConnection(URL, USERNAME, PWD);
        // c.发送sql，执行增删改查
        Statement stmt = connection.createStatement();
        //增加  String sql = "insert into student values(2,'李四',21)";
        //修改  String sql = "update student set name='张三'  where id=1";
        //删除  
        String sql = "delete from student where id=1";
        int count = stmt.executeUpdate(sql);
        if (count > 0) {
            System.out.println("操作成功！");
        }
        stmt.close();
        connection.close();

    }
    public static void query() throws ClassNotFoundException, SQLException {// 增删改
        // a. 导入驱动，加载具体的驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
        // b.与数据库建立连接
        Connection connection = DriverManager.getConnection(URL, USERNAME, PWD);
        // c.发送sql，执行增删改[查]
        Statement stmt = connection.createStatement();
        char stuname='a';
        //模糊查询
        String sql = "select id,name,age from student where name like '%"+stuname+"%'";
        ResultSet rs=stmt.executeQuery(sql);
        //int count = stmt.executeUpdate(sql);
        while (rs.next()) {
            int id=rs.getInt("id");
            String name=rs.getString("name");
            int age=rs.getInt("age");
            System.out.println(id+"--"+name+"--"+age);
            
        }
        rs.close();
        stmt.close();
        connection.close();

    }
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
//        update();
        query();
    }
}

package jdbcproject;

import java.lang.invoke.StringConcatFactory;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCPreparedStatementDemo {
    private static final String URL = "jdbc:mysql://localhost:3306/mydatabase?serverTimezone=GMT%2B8";
    private static final String USERNAME = "root";
    private static final String PWD = "password";

    public static void update() throws ClassNotFoundException, SQLException {// 增删改
        // a. 导入驱动，加载具体的驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
        // b.与数据库建立连接
        Connection connection = DriverManager.getConnection(URL, USERNAME, PWD);
        // c.发送sql，执行增删改查
        //sql提前写
        String sql="insert into student values(?,?,?)";
        PreparedStatement pstmt = connection.prepareStatement(sql);//预编译
        pstmt.setInt(1, 5);
        pstmt.setString(2, "超凡");
        pstmt.setInt(3, 21);
        
        //增加  String sql = "insert into student values(2,'李四',21)";
        //修改  String sql = "update student set name='张三'  where id=1";
        //删除  String sql = "delete from student where id=1";
        //这里括号不用再写sql
        int count = pstmt.executeUpdate();
        if (count > 0) {
            System.out.println("操作成功！");
        }
        //后开先关，先开的后关，和栈类似
        pstmt.close();
        connection.close();

    }
    public static void query() throws ClassNotFoundException, SQLException {// 增删改
        // a. 导入驱动，加载具体的驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
        // b.与数据库建立连接
        Connection connection = DriverManager.getConnection(URL, USERNAME, PWD);
        // c.发送sql，执行增删改[查]
        //sql提前写
        char stuname='a';
        String sql = "select * from student where name like ?";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1, "%g%");
        //模糊查询
        
        ResultSet rs=pstmt.executeQuery();
        //int count = stmt.executeUpdate(sql);
        while (rs.next()) {
            int id=rs.getInt("id");
            String name=rs.getString("name");
            int age=rs.getInt("age");
            System.out.println(id+"--"+name+"--"+age);
            
        }
        //后开先关，先开的后关，和栈类似
        rs.close();
        pstmt.close();
        connection.close();

    }
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
//        update();
        query();
    }
}