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的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。