java - preparedStatement.executeUpdate() 不插入值

Question

我正在 JBehave 非 Maven 框架中工作，并且正在运行一个文本文件内的插入语句，我将其作为 qry 传递给我的方法。插入语句为:

Insert into croutreach.ACME_OUTREACH_EMT_STG 
(SEQ,EID,EVENT_TYPE_HINT,LOADER_TYPE,CLIENT_ID_LIST,ACCT_NUM_LIST,PROCS_DT)
values (49,<EID>,'new','CI',null,null,'02-SEP-13 06.55.41.952000000 PM')

下面的代码正在执行并打印下面的语句

Executing to run insert query

但在此之后，它不会继续进行下一步，这就是连接没有关闭的原因。它陷入了 preparedstatement.executeupdate() 的困境。

private Connection dbConnection = null;
public boolean runInsertQuery(String qry) {
int result;
System.out.println("Preparing to run insert query");
try {
    PreparedStatement preparedStatement =dbConnection.prepareStatement(qry);
    System.out.println("Execuing to run insert query");
    result = preparedStatement.executeUpdate();
    System.out.println("Completed to run insert query");
    return result == 1;
    } catch (SQLException e) {
        LOG.fatal("Error in insertion into table  :", e);
        return false;
    } finally {
        this.disconnectDb();
    }
}

我的输出如下:-

Method public org.jbehave.core.configuration.Configuration com.TEA.framework.Functional.OnlineCoachingStory.configuration() has a @Test annotation but also a return value: ignoring it. Use <suite allow-return-values="true"> to fix this
Method public org.jbehave.core.steps.InjectableStepsFactory com.TEA.framework.Functional.OnlineCoachingStory.stepsFactory() has a @Test annotation but also a return value: ignoring it. Use <suite allow-return-values="true"> to fix this  
[TestNG] Running:  
  C:\Users\M41974\AppData\Local\Temp\testng-eclipse-1062921629\testng-customsuite.xml

Processing system properties {}  
Using controls EmbedderControls[batch=false,skip=false,generateViewAfterStories=true,ignoreFailureInStories=false,ignoreFailureInView=false,verboseFailures=false,verboseFiltering=false,storyTimeoutInSecs=900000000,threads=1]

(BeforeStories)

Running story com/TEA/framework/Story/Copy of OnlineCoaching.story  

(com/TEA/framework/Story/Copy of OnlineCoaching.story)  
Scenario: Setup the Environment  
2015-11-03 04:46:49,425 FATAL pool-1-thread-1- ACME.SAT.Server.Path not found in the Config.properties file  
2015-11-03 04:46:49,440 INFO pool-1-thread-1- Host:- lsh1042a.sys.cigna.com  Username:- ank_1   password:- abc_123 path:- null  
Given Set the environment to SAT  
2015-11-03 04:46:49,768 INFO pool-1-thread-1- DB Connection successful  
Insert into croutreach.ACME_OUTREACH_EMT_STG (SEQ,EID,EVENT_TYPE_HINT,LOADER_TYPE,CLIENT_ID_LIST,ACCT_NUM_LIST,PROCS_DT) values (49,'1234567890','new','CI',null,null,'02-SEP-13 06.55.41.952000000 PM')  
Preparing to run insert query  
Executing to run insert query  
|

以前，我一直保持 SQL 开发人员窗口打开，当关闭该窗口后尝试并再次运行相同的脚本时，它显示 fatal error :ORA-00001:违反了唯一约束。

我面临 3 个不同的问题:

1. 当 SQL Developer 打开时，运行脚本在插入时卡住。

2. 当卡住时，我关闭了 SQL Developer 窗口 session ，之后输出如下:

   2015-11-03 05:01:35,061 INFO main- DB Connection successful
   Insert into croutreach.ACME_OUTREACH_EMT_STG (SEQ,EID,EVENT_TYPE_HINT,LOADER_TYPE,CLIENT_ID_LIST,ACCT_NUM_LIST,PROCS_DT) values (49,'02123456789','new','CI',null,null,'02-SEP-13 06.55.41.952000000 PM')
   Preparing to run insert query
   Executing to run insert query
   Completed to run insert query
   2015-11-03 05:02:11,128 INFO main- DB Disconnected!!
   Insert complete
   

3. 关闭 SQL Developer 窗口后，再次运行脚本已完成，但出现 fatal error ，如下所示:

   2015-11-03 05:13:08,177 INFO main- DB Connection successful
   Insert into croutreach.ACME_OUTREACH_EMT_STG (SEQ,EID,EVENT_TYPE_HINT,LOADER_TYPE,CLIENT_ID_LIST,ACCT_NUM_LIST,PROCS_DT) values (49,'02123456789','new','CI',null,null,'02-SEP-13 06.55.41.952000000 PM')
   Preparing to run insert query
   Executing to run insert query
   2015-11-03 05:13:08,319 FATAL main- Error in insertion into table  :
   java.sql.SQLException: ORA-00001: unique constraint (CROUTREACH.ACME_OUTREACH_EMT_STG_PK) violated
   
       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
       at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
       at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
       at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
       at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
       at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)
       at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3400)
       at com.TEA.framework.Technology.DatabaseClass.runInsertQuery(DatabaseClass.java:169)
       at com.TEA.framework.Business.AcmeDB.insertACME_OUTREACH_EMT_STG(AcmeDB.java:186)
       at com.TEA.framework.Technology.MainClass.DBinsertTest(MainClass.java:123)
       at com.TEA.framework.Technology.MainClass.main(MainClass.java:24)
   2015-11-03 05:13:08,350 INFO main- DB Disconnected!!
   2015-11-03 05:13:08,365 INFO main- DB Disconnected!!
   

Answer 1

您只是看到一个 session 阻塞另一个 session 。当您在 SQL Developer 中执行插入操作并让 session 保持运行而不提交或回滚时，您将锁定新插入的行和一个(某种待处理的)索引条目。

如果您运行程序并再次执行相同的插入，那么这是在不同的 session 中。它看不到您通过 SQL Developer 插入的数据，但它可以看到锁，并等待该锁被清除。当它被阻止时，它只是坐在那里等待，您将其解释为它被卡住了。

如果您回滚 SQL Developer 插入，则锁定将被清除，并且您的程序被阻止的插入将继续成功。新记录将被插入，并将被提交或回滚，具体取决于您的代码下一步执行的操作或连接的自动提交设置。

它看起来像是提交了，因为再次运行它尝试重复插入，现在它确实看到了现有的提交数据 - 因此出现 ORA-00001 错误，因为您试图使用相同的主条目创建第二个条目核心值(value)。如果您提交了 SQL Developer session 而不是回滚，您会看到同样的情况。

如果您想再次运行程序，则需要在再次尝试插入之前删除该行(并提交更改!)。