Statement以及PrparedStatement

Statement以及PrparedStatement

标签（空格分隔）： JDBC Statement PrparedStatement

---

Statement详解

Statement是与数据库打交道最关键的一个接口，我们查看API文档

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment’s current ResultSet object if an open one exists.

在默认情况下，同一时间每个Statement只能打开一个ResultSet对象。因此，如果读取一个ResultSet对象与读取另一个交叉，则这两个对象必须是又不同的Statement对象生成。如果使用相同的Statement打开了新的ResultSet对象，则Statement接口中的所有执行方法都会隐式关闭之前的ResultSet对象。

   @Test
    public void test() {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            conn = JdbcUtils.getConn();
            statement = conn.createStatement();
            String sql = "select count(*) from db_user";
            resultSet = statement.executeQuery(sql);

            String _sql = "select user_name,password,email,birthday from db_user where id = 1";
            //会隐式关闭之前的ResultSet对象
            ResultSet _resultSet = statement.executeQuery(_sql);

            //As it seems the junit.framework.Assert class has been moved to org.junit.Assert in JUnit 4.0 - you can use that instead, it's not deprecated.
            Assert.assertEquals(true, resultSet.isClosed());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(resultSet,statement,conn);
        }
    }

CRUD操作

1.新增数据的方法

    @Override
    public int insert(User user) {
        int result = -1;
        if (user == null) return result;
        Connection connection = null;
        Statement statement = null;

        try {
            String sql = "insert into db_jdbc.db_user(user_name,password,email,birthday) values('%s','%s','%s','%s')";
            sql = String.format(sql, user.getUserName(), user.getPassword(), user.getEmail(), user.getBirthday());
            connection = JdbcUtils.getConn();

            statement = connection.createStatement();

            statement.executeUpdate(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(null, statement, connection);
        }

        return result;
    }

2.读取数据的方法

    @Override
    public User selectById(Integer id) {
        User record = null;
        if (id == null || id <= 0) return record;
        String sql = "select * from db_jdbc.db_user where id = %s";
        sql = String.format(sql, id);
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConn();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                String userName = resultSet.getString(2);
                String password = resultSet.getString(3);
                String email = resultSet.getString(4);
                Timestamp birthday = resultSet.getTimestamp(5);

                record = new User();
                record.setId(id);
                record.setUserName(userName);
                record.setEmail(email);
                record.setBirthday(birthday);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(resultSet, statement, connection);
        }
        return record;
    }

3.修改数据的方法

    @Override
    public int update(User user) {
        int result = -1;
        if (user == null) return result;
        User record = selectById(user.getId());

        String sql = "update db_jdbc.db_user set user_name='%s',password='%s',email='%s',birthday='%s' where id = %s";
        String userName = (user.getUserName() == null) ? record.getUserName() : user.getUserName();
        String password = (user.getPassword() == null) ? record.getPassword() : user.getPassword();
        String email = (user.getEmail() == null) ? record.getEmail() : user.getEmail();
        Timestamp birthday = (user.getBirthday() == null) ? record.getBirthday() : user.getBirthday();
        sql = String.format(sql, userName, password, email, birthday,user.getId());
        Connection connection = null;
        Statement statement = null;

        try {
            connection = JdbcUtils.getConn();
            statement = connection.createStatement();
            result = statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(null, statement, connection);
        }

        return result;
    }

4.删除数据的方法

    @Override
    public int delete(Integer id) {
        Integer result = -1;
        if (id == null || id <= 0) return result;
        String sql = "delete from db_jdbc.db_user where id = %s";
        sql = String.format(sql, id);
        Connection connection = null;
        Statement statement = null;
        try {
            connection = JdbcUtils.getConn();
            statement = connection.createStatement();
            result = statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(null, statement, connection);
        }
        return result;
    }

参数化查询

SQL Query 1:字符串追加形式的PreparedStatement

String userName = getUserName();
PreparedStatement prestmt = conn.prepareStatement("select user_name,password,email,birthday from db_user where user_name=" + userName);

SQL Query 2：使用参数化查询的PreparedStatement

String userName = getUserName();
PreparedStatement prestmt = conn.prepareStatement("select user_name,password,email,birthday from db_user where user_name=?");
prestmt.setString(1,userName);

这里的”?”就是参数的占位符(占位符的索引位置从1开始而不是0，如果填入0会导致java.sql.SQLException invalid column index异常。所以如果PreparedStatement有两个占位符，那么第一个参数的索引时1，第二个参数的索引是2.),比起凌乱的字符串追加似的查询，PreparedStatement查询可读性更好、更安全。

PrearedStatement是java.sql包下面的一个驱动，用来执行SQL语句查询. 如果还是用 PreparedStatement 做同样的查询，哪怕参数值不一样，比如：”any_value”或者”ROOIKE”作为参数值，数据库系统还是会去调用之前编译器编译好的执行语句（系统库系统初次会对查询语句做最大的性能优化）。默认会返回”TYPE_FORWARD_ONLY”类型的结果集（ResultSet）,当然你也可以使用preparedstatment()的重载方法返回不同类型的结果集。

Statement的SQL注入问题

如某个网站的登录验证SQL查询代码为：

strSQL = "SELECT * FROM db_user WHERE name = '" + userName + "' and pw = '"+ passWord +"';"

如果我们恶意输入如下的参数:

userName	passWord
any_value’ or ‘1’ = ‘1’;show tables – rooike	any_value

那么最终SQL语句就变成了：

strSQL = "SELECT * FROM db_user WHERE name = 'any_value' or '1' = '1';show tables -- rooike and pw = 'any_value';"

在MySQL中，最后连续的两个减号表示忽略此SQL减号后面的语句.因为WHERE条件恒为真，这就相当于执行：

strSQL = "SELECT * FROM db_user;SHOW TABLES"

注意：在MySQL中，最后连续的两个减号表示忽略此SQL减号后面的语句，我本机的MySQL版本号为5.6.12，目前几乎所有SQL注入实例都是直接采用两个减号结尾，但是实际测试，这个版本号的MySQL要求两个减号后面必须要有空格才能正常注入，而浏览器是会自动删除掉URL尾部空格的，所以我们的注入会在两个减号后面统一添加任意一个字符或单词，本篇文章的SQL注入实例统一以 – rooike 结尾。

因此可以达到无账号密码亦可登录网站且数据库表的名字db_user也被打印在了页面上。如果恶意用户要是更坏一点，用户输入如下参数:

userName	passWord
any_value	‘; DROP TABLE db_user where ‘1’ = ‘1

那么最终SQL语句变成了：

strSQL = "SELECT * FROM db_user WHERE name = 'any_value' and pw = ''; DROP TABLE db_user where '1' = '1';"

这样一来，虽然没有登录，但是数据表都被删除了。

预处理语句

应用程序在创建好PreparedStatement后，DBMS就已经对SQL进行了预编译(数据库对SQL语句的分析，编译，优化已经在第一次查询前完成了)，同时DBMS会把SQL保存到高速缓存中，调用executeupdate()后，DBMS就直接执行SQL语句了，第二次执行时DBMS会直接查询高速缓存，只要高速缓存中还能找到相同的SQL就无需重新对SQL进行语法语义分析了.

 private String url = "jdbc:mysql://localhost:3306/db_jdbc";

    private String userName = "root";

    private String password = "123456";

    @Override
    public int insert(User user) {
        int result = -1;
        if (user == null) return result;
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            String sql = "insert into db_jdbc.db_user(user_name,password,email,birthday) values(?,?,?,?)";
            sql = String.format(sql, user.getUserName(), user.getPassword(), user.getEmail(), user.getBirthday());
            connection = JdbcUtils.getConn();

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1,user.getUserName());
            preparedStatement.setString(2,user.getPassword());
            preparedStatement.setString(3,user.getEmail());
            preparedStatement.setTimestamp(4, user.getBirthday());

            preparedStatement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(null, preparedStatement, connection);
        }

        return result;
    }

在使用参数化查询的情况下，数据库系统（eg:MySQL）不会将参数的内容视为SQL指令的一部分来处理，而是在数据库完成SQL指令的编译后，才套用参数运行，因此就算参数中含有破坏性的指令，也不会被数据库所运行。因此使用PreparedStatement的参数化的查询可以阻止大部分的SQL注入。

PreparedStatement的局限性

为了防止SQL注入攻击，PreparedStatement不允许一个占位符（？）有多个值，在执行有IN子句查询的时候这个问题变得棘手起来。下面这个SQL查询使用PreparedStatement就不会返回任何结果

String sql = "SELECT * FROM db_user WHERE user_name IN (?)";
preparedSatement.setString(1, "'jjr123', 'rooike', 'js123'");

简单总结一下PreparedStatement的优点:

• PreparedStatement可以阻止常见的SQL注入式攻击。
• PreparedStatement可以写参数化查询，比起凌乱的字符串追加似的查询，PreparedStatement查询可读性更好、更安全.
• 对于PreparedStatement来说，数据库可以使用已经编译过及定义好的执行计划，这种预处理语句查询比普通的查询运行速度更快(Statement总是在执行时发送sql，影响效率.同样的sql，每次都要发送，不能进行有效的缓存，是一种资源的浪费)