updatemany java_JDBC Update深度优化

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

}

}