JDBC Update深度优化-阿里云开发者社区

Update是数据同步过程中一个不可缺少的操作，这里所讨论的更新并非写一个update语句更新了一批数据，如果是这样，就没必要写此文章了。

这里所讨论的更新是根据查询对比，决定是否更新、删除等，甚至还要处理一些相关业务。对于这样的操作，JDBC比任何方式的效率都好。这里所谓的批量，是说有一大批这样数据要通过查询检查，然后去做更新、删除操作。

为了进行测试，将问题抽象简化，根据查询更新、删除。

环境：
MySQL 5.1
RedHat Linux AS 5
JavaSE 1.5
DbConnectionBroker 微型数据库连接池

优化的策略：

1、使用连接池

2、尽可能减少数据库的链接和检索次数

做到这两个方面，就达到优化的的目标了。

SQL脚本

DROP TABLE IF EXISTS tuser;

CREATE TABLE tuser (
        id bigint(20) NOT NULL AUTO_INCREMENT,
         name varchar(12) DEFAULT NULL,
        remark varchar(24) DEFAULT NULL,
        createtime datetime DEFAULT NULL,
        updatetime datetime DEFAULT NULL,
         PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

下面是优化实现代码：

import java.io.IOException;
import java.sql.*;

/**
* JDBC批量Update深度优化
*
* @author leizhimin 2009-7-30 8:38:33
*/
public class TestQuery {
         public static DbConnectionBroker myBroker = null;

         static {
                 try {
                        myBroker = new DbConnectionBroker( "com.mysql.jdbc.Driver",
                                         "jdbc:mysql://192.168.104.163:3306/testdb",
                                        "vcom", "vcom", 2, 4,
                                        "c:\\testdb.log", 0.01);
                } catch (IOException e) {
                        e.printStackTrace();
                }
        }

        /**
         * 初始化测试环境
         *
         * @throws SQLException 异常时抛出
         */
        public static void init() throws SQLException {
                Connection conn = myBroker.getConnection();
                conn.setAutoCommit(false);
                Statement stmt = conn.createStatement();
                stmt.addBatch("DROP TABLE IF EXISTS tuser");
                stmt.addBatch("CREATE TABLE tuser (\n" +
                                "    id bigint(20) NOT NULL AUTO_INCREMENT,\n" +
                                "    name varchar(12) DEFAULT NULL,\n" +
                                "    remark varchar(24) DEFAULT NULL,\n" +
                                "    createtime datetime DEFAULT NULL,\n" +
                                "    updatetime datetime DEFAULT NULL,\n" +
                                "    PRIMARY KEY (id)\n" +
                                ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8");
                stmt.executeBatch();
                conn.commit();
                System.out.println("--------数据库所支持的ResultSet的类型---------");
                System.out.println("ResultSet.TYPE_FORWARD_ONLY\t\t\t:"+conn.getMetaData().supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY));
                System.out.println("ResultSet.TYPE_SCROLL_INSENSITIVE\t:"+conn.getMetaData().supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE));
                System.out.println("ResultSet.TYPE_SCROLL_SENSITIVE\t\t:"+conn.getMetaData().supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE));
                myBroker.freeConnection(conn);
        }

        /**
         * n条预定义SQL插入
         *
         * @throws Exception 异常时抛出
         */
        public static void initData(int n) throws Exception {
                init();         //初始化环境
                Long start = System.currentTimeMillis();
                String sql = "" +
                                "insert into testdb.tuser\n" +
                                "    (name, remark, createtime, updatetime)\n" +
                                "values\n" +
                                "    (?, ?, ?, ?)";
                for (int i = 0; i < n; i++) {
                        Connection conn = myBroker.getConnection();
                        conn.setAutoCommit(false);
                        PreparedStatement pstmt = conn.prepareStatement(sql);
                        pstmt.setString(1, RandomToolkit.generateString(12));
                        pstmt.setString(2, RandomToolkit.generateString(24));
                        pstmt.setDate(3, new Date(System.currentTimeMillis()));
                        pstmt.setDate(4, new Date(System.currentTimeMillis()));
                        pstmt.executeUpdate();
                        conn.commit();
                        pstmt.close();
                        myBroker.freeConnection(conn);
                }
                Long end = System.currentTimeMillis();
                System.out.println("单条执行" + n + "条Insert操作，共耗时：" + (end - start) / 1000f + "秒！");
        }

        /**
         * 查询一条数据，并更新该条数据
         *
         * @throws SQLException
         */
        public static void testQueryOne4Update() throws SQLException {
                String query_sql = "select id, name, remark, createtime, updatetime\n" +
                                "    from testdb.tuser where id = 1";
                Connection conn = myBroker.getConnection();
                conn.setAutoCommit(false);
                //注意结果集的参数配置
                Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
                System.out.println("连接是否为只读模式：" + conn.isReadOnly());
                System.out.println("查询使用的SQL所对应的本地SQL脚本：" + conn.nativeSQL(query_sql));
                ResultSet rs = stmt.executeQuery(query_sql);
                while (rs.next()) {    //一行数据，本while可以省略
                        //更新数据的name列
                        rs.updateString("name", "new name");
                        //保存更新行
                        rs.updateRow();
                }
                conn.commit();
                myBroker.freeConnection(conn);
        }

        /**
         * 查询多条记录并做更新操作
         *
         * @throws SQLException
         */
        public static void testQueryMany4Update() throws SQLException {
                String query_sql = "select id, name, remark, createtime, updatetime\n" +
                                "    from testdb.tuser where id >2 and id<5";
                Connection conn = myBroker.getConnection();
                conn.setAutoCommit(false);
                Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
                ResultSet rs = stmt.executeQuery(query_sql);
                //循环逐条更新查询结果集数据
                while (rs.next()) {
                        //更新数据的name列
                        rs.updateString("name", "lavasoft25");
                        rs.updateDate("updatetime", new Date(System.currentTimeMillis()));
                        //保存更新行
                        rs.updateRow();
                }
                System.out.println(conn.isReadOnly());
                System.out.println(conn.nativeSQL(query_sql));
                conn.commit();
                myBroker.freeConnection(conn);
        }

        /**
         * 查询一条记录并做插入操作
         *
         * @throws SQLException
         */
        public static void testQueryOne4Insert() throws SQLException {
                String query_sql = "select id, name, remark, createtime, updatetime\n" +
                                "    from testdb.tuser where id = 1 ";
                Connection conn = myBroker.getConnection();
                conn.setAutoCommit(false);
                Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
                ResultSet rs = stmt.executeQuery(query_sql);
                //将结果集指针移动到可插入的行（这行是在内存中的一个虚拟行）
                rs.moveToInsertRow();
                //设定行各个字段的数据
                rs.updateString(2, "熔岩");
                rs.updateString(3, "ttt");
                rs.updateDate(4, new Date(System.currentTimeMillis()));
                rs.updateDate(5, new Date(System.currentTimeMillis()));
                //插入行数据到该表中
                rs.insertRow();
                //指针复位：将指针移动到执行moveToInsertRow()之前的位置
                rs.moveToCurrentRow();
                conn.commit();
                myBroker.freeConnection(conn);
        }

        /**
         * 查询一批数据，并做插入操作
         *
         * @throws SQLException
         */
        public static void testQueryMany4Insert() throws SQLException {
                String query_sql = "select id, name, remark, createtime, updatetime\n" +
                                "    from testdb.tuser where id >4 and id<8";
                Connection conn = myBroker.getConnection();
                conn.setAutoCommit(false);
                Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
                ResultSet rs = stmt.executeQuery(query_sql);
                while (rs.next()) {
                        //将结果集指针移动到可插入的行（这行是在内存中的一个虚拟行）
                        rs.moveToInsertRow();
                        //设定行各个字段的数据
                        rs.updateString(2, "lavasoft3");
                        rs.updateString(3, "ttt");
                        rs.updateDate(4, new Date(System.currentTimeMillis()));
                        rs.updateDate(5, new Date(System.currentTimeMillis()));
                        //插入行数据到该表中
                        rs.insertRow();
                        //指针复位：将指针移动到执行moveToInsertRow()之前的位置
                        rs.moveToCurrentRow();
                        //将指针从当前位置下移一行
                        rs.next();
                }
                conn.commit();
                myBroker.freeConnection(conn);
        }

        /**
         * 查询一条数据，并做插入操作
         *
         * @throws SQLException
         */
        public static void testQueryOne4Delete() throws SQLException {
                String query_sql = "select id, name, remark, createtime, updatetime\n" +
                                "    from testdb.tuser where id=8";
                Connection conn = myBroker.getConnection();
                conn.setAutoCommit(false);
                Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
                ResultSet rs = stmt.executeQuery(query_sql);
                //将指针移动到要删除的行上
                rs.next();
                //从此 ResultSet 对象和底层数据库中删除当前行。指针不位于插入行上时不能调用此方法。
                rs.deleteRow();
                rs.next();
                conn.commit();
                myBroker.freeConnection(conn);
        }

        /**
         * 查询一批数据，并做插入操作
         *
         * @throws SQLException
         */
        public static void testQueryMany4Delete() throws SQLException {
                String query_sql = "select id, name, remark, createtime, updatetime\n" +
                                "    from testdb.tuser where id>1";
                Connection conn = myBroker.getConnection();
                conn.setAutoCommit(false);
                Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);
                ResultSet rs = stmt.executeQuery(query_sql);
                while (rs.next()) {
                        //从此 ResultSet 对象和底层数据库中删除当前行。指针不位于插入行上时不能调用此方法。
                        System.out.println(rs.getRow());
                        rs.deleteRow();
                        rs.beforeFirst();
                }
                conn.commit();
                myBroker.freeConnection(conn);
        }

        public static void main(String[] args) throws SQLException {
                init();
//                testQueryMany4Delete();
        }
}

总结：

1、优化思想就是尽量减少数据库连接和请求。根据查询结果，直接更改结果，然后保存实现了数据的更新，一个连接做多件事情，性能相比先查询结果集合，然后构建更新SQL再执行要好很多。删除操作也类似。

2、优化的关键就是在查询结果集上做文章，并不是所有的结果集都支持这样的更新和删除操作，在构建Statement对象的时候，几个ResultSet参数需要特别注意：

TYPE_FORWARD_ONLY
                    该常量指示指针只能向前移动的 ResultSet 对象的类型，默认类型。只允许向前一条一条的访问，并且不会受到其他用户对该数据库所作更改的影响。
。
TYPE_SCROLL_INSENSITIVE
                    该常量指示可滚动但通常不受其他的更改影响的 ResultSet 对象的类型，允许在列表中向前或向后移动，甚至可以进行特定定位，不会受到其他用户对该数据库所作更改的影响。
。
TYPE_SCROLL_SENSITIVE
                    该常量指示可滚动并且通常受其他的更改影响的 ResultSet 对象的类型，像TYPE_SCROLL_INSENSITIVE一样，允许在记录中定位。这种类型受到其他用户所作更改的影响。如果用户在执行完查询之后删除一个记录，那个记录将从ResultSet中消失。类似的，对数据值的更改也将反映在ResultSet中。

CONCUR_READ_ONLY
                    该常量指示不可以更新的 ResultSet 对象的并发模式，适合只查询，不对结果集修改的操作。
CONCUR_UPDATABLE
                    该常量指示可以更新的 ResultSet 对象的并发模式，适合对查询结果集做更新、删除的操作。

CLOSE_CURSORS_AT_COMMIT
                    该常量指示调用 Connection.commit 方法时应该关闭 ResultSet 对象，一般情况下都应该如此。
HOLD_CURSORS_OVER_COMMIT
                    该常量指示调用 Connection.commit 方法时不应关闭 ResultSet 对象，很少用到。

3、更新查询结果集数据能否真正持久化，与JDBC驱动程序的完善程度有关，不是所有的JDBC驱动程序都支持结果集的高级特性。可以通过DatabaseMetaData来查询驱动程序的支持情况。

本文转自 leizhimin 51CTO博客，原文链接：http://blog.51cto.com/lavasoft/185354，如需转载请自行联系原作者

JDBC Update深度优化

热门文章

最新文章

相关课程

相关电子书

相关实验场景