13.4.3 使用 PreparedStatement执行SQL语句

发表于 2019-07-04 更新于 2020-02-07 分类于 1 疯狂Java讲义 (第4版) ，第13章 MySQL数据库与JDBC编程， 13.4 执行SQL语句的方式 Waline：本文字数： 7.1k 阅读时长 ≈ 6 分钟

13.4.3 使用 PreparedStatement执行SQL语句

如果经常需要反复执行一条结构相似的SQL语句,例如如下两条SQL语句:

1 2	insert into student_table values(null, '张三', 1); insert into student_table values(null, '李四', 2);

对于这两条SQL语句而言,它们的结构基本相似,只是执行插入时插入的值不同而已。对于这种情况,可以使用带占位符(?)参数的SQL语句来代替它:
insert into student_table values(null,?,?);
但Statement执行SQL语句时不允许使用问号占位符参数,而且这个问号占位符参数必须获得值后以执行。为了满足这种功能,JDBC提供了PreparedStatement接口,它是Statement接口的子接口,它可以预编译SQL语句,预编译后的SQL语句被存储在PreparedStatement对象中,然后可以使用该对象多次高效地执行该语句。简而言之,使用PreparedStatement比使用Statement的效率要高。
创建PreparedStatement对象使用Connection的prepareStatement()方法,该方法需要传入一个SQL字符串,该SQL字符串可以包含占位符参数。如下代码所示:

1	pstmt=conn.prepareStatement("insert into student_table values (null,?,1)");

PreparedStatement也提供了execute()、executeUpdate()、executeQuery()三个方法来执行SQL语句,不过这三个方法无须传入参数,因为PreparedStatement已存储了预编译的SQL语句。
使用PreparedStatement预编译SQL语句时,该SQL语句可以带占位符参数,因此在执行SQL语句之前必须为这些参数传入参数值, Preparedstatement提供了以下两种方式来传入参数值:

如果程序知道预编译SQL语句中各参数的类型(如参数类型为Xxx),则使用setXxx(int index, Xxx value)方法来传入参数值。
如果程序不清楚预编译SQL语句中各参数的类型,则可以使用setObject()方法来传入参数,由PreparedStatement来负责类型转换。

下面程序示范了使用Statement和PreparedStatement分别插入100条记录的对比。使用Statement需要传入100条SQL语句,但使用PreparedStatement则只需要传入1条预编译的SQL语句,然后100次为该PreparedStatement的参数设值即可.

import java.util.*;
import java.io.*;
import java.sql.*;

public class PreparedStatementTest
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile) throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
        // 1.加载驱动
        Class.forName(driver);
    }
    public void insertUseStatement() throws Exception
    {
        long start = System.currentTimeMillis();
        try (
                // 2.获取数据库连接
                Connection conn = DriverManager.getConnection(url, user, pass);
                // 3.使用Connection来创建一个Statment对象
                Statement stmt = conn.createStatement())
        {
            // 需要使用100条SQL语句来插入100条记录
            for (int i = 0; i < 100; i++)
            {
                stmt.executeUpdate("insert into student_table values("
                        + " null ,'姓名" + i + "' , 1)");
            }
            System.out.println(
                    "使用Statement费时:" + (System.currentTimeMillis() - start));
        }
    }
    public void insertUsePrepare() throws Exception
    {
        long start = System.currentTimeMillis();
        try (
                // 2.获取数据库连接
                Connection conn = DriverManager.getConnection(url, user, pass);
                // 3.使用Connection来创建一个PreparedStatement对象
                PreparedStatement pstmt = conn.prepareStatement(
                        "insert into student_table values(null,?,1)"))

        {
            // 100次为PreparedStatement的参数设值，就可以插入100条记录
            for (int i = 0; i < 100; i++)
            {
                pstmt.setString(1, "姓名" + i);
                pstmt.executeUpdate();
            }
            System.out.println("使用PreparedStatement费时:"
                    + (System.currentTimeMillis() - start));
        }
    }
    public static void main(String[] args) throws Exception
    {
        PreparedStatementTest pt = new PreparedStatementTest();
        pt.initParam("mysql.ini");
        pt.insertUseStatement();
        pt.insertUsePrepare();
    }
}

多次运行上面程序,可以发现使用PreparedStatement插入100条记录所用的时间比使用Statement插入100条记录所用的时间少,这表明**PreparedStatement的执行效率比Statement的执行效率高**。
除此之外,使用PreparedStatement还有一个优势——当SQL语句中要使用参数时,无须”拼接SQL字符串。而使用Statement则要”拼接”SQL字符串,这是相当容易出现错误的,尤其是当SQL语句中有多个字符串参数时。使用PreparedStatement则只需要使用问号占位符来代替这些参数即可,降低了编程复杂度。
使用PreparedStatement还有一个很好的作用—用于防止SQL注入(SQL注入是一个较常见的Cracker入侵方式,它利用SQL语句的漏洞来入侵)。
下面以一个简单的登录窗口为例来介绍这种SQL注入的结果。下面登录窗口包含两个文本框,个用于输入用户名,一个用于输入密码,系统根据用户输入与jdbc_test表里的记录进行匹配,如果找到相应记录则提示登录成功.

package jdbc.test;

import java.awt.BorderLayout;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JTextField;

public class LoginFrame
{
    private final String PROP_FILE = "mysql.ini";
    private String driver;
    // url是数据库的服务地址
    private String url;
    private String user;
    private String pass;
    // 登录界面的GUI组件
    private JFrame jf = new JFrame("登录");
    private JTextField userField = new JTextField(20);
    private JTextField passField = new JTextField(20);
    private JButton loginButton = new JButton("登录");
    public void init() throws Exception
    {
        Properties connProp = new Properties();
        connProp.load(new FileInputStream(PROP_FILE));
        driver = connProp.getProperty("driver");
        url = connProp.getProperty("url");
        user = connProp.getProperty("user");
        pass = connProp.getProperty("pass");
        // 加载驱动
        Class.forName(driver);
        // 为登录按钮添加事件监听器
        loginButton.addActionListener(e ->
        {
            // 登录成功则显示“登录成功”
            if (validate(userField.getText(), passField.getText()))
            {
                JOptionPane.showMessageDialog(jf, "登录成功");
            }
            // 否则显示“登录失败”
            else
            {
                JOptionPane.showMessageDialog(jf, "登录失败");
            }
        });
        jf.add(userField, BorderLayout.NORTH);
        jf.add(passField);
        jf.add(loginButton, BorderLayout.SOUTH);
        jf.pack();
        jf.setVisible(true);
    }
    private boolean validate(String userName, String userPass)
    {
        // 执行查询的SQL语句
        String sql = "select * from jdbc_test " + "where jdbc_name='" + userName
                + "' and jdbc_desc='" + userPass + "'";
        System.out.println(sql);
        try (Connection conn = DriverManager.getConnection(url, user, pass);
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql))
        {
            // 如果查询的ResultSet里有超过一条的记录，则登录成功
            if (rs.next())
            {
                return true;
            }
        } catch (Exception e)
        {
            e.printStackTrace();
        }
        return false;
    }

    // private boolean validate(String userName, String userPass)
    // {
    // try (Connection conn = DriverManager.getConnection(url, user, pass);
    // PreparedStatement pstmt = conn.prepareStatement(
    // "select * from jdbc_test where jdbc_name=? and jdbc_desc=?"))
    // {
    // pstmt.setString(1, userName);
    // pstmt.setString(2, userPass);
    // try (ResultSet rs = pstmt.executeQuery())
    // {
    // // 如果查询的ResultSet里有超过一条的记录，则登录成功
    // if (rs.next())
    // {
    // return true;
    // }
    // }
    // } catch (Exception e)
    // {
    // e.printStackTrace();
    // }
    // return false;
    // }

    public static void main(String[] args) throws Exception
    {
        new LoginFrame().init();
    }
}

运行上面程序,如果用户正常输入其用户名、密码当然没有问题,输入正确可以正常登录,输入错误将提示输入失败。但如果这个用户是一个Cracker,他可以在文本框中输入' or true or ',这个输入明显是不正确的,但当单击”登录”按钮后也会显示”登录成功”对话框。可以在程序运行的后台看到如下SOL语句:

1	select * from jdbc_test where jdbc_name='' or true or '' and jdbc_desc=''

看到这条SQL语句,读者应该不难明白为什么这样输入也可以显示”正常登录”对话框了,因为Cracker直接输入了true,而SQL把这个true当成了直接量。
如果把上面的validate()方法注释掉,然后取消另一个validate()方法的注释,换成使用PreparedStatement来执行验证.
这样,即使用户输入了:' or true or ',系统一样会显示”登录失败”对话框.
总体来看,使用PreparedStatement比使用Statement多了如下三个好处:

PreparedStatement预编译SQL语句,性能更好。
PreparedStatement无须”拼接”SQL语句,编程更简单。
PreparedStatement可以防止SQL注入,安全性更好。

基于以上三点,通常推荐避免使用Statement来执行SQL语句,改为使用PreparedStatement执行SQL语句.

注意

使用PreparedStatement执行带占位符参数的SQL语句时,SQL语句中的占位符参数只能代替普通值,不要使用占位符参数代替表名、列名等数据库对象,更不要用占位符参数来代替SQL语句中的Insert、 select等关键字。