JDBC---PreparedStatement操作

版权声明：本文为小斑马伟原创文章，转载请注明出处！

上篇播客介绍了SQL注入问题。本播客将介绍PreparedStatement来解决该问题。PreparedStatement：

• 1 可以通过调用 Connection 对象的 preparedStatement() 方法获取 PreparedStatement 对象。

• 2 PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句。

• 3 PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的 setXXX() 方法来设置这些参数. setXXX() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始)，第二个是设置的 SQL 语句中的参数的值。

   /**
   * 使用 PreparedStatement 将有效的解决 SQL 注入问题.
   */
  @Test
   public void testSQLInjection2() {
    String username = "a' OR PASSWORD = ";
    String password = " OR '1'='1";
  
    String sql = "SELECT * FROM users WHERE username = ? "
            + "AND password = ?";
  
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
  
    try {
        connection = JDBCTools.getConnection();
        preparedStatement = connection.prepareStatement(sql);
  
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, password);
  
        resultSet = preparedStatement.executeQuery();
  
        if (resultSet.next()) {
            System.out.println("登录成功!");
        } else {
            System.out.println("用户名和密码不匹配或用户名不存在. ");
        }
  
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCTools.releaseDB(resultSet, preparedStatement, connection);
    }
  }
  

PreparedStatement vs Statement
代码的可读性和可维护性. （防止SQL语句写错）
PreparedStatement 能最大可能提高性能：
DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。

在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存.这样每执行一次都要对传入的语句编译一次.
(语法检查，语义检查，翻译成二进制命令，缓存)
PreparedStatement 可以防止 SQL 注入
PreparedStatement更新（插入 删除 更新）操作

@Test
public void testPreparedStatement() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;

    try {
        connection = JDBCTools.getConnection();
        String sql = "INSERT INTO customers (name, email, birth) "
                + "VALUES(?,?,?)";

        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, "ATGUIGU");
        preparedStatement.setString(2, "simpleit@163.com");
        preparedStatement.setDate(3,
                new Date(new java.util.Date().getTime()));

        preparedStatement.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCTools.releaseDB(null, preparedStatement, connection);
    }
}

PreparedStatement查询操作

/**
 * 具体查询学生信息的. 返回一个 Student 对象. 若不存在, 则返回 null
 * 
 * @param searchType
 *            : 1 或 2.
 * @return
 */
private Student searchStudent(int searchType) {

    String sql = "SELECT flowid, type, idcard, examcard,"
            + "studentname, location, grade " + "FROM examstudent "
            + "WHERE ";

    Scanner scanner = new Scanner(System.in);

    // 1. 根据输入的 searchType, 提示用户输入信息:
    // 1.1 若 searchType 为 1, 提示: 请输入身份证号. 若为 2 提示: 请输入准考证号
    // 2. 根据 searchType 确定 SQL
    if (searchType == 1) {
        System.out.print("请输入准考证号:");
        String examCard = scanner.next();
        sql = sql + "examcard = '" + examCard + "'";
    } else {
        System.out.print("请输入身份证号:");
        String examCard = scanner.next();
        sql = sql + "idcard = '" + examCard + "'";
    }

    // 3. 执行查询
    Student student = getStudent(sql);

    // 4. 若存在查询结果, 把查询结果封装为一个 Student 对象

    return student;
}

/**
 * 根据传入的 SQL 返回 Student 对象
 * 
 * @param sql
 * @return
 */
public Student getStudent(String sql, Object... args) {
    Student stu = null;

    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
        connection = JDBCTools.getConnection();
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            preparedStatement.setObject(i + 1, args[i]);
        }
        resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            stu = new Student();
            stu.setFlowId(resultSet.getInt(1));
            stu.setType(resultSet.getInt(2));
            stu.setIdCard(resultSet.getString(3));
            // ...
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCTools.releaseDB(resultSet, preparedStatement, connection);
    }

    return stu;
}

/**
 * 从控制台读入一个整数, 确定要查询的类型
 * 
 * @return: 1. 用身份证查询. 2. 用准考证号查询 其他的无效. 并提示请用户重新输入.
 */
private int getSearchTypeFromConsole() {

    System.out.print("请输入查询类型: 1. 用身份证查询. 2. 用准考证号查询 ");

    Scanner scanner = new Scanner(System.in);
    int type = scanner.nextInt();

    if (type != 1 && type != 2) {
        System.out.println("输入有误请重新输入!");
        throw new RuntimeException();
    }

    return type;
}

Student类：

 public class Student {

// 流水号
private int flowId;
// 考试的类型
private int type;
// 身份证号
private String idCard;
// 准考证号
private String examCard;
// 学生名
private String studentName;
// 学生地址
private String location;
// 考试分数.
private int grade;

public int getFlowId() {
    return flowId;
}

public void setFlowId(int flowId) {
    this.flowId = flowId;
}

public int getType() {
    return type;
}

public void setType(int type) {
    this.type = type;
}

public String getIdCard() {
    return idCard;
}

public void setIdCard(String idCard) {
    this.idCard = idCard;
}

public String getExamCard() {
    return examCard;
}

public void setExamCard(String examCard) {
    this.examCard = examCard;
}

public String getStudentName() {
    return studentName;
}

public void setStudentName(String studentName) {
    this.studentName = studentName;
}

public String getLocation() {
    return location;
}

public void setLocation(String location) {
    this.location = location;
}

public int getGrade() {
    return grade;
}

public void setGrade(int grade) {
    this.grade = grade;
}

public Student(int flowId, int type, String idCard, String examCard,
        String studentName, String location, int grade) {
    super();
    this.flowId = flowId;
    this.type = type;
    this.idCard = idCard;
    this.examCard = examCard;
    this.studentName = studentName;
    this.location = location;
    this.grade = grade;
}

public Student() {
    // TODO Auto-generated constructor stub
}

@Override
public String toString() {
    return "Student [flowId=" + flowId + ", type=" + type + ", idCard="
            + idCard + ", examCard=" + examCard + ", studentName="
            + studentName + ", location=" + location + ", grade=" + grade
            + "]";
}

}