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";

//注意結果集的參數配置

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();

* 查詢多條記錄并做更新操作

public static void testQueryMany4Update() throws SQLException {

" from testdb.tuser where id >2 and id<5";

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);

//循環逐條更新查詢結果集資料

while (rs.next()) {

rs.updateString("name", "lavasoft25");

rs.updateDate("updatetime", new Date(System.currentTimeMillis()));

System.out.println(conn.isReadOnly());

System.out.println(conn.nativeSQL(query_sql));

* 查詢一條記錄并做插入操作

public static void testQueryOne4Insert() throws SQLException {

" from testdb.tuser where id = 1 ";

//将結果集指針移動到可插入的行（這行是在記憶體中的一個虛拟行）

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();

* 查詢一批資料，并做插入操作

public static void testQueryMany4Insert() throws SQLException {

" from testdb.tuser where id >4 and id<8";

//将結果集指針移動到可插入的行（這行是在記憶體中的一個虛拟行）

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();

* 查詢一條資料，并做插入操作

public static void testQueryOne4Delete() throws SQLException {

" from testdb.tuser where id=8";

//将指針移動到要删除的行上

rs.next();

//從此 ResultSet 對象和底層資料庫中删除目前行。指針不位于插入行上時不能調用此方法。

rs.deleteRow();

public static void testQueryMany4Delete() throws SQLException {

" from testdb.tuser where id>1";

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT);

//從此 ResultSet 對象和底層資料庫中删除目前行。指針不位于插入行上時不能調用此方法。

System.out.println(rs.getRow());

rs.deleteRow();

rs.beforeFirst();

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深度優化

繼續閱讀

Java String.format方法的簡單使用

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

Effective Java 8:通用程式設計

SequoiaDB巨杉資料庫C++驅動概述

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method