Announcement Announcement Module
Collapse
No announcement yet.
JDBC BatchInsert Question Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JDBC BatchInsert Question

    Team,

    I have to do inserts in multiple tables. Currently i am doing like this.

    example:-
    String sql1 = "insert into emp(empid, empname) values(?,?)";
    Object params[] = new Object[] { id ,"selva" };
    getJdbcTemplate().update(sql, params);
    String sql2 = "insert into emp_address(empid,empaddresid,city) values(?,?,?)";
    Object params1[] = new Object[] { id1, id2, "denver" };
    getJdbcTemplate().update(sql, params1);

    I want these two different insert statements to be executed at one shot in a batch. There is a method available in jdbctemplate class which takes sql array as parameter {batchUpdate(sql[])} . But i dont have the complete sql with me i have an insert query and bind variables in object array. These queries and object arrays are formed dynamically.
    I am looking for a batch update method which can take multiple sqls and multiple object params.

    Please suggest me if there is any direct method available or a different way to accomplish my requirement.

    Thanks in advance
    selva

  • #2
    Typically with a batch, you use the same SQL statement multiple times with different parameters. For each item you call addBatch. What you are trying to do is execute two different SQL statements within a batch, why? Wouldn't simply ensuring they are within the same transaction be enough?
    http://www.springframework.org/docs/...ansaction.html

    Comment


    • #3
      Jdbc batch insert question

      Thanks karl for your input.

      I am writing a batch process where in i am forming the dynamic query and corresponding object params using a general logic. These are queries to different tables but uses the same logic. If i use transaction management (propogation required) once one query is formed i have to call update and then again there will be time taken for another query to be formed and then another update call occurs. This may happen for 100 such queries and transaction ends after 100 queries execution. Where in time taken for query forming is also considered in transaction time. I dont want query forming time to be included in batch instead i will form queries and add it to them in a object and finally call a method passing sql array and object param array.

      please let me know whether there is any different way to achieve this in spring so that query forming time is not included in the transaction execution time. Also correct me if my understanding of transaction execution time is wrong.

      Thanks in advance for any help.
      Selva

      Comment


      • #4
        You can get a connection, create prepared statements, put them in a collection, start a transaction, iterate over the collection, execute them, commit.

        Comment


        • #5
          I would agree with dejanp. This seems to be quite a straight forward approach.

          Comment


          • #6
            Originally posted by karldmoore View Post
            Typically with a batch, you use the same SQL statement multiple times with different parameters. For each item you call addBatch. What you are trying to do is execute two different SQL statements within a batch, why? Wouldn't simply ensuring they are within the same transaction be enough?
            http://www.springframework.org/docs/...ansaction.html
            I too looking for the solution for the same question.
            I have java.util.List of sql query and java.util.List of Object[] parameters

            how i should persist using SimpleJdbcTemplate

            Comment

            Working...
            X