Java 技术篇 - java同时连接多种数据库执行sql语句的兼容性验证，数据库类型包括：oracle、sqlserver、DB2、人大金仓、达梦、PG、瀚高、polardb
·
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、高斯。
喜欢的点个赞❤吧！
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
3
0- 0
已为社区贡献5条内容
所有评论(0)