使用2个表的更新不能从批处理工作

问题描述：

我正在使用Oracle数据库。

我有以下查询：

UPDATE EFFET_COMMERCE 
SET EFFET_COMMERCE.CI21_ENCOF7 = (SELECT SUM(EFFET_COMMERCE_23_TMP.CI23_CUMTCR) FROM 
EFFET_COMMERCE_23_TMP WHERE EFFET_COMMERCE.CI2X_IDCONT = EFFET_COMMERCE_23_TMP.CI2X_IDCONT) 
WHERE EFFET_COMMERCE.CI21_CDPRRT = '036993' 
AND EXISTS (SELECT SUM(EFFET_COMMERCE_23_TMP.CI23_CUMTCR) FROM 
EFFET_COMMERCE_23_TMP WHERE EFFET_COMMERCE.CI2X_IDCONT = EFFET_COMMERCE_23_TMP.CI2X_IDCONT)

所以我需要更新从EFFET_COMMERCE表CI21_ENCOF7的列，其中CI21_CDPRRT是'036993'与具有相同CI2X_IDCONT的EFFET_COMMERCE_23_TMP的CI23_CUMTCR值的总和。

在SQLDeveloper上，此查询似乎正常工作。但是当我在Spring Batch的使用它，我发现了错误：

坟墓遇到执行步骤injectionDesDonnees在工作effetCommercialeBeanJob org.springframework.jdbc.BadSqlGrammarException错误：PreparedStatementCallback;糟糕的SQL语法[

INSERT INTO EFFET_COMMERCE（CI10_DATRAI，CI2X_IDCGIC，CI2X_IDCONT，CI20_CDDV，CI21_CDPRRT，CI21_CDEPR3，CI21_DACRR7，CI21_DAFEPN，CI21_DADAU8，CI21_DAFNAU，CI21_MTAUTN，CI21_DADTAP，CI21_DAFIAB，CI21_MTAUTP，CI21_ENCOF7，CI21_DAECPN，CI21_STCOLE）值（？（SELECT SUM（EFFET_COMMERCE_23_TMP.CI23_CUMTCR）FROM（from gamasutra）FROM（from gamasutra）FROM（from gamasutra）FROM（from gamasutra）FROM（from gamasutra）FROM EFFET_COMMERCE_23_TMP WHERE EFFET_COMMERCE.CI2X_IDCONT = EFFET_COMMERCE_23_TMP.CI2X_IDCONT）WHERE EFFET_COMMERCE.CI21_CDPRRT = '036993' AND EXISTS（SELECT SUM（EFFET_COMMERCE_23_TMP.CI23_CUMTCR） FROM EFFET_COMMERCE_23_TMP WHERE EFFET_COMMERCE.CI2X_IDCONT = EFFET_COMMERCE_23_TMP.CI2X_IDCONT） ];嵌套的例外是java.sql.BatchUpdateException：ORA-00933：LA COMMANDE SQL NE SE termine PAS correctement

（SQL命令犯规”端正确）

任何想法可能是错误的？

编辑： 这里是从作业-report.xml将豆：

<bean id="insertItemWriter" 
    class="org.springframework.batch.item.database.JdbcBatchItemWriter"> 
    <property name="dataSource" ref="dataSource" /> 
    <property name="sql"> 
     <value> 
     <![CDATA[ 
      insert into EFFET_COMMERCE(CI10_DATRAI,CI2X_IDCGIC,CI2X_IDCONT,CI20_CDDV,CI21_CDPRRT,CI21_CDEPR3,CI21_DACRR7,CI21_DAFEPN,CI21_DADAU8,CI21_DAFNAU,CI21_MTAUTN,CI21_DADTAP,CI21_DAFIAB,CI21_MTAUTP,CI21_ENCOF7,CI21_DAECPN,CI21_STCOLE) values (:CI10_DATRAI, :CI2X_IDCGIC, :CI2X_IDCONT, :CI20_CDDV, :CI21_CDPRRT, :CI21_CDEPR3, :CI21_DACRR7, :CI21_DAFEPN, :CI21_DADAU8, :CI21_DAFNAU, :CI21_MTAUTN, :CI21_DADTAP, :CI21_DAFIAB, :CI21_MTAUTP, :CI21_ENCOF7, :CI21_DAECPN, :CI21_STCOLE); 
      UPDATE EFFET_COMMERCE SET EFFET_COMMERCE.CI21_ENCOF7 = (SELECT SUM(EFFET_COMMERCE_23_TMP.CI23_CUMTCR) FROM EFFET_COMMERCE_23_TMP WHERE EFFET_COMMERCE.CI2X_IDCONT = EFFET_COMMERCE_23_TMP.CI2X_IDCONT) WHERE EFFET_COMMERCE.CI21_CDPRRT = '036993' AND EXISTS (SELECT SUM(EFFET_COMMERCE_23_TMP.CI23_CUMTCR) FROM EFFET_COMMERCE_23_TMP WHERE EFFET_COMMERCE.CI2X_IDCONT = EFFET_COMMERCE_23_TMP.CI2X_IDCONT); 
     ]]> 
     </value> 
    </property> 
    <!-- It will take care matching between object property and sql name parameter --> 
    <property name="itemSqlParameterSourceProvider"> 
     <bean 
      class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" /> 
    </property> 
</bean>

从工作-report.xml将在compositeWriter豆：

<bean id="compositeWriter" class="com.socgen.cie.config.CompositeWriter" scope="step"> 
    <property name="datRaiP" value="#{jobParameters['datRaiP']}" /> 
    <property name="delegate1" ref="insert_20_ItemWriter" /> 
    <property name="delegate2" ref="insert_21_ItemWriter" /> 
    <property name="delegate3" ref="insert_23_ItemWriter" /> 
</bean>

而且compositeWriter java类：

public class CompositeWriter implements ItemWriter<EffetCommercialeBean> { 

    ItemWriter<EffetCommercialeBean> itemWriter1; 
    ItemWriter<EffetCommercialeBean> itemWriter2; 
    ItemWriter<EffetCommercialeBean> itemWriter3; 
    private Date datRaiP; 


    public void write(List<? extends EffetCommercialeBean> items) throws Exception { 
     List<EffetCommercialeBean> inserts_20 = new ArrayList<EffetCommercialeBean>(); 
     List<EffetCommercialeBean> inserts_21 = new ArrayList<EffetCommercialeBean>(); 
     List<EffetCommercialeBean> inserts_23 = new ArrayList<EffetCommercialeBean>(); 
     Date date = getDatRaiP(); 
     for(EffetCommercialeBean re : items) { 
//   if(re.getCI10_DATRAI()!=null){ 
//    date = dateFinDuMois(re.getCI10_DATRAI()); 
//   } 
      re.setCI10_DATRAI(date); 
      if("20".equals(re.getPattern())) { 
       inserts_20.add(re); 
      } 
      if("21".equals(re.getPattern())) { 
       inserts_21.add(re); 
      } 
      if("23".equals(re.getPattern())) { 
       inserts_23.add(re); 
      } 
     } 
     itemWriter1.write(inserts_20); 
     itemWriter2.write(inserts_21); 
     itemWriter3.write(inserts_23); 
    } 

    public void setDelegate1(ItemWriter<EffetCommercialeBean> itemWriter){ 
     this.itemWriter1 = itemWriter; 
    } 

    public void setDelegate2(ItemWriter<EffetCommercialeBean> itemWriter) { 
     this.itemWriter2 = itemWriter; 
    } 

    public void setDelegate3(ItemWriter<EffetCommercialeBean> itemWriter) { 
     this.itemWriter3 = itemWriter; 
    } 

    public Date getDatRaiP() { 
     return datRaiP; 
    } 

    public void setDatRaiP(Date datRaiP) { 
     this.datRaiP = datRaiP; 
    } 

}

您使用了不正确的Spring方法来执行更新。发布您的Java代码。解释器将你的SQL语句转换为INSERT INTO –

刚刚编辑了这篇文章，现在我猜我需要在'job-report.xml'中为更新创建一个特定的bean？我想我还需要有一个compositeUpdater java类？ – Ellone

答

最后，因为我想执行UPDATE最后，我结束了作业执行后做，因为我不知道春天什么批次，这对我来说是简单的解决方案：

execution = jobLauncher.run(job, new JobParameters(jobParametersMap)); 

       DataSource datasource = (DataSource) context.getBean("dataSource"); 
       Connection conn = datasource.getConnection(); 
       PreparedStatement query = conn.prepareStatement("UPDATE EFFET_COMMERCE SET EFFET_COMMERCE.CI21_ENCOF7 = (SELECT SUM(EFFET_COMMERCE_23_TMP.CI23_CUMTCR) FROM EFFET_COMMERCE_23_TMP WHERE EFFET_COMMERCE.CI2X_IDCONT = EFFET_COMMERCE_23_TMP.CI2X_IDCONT) WHERE EFFET_COMMERCE.CI21_CDPRRT = '036993' AND EXISTS (SELECT SUM(EFFET_COMMERCE_23_TMP.CI23_CUMTCR) FROM EFFET_COMMERCE_23_TMP WHERE EFFET_COMMERCE.CI2X_IDCONT = EFFET_COMMERCE_23_TMP.CI2X_IDCONT)"); 
       query.executeQuery();

使用2个表的更新不能从批处理工作

相关推荐