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.49at 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语句，成功返回修改行数，不成功返回0rs = st.executeUpdate(sql);}catch(SQLException e){e.printStackTrace();}catch (Exception e){e.printStackTrace();}return rs;}}

③ 测试驱动和 JDK 版本

由于有些驱动没有现成的数据库，未进行测试，如：mysql、高斯。

喜欢的点个赞❤吧！

Java 技术篇 - java同时连接多种数据库执行sql语句的兼容性验证 数据库类型包括：oracle sqlserver DB2 人大金仓 达梦 PG 瀚高 polardb