Apache camel how to insert map value to data base using sql component

Question

Apache camel how to insert map value to data base using SQL component

My Class file:

public class PolluxDataController  {

    List<PolluxData> stationsMasterList=new ArrayList<PolluxData>();
    List<PolluxData> stationProccessedList=new ArrayList<PolluxData>();
    Map<String,Object> stationMap=new HashMap<String,Object>();


    @SuppressWarnings("unchecked")
    public Map<String, Object> processPolluxData(Exchange exchange) throws Exception {

        stationsMasterList= (List<PolluxData>) exchange.getIn().getBody();

        for (PolluxData value:stationsMasterList){

                   System.out.println(value.getStationCode() +","+value.getStationShortDescription());  
                   stationMap.put("id",value.getStationCode());
                   stationMap.put("ltr", value.getStationShortDescription());                  

        }

        return stationMap;
    }

---

sql.properties file is:

sql.insertNewRecord=INSERT INTO GSI_DEVL.POLLUX_DATA(STID,CLLTR) VALUES(:#id,#ltr)

---

Context.xml is

<!-- configure the Camel SQL component to use the JDBC data source -->
    <bean id="sqlComponent" class="org.apache.camel.component.sql.SqlComponent">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <bean name="polluxDataController" id="polluxDataController" class="com.nielsen.polluxloadspring.controller.PolluxDataController" />


    <camelContext trace="false" xmlns="http://camel.apache.org/schema/spring">

        <!-- use Camel property placeholder loaded from the given file -->
        <propertyPlaceholder id="placeholder" location="classpath:sql.properties" />

        <camel:route id="bindy-csv-marhalling-unmarshalling-exmaple" autoStartup="true">
                <camel:from uri="file://D://cameltest//input?noop=true&amp;delay=10" />

                <camel:log message="CAMEL BINDY CSV MARSHALLING UNMARSHALLING EXAMPLE" loggingLevel="WARN"/>
                <camel:unmarshal ref="bindyDataformat" >
                    <camel:bindy type="Csv"  classType="com.nielsen.polluxloadspring.model.PolluxData"  />
                </camel:unmarshal>
                <camel:log message="Station Details are ${body}" loggingLevel="WARN" />

                <camel:bean ref="polluxDataController" method="processPolluxData"  />

                <camel:log message="Station Details after bean process ${body}" loggingLevel="WARN" />

                <to uri="sqlComponent:{{sql.insertNewRecord}}" />
                <log message="Inserted new NewTopic ${body[id]}" />
                <log message="Inserted new NewTopic ${body[ltr]}" />

                <camel:log message="COMPLETED BINDY SIMPLE CSV EXAMPLE" loggingLevel="WARN" />
        </camel:route>

    </camelContext>    

Problem is this will insert only one row to database, but the file contains 2000 rows how can I acheive this

Answer 1

You map stationMap will contains only two entries. In for (PolluxData value:stationsMasterList) you always reset this two entries for each PolluxData. Only one map with two enties within - only one insert, not 2000. Something wrong in business logic (with algorithm of filling the map stationMap, maybe), I think.

Answer 2

Change the Bean method as below

public class PolluxDataController  {

List<PolluxData> stationsMasterList=new ArrayList<PolluxData>();

Map<String,Object> stationMap=null;
List<Map<String,Object>> stationProccessedList=new ArrayList<Map<String,Object>>();

@SuppressWarnings("unchecked")
public List<Map<String,Object>>  processPolluxData(Exchange exchange) throws Exception {

    stationsMasterList= (List<PolluxData>) exchange.getIn().getBody();

    for (PolluxData value:stationsMasterList){

               System.out.println(value.getStationCode() +","+value.getStationShortDescription());  
               stationMap=new HashMap<String,Object>();
               stationMap.put("id",value.getStationCode());
               stationMap.put("ltr", value.getStationShortDescription());                  
               stationProccessedList.add(stationMap);
    }

    return stationProccessedList;
}

}

change the sql.properties by adding a parameter batch=true , by default this will insert everything in your list to the db not once record. If you want to select and insert only two records at a time then your business logic is wrong.