java - Spring Batch 中连接过多异常

Question

在我的 Web 应用程序中，我使用 Spring Batch 框架和 mysql 数据库。在 DAO 类中，我关闭了所有连接，但仍然收到“连接过多”异常。请帮助如何解决此问题。

这是我的 Spring Batch 配置文件:

EramBatchJob.xml:

<?xml version="1.0" encoding="UTF-8"?>

<beans:beans xmlns="http://www.springframework.org/schema/batch"
xmlns:beans="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
http://www.springframework.org/schema/batch
http://www.springframework.org/schema/batch/spring-batch-2.1.xsd">

<beans:bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <beans:property name="location">
            <beans:value>eramBatchApp.properties</beans:value>
        </beans:property>
    </beans:bean>

 <beans:import resource="EramGBT-JOBREPOSITORY.xml"/>




   <!-- Headers -->
   <beans:bean id="accountHeader" class="com.order.batch.AccountHeader" scope="step">
   <beans:property name="seperator" value="#{jobParameters['Delimiter']}">
   </beans:property>
   </beans:bean>
   <beans:bean id="customerHeader" class="com.order.batch.CustomerHeader" scope="step">
  <beans:property name="seperator" value="#{jobParameters['Delimiter']}"></beans:property>
   </beans:bean>
   <beans:bean id="finstatHeader" class="com.order.batch.FinStatHeader" scope="step">
   <beans:property name="seperator" value="#{jobParameters['Delimiter']}"></beans:property>
   </beans:bean>
   <!-- Headers -->



   <!-- Asynchronous Executor -->
   <beans:bean id="taskExecutor"    class="org.springframework.core.task.SimpleAsyncTaskExecutor" scope="step">
<!-- <beans:property name="concurrencyLimit" value="5"/> -->
<beans:property name="concurrencyLimit" value="#{jobParameters['concurrencyLimit']}"/> 
</beans:bean>

 <!-- Reader --> 
<beans:bean id="accountInfoFileReader" class="org.springframework.batch.item.file.FlatFileItemReader" scope="step">
<beans:property name="resource" value="file:#{jobParameters['resource']}" />                            
<beans:property name="lineMapper">
<beans:bean class="org.springframework.batch.item.file.mapping.DefaultLineMapper">  
<beans:property name="lineTokenizer">               
<beans:bean class="org.springframework.batch.item.file.transform.FixedLengthTokenizer">
<!-- <beans:property name="delimiter" value=" "/> -->
<!-- <beans:property name="columns" value="1-30,31-120,121-190,191-260,261-310,311-360,361-370,371-380,381-382,420-423,400-419,385-393,424-451" /> Correct -->
<!-- <beans:property name="columns" value="1-30,31-120,121-190,191-260,261-310,311-360,361-370,371-380,381-382,371-380,400-419,385-393,420-451" />  -->
 <beans:property name="columns" value="#{jobParameters['InputDeLimiter']}" />
<beans:property name="names" value="accountNumber,businessName,addr1,addr2,city,state,zip,phone,countryCode,phoneCode,bizID,duns,dummy" /> 
</beans:bean>
</beans:property>
<beans:property name="fieldSetMapper">
<beans:bean class="com.order.batch.AccountInfoFiledSetMap" />
</beans:property>
</beans:bean>
</beans:property>
</beans:bean>

<!-- Processor -->

 <beans:bean id="productOrderProccesor" class="com.order.batch.ProductOrderProccesor" scope="step">
 <beans:property name="toolKitId" value="#{jobParameters['toolKitId']}"/>
 <beans:property name="toolKitPwd" value="#{jobParameters['toolKitPwd']}"/>
 </beans:bean>

<!-- Wrtiter -->

<!-- Account File Writer -->
<beans:bean id="accountFileWriter" class="org.springframework.batch.item.file.FlatFileItemWriter" scope="step">
<beans:property name="resource" value="file:#{jobParameters['AccountFilePath']}" />
 <!--  <beans:property name="appendAllowed" value="true" />-->
<!--<beans:property name="shouldDeleteIfExists" value="true"/>  -->
<beans:property name="lineAggregator">
<beans:bean class="org.springframework.batch.item.file.transform.DelimitedLineAggregator">
<beans:property name="delimiter" value="#{jobParameters['Delimiter']}"/>
<!-- <beans:property name="columns" value="1-30,31-120,121-190,191-260,261-310,311-360,361-370,371-380,381-382,371-380,400-419,385-393,420-452" />  -->
<beans:property name="fieldExtractor">
<beans:bean class="org.springframework.batch.item.file.transform.BeanWrapperFieldExtractor">
<beans:property name="names" value="sacct_Nbr_Cust,scountryCode_Cust,sCrcy_Cd,sDuns_Nbr,sEnq_Duns,sPrim_Name,sAddr_Line,sPost_Town,sPrim_Geo_Area,sPost_Cd,sCntry_Cd,sTlcm_Nbr,sCurr_Cntl_Yr,sTotl_Emp,sPrim_Sic,sLoc_Stat,sLgl_Form,sAddr_Tenr_Type_Cd,sCeo_Nme,sPayd_Scr,sPays_3_Mo_Ago,sPayd_Norm,sAvg_High_Cr,sHigh_Cr,sTot_Pmt,sFinl_Embt_Ind,sOut_Bus_In,sCrim_Ind,sHist_Ind,sImpt_Ind,sExpt_Ind,sDnb_Ratg,sRat_Cd,sAaa_Rat,sBus_Stru,sHq_Duns,sHq_Nme,sHq_Cntry_Cd,sPnt_Nme,sPnt_Duns,sPnt_Ctry_Cd,sDom_Ult_Pnt_Nme,sDom_Ult_Pnt_Duns,sDom_Ult_Ctry_Cd,sGbl_Ult_Pnt_Nme,sGbl_Ult_Pnt_Duns,sGbl_Ult_Ctry_Cd,sDelq_Scr_Entr_Natl_Pctl,sDelq_Scr_Entr_Inds_Pctl,sDelq_Scr_Entr_Inds_Defu_Indn,sFail_Scr_Entr_Natl_Pctl,sFail_Scr_Entr_Defu_Indn,sFail_Scr_Entr_Inds_Pctl,sFail_Scr_Entr_Inds_Defu_Indn,sCurr_Rato,sQk_Rato,sClm_Ind,sSuit_Jdgt_Ind,sTrdg_Styl,sTrdg_Styl1,sTrdg_Styl2,sTrdg_Styl3,sTrdg_Styl4,sPrim_Sic,sPrim_Sic_Type_Cd,sLcl_Atv_Cd,sLcl_Actv_Cd_Type,sStrt_Yr,sBus_Regn_Nbr,sMax_Cr,sIncn_Yr,sEu_Admin_App,sEu_Burgalary_Ind,sEu_Bus_Cease_Ind,sEu_Bus_Windup_Ind,sFire_Ind,sEu_Insol_Stmt,sEu_Liqd_It,sEu_Meet_Crdtr,sEu_Min_Dat_Ind,sEu_Misc_Ovrd,sEu_Neg_Inf_Ind,sFail_Scr_Entr_Scr_Cmty_Cd,sFail_Scr_Entr_Scr_Cmty_Cd1,sFail_Scr_Entr_Scr_Cmty_Cd2,sFail_Scr_Entr_Scr_Cmty_Cd3,sFail_Scr_Entr_Scr_Cmty_Cd4,sFail_Scr_Entr_Scr_Cmty_Cd5,sFail_Scr_Entr_Scr_Cmty_Cd6,sFail_Scr_Entr_Scr_Cmty_Cd7,sFail_Scr_Entr_Scr_Cmty_Cd8,sFail_Scr_Entr_Scr_Cmty_Cd9,sFail_Scr_Entr_Scr_Cmty_Cd10,sFail_Scr_Entr_Scr_Cmty_Cd11,sFail_Scr_Entr_Scr_Cmty_Cd12,sFail_Scr_Entr_Scr_Cmty_Cd13,sFail_Scr_Entr_Scr_Cmty_Cd14,sFail_Scr_Entr_Scr_Cmty_Cd15,sFail_Scr_Entr_Scr_Cmty_Cd16,sFail_Scr_Entr_Scr_Cmty_Cd17,sFail_Scr_Entr_Scr_Cmty_Cd18,sFail_Scr_Entr_Scr_Cmty_Cd19,sFail_Scr_Entr_Scr_Cmty_Cd20,sFail_Scr_Entr_Scr_Cmty_Cd21,sFail_Scr_Entr_Scr_Cmty_Cd22,sFail_Scr_Entr_Scr_Cmty_Cd23,sFail_Scr_Entr_Scr_Cmty_Cd24,sFail_Scr_Entr_Scr_Cmty_Cd25,sFail_Scr_Entr_Scr_Cmty_Cd26,sFail_Scr_Entr_Scr_Cmty_Cd27,sFail_Scr_Entr_Scr_Cmty_Cd28,sFail_Scr_Entr_Scr_Cmty_Cd29,sFail_Scr_Entr_Scr_Ovrd_Cd,sFail_Scr_Entr_Scr_Ovrd_Cd1,sFail_Scr_Entr_Scr_Ovrd_Cd2,sFail_Scr_Entr_Scr_Ovrd_Cd3,sFail_Scr_Entr_Scr_Ovrd_Cd4,sDelq_Scr_Entr_Scr_Cmty_Cd,sDelq_Scr_Entr_Scr_Cmty_Cd1,sDelq_Scr_Entr_Scr_Cmty_Cd2,sDelq_Scr_Entr_Scr_Cmty_Cd3,sDelq_Scr_Entr_Scr_Cmty_Cd4,sDelq_Scr_Entr_Scr_Cmty_Cd5,sDelq_Scr_Entr_Scr_Cmty_Cd6,sDelq_Scr_Entr_Scr_Cmty_Cd7,sDelq_Scr_Entr_Scr_Cmty_Cd8,sDelq_Scr_Entr_Scr_Cmty_Cd9,sDelq_Scr_Entr_Scr_Cmty_Cd10,sDelq_Scr_Entr_Scr_Cmty_Cd11,sDelq_Scr_Entr_Scr_Cmty_Cd12,sDelq_Scr_Entr_Scr_Cmty_Cd13,sDelq_Scr_Entr_Scr_Cmty_Cd14,sDelq_Scr_Scr_Ovrd_Cd,sDelq_Scr_Scr_Ovrd_Cd1,sDelq_Scr_Scr_Ovrd_Cd2,sDelq_Scr_Scr_Ovrd_Cd3,sDelq_Scr_Scr_Ovrd_Cd4,sDast_Ref_Dt,sRate_Id,sRpt_Net_Wrth,sRpt_Net_Sls,sRpt_Net_Income,sCustomer_Bs_Name,dummydelimiter"/>
</beans:bean>
</beans:property>
</beans:bean>
</beans:property>
<beans:property name="headerCallback" ref="accountHeader"></beans:property>
</beans:bean> 

<!-- Customer File Writer -->
<beans:bean id="customerFileWriter" class="org.springframework.batch.item.file.FlatFileItemWriter" scope="step">
<beans:property name="resource" value="file:#{jobParameters['CustomerFilePath']}" />
 <!-- <beans:property name="appendAllowed" value="true" /> -->
<!--<beans:property name="shouldDeleteIfExists" value="true"/> -->
<beans:property name="lineAggregator">
<beans:bean class="org.springframework.batch.item.file.transform.DelimitedLineAggregator">
<beans:property name="delimiter" value="#{jobParameters['Delimiter']}"/>
<beans:property name="fieldExtractor">
<beans:bean class="org.springframework.batch.item.file.transform.BeanWrapperFieldExtractor">
<beans:property name="names" value="sacct_Nbr_Cust,sbusiness_Nme_Cust,saddr1_Cust,saddr2_Cust,scity_Cust,sstate_Cust,szip_Cust,sphoneCode_Cust,sphone_Cust,scountryCode_Cust,sDuns_cust"/>
</beans:bean>
</beans:property>
</beans:bean>
</beans:property>
<beans:property name="headerCallback" ref="customerHeader"></beans:property>
</beans:bean>

<!-- Finance Statement Writer -->
<beans:bean id="financeStatFileWriter" class="org.springframework.batch.item.file.FlatFileItemWriter" scope="step">
<beans:property name="resource" value="file:#{jobParameters['FinStatFilePath']}" />
<!-- <beans:property name="appendAllowed" value="true" />  -->
<!-- <beans:property name="shouldDeleteIfExists" value="true"/> -->
<beans:property name="lineAggregator">
<beans:bean class="org.springframework.batch.item.file.transform.DelimitedLineAggregator">
<beans:property name="delimiter" value="#{jobParameters['Delimiter']}"/>
<beans:property name="fieldExtractor">
<beans:bean class="org.springframework.batch.item.file.transform.BeanWrapperFieldExtractor">
<beans:property name="names" value="sacct_Nbr_Cust,sDnb_Ind,sCash_Liq_Aset,sAct_Rec,sAct_Pay,sStk,sTot_Curr_Aset,sTot_Curr_Liab,sTot_Aset,sTot_Liab,sNet_Wrth,sItng_Aset,sSls,sNet_Incm,sStmt_Dt,sStmt_Type,sRate_Id,sStmt_Crcy_Cd,dummydelimiter"/>
</beans:bean>
</beans:property>
</beans:bean>
</beans:property>
<beans:property name="headerCallback" ref="finstatHeader"></beans:property>
</beans:bean>

<!-- Composite Writer -->
<beans:bean id="compositeWriter" class="org.springframework.batch.item.support.CompositeItemWriter">
<beans:property name="delegates">
<beans:list>
<beans:ref bean="accountFileWriter" />
<beans:ref bean="customerFileWriter" />
<beans:ref bean="financeStatFileWriter" />
</beans:list>
</beans:property>
</beans:bean>

<!-- Writers fro Skip Plocies -->
<beans:bean id="excludeWriter" class="org.springframework.batch.item.file.FlatFileItemWriter">

    <beans:property name="shouldDeleteIfExists" value="true"/> 
    <beans:property name="lineAggregator">
      <beans:bean class="org.springframework.batch.item.file.transform.DelimitedLineAggregator">
      <beans:property name="delimiter" value=","/>
      <beans:property name="fieldExtractor">
      <beans:bean class="org.springframework.batch.item.file.transform.BeanWrapperFieldExtractor">
    <beans:property name="names" value="errormessage,accountNumber,businessName,addr1,addr2,city,state,zip,phone,countryCode,phoneCode,bizID,duns"/>
    </beans:bean>
    </beans:property>
    </beans:bean>
    </beans:property>
  </beans:bean>

  <beans:bean id="writeWriter" class="org.springframework.batch.item.file.FlatFileItemWriter">

    <beans:property name="shouldDeleteIfExists" value="true"/> 
    <beans:property name="lineAggregator">
      <beans:bean class="org.springframework.batch.item.file.transform.DelimitedLineAggregator">
      <beans:property name="delimiter" value=","/>
      <beans:property name="fieldExtractor">
      <beans:bean class="org.springframework.batch.item.file.transform.BeanWrapperFieldExtractor">
    <beans:property name="names" value="sAcct_Nbr,sDuns_Nbr,sCustomer_Bs_Name"/>
    </beans:bean>
    </beans:property>
    </beans:bean>
    </beans:property>
  </beans:bean>

<!-- Listeners -->
<beans:bean id="eramBatchListener" class="com.order.batch.EramJobListener">
  <beans:property name="excludeWriter" ref="excludeWriter" ></beans:property>
   <beans:property name="writeWriter" ref="writeWriter" ></beans:property>
  </beans:bean>



<!-- Job  -->

<job id="EramBatchJob" job-repository="jobRepository" > 
<step id="step2"> 
<tasklet transaction-manager="jobRepository-transactionManager" task-executor="taskExecutor">
<chunk reader="accountInfoFileReader" processor="productOrderProccesor" writer="compositeWriter" 
 commit-interval="#{jobParameters['CommitInterval']}" skip-limit="10000" retry-limit="1">
 <streams>
<stream ref="compositeWriter"/> 
 <stream ref="accountInfoFileReader"/> 
  <stream ref="excludeWriter"/> 
  <stream ref="writeWriter"/>
  </streams>

<retryable-exception-classes>
<include class="java.lang.Exception"/>
</retryable-exception-classes>
<skippable-exception-classes>       

                <include class="org.springframework.batch.item.file.FlatFileParseException"/>       
                <include class="org.springframework.batch.item.file.transform.IncorrectLineLengthException"/> 
                <include class="org.springframework.batch.core.step.skip.NonSkippableReadException"/>
                <include class="java.io.IOException"/>
                <include class="org.springframework.beans.NotReadablePropertyException"/>
                <include class="org.springframework.batch.item.ItemStreamException"/>
                <include class="com.order.exception.InvalidDunsOrCountryCodeException"/>        

</skippable-exception-classes>
        <listeners>
<listener ref="eramBatchListener" />
</listeners> 
 </chunk> 

</tasklet>
<listeners>
<listener ref="eramBatchListener"/>
</listeners>
</step> 

<listeners>
<listener ref="eramBatchListener"/>
</listeners>    
</job>
</beans:beans>

EramGBT-JOBREPOSITORY.xml:

    <?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
xsi:schemaLocation="http://www.springframework.org/schema/beans 
http://www.springframework.org/schema/beans/spring-beans-2.0.xsd"> 

<bean id="jobRepository-dataSource" 
class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">                  
    <property name="driverClassName" value="${EramBatch.App.DataBase.DriverClass}" />              
    <property name="url" value="${EramBatch.App.DataBase.DataBaseURL}" />                
    <property name="username" value="${EramBatch.App.DataBase.UserName}" />  <!-- your user id. e.g. root-->         
    <property name="password" value="${EramBatch.App.DataBase.Password}" /> <!-- your password-->
    <property name="maxIdle" value="10"/>    
    <property name="maxActive" value="100"/>    
    <property name="maxWait" value="10000"/>    
    <property name="validationQuery" value="select 1"/>    
    <property name="testOnBorrow" value="false"/>    
    <property name="testWhileIdle" value="true"/>    
    <property name="timeBetweenEvictionRunsMillis" value="1200000"/>    
    <property name="minEvictableIdleTimeMillis" value="1800000"/>    
    <property name="numTestsPerEvictionRun" value="5"/>    
    <property name="defaultAutoCommit" value="false"/>     
</bean> 

<bean id="jobRepository-transactionManager"  
class="org.springframework.jdbc.datasource.DataSourceTransactionManager" lazy-init="true">
<property name="dataSource"  ref="jobRepository-dataSource" />   
</bean>

<bean id="jobRepository" 
class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">
  <property name="dataSource" ref="jobRepository-dataSource" />
  <property name="transactionManager" ref="jobRepository-transactionManager"/>
  <property name="isolationLevelForCreate" value="ISOLATION_READ_UNCOMMITTED" />
  <property name="databaseType" value="mysql" />
  <property name="tablePrefix" value="batch_"/>
</bean>

<bean id="asyncTaskExecutor" 
class="org.springframework.core.task.SimpleAsyncTaskExecutor"/>


<bean id="jobLauncher" 
class="org.springframework.batch.core.launch.support.SimpleJobLauncher">  
    <property name="jobRepository" ref="jobRepository"/>    
    <property name="taskExecutor">
<bean class="org.springframework.core.task.SimpleAsyncTaskExecutor" />
</property>     
</bean>

<bean id="jobExplorer" 
class="org.springframework.batch.core.explore.support.JobExplorerFactoryBean" 
p:dataSource-ref="jobRepository-dataSource" p:tablePrefix="batch_" />

<bean id="jobRegistry"  class="org.springframework.batch.core.configuration.support.MapJobRegistry"/>

<bean id="jobOperator"   class="org.springframework.batch.core.launch.support.SimpleJobOperator">
        <property name="jobExplorer" ref="jobExplorer"/>
        <property name="jobRepository" ref="jobRepository"/>
        <property name="jobRegistry" ref="jobRegistry" />
        <property name="jobLauncher" ref="jobLauncher" />
</bean>

</beans>

Answer 1

这看起来像是 MySQL 错误。尝试增加/etc/my.cnf 中的 max_connections。

您还可以研究应用程序的多处理和线程模型。 MySQL 连接耗尽可能表明应用程序始终产生新线程/进程的问题。