Reputation: 23
I'm facing a problema with my job,
I'm trying to read records from database and write in a txt file. The database contains 1.800.000 records, with 149 columns, the problem is that the select is in the jobConfig.xml, in the bean 'mysqlItemReader', but, i think the select try to load all records in the JVM memory and then i got out of memory, using randtb.cliente limit 200000 it runs ok, but more than 500k of records i got out of memory, how avoid this error? Thanks!
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:batch="http://www.springframework.org/schema/batch" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:util="http://www.springframework.org/schema/util"
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">
<import resource="Context.xml" />
<bean id="tutorial" class="extractor.main.Tutorial" scope="prototype" />
<bean id="itemProcessor" class="extractor.main.CustomItemProcessor" />
<batch:job id="helloWorldJob">
<batch:step id="step1">
<batch:tasklet>
<batch:chunk reader="mysqlItemReader" writer="flatFileItemWriter"
processor="itemProcessor" commit-interval="50">
</batch:chunk>
</batch:tasklet>
</batch:step>
</batch:job>
<bean id="mysqlItemReader"
class="org.springframework.batch.item.database.JdbcCursorItemReader">
<property name="dataSource" ref="dataSource"/>
<property name="sql" value="select * from randtb.cliente"/>
<property name="rowMapper">
<bean class="extractor.main.TutorialRowMapper"/>
</property>
</bean>
<bean id="flatFileItemWriter" class=" org.springframework.batch.item.file.FlatFileItemWriter">
<property name="resource" value="file:target/outputfiles/employee_output.txt" />
<property name="lineAggregator">
<bean
class=" org.springframework.batch.item.file.transform.PassThroughLineAggregator" />
</property>
</bean>
Upvotes: 2
Views: 7331
Reputation: 23
Without verifyCursorPosition i got the om.mysql.jdbc.RowDataDynamic$OperationNotSupportedException: Operation not supported for streaming result sets
But, adding it, looks like it worked
<bean id="mysqlItemReader"
class="org.springframework.batch.item.database.JdbcCursorItemReader">
<property name="dataSource" ref="dataSource"/>
<property name="sql" value="select * from randtb.cliente"/>
<property name="fetchSize" value="#{T(java.lang.Integer).MIN_VALUE}"/>
<property name="verifyCursorPosition" value="false"/>
<property name="rowMapper">
<bean class="extractor.main.TutorialRowMapper"/>
</property>
Upvotes: 0
Reputation: 21463
By default, MySql will return everything in the ResultSet
which is causing your OOM exception. In order for it not to do that, you need to set the JdbcCursorItemReader#setFetchSize(Integer.MIN_VALUE)
. This will tell Spring Batch to set that value on the PreparedStatement
as well as setting PreparedStatement#setFetchDirection(ResultSet.FETCH_FORWARD)
. This will tell MySql to stream the data, thereby not blowing your stack.
So for your specific example, you need to change your ItemReader
configuration to be:
<bean id="mysqlItemReader"
class="org.springframework.batch.item.database.JdbcCursorItemReader">
<property name="dataSource" ref="dataSource"/>
<property name="sql" value="select * from randtb.cliente"/>
<property name="fetchSize" value="#{T(java.lang.Integer).MIN_VALUE}"/>
<property name="rowMapper">
<bean class="extractor.main.TutorialRowMapper"/>
</property>
</bean>
You can read more about how this works in MySql in their documentation here: https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-implementation-notes.html (see the ResultSet section).
Upvotes: 7