Spring Batch connection to Oracle DataBase

Question

I am Using Spring Batch for Writing Data from flat file to Oracle DB .But i am getting error for Automatic Table Creation .Can Anyone Please Help On this.

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]; nested exception is java.sql.SQLException: ORA-00942: table or view does not exist

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

<bean id="report" class="com.mkyong.model.Report" scope="prototype" />

<batch:job id="reportJob">
    <batch:step id="step1">
        <batch:tasklet>
            <batch:chunk reader="cvsFileItemReader" writer="oracleItemWriter"
                commit-interval="2">
            </batch:chunk>
        </batch:tasklet>
    </batch:step>
</batch:job>

<bean id="cvsFileItemReader" class="org.springframework.batch.item.file.FlatFileItemReader">

    <!-- Read a csv file -->
    <property name="resource" value="classpath:cvs/report.csv" />

    <property name="lineMapper">
        <bean class="org.springframework.batch.item.file.mapping.DefaultLineMapper">

            <!-- split it -->
            <property name="lineTokenizer">
                <bean
                    class="org.springframework.batch.item.file.transform.DelimitedLineTokenizer">
                    <property name="names" value="date,impressions,clicks,earning" />
                </bean>
            </property>

            <property name="fieldSetMapper">

                <!-- return back to reader, rather than a mapped object. -->
                <!--
                    <bean class="org.springframework.batch.item.file.mapping.PassThroughFieldSetMapper" />
                -->

                <!-- map to an object -->
                <bean
                    class="org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper">
                    <property name="prototypeBeanName" value="report" />
                </bean>

            </property>

        </bean>
    </property>

</bean>

<bean id="oracleItemWriter"
    class="org.springframework.batch.item.database.JdbcBatchItemWriter">
    <property name="dataSource" ref="dataSource" />
    <property name="sql">
        <value>
        <![CDATA[        
            insert into RAW_REPORT(DATE1,IMPRESSIONS,CLICKS,EARNING) values (:date, :impressions, :clicks, :earning)
        ]]>
        </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>

DataBase.xml

<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:jdbc="http://www.springframework.org/schema/jdbc" 
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-3.2.xsd
    http://www.springframework.org/schema/jdbc 
    http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd">

<!-- connect to database -->
<!-- <bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/Sakila" />
    <property name="username" value="root" />
    <property name="password" value="" />
</bean> -->

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

-->
</bean> -->

<!-- <bean id="jobRepository" class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">
     <property name="dataSource" ref="dataSource" />
     <property name="transactionManager" ref="transactionManager"/>
     <property name="databaseType" value="oracle" />
     <property name="tablePrefix" value="BATCH_"/>
     <property name="isolationLevelForCreate" value="ISOLATION_DEFAULT"/>
</bean>   -->

<bean id="jobRepository"
class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean">
<property name="transactionManager" ref="transactionManager" />
</bean>

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
    <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
    <property name="username" value="system"/>
    <property name="password" value=""/>
</bean>

 <bean id="transactionManager"
    class="org.springframework.batch.support.transaction.ResourcelessTransactionManager" />  

<!-- create job-meta tables automatically -->
<!-- <jdbc:initialize-database data-source="dataSource">
    <jdbc:script location="org/springframework/batch/core/schema-drop-mysql.sql" />
    <jdbc:script location="org/springframework/batch/core/schema-mysql.sql" />
</jdbc:initialize-database> -->


<jdbc:initialize-database data-source="dataSource"  >
    <jdbc:script location="classpath:/org/springframework/batch/core/schema-oracle10g.sql" />
    <jdbc:script location="classpath:/org/springframework/batch/core/schema-drop-oracle10g.sql" />
     <!-- <jdbc:script location="classpath:oracle10g/initial-query.sql" /> -->

</jdbc:initialize-database>

Context.xml

<beans xmlns="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-3.2.xsd">

<!-- stored job-meta in database -->
<bean id="jobRepository"
    class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="transactionManager" ref="transactionManager" />
    <property name="databaseType" value="oracle" />
</bean>

<!-- stored job-meta in memory -->
<!-- 
<bean id="jobRepository"
    class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean">
    <property name="transactionManager" ref="transactionManager" />
</bean>
-->

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

Thanks Sh anti Bhushan

krohit krohit · Accepted Answer · 2018-03-20T12:29:41

The sequence in which script should be run is , Drop schema/tables first and then create the schema/tables.

i.e.

<jdbc:script location="classpath:/org/springframework/batch/core/schema-drop-oracle10g.sql" />

<jdbc:script location="classpath:/org/springframework/batch/core/schema-oracle10g.sql" />

you are using opposite sequence , which means dropping tables - hence, you get table or view doesnot exists.(because the tables/schemas are dropped)

Spring Batch connection to Oracle DataBase

1 Answers