Announcement Announcement Module
Collapse
No announcement yet.
Multiple Inserts in int-jdbc Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Multiple Inserts in int-jdbc

    I have the need to perform multiple inserts into various tables at one time.

    I ran the following and in the logs, spring actually sees the two insert statements as two prepare statements. And it throws a sql error code 90008.

    However, if I hard code the values instead of using the name parameters, it works. No SQL errors and the inserts works as usual.

    I am beginning to suspect that I have to separate the inserts into separate channels instead of clumping them together into one. Before I do that, I like to verify if what I am trying to do below is possible. is the SQL error thrown an expected behavior?

    thank you in advance for any inputs.

    <int-jdbc:outbound-channel-adapter channel="insertDataRequestChannel"
    query="insert into foo(x, y, z) values (:name_x, :name_y, :name_z);
    insert into bar(a, b, c) values (:name_a, :name_b, :name_c);"
    sql-parameter-source-factory="requestSource"
    data-source="dataSource" />

    <bean id="requestSource" class="org.springframework.integration.jdbc.Expres sionEvaluatingSqlParameterSourceFactory">
    <property name="parameterExpressions">
    <map>
    <entry key="name_x" value="payload.name_x"/>
    ....
    </bean>

  • #2
    Hi!
    I like to verify if what I am trying to do below is possible.
    No, it isn't possible.
    I'd say it isn't OK for plain JDBC too: one expression - one PreparedStatement

    is the SQL error thrown an expected behavior?
    Right. It points you out, that there is something wrong in your usage.

    Try to live with similar behaviour and don't reinvent the wheel.

    Take care,
    Artem

    Comment


    • #3
      Thanks, Artem.

      I was able to get it to work by setting up separate channels and separate outbound adapter for each insert.

      I have couple followup questions.

      1) What's the best way to implement transaction in this case? I have 15 inserts, which equates to 15 separate outbound adapters and having a service methods mapped to each adapter. Will @transaction work in this case? Since I have interface methods mapped to the outbound gateway, does setting @transaction in com.foo.myService ensure that all 15 inserts will either ALL fail or ALL succeed? At the end of the day, they are all spring beans, so I would assume it would work. I have never applied Transaction to Spring EI configurations before.

      <int:gateway id="myService" service-interface="com.foo.myService">
      <int:method name="insertMyObj1"
      request-channel="output"
      reply-channel="input"
      />
      ...
      <int:method name="insertMyObj15"
      request-channel="output15"
      reply-channel="input15"
      />
      </int:gateway>

      2) what is the performance cost in creating input/output channels and the numerous outbound adapters ? I have 15 inserts. So I will have 15 x 2 channels and 15 adapters. It appears this is not the most efficient way to execute the inserts.

      Again, thank you in advance for any inputs.

      Originally posted by Cleric View Post
      Hi!

      No, it isn't possible.
      I'd say it isn't OK for plain JDBC too: one expression - one PreparedStatement


      Right. It points you out, that there is something wrong in your usage.

      Try to live with similar behaviour and don't reinvent the wheel.

      Take care,
      Artem

      Comment


      • #4
        does setting @transaction in com.foo.myService ensure that all 15 inserts will either ALL fail or ALL succeed?
        Yes, if all downstream channels are direct. In this case the subscribers on those channels will be invoked in series, at the same 'transaction-aware' Thread. Spring Transaction boundries are binded to the Thread.
        From other other side: if you need such specific unit of work with so much INSERTs, how about to separate them within some business service-method? Or even move the insert logic to the Stored Procedure?..

        Be clever: think different
        .
        Spring Integration is not a panacea for all occasions.
        Your use-case tells me, that there is no any integration cases and you're trying to close all the holes with tool that may not be appropriate for that...

        Comment


        • #5
          I agree with Artem - however you can still use Spring Integration, you could put your 15 updates in a POJO, using a JdbcTemplate, and invoke it from a <service-activator/>.

          Comment


          • #6
            Artem/Gary, Thank you both. Very valuable input.

            I will try what Gary suggested. I saw some examples posted in other threads.

            Originally posted by Gary Russell View Post
            I agree with Artem - however you can still use Spring Integration, you could put your 15 updates in a POJO, using a JdbcTemplate, and invoke it from a <service-activator/>.

            Comment


            • #7
              Can you verify if i am on the right track? jdbcTemplate code is all sitting in barDao. The initial call is made to fooService which in turns activates the dao code. No JDBC adapter involved. I tested this and it works. Just checking to see if there is another way of configuring this that I might have overlooked.

              <int:gateway id="fooService" service-interface="com.foo.fooService">
              <int:method name="fooMethod"
              request-channel="input"
              reply-channel="output"
              />
              </int:gateway>

              <int:chain input-channel="input">
              <int:service-activator ref="barDao" method="insertBar"/>
              </int:chain>

              Originally posted by Gary Russell View Post
              I agree with Artem - however you can still use Spring Integration, you could put your 15 updates in a POJO, using a JdbcTemplate, and invoke it from a <service-activator/>.

              Comment


              • #8
                Looks ok to me.

                Comment

                Working...
                X