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

Maybe you need to use the splitter to split the 2000 rows into 1 row which you can insert one at a time: http://camel.apache.org/splitter — Claus Ibsen, Jan 01 '16 at 23:00
My requirement is my bean `` will return Map from a list.The list of type model class ie:List.I will retrieve 2 values from each instance of my model class. I need to use camel sql component for insertion of model class values.Which variable i need to use in values part of sql? `VALUES(:#stationCode,:#callLetter)` Can i insert this using loop ,i mean can i call this each time for a new value ? Please help me on this ? I am stuck up with this — Bibin Mathew, Jan 05 '16 at 10:20

score 1 · Answer 1 · answered Jan 08 '16 at 21:43

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.

Can you mark as answered or vote if this helped you .. So that others can view this in a later time — Sundar, Jan 12 '16 at 18:38

Alexey Yakunin · Answer 2 · 2016-01-08T21:41:06.287

0

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.

edited Jan 08 '16 at 21:41

answered Jan 08 '16 at 21:31

Alexey Yakunin

1,743
1
19
21

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

2 Answers2