Java 操作多类型数据库下的兼容性验证
- 第一章：数据库测试
- ①【 oracle 数据库不支持分号】ORA-00933: SQL 命令未正确结束
- ② 【sqlserver 数据库不支持 commit】COMMIT TRANSACTION 请求没有对应的 BEGIN TRANSACTION
- ③ 【DB2 数据库不支持分号】SQLSTATE=42601, SQLERRMC=;;de = '报表合并体系1';END-OF-STATEMENT
- ④ 【kingbase 人大金仓数据库没问题】
- ⑤ 【DM 达梦数据库没问题】
- ⑥ 【PG、瀚高数据库没问题】
- ⑦ 【polardb 数据库没问题】
- 第二章：测试环境
- ① 测试 sql 语句
- ② 测试代码块
- ③ 测试驱动和 JDK 版本
第一章：数据库测试
①【 oracle 数据库不支持分号】ORA-00933: SQL 命令未正确结束
报错：OriginalSql = delete from org_reportcombinestru where code = '报表合并体系2';, Error Msg = ORA-00933: SQL 命令未正确结束
java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束 at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41) at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205) at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1747) at oracle.jdbc.driver.OracleStatement.executeLargeUpdate(OracleStatement.java:1712) at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1699) at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:285) at com.dbmanage.db.DBUpdate.getRs(DBUpdate.java:21) at com.dbmanage.db.DBMain.update_sql(DBMain.java:57) at com.dbmanage.db.ReadFile.getFileContent(ReadFile.java:28) at com.dbmanage.db.ReadClipboard.lostOwnership(ReadClipboard.java:87) at sun.awt.datatransfer.SunClipboard.lostOwnershipNow(SunClipboard.java:313) at sun.awt.datatransfer.SunClipboard.lambda$lostOwnershipLater$0(SunClipboard.java:283) at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:311) at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758) at java.awt.EventQueue.access$500(EventQueue.java:97) at java.awt.EventQueue$3.run(EventQueue.java:709) at java.awt.EventQueue$3.run(EventQueue.java:703) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74) at java.awt.EventQueue.dispatchEvent(EventQueue.java:728) at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:205) at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93) at java.awt.EventDispatchThread.run(EventDispatchThread.java:82) Caused by: Error : 933, Position : 68, Sql = delete from org_reportcombinestru where code = '报表合并体系1';, OriginalSql = delete from org_reportcombinestru where code = '报表合并体系1';, Error Msg = ORA-00933: SQL 命令未正确结束 at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513) ... 33 more
② 【sqlserver 数据库不支持 commit】COMMIT TRANSACTION 请求没有对应的 BEGIN TRANSACTION
sqlservser 数据库测试
报错：com.microsoft.sqlserver.jdbc.SQLServerException: COMMIT TRANSACTION 请求没有对应的 BEGIN TRANSACTION
com.microsoft.sqlserver.jdbc.SQLServerException: COMMIT TRANSACTION 请求没有对应的 BEGIN TRANSACTION。 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1608) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:859) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:759) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:706) at com.dbmanage.db.DBUpdate.getRs(DBUpdate.java:21) at com.dbmanage.db.DBMain.update_sql(DBMain.java:87) at com.dbmanage.db.ReadFile.getFileContent(ReadFile.java:28) at com.dbmanage.db.ReadClipboard.lostOwnership(ReadClipboard.java:87) at sun.awt.datatransfer.SunClipboard.lostOwnershipNow(SunClipboard.java:313) at sun.awt.datatransfer.SunClipboard.lambda$lostOwnershipLater$0(SunClipboard.java:283) at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:311) at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758) at java.awt.EventQueue.access$500(EventQueue.java:97) at java.awt.EventQueue$3.run(EventQueue.java:709) at java.awt.EventQueue$3.run(EventQueue.java:703) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74) at java.awt.EventQueue.dispatchEvent(EventQueue.java:728) at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:205) at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93) at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
③ 【DB2 数据库不支持分号】SQLSTATE=42601, SQLERRMC=;;de = ‘报表合并体系1’;END-OF-STATEMENT
db2 数据库测试
报错：com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;de = '报表合并体系1';END-OF-STATEMENT, DRIVER=4.19.49
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;de = '报表合并体系1';END-OF-STATEMENT, DRIVER=4.19.49 at com.ibm.db2.jcc.am.kd.a(Unknown Source) at com.ibm.db2.jcc.am.kd.a(Unknown Source) at com.ibm.db2.jcc.am.kd.a(Unknown Source) at com.ibm.db2.jcc.am.fp.c(Unknown Source) at com.ibm.db2.jcc.am.fp.d(Unknown Source) at com.ibm.db2.jcc.am.fp.b(Unknown Source) at com.ibm.db2.jcc.t4.bb.i(Unknown Source) at com.ibm.db2.jcc.t4.bb.c(Unknown Source) at com.ibm.db2.jcc.t4.p.b(Unknown Source) at com.ibm.db2.jcc.t4.vb.h(Unknown Source) at com.ibm.db2.jcc.am.fp.jb(Unknown Source) at com.ibm.db2.jcc.am.fp.a(Unknown Source) at com.ibm.db2.jcc.am.fp.c(Unknown Source) at com.ibm.db2.jcc.am.fp.executeUpdate(Unknown Source) at com.dbmanage.db.DBUpdate.getRs(DBUpdate.java:21) at com.dbmanage.db.DBMain.update_sql(DBMain.java:88) at com.dbmanage.db.ReadFile.getFileContent(ReadFile.java:28) at com.dbmanage.db.ReadClipboard.lostOwnership(ReadClipboard.java:87) at sun.awt.datatransfer.SunClipboard.lostOwnershipNow(SunClipboard.java:313) at sun.awt.datatransfer.SunClipboard.lambda$lostOwnershipLater$0(SunClipboard.java:283) at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:311) at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758) at java.awt.EventQueue.access$500(EventQueue.java:97) at java.awt.EventQueue$3.run(EventQueue.java:709) at java.awt.EventQueue$3.run(EventQueue.java:703) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74) at java.awt.EventQueue.dispatchEvent(EventQueue.java:728) at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:205) at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93) at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
④ 【kingbase 人大金仓数据库没问题】
⑤ 【DM 达梦数据库没问题】
⑥ 【PG、瀚高数据库没问题】
⑦ 【polardb 数据库没问题】
第二章：测试环境
① 测试 sql 语句
形式如下面的语句以及在此基础上的嵌套 sql 语句：
insert into org_xxx (a, b, c, ...) values (a1, b1, c1, ...); commit; delete from org_xxx where code = 'xxx'; commit; update org_xxx set name = "xxx" where code = 'xxx'; commit;
② 测试代码块
外部调用下面的代码来执行。
import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; public class DBUpdate { public static int getRs(Connection conn, String sql) { /* 作用：插入、更新、删除数据库 返回：影响的行数：0代表无影响 */ Statement st = null; int rs = 0; try { // statement对象用于执行sql语句 st = conn.createStatement(); // 执行sql语句，成功返回修改行数，不成功返回0 rs = st.executeUpdate(sql); }catch(SQLException e){ e.printStackTrace(); }catch (Exception e){ e.printStackTrace(); } return rs; } }
③ 测试驱动和 JDK 版本
由于有些驱动没有现成的数据库，未进行测试，如：mysql、高斯。
喜欢的点个赞❤吧！
