OracleCachedRowSet 数据更新测试

OracleCachedRowSet 数据更新测试

1、OracleCachedRowSet. acceptChanges()

1.1.测试代码

import oracle.jdbc.rowset.OracleCachedRowSet;

import org.junit.Test;

import org.springframework.boot.test.context.SpringBootTest;

import java.sql.*;

/**

* Created by Administrator on 2022-12-09.

*/

@SpringBootTest

public class RowSetUpdateTest {

private static String url = "jdbc:oracle:thin:@localhost:1521:HIS";

private static String user="system";

private static String password="manager";

private static String _driver = "oracle.jdbc.OracleDriver" ;

private Connection conn = null ;

private PreparedStatement ps = null;

private ResultSet rs = null;

private OracleCachedRowSet _oracleCachedrs=null;

/**

* 乐观锁测试

*/

@Test

public void UpdateRowSet()

{

String sql="select SERIAL_NO,DEPT_CODE,DEPT_NAME,DEPT_ALIAS,CLINIC_ATTR,OUTP_OR_INP from COMM.DEPT_DICT a where a.dept_code IN('010103','010102')";

System.out.println("=========start========");

//1.建立 连接

try {

conn = DriverManager.getConnection(url, user, password);

}catch (Exception ex)

{

ex.printStackTrace();

}

//2.查询数据到OracleCachedRowSet

try

{

Statement stmt = conn.createStatement();

rs = stmt.executeQuery(sql);

_oracleCachedrs = new OracleCachedRowSet();

_oracleCachedrs.setReadOnly(false);

_oracleCachedrs.setTableName("DEPT_DICT");

_oracleCachedrs.populate(rs);

conn.close(); //断开连接

}catch (Exception ex)

{

ex.printStackTrace();

}

//3.修改OracleCachedRowSet 数据

try

{

// 遍历修改数据

while (_oracleCachedrs.next()) {

//修改科室名称

String dept_name = _oracleCachedrs.getString("dept_name");

System.out.println("原始值:"+_oracleCachedrs.getString("dept_name"));

_oracleCachedrs.updateString("dept_name", dept_name+"aA");

_oracleCachedrs.updateRow(); //更新内存数据

System.out.println("修改后值:"+_oracleCachedrs.getString("dept_name"));

}

System.out.println("====================================");

ResultSet _ors=_oracleCachedrs.getOriginal();

while (_ors.next())

{

//修改科室名称

String dept_name = _ors.getString(3);

System.out.println("原始值:"+dept_name);

}

}catch (Exception ex)

{

ex.printStackTrace();

}

//4.先把数据库中的记录原始值修改掉，再更新OracleCachedRowSet ，看其是否成功

String sqlUpdate="UPDATE DEPT_DICT A SET A.DEPT_NAME='消化内科-AAA' WHERE A.DEPT_CODE='010103'";

try

{

conn = DriverManager.getConnection(url, user, password);

Statement s = conn.createStatement();

s.execute(sqlUpdate);

_oracleCachedrs.acceptChanges(conn);

conn.close();

System.out.println("=================");

}catch (Exception ex)

{

ex.printStackTrace();

}

System.out.println("=========end========");

}

}

1.2测试结果

1.测试前数据

2.先用UPDATE DEPT_DICT A SET A.DEPT_NAME='消化内科-AAA' WHERE A.DEPT_CODE=‘010103’把其中一条科室名称修改 掉；

String sqlUpdate="UPDATE DEPT_DICT A SET A.DEPT_NAME='消化内科-AAA' WHERE A.DEPT_CODE='010103'";

conn = DriverManager.getConnection(url, user, password);

Statement s = conn.createStatement();

s.execute(sqlUpdate);

修改后结果：

3.再提交OracleCachedRowSet

根据结果显示，OracleCachedRowSet. acceptChanges 执行成功 ，但是 原始值 被改掉 的 那条记录 没有 执行成功 。

2、OracleCachedRowSet. acceptChanges()

2.1 测试代码

import oracle.jdbc.rowset.OracleCachedRowSet;

import org.junit.Test;

import org.springframework.boot.test.context.SpringBootTest;

import java.sql.*;

/**

* Created by Administrator on 2022-12-09.

*/

@SpringBootTest

public class RowSetUpdatesTest {

private static String url = "jdbc:oracle:thin:@localhost:1521:HIS";

private static String user="system";

private static String password="manager";

private static String _driver = "oracle.jdbc.OracleDriver" ;

private Connection conn = null ;

private PreparedStatement ps = null;

private ResultSet rs = null;

private OracleCachedRowSet _oracleCachedrs=null;

/**

* 乐观锁测试

*/

@Test

public void UpdateRowSet()

{

String sql="select SERIAL_NO,DEPT_CODE,DEPT_NAME,DEPT_ALIAS,CLINIC_ATTR,OUTP_OR_INP from COMM.DEPT_DICT a where a.dept_code IN('010103','010102')";

System.out.println("=========start========");

//1.建立 连接

try {

conn = DriverManager.getConnection(url, user, password);

}catch (Exception ex)

{

ex.printStackTrace();

}

//2.查询数据到OracleCachedRowSet

try

{

Statement stmt = conn.createStatement();

rs = stmt.executeQuery(sql);

_oracleCachedrs = new OracleCachedRowSet();

_oracleCachedrs.setReadOnly(false);

_oracleCachedrs.setTableName("DEPT_DICT");

_oracleCachedrs.populate(rs);

conn.close(); //断开连接

}catch (Exception ex)

{

ex.printStackTrace();

}

//3.修改OracleCachedRowSet 数据

try

{

// 遍历修改数据

while (_oracleCachedrs.next()) {

//修改科室名称

String deptNname = _oracleCachedrs.getString("dept_name");

String deptCode= _oracleCachedrs.getString("dept_code");

System.out.println("原始值:"+_oracleCachedrs.getString("dept_name"));

if(deptCode.equals("010103"))

{

//超过数据精度赋值

_oracleCachedrs.updateString("dept_name", "中国北京天健医院消化内科");

}

else

{

_oracleCachedrs.updateString("dept_name", deptNname+"aA");

}

_oracleCachedrs.updateRow(); //更新内存数据

System.out.println("修改后值:"+_oracleCachedrs.getString("dept_name"));

}

System.out.println("====================================");

ResultSet _ors=_oracleCachedrs.getOriginal();

while (_ors.next())

{

//修改科室名称

String dept_name = _ors.getString(3);

System.out.println("原始值:"+dept_name);

}

}catch (Exception ex)

{

ex.printStackTrace();

}

//4.提交OracleCachedRowSet

try

{

conn = DriverManager.getConnection(url, user, password);

_oracleCachedrs.acceptChanges(conn);

conn.close();

}catch (Exception ex)

{

ex.printStackTrace();

}

System.out.println("=========end========");

}

}

2.2测试结果

1.测试前数据：

2. 超过数据精度给数据集赋值

执行报错，整个数据集中没有一条记录执行成功。

3.SyncProvider

3.1 getProviderGrade（） 抽象方法

package javax.sql.rowset.spi;

import javax.sql.*;

public abstract class SyncProvider {

public SyncProvider()

{

}

*/

public abstract String getProviderID();

public abstract RowSetReader getRowSetReader();

public abstract RowSetWriter getRowSetWriter();

public abstract int getProviderGrade();

public abstract void setDataSourceLock(int datasource_lock)

throws SyncProviderException;

public abstract int getDataSourceLock()

throws SyncProviderException;

public abstract int supportsUpdatableView(); */

public abstract String getVersion(); */

public abstract String getVendor();

public static final int GRADE_NONE = 1;

public static final int GRADE_CHECK_MODIFIED_AT_COMMIT = 2;

public static final int GRADE_CHECK_ALL_AT_COMMIT = 3;

public static final int GRADE_LOCK_WHEN_MODIFIED = 4;

public static final int GRADE_LOCK_WHEN_LOADED = 5;

public static final int DATASOURCE_NO_LOCK = 1;

public static final int DATASOURCE_ROW_LOCK = 2;

public static final int DATASOURCE_TABLE_LOCK = 3;

public static final int DATASOURCE_DB_LOCK = 4;

public static final int UPDATABLE_VIEW_SYNC = 5

public static final int NONUPDATABLE_VIEW_SYNC = 6;

}

4.RIOptimisticProvider

4.1 getProviderGrade()

返回值为2 GRADE_CHECK_MODIFIED_AT_COMMIT

package com.sun.rowset.providers;

import com.sun.rowset.JdbcRowSetResourceBundle;

import com.sun.rowset.internal.CachedRowSetReader;

import com.sun.rowset.internal.CachedRowSetWriter;

import java.io.IOException;

import java.io.ObjectInputStream;

import java.io.Serializable;

import java.sql.SQLException;

import javax.sql.RowSetReader;

import javax.sql.RowSetWriter;

import javax.sql.rowset.spi.SyncProvider;

import javax.sql.rowset.spi.SyncProviderException;

public final class RIOptimisticProvider extends SyncProvider implements Serializable {

private CachedRowSetReader reader;

private CachedRowSetWriter writer;

private String providerID = "com.sun.rowset.providers.RIOptimisticProvider";

private String vendorName = "Oracle Corporation";

private String versionNumber = "1.0";

private JdbcRowSetResourceBundle resBundle;

static final long serialVersionUID = -3143367176751761936L;

public RIOptimisticProvider() {

this.providerID = this.getClass().getName();

this.reader = new CachedRowSetReader();

this.writer = new CachedRowSetWriter();

try {

this.resBundle = JdbcRowSetResourceBundle.getJdbcRowSetResourceBundle();

} catch (IOException var2) {

throw new RuntimeException(var2);

}

}

public String getProviderID() {

return this.providerID;

}

public RowSetWriter getRowSetWriter() {

try {

this.writer.setReader(this.reader);

} catch (SQLException var2) {

;

}

return this.writer;

}

public RowSetReader getRowSetReader() {

return this.reader;

}

public int getProviderGrade() {

return 2;

}

public void setDataSourceLock(int var1) throws SyncProviderException {

if(var1 != 1) {

throw new SyncProviderException(this.resBundle.handleGetObject("riop.locking").toString());

}

}

public int getDataSourceLock() throws SyncProviderException {

return 1;

}

public int supportsUpdatableView() {

return 6;

}

public String getVersion() {

return this.versionNumber;

}

public String getVendor() {

return this.vendorName;

}

private void readObject(ObjectInputStream var1) throws IOException, ClassNotFoundException {

var1.defaultReadObject();

try {

this.resBundle = JdbcRowSetResourceBundle.getJdbcRowSetResourceBundle();

} catch (IOException var3) {

throw new RuntimeException(var3);

}

}

}

5.CachedRowSet

String provider= “com.sun.rowset.providers”

　　CachedRowSet crs=new CachedRowSetImpl(provider);

如果需要其他的并发控制机制，可使用方法setSyncProvider 插入其他 SyncProvider 实现。

要使用乐观并发控制例程，RIOptismisticProvider 要同时维护其当前值及其原始值（刚好位于当前值之前的值）。注意，如果没有对 RowSet 对象中的数据进行任何更改，则其当前值和原始值相同，都是最初填充 RowSet 对象时使用的值。但是，一旦更改了 RowSet 对象中的任何值，当前值和原始值就不同了，尽管此时原始值仍是最初的值。随着后续对 RowSet 对象中的数据进行更改，其原始值和当前值仍保持不同，但是其原始值将是前一个当前值。

关注原始值允许writer 对 RowSet 对象的原始值和数据库中的值进行比较。如果数据库中的值与 RowSet 对象的原始值不同，则意味着数据库中的值已经更改，出现了冲突。writer 是否检查冲突、检查的程度如何，以及它如何处理冲突都取决于它的实现方式。

6.OracleCachedRowSet

public class OracleCachedRowSet extends OracleRowSet implements RowSet, RowSetInternal, Serializable, Cloneable, CachedRowSet {

}

6.1 OracleCachedRowSet() 构造函数

this.syncProvider = SyncFactory.getInstance("com.sun.rowset.providers.RIOptimisticProvider");

GRADE_CHECK_MODIFIED_AT_COMMIT 指示关于原始数据源的低级别乐观同步等级

public OracleCachedRowSet() throws SQLException {

try {

this.syncProvider = SyncFactory.getInstance("com.sun.rowset.providers.RIOptimisticProvider");

} catch (SyncFactoryException var3) {

SQLException var2 = DatabaseError.createSqlException(this.getConnectionDuringExceptionHandling(), 304);

var2.fillInStackTrace();

throw var2;

}

this.setReader(new OracleCachedRowSetReader());

this.setWriter(new OracleCachedRowSetWriter());

this.currentPage = 0;

this.pageSize = 0;

this.isPopulateDone = false;

this.keyColumns = null;

this.tableName = null;

}

6.2 acceptChanges

说明：此方法中的writeData（） 开启了事务机制。

public void acceptChanges() throws SyncProviderException {

try {

this.getWriter().writeData(this);

} catch (SQLException var2) {

throw new SyncProviderException(var2.getMessage());

}

}

6.3 getMetaData

public ResultSetMetaData getMetaData() throws SQLException {

return this.rowsetMetaData;

}

7.OracleCachedRowSetWriter

7.1 writeData

说明：此方法中开启了事务机制

public synchronized boolean writeData(RowSetInternal var1) throws SQLException {

OracleCachedRowSet var2 = (OracleCachedRowSet)var1;

this.connection = ((OracleCachedRowSetReader)var2.getReader()).getConnection(var1);

if(this.connection == null) {

SQLException var9 = DatabaseError.createSqlException(this.getConnectionDuringExceptionHandling(), 342);

var9.fillInStackTrace();

throw var9;

} else {

if(this.connection.getAutoCommit()) {

this.connection.setAutoCommit(false);

}

try {

this.connection.setTransactionIsolation(var2.getTransactionIsolation());

} catch (Exception var8) {

;

}

this.initSQLStatement(var2);

if(this.columnCount < 1) {

this.connection.close();

return true;

} else {

boolean var3 = var2.getShowDeleted();

var2.setShowDeleted(true);

var2.beforeFirst();

boolean var4 = true;

boolean var5 = true;

boolean var6 = true;

OracleRow var7 = null;

while(true) {

label84:

do {

while(var2.next()) {

if(var2.rowInserted()) {

//新增 行

continue label84;

}

if(var2.rowUpdated()) {

//更新行

var7 = var2.getCurrentRow();

var4 = this.updateRow(var2, var7) || var4;

}

else if(var2.rowDeleted()) {

//删除行

var7 = var2.getCurrentRow();

var6 = this.deleteRow(var2, var7) || var6;

}

}

if(var4 && var5 && var6) {

this.connection.commit();

var2.setOriginal();//提交后设置数据为原始值标志

} else {

this.connection.rollback();

}

this.insertStmt.close();

this.updateStmt.close();

this.deleteStmt.close();

if(!var2.isConnectionStayingOpen()) {

this.connection.close();

}

var2.setShowDeleted(var3);

return true;

} while(var2.rowDeleted());

var7 = var2.getCurrentRow();

var5 = this.insertRow(var7) || var5;

}

}

}

}

7.2 initSQLStatement

private void initSQLStatement(RowSet var1) throws SQLException {

this.insertClause = new StringBuffer("INSERT INTO " + this.getTableName(var1) + "(");

this.updateClause = new StringBuffer("UPDATE " + this.getTableName(var1) + " SET ");

this.deleteClause = new StringBuffer("DELETE FROM " + this.getTableName(var1) + " WHERE ");

this.rsmd = var1.getMetaData();

this.columnCount = this.rsmd.getColumnCount();

int var2;

for(var2 = 0; var2 < this.columnCount; ++var2) {

if(var2 != 0) {

this.insertClause.append(", ");

}

this.insertClause.append(this.rsmd.getColumnName(var2 + 1));

if(var2 != 0) {

this.updateClause.append(", ");

}

this.updateClause.append(this.rsmd.getColumnName(var2 + 1) + " = :" + var2);

if(var2 != 0) {

this.deleteClause.append(" AND ");

}

this.deleteClause.append(this.rsmd.getColumnName(var2 + 1) + " = :" + var2);

}

this.insertClause.append(") VALUES (");

this.updateClause.append(" WHERE ");

for(var2 = 0; var2 < this.columnCount; ++var2) {

if(var2 != 0) {

this.insertClause.append(", ");

}

this.insertClause.append(":" + var2);

if(var2 != 0) {

this.updateClause.append(" AND ");

}

this.updateClause.append(this.rsmd.getColumnName(var2 + 1) + " = :" + var2);

}

this.insertClause.append(")");

this.insertStmt = this.connection.prepareStatement(this.insertClause.substring(0, this.insertClause.length()));

this.updateStmt = this.connection.prepareStatement(this.updateClause.substring(0, this.updateClause.length()));

this.deleteStmt = this.connection.prepareStatement(this.deleteClause.substring(0, this.deleteClause.length()));

}

拼接的UPDATE Sql 如下：

UPDATE DEPT_DICT

SET SERIAL_NO = :0,

DEPT_CODE = :1,

DEPT_NAME = :2,

DEPT_ALIAS = :3,

CLINIC_ATTR = :4,

OUTP_OR_INP = :5

WHERE SERIAL_NO = :0

AND DEPT_CODE = :1

AND DEPT_NAME = :2

AND DEPT_ALIAS = :3

AND CLINIC_ATTR = :4

AND OUTP_OR_INP = :5