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

PreparedStatement与Statement在使用时的区别：

1.Statement:

String sql=" ";

executeUpdate(sql)

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

    }

}

JDBCStatementDemo

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

    }

}

JDBCPreparedStatementDemo

巴特西

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

最新文章

热门文章