java - Update Data Using Spring Integration JDBC Outbound Gateway -
i need on spring integration jdbc outbound gateway. playing spring integration sample jdbc. add codes update data using jdbc outbound gateway, here spring-integration-context.xml:
<?xml version="1.0" encoding="utf-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xmlns:int="http://www.springframework.org/schema/integration" xmlns:int-jdbc="http://www.springframework.org/schema/integration/jdbc" xmlns:jdbc="http://www.springframework.org/schema/jdbc" xsi:schemalocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd http://www.springframework.org/schema/integration http://www.springframework.org/schema/integration/spring-integration.xsd http://www.springframework.org/schema/integration/jdbc http://www.springframework.org/schema/integration/jdbc/spring-integration-jdbc.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"> <jdbc:embedded-database id="datasource" type="h2"> <jdbc:script location="classpath:setup-tables.sql"/> </jdbc:embedded-database> <!-- see also: http://static.springsource.org/spring-integration/reference/htmlsingle/#gateway-proxy http://www.eaipatterns.com/messaginggateway.html --> <int:channel id="createpersonrequestchannel"/> <int:channel id="createpersonreplychannel"/> <int:channel id="findpersonrequestchannel"/> <int:channel id="findpersonreplychannel"/> <int:channel id="updatepersonrequestchannel"/> <int:channel id="updatepersonreplychannel"/> <int:gateway id="personservice" service-interface="org.springframework.integration.samples.jdbc.service.personservice"> <int:method name="createperson" request-channel="createpersonrequestchannel" request-timeout="5000" reply-channel="createpersonreplychannel" reply-timeout="5000"/> <int:method name="updateperson" request-channel="updatepersonrequestchannel" request-timeout="5000" reply-channel="updatepersonreplychannel" reply-timeout="5000"/> <int:method name="findpersonbyname" request-channel="findpersonrequestchannel" request-timeout="5000" reply-channel="findpersonreplychannel" reply-timeout="5000"/> </int:gateway> <int-jdbc:outbound-gateway data-source="datasource" update="update dummy set dummy_value='test'" request-channel="findpersonrequestchannel" query="select * person lower(name)=lower(:payload)" reply-channel="findpersonreplychannel" row-mapper="personresultmapper" max-rows-per-poll="100"> </int-jdbc:outbound-gateway> <bean id="personresultmapper" class="org.springframework.integration.samples.jdbc.personmapper"/> <int-jdbc:outbound-gateway data-source="datasource" request-channel="createpersonrequestchannel" reply-channel="createpersonreplychannel" update="insert person (name,gender,dateofbirth) values (:name,:gender,:dateofbirth)" query="select * person id = :id" request-sql-parameter-source-factory="requestsource" reply-sql-parameter-source-factory="replysource" row-mapper="personresultmapper" keys-generated="true"/> <int-jdbc:outbound-gateway data-source="datasource" request-channel="updatepersonrequestchannel" reply-channel="updatepersonreplychannel" update="update person set name = :name, gender = :gender id = :id" query="select * person id = :id" request-sql-parameter-source-factory="requestsource" reply-sql-parameter-source-factory="replysource" row-mapper="personresultmapper"/> <bean id="replysource" class="org.springframework.integration.jdbc.expressionevaluatingsqlparametersourcefactory"> <property name="parameterexpressions"> <map> <entry key="id" value="#this['scope_identity()']"/> </map> </property> </bean> <bean id="requestsource" class="org.springframework.integration.jdbc.expressionevaluatingsqlparametersourcefactory"> <property name="parameterexpressions"> <map> <entry key="name" value="payload.name.touppercase()"/> <entry key="gender" value="payload.gender.identifier"/> <entry key="dateofbirth" value="payload.dateofbirth"/> </map> </property> </bean> </beans> here personservice.java:
package org.springframework.integration.samples.jdbc.service; import java.util.list; import org.springframework.integration.samples.jdbc.person; /** * service used create person instance in database * @author amol nayak * */ public interface personservice { /** * creates {@link person} instance {@link person} instance passed * * @param created person instance, contain generated primary key , formated name * @return */ person createperson(person person); /** * find person person name, name search case insensitive, * spaces not ignored * * @param name * @return matching {@link person} record */ list<person> findpersonbyname(string name); person updateperson(person person); } the main.java:
public final class main { private static final logger logger = logger.getlogger(main.class); private main() { } /** * load spring integration application context * * @param args - command line arguments */ public static void main(final string... args) { logger.info("\n=========================================================" + "\n " + "\n welcome spring integration! " + "\n " + "\n more information please visit: " + "\n http://www.springsource.org/spring-integration " + "\n " + "\n=========================================================" ); final abstractapplicationcontext context = new classpathxmlapplicationcontext("classpath:meta-inf/spring/integration/*-context.xml"); context.registershutdownhook(); final scanner scanner = new scanner(system.in); final personservice personservice = context.getbean(personservice.class); logger.info("\n=========================================================" + "\n " + "\n please press 'q + enter' quit application. " + "\n " + "\n=========================================================" ); system.out.println("please enter choice , press <enter>: "); system.out.println("\t1. find person details"); system.out.println("\t2. create new person detail"); system.out.println("\t3. update person detail"); system.out.println("\tq. quit application"); system.out.print("enter choice: "); while (true) { final string input = scanner.nextline(); if("1".equals(input.trim())) { getpersondetails(scanner, personservice); } else if("2".equals(input.trim())) { createpersondetails(scanner,personservice); } else if("3".equals(input.trim())) { updatepersondetails(scanner,personservice); } else if("q".equals(input.trim())) { break; } else { system.out.println("invalid choice\n\n"); } system.out.println("please enter choice , press <enter>: "); system.out.println("\t1. find person details"); system.out.println("\t2. create new person detail"); system.out.println("\tq. quit application"); system.out.print("enter choice: "); } logger.info("exiting application...bye."); system.exit(0); } private static void createpersondetails(final scanner scanner,personservice service) { while(true) { system.out.print("\nenter person's name:"); string name = scanner.nextline(); gender gender; while(true) { system.out.print("enter person's gender(m/f):"); string genderstr = scanner.nextline(); if("m".equalsignorecase(genderstr) || "f".equalsignorecase(genderstr)) { gender = gender.getgenderbyidentifier(genderstr.touppercase()); break; } } date dateofbirth; simpledateformat format = new simpledateformat("dd/mm/yyyy"); while(true) { system.out.print("enter person's date of birth in dd/mm/yyyy format:"); string dobstr = scanner.nextline(); try { dateofbirth = format.parse(dobstr); break; } catch (parseexception e) { //silently suppress , ask enter details again } } person person = new person(); person.setdateofbirth(dateofbirth); person.setgender(gender); person.setname(name); person = service.createperson(person); system.out.println("created person record id: " + person.getpersonid()); system.out.print("do want create person? (y/n)"); string choice = scanner.nextline(); if(!"y".equalsignorecase(choice)) break; } } private static void updatepersondetails(final scanner scanner,personservice service) { while(true) { system.out.print("\nenter new person's name:"); string name = scanner.nextline(); gender gender; while(true) { system.out.print("enter new person's gender(m/f):"); string genderstr = scanner.nextline(); if("m".equalsignorecase(genderstr) || "f".equalsignorecase(genderstr)) { gender = gender.getgenderbyidentifier(genderstr.touppercase()); break; } } integer id; while(true) { system.out.print("enter person's id:"); string idstr = scanner.nextline(); id = integer.valueof(idstr); break; } person person = new person(); person.setname(name); person.setgender(gender); person.setpersonid(id); service.updateperson(person); system.out.println("updated person record id: " + person.getpersonid()); break; } } /** * @param service * @param input */ private static void getpersondetails(final scanner scanner,final personservice service) { while(true) { system.out.print("please enter name of person , press<enter>: "); string input = scanner.nextline(); final list<person> personlist = service.findpersonbyname(input); if(personlist != null && !personlist.isempty()) { for(person person:personlist) { system.out.print( string.format("person found - person id: '%d', person name is: '%s', gender: '%s'", person.getpersonid(),person.getname(), person.getgender())); system.out.println(string.format(", date of birth: '%1$td/%1$tm/%1$tc%1$ty'", person.getdateofbirth())); } } else { system.out.println( string.format("no person record found name: '%s'.", input)); } system.out.print("do want find person? (y/n)"); string choice = scanner.nextline(); if(!"y".equalsignorecase(choice)) break; } } } why error message whenever tried run it?
04:36:29.402 warn [main][org.springframework.integration.gateway.gatewayproxyfactorybean$methodinvocationgateway] failure occurred in gateway sendandreceive org.springframework.integration.messagehandlingexception: error occurred in message handler [org.springframework.integration.jdbc.jdbcoutboundgateway#2] @ org.springframework.integration.handler.abstractmessagehandler.handlemessage(abstractmessagehandler.java:79) @ org.springframework.integration.dispatcher.unicastingdispatcher.dodispatch(unicastingdispatcher.java:115) @ org.springframework.integration.dispatcher.unicastingdispatcher.dispatch(unicastingdispatcher.java:102) @ org.springframework.integration.channel.abstractsubscribablechannel.dosend(abstractsubscribablechannel.java:77) @ org.springframework.integration.channel.abstractmessagechannel.send(abstractmessagechannel.java:157) @ org.springframework.integration.core.messagingtemplate.dosend(messagingtemplate.java:288) @ org.springframework.integration.core.messagingtemplate.dosendandreceive(messagingtemplate.java:318) @ org.springframework.integration.core.messagingtemplate.sendandreceive(messagingtemplate.java:239) @ org.springframework.integration.core.messagingtemplate.convertsendandreceive(messagingtemplate.java:274) @ org.springframework.integration.gateway.messaginggatewaysupport.dosendandreceive(messaginggatewaysupport.java:224) @ org.springframework.integration.gateway.messaginggatewaysupport.sendandreceive(messaginggatewaysupport.java:203) @ org.springframework.integration.gateway.gatewayproxyfactorybean.invokegatewaymethod(gatewayproxyfactorybean.java:306) @ org.springframework.integration.gateway.gatewayproxyfactorybean.doinvoke(gatewayproxyfactorybean.java:269) @ org.springframework.integration.gateway.gatewayproxyfactorybean.invoke(gatewayproxyfactorybean.java:260) @ org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:172) @ org.springframework.aop.framework.jdkdynamicaopproxy.invoke(jdkdynamicaopproxy.java:202) @ com.sun.proxy.$proxy0.updateperson(unknown source) @ org.springframework.integration.samples.jdbc.main.updatepersondetails(main.java:172) @ org.springframework.integration.samples.jdbc.main.main(main.java:89) caused by: org.springframework.jdbc.uncategorizedsqlexception: preparedstatementcallback; uncategorized sqlexception sql [update person set name = ?, gender = ? id = ?]; sql state [90026]; error code [90026]; serialization failed, cause: "java.io.notserializableexception: java.lang.object" [90026-168]; nested exception org.h2.jdbc.jdbcsqlexception: serialization failed, cause: "java.io.notserializableexception: java.lang.object" [90026-168] @ org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:83) @ org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:80) @ org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:80) @ org.springframework.jdbc.core.jdbctemplate.execute(jdbctemplate.java:603) @ org.springframework.jdbc.core.jdbctemplate.update(jdbctemplate.java:812) @ org.springframework.jdbc.core.jdbctemplate.update(jdbctemplate.java:834) @ org.springframework.jdbc.core.namedparam.namedparameterjdbctemplate.update(namedparameterjdbctemplate.java:260) @ org.springframework.integration.jdbc.jdbcmessagehandler.executeupdatequery(jdbcmessagehandler.java:124) @ org.springframework.integration.jdbc.jdbcoutboundgateway.handlerequestmessage(jdbcoutboundgateway.java:128) @ org.springframework.integration.handler.abstractreplyproducingmessagehandler.handlemessageinternal(abstractreplyproducingmessagehandler.java:134) @ org.springframework.integration.handler.abstractmessagehandler.handlemessage(abstractmessagehandler.java:73) ... 18 more caused by: org.h2.jdbc.jdbcsqlexception: serialization failed, cause: "java.io.notserializableexception: java.lang.object" [90026-168] @ org.h2.message.dbexception.getjdbcsqlexception(dbexception.java:329) @ org.h2.message.dbexception.get(dbexception.java:158) @ org.h2.util.utils.serialize(utils.java:260) @ org.h2.value.valuejavaobject.getnocopy(valuejavaobject.java:42) @ org.h2.value.datatype.converttovalue(datatype.java:941) @ org.h2.jdbc.jdbcpreparedstatement.setobject(jdbcpreparedstatement.java:439) @ org.springframework.jdbc.core.statementcreatorutils.setvalue(statementcreatorutils.java:365) @ org.springframework.jdbc.core.statementcreatorutils.setparametervalueinternal(statementcreatorutils.java:217) @ org.springframework.jdbc.core.statementcreatorutils.setparametervalue(statementcreatorutils.java:128) @ org.springframework.jdbc.core.preparedstatementcreatorfactory$preparedstatementcreatorimpl.setvalues(preparedstatementcreatorfactory.java:298) @ org.springframework.jdbc.core.preparedstatementcreatorfactory$preparedstatementcreatorimpl.createpreparedstatement(preparedstatementcreatorfactory.java:251) @ org.springframework.jdbc.core.jdbctemplate.execute(jdbctemplate.java:581) ... 25 more caused by: java.io.notserializableexception: java.lang.object @ java.io.objectoutputstream.writeobject0(objectoutputstream.java:1183) @ java.io.objectoutputstream.writeobject(objectoutputstream.java:347) @ org.h2.util.utils.serialize(utils.java:257) ... 34 more exception in thread "main" org.springframework.jdbc.uncategorizedsqlexception: preparedstatementcallback; uncategorized sqlexception sql [update person set name = ?, gender = ? id = ?]; sql state [90026]; error code [90026]; serialization failed, cause: "java.io.notserializableexception: java.lang.object" [90026-168]; nested exception org.h2.jdbc.jdbcsqlexception: serialization failed, cause: "java.io.notserializableexception: java.lang.object" [90026-168] @ org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:83) @ org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:80) @ org.springframework.jdbc.support.abstractfallbacksqlexceptiontranslator.translate(abstractfallbacksqlexceptiontranslator.java:80) @ org.springframework.jdbc.core.jdbctemplate.execute(jdbctemplate.java:603) @ org.springframework.jdbc.core.jdbctemplate.update(jdbctemplate.java:812) @ org.springframework.jdbc.core.jdbctemplate.update(jdbctemplate.java:834) @ org.springframework.jdbc.core.namedparam.namedparameterjdbctemplate.update(namedparameterjdbctemplate.java:260) @ org.springframework.integration.jdbc.jdbcmessagehandler.executeupdatequery(jdbcmessagehandler.java:124) @ org.springframework.integration.jdbc.jdbcoutboundgateway.handlerequestmessage(jdbcoutboundgateway.java:128) @ org.springframework.integration.handler.abstractreplyproducingmessagehandler.handlemessageinternal(abstractreplyproducingmessagehandler.java:134) @ org.springframework.integration.handler.abstractmessagehandler.handlemessage(abstractmessagehandler.java:73) @ org.springframework.integration.dispatcher.unicastingdispatcher.dodispatch(unicastingdispatcher.java:115) @ org.springframework.integration.dispatcher.unicastingdispatcher.dispatch(unicastingdispatcher.java:102) @ org.springframework.integration.channel.abstractsubscribablechannel.dosend(abstractsubscribablechannel.java:77) @ org.springframework.integration.channel.abstractmessagechannel.send(abstractmessagechannel.java:157) @ org.springframework.integration.core.messagingtemplate.dosend(messagingtemplate.java:288) @ org.springframework.integration.core.messagingtemplate.dosendandreceive(messagingtemplate.java:318) @ org.springframework.integration.core.messagingtemplate.sendandreceive(messagingtemplate.java:239) @ org.springframework.integration.core.messagingtemplate.convertsendandreceive(messagingtemplate.java:274) @ org.springframework.integration.gateway.messaginggatewaysupport.dosendandreceive(messaginggatewaysupport.java:224) @ org.springframework.integration.gateway.messaginggatewaysupport.sendandreceive(messaginggatewaysupport.java:203) @ org.springframework.integration.gateway.gatewayproxyfactorybean.invokegatewaymethod(gatewayproxyfactorybean.java:306) @ org.springframework.integration.gateway.gatewayproxyfactorybean.doinvoke(gatewayproxyfactorybean.java:269) @ org.springframework.integration.gateway.gatewayproxyfactorybean.invoke(gatewayproxyfactorybean.java:260) @ org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:172) @ org.springframework.aop.framework.jdkdynamicaopproxy.invoke(jdkdynamicaopproxy.java:202) @ com.sun.proxy.$proxy0.updateperson(unknown source) @ org.springframework.integration.samples.jdbc.main.updatepersondetails(main.java:172) @ org.springframework.integration.samples.jdbc.main.main(main.java:89) caused by: org.h2.jdbc.jdbcsqlexception: serialization failed, cause: "java.io.notserializableexception: java.lang.object" [90026-168] @ org.h2.message.dbexception.getjdbcsqlexception(dbexception.java:329) @ org.h2.message.dbexception.get(dbexception.java:158) @ org.h2.util.utils.serialize(utils.java:260) @ org.h2.value.valuejavaobject.getnocopy(valuejavaobject.java:42) @ org.h2.value.datatype.converttovalue(datatype.java:941) @ org.h2.jdbc.jdbcpreparedstatement.setobject(jdbcpreparedstatement.java:439) @ org.springframework.jdbc.core.statementcreatorutils.setvalue(statementcreatorutils.java:365) @ org.springframework.jdbc.core.statementcreatorutils.setparametervalueinternal(statementcreatorutils.java:217) @ org.springframework.jdbc.core.statementcreatorutils.setparametervalue(statementcreatorutils.java:128) @ org.springframework.jdbc.core.preparedstatementcreatorfactory$preparedstatementcreatorimpl.setvalues(preparedstatementcreatorfactory.java:298) @ org.springframework.jdbc.core.preparedstatementcreatorfactory$preparedstatementcreatorimpl.createpreparedstatement(preparedstatementcreatorfactory.java:251) @ org.springframework.jdbc.core.jdbctemplate.execute(jdbctemplate.java:581) ... 25 more caused by: java.io.notserializableexception: java.lang.object @ java.io.objectoutputstream.writeobject0(objectoutputstream.java:1183) @ java.io.objectoutputstream.writeobject(objectoutputstream.java:347) @ org.h2.util.utils.serialize(utils.java:257) ... 34 more the person.java pojo:
public class person implements serializable { private int personid; private string name; private gender gender; private date dateofbirth; /** * sets person id * @return */ public int getpersonid() { return personid; } /** * person id * @param personid */ public void setpersonid(int personid) { this.personid = personid; } /** * gets name of person * @return */ public string getname() { return name; } public void setname(string name) { this.name = name; } /** * gets gender of person * @return */ public gender getgender() { return gender; } public void setgender(gender gender) { this.gender = gender; } /** * gets date of birth of person * @return */ public date getdateofbirth() { return dateofbirth; } public void setdateofbirth(date dateofbirth) { this.dateofbirth = dateofbirth; } } please me, don't know java.lang.object, since don't use anywhere in code. thanks
you have missed id update:
<bean id="requestsource" class="org.springframework.integration.jdbc.expressionevaluatingsqlparametersourcefactory"> <property name="parameterexpressions"> <map> <entry key="id" value="payload.personid"/> <entry key="name" value="payload.name.touppercase()"/> <entry key="gender" value="payload.gender.identifier"/> <entry key="dateofbirth" value="payload.dateofbirth"/> </map> </property> </bean> plus there need separate expressionevaluatingsqlparametersourcefactory select on update :
<bean id="updatereplysource" class="org.springframework.integration.jdbc.expressionevaluatingsqlparametersourcefactory"> <property name="parameterexpressions"> <map> <entry key="id" value="payload.personid"/> </map> </property> </bean>
Comments
Post a Comment