Reading XML and writing to database using Spring Batch

Question

I want to read an XML and write it to a MySql database

<?xml version="1.0" encoding="UTF-8"?>
<parentNode id="001" name="p1" type="t1">
    <line>message1</line>
    <line>message2</line>
    <line>message3</line>
    <line>message4</line>
</parentNode>

I have the following reader

@XmlRootElement(name = "parentNode")
public class ParentNode {
    private String id;
    private String name;
    private String type;
    private List<String> lineNode;

    @XmlAttribute(name = "id")
    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    @XmlAttribute(name = "name")
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @XmlAttribute(name = "type")
    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    @XmlElement(name = "line")
    public List<String> getLineNode() {
        return lineNode;
    }

    public void setLineNode(List<String> lineNode) {
        this.lineNode = lineNode;
    }
}

I have a writer which is as follows

public class ParentNodeItemPreparedStatementSetter implements ItemPreparedStatementSetter<ParentNode> {

@Override
public void setValues(ParentNode parentNode, PreparedStatement ps) throws SQLException {
    ps.setString(1, parentNode.getId());
    ps.setString(1, parentNode.getLineNode()); //HOW DO I WRITE EACH LINE INSTEAD OF ONLY THE LAST ONE

}

The question is how do I write all three line into the database. This code only writes <line>message4</line> message4 into the database. I want the database to have

ID | LINE
001 | message1
001 | message2
001 | message3
001 | message4

My configuration is as follows

    <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"
    xsi:schemaLocation="http://www.springframework.org/schema/batch http://www.springframework.org/schema/batch/spring-batch-3.0.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd">

    <import resource="classpath:context-datasource.xml" />

    <!-- JobRepository and JobLauncher are configuration/setup classes -->
    <bean id="jobRepository"
        class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean" />

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

    <bean id="xmlItemReaderStep" class="org.springframework.batch.item.xml.StaxEventItemReader">
        <property name="resource" value="classpath:test.xml" />
        <property name="fragmentRootElementName" value="parentNode" />
        <property name="unmarshaller">
            <bean class="org.springframework.oxm.jaxb.Jaxb2Marshaller">
                <property name="classesToBeBound">
                    <list>
                        <value>com.results.model.ParentNode</value>
                    </list>
                </property>
            </bean>
        </property>
    </bean>

    <bean id="resultsWriter"
        class="org.springframework.batch.item.database.JdbcBatchItemWriter">
        <property name="dataSource" ref="dataSource" />
        <property name="sql">
            <value>
                <![CDATA[        
                    insert into playground.LINE(id, line) values (?, ?);
                ]]>
            </value>
        </property>
        <property name="ItemPreparedStatementSetter">
            <bean
                class="com.results.preparedstatement.ParentNodeItemPreparedStatementSetter" />
        </property>
    </bean>

    <bean id="itemProcessor"
        class="com.results.itemprocessors.ParentNodeItemProcessor" />


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


    <batch:job id="resultJob">
        <batch:step id="step1">
            <batch:tasklet transaction-manager="transactionManager">
                <batch:chunk reader="xmlItemReaderStep" writer="resultsWriter"
                    processor="itemProcessor" commit-interval="100" />
            </batch:tasklet>
        </batch:step>
    </batch:job>

</beans> 

EDIT: I am able get what I need by modifying the ParentNodeItemPreparedStatementSetter like below. But It looks like not the right way to do it.

public class ParentNodeItemPreparedStatementSetter implements ItemPreparedStatementSetter<ParentNode> {
@Override
public void setValues(ParentNode parentNode, PreparedStatement ps) throws SQLException {
    for (int i = 0; i < parentNode.getLineNode().size(); i++) {
        ps.setString(1, parentNode.getId());
        ps.setString(2, parentNode.getLineNode().get(i));
        if (!(i == parentNode.getLineNode().size()-1))
            ps.addBatch();
    }
}

}

Answer 1

It seems that there is not matching between which you expect :

ID | LINE
001 | message1
001 | message2
001 | message3
001 | message4

and you two input structures :

<?xml version="1.0" encoding="UTF-8"?>
<parentNode id="001" name="p1" type="t1">
  <line>message1</line>
  <line>message2</line>
  <line>message3</line>
  <line>message4</line>
</parentNode>


@XmlRootElement(name = "parentNode")
public class ParentNode {
  private String id;
  private String name;
  private String type;
  private List<String> lineNode;
  ...

In input, you have 1 line in your xml and therefore a single ParentNode instance when your reader has done its work but after the writing, in your tables you want to have as many lines as elements in the lineNode List, so 4 lines. It's not symmetric.

If your actual input xml structure is required, change nothing and do a kind of custom mapping during the writing.

And in a some way, you do that with ParentNodeItemPreparedStatementSetter by adding as many lines as elements in the lineNode List :

 for (int i = 0; i < parentNode.getLineNode().size(); i++) {
    ps.setString(1, parentNode.getId());
    ps.setString(2, parentNode.getLineNode().get(i));
    if (!(i == parentNode.getLineNode().size()-1))
        ps.addBatch();
}

It is a trick but according the documentation, it seems not bad :

public interface ItemPreparedStatementSetter

A convenient strategy for SQL updates, acting effectively as the inverse of RowMapper.

Edit : to answer to your comment. If your actual input xml structure can be modified because it is generated for example, you could design the xml structure for that it matches with your expected. In input you should add the parent-id value :

<?xml version="1.0" encoding="UTF-8"?>
<parentNode id="001" name="p1" type="t1">
   <line parent-id="001">message1</line>
   <line parent-id="001">message2</line>
   <line parent-id="001">message3</line>
   <line parent-id="001">message4</line>
</parentNode>

And in your reader conf xml, you would replace

<property name="fragmentRootElementName" value="parentNode" />

by <property name="fragmentRootElementName" value="line" />

You should also modify the structure of your xml objects. Changing your line List from the String type to its own class which owns the two needed field : id and line. In this way, you could have the expected data in input and in output without no one processing.