Search code examples
xmlspringspring-batchxstream

Spring Batch : Database to XML issue

I have a simple spring batch program, which reads from a database table and forms an output XML using default XStreamMarshaller writer class below

org.springframework.oxm.xstream.XStreamMarshaller

My database table TOY from where input gets read contains two columns : TOY_NAME, TOY_COLOR

For a single TOY, there could be several entries for different colors.

For e.g. for TOY CAR and TOY BUS, database entries would be as below:

TOY_NAME   |    TOY_COLOR
--------------------------
 CAR       |    RED
 CAR       |    YELLOW
 CAR       |    BLUE
 BUS       |    RED
 BUS       |    YELLOW
--------------------------

The result set which will get passed to ROW MAPPER would be 5 rows each having two columns viz. toy_name and color.

My java domain objects consists of a main object TOYS containing list of TOY objects.

TOY object in itself contains two fields viz. toyName and list of String toyColor.

My output XML with simple read-write configuration is as below:

<TOYS>

   <TOY>
       <NAME>CAR</NAME>
       <COLORS>
         <COLOR>RED</COLOR>
       </COLORS>
   </TOY>

   <TOY>
       <NAME>CAR</NAME>
       <COLORS>
         <COLOR>YELLOW</COLOR>
       </COLORS>
   </TOY>

   <TOY>
       <NAME>CAR</NAME>
       <COLORS>
         <COLOR>BLUE</COLOR>
       </COLORS>
   </TOY>

   <TOY>
       <NAME>BUS</NAME>
       <COLORS>
         <COLOR>RED</COLOR>
       </COLORS>
   </TOY>

   <TOY>
       <NAME>BUS</NAME>
       <COLORS>
         <COLOR>BLUE</COLOR>
       </COLORS>
   </TOY>

</TOYS>

As is clear from above, for each row of the resultset, a separate TOY node gets created.

However, I want output as below:

<TOYS>

   <TOY>
       <NAME>CAR</NAME>
       <COLORS>
         <COLOR>RED</COLOR>
         <COLOR>YELLOW</COLOR>
         <COLOR>BLUE</COLOR>
       </COLORS>
   </TOY>

   <TOY>
       <NAME>BUS</NAME>
       <COLORS>
         <COLOR>RED</COLOR>
         <COLOR>BLUE</COLOR>
       </COLORS>
   </TOY>

 </TOYS>

That is - for each TOY, just a single TOY node should get created containing all the colors for that particular TOY.

How to handle this requirement in my READER (or ROW MAPPER) based on the resultset I have?

Is there a way to tweak my reader sql so that above mentioned scenario is met?

My current sql (which generates first xml) is:

SELECT TOY_NAME, TOY_COLOR FROM TOY ORDER BY TOY_NAME

Thanks for reading!

Solution

  • Instead of using a List in your domain object Toys, i would try using a Map with the key = to the name. Between your reader and writer, i would implement an ItemProcessor that try to get the toy object from the map and addColor() to it, then put it back in the map.

    So in your example, you would end up with 

    [car,[toy[name=car,colors={blue,red,yellow}]]    
[bus,[toy[name=bus,colors={blue,red}]]

    Then XStream would write the output you need. (you may have to use a converter)

    This would only work with a small resultSet... Well a resultSet samller then your commit-interval. Since if your resultSet is larger than your commit-interval, you won't have access to your Map and you might end-up with more than one 

    <TOY><name="car">

    The problem with this king of merging is that you have to build all your XML document in memory before flushing it to the disk!!!

    I will EDIT if i think of something better.

    EDIT : Oh you could also use a GROUP BY name and CONCAT the color

    this would give you

    car | blue,yellow,red
bus | red,blue

    Then you could use an ItemProcessor to take this string : blue,yellow,red and extract each color to add it to your domain object list of color.

    Good luck