java.sql下的StatementPreparedStatementCallableStatement是用来执行SQL查询的API。其中Statement用于执行普通查询,PreparedStatement用于执行参数化查询,CallableStatement则用于存储过程。他们之间的继承关系:PreparedStatement extends StatementCallableStatement extends PreparedStatement

本文主要介绍的是PreparedStatement的简单使用、相比Statement的优势,以及PreparedStatement的局限性。

PreparedStatement示例

String url = "jdbc:mysql://127.0.0.1:3306/dbname";
//获取一个连接
Connection conn = DriverManager.getConnection(url, "username", "password");
//PreparedStatement 为预编译对象
PreparedStatement pst = conn.prepareStatement("select uuid from test limit ?");
pst.setInt(1, 1);
//结果对象
ResultSet result = pst.executeQuery();
while (result.next()) {
    System.out.println(result.getString("uuid"));
}
//依次关闭
result.close();
pst.close();
connection.close();

PreparedStatement优势

参数化:

用PreparedStatement可以编写带参数的SQL语句,如上面示例中的select uuid from test limit ?

更好的性能:

使用PreparedStatement最重要的一点是性能优势,由于SQL语句被预编译在数据库系统中(个人理解为缓存起来),数据库对SQL语句的分析,编译,优化已经完成,因此性能上更好一些。但是需要注意使用字符串追加的方式并不会带来性能上的优势。

String num = 1;
PreparedStatement pst = conn.prepareStatement("select uuid from test limit " + num);

防止SQL注入:

比如网站的登录SQL一般是形如select * from user where user = '" + user + "' and pwd = '" + pwd + "';
如果用户填入user = "1' OR '1'='1" , pwd = "1' OR '1'='1"
则SQL为SELECT * FROM users WHERE name = '1' OR '1'='1' and pw = '1' OR '1'='1';
因为where条件恒为真,则SQL实际执行的为select * from user;

mysql-connector-java-5.1.40-bin.jar中的setString()源码为例:

如果不包含需要转义的字符则在原字符串的最前和最后分别增加\';
如果包含转义字符则对字符串的每一个字符分别使用switch()判断并对特殊字符做相应的转义,这个操作就是为了防止SQL注入。

当使用PreparedStatement时SQL为: "select * from user where user = ? and pwd = ?",其中?是占位符,用户依然填入user = "1' OR '1'='1" , pwd = "1' OR '1'='1",则转换为select * from user where user = '1\' OR \'1\'=\'1\'' and pwd = '1\' OR \'1\'=\'1\''就可以防止SQL注入了。

代码位置:com.mysql.jdbc.PreparedStatement#setString()

 public void setString(int parameterIndex, String x) throws SQLException{
    synchronized (checkClosed().getConnectionMutex()){
      if (x == null) {
        setNull(parameterIndex, 1);
      } else {
        checkClosed();
        int stringLength = x.length();
        if (this.connection.isNoBackslashEscapesSet()){
          boolean needsHexEscape = isEscapeNeededForString(x, stringLength);
          if (!needsHexEscape) {
            byte[] parameterAsBytes = null;
            StringBuilder quotedString = new StringBuilder(x.length() + 2);
            quotedString.append('\'');
            quotedString.append(x);
            quotedString.append('\'');
            if (!this.isLoadDataQuery) {
              parameterAsBytes = StringUtils.getBytes(quotedString.toString(), this.charConverter, this.charEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor());
            }
            else {
              parameterAsBytes = StringUtils.getBytes(quotedString.toString());
            }
            setInternal(parameterIndex, parameterAsBytes);
          } else {
            byte[] parameterAsBytes = null;
            if (!this.isLoadDataQuery) {
              parameterAsBytes = StringUtils.getBytes(x, this.charConverter, this.charEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor());
            }
            else{
              parameterAsBytes = StringUtils.getBytes(x);
            }
            setBytes(parameterIndex, parameterAsBytes);
          }
          return;
        }
        String parameterAsString = x;
        boolean needsQuoted = true;
        if ((this.isLoadDataQuery) || (isEscapeNeededForString(x, stringLength))) {
          needsQuoted = false;
          StringBuilder buf = new StringBuilder((int)(x.length() * 1.1D));
          buf.append('\'');
          for (int i = 0; i < stringLength; i++) {
            char c = x.charAt(i);
            switch (c) {
            case '\000': 
              buf.append('\\');
              buf.append('0');
              break;
            case '\n': 
              buf.append('\\');
              buf.append('n');
              break;
            case '\r': 
              buf.append('\\');
              buf.append('r');
              break;
            case '\\': 
              buf.append('\\');
              buf.append('\\');
              break;
            case '\'': 
              buf.append('\\');
              buf.append('\'');
              break;
            case '"': 
              if (this.usingAnsiMode) {
                buf.append('\\');
              }
              buf.append('"');
              break;
            case '\032': 
              buf.append('\\');
              buf.append('Z');
              break;
            case '\u00A5': 
            case '\u20A9': 
              if (this.charsetEncoder != null) {
                CharBuffer cbuf = CharBuffer.allocate(1);
                ByteBuffer bbuf = ByteBuffer.allocate(1);
                cbuf.put(c);
                cbuf.position(0);
                this.charsetEncoder.encode(cbuf, bbuf, true);
                if (bbuf.get(0) == 92) {
                  buf.append('\\');
                }
              }
              buf.append(c);
              break;
              default: 
              buf.append(c);
            }
          }
          buf.append('\'');
          parameterAsString = buf.toString();
        }
        byte[] parameterAsBytes = null;
        if (!this.isLoadDataQuery) {
          if (needsQuoted) {
            parameterAsBytes = StringUtils.getBytesWrapped(parameterAsString, '\'', '\'', this.charConverter, this.charEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor());
          }else {
            parameterAsBytes = StringUtils.getBytes(parameterAsString, this.charConverter, this.charEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor());
          }
        }else {
          parameterAsBytes = StringUtils.getBytes(parameterAsString);
        }
        setInternal(parameterIndex, parameterAsBytes);
        this.parameterTypes[(parameterIndex - 1 + getParameterIndexOffset())] = 12;
      }
    }
  }

PreparedStatement的局限性

为了防止SQL注入,PreparedStatement只允许一个占位符(?)只能有一个值,这样在含有in的子查询时就难以实现:

    select uuid from test where id in (?);
    pst.setString(1, "'1','2'");

上面的查询返回结果为空,可以使用批处理addBatch()实现。

标签: Java, PreparedStatement

添加新评论