Solved

Spring JdbcDaoSupport - Binding parameters in Batch update .

Posted on 2013-11-11
7
615 Views
Last Modified: 2014-01-21
All

I am trying to perform a batch update of different sql queries not the same update statement. The option that is availble is  "int[]      batchUpdate(String[] sql) " .

in this case I will have to generate sql statement with values using + operator and pass it , i there a way I can dynamically bind values into the query String ?

Example :Query1 ===  "Update dept where set hours ="+ timeVariable +" where  employee = "+employeeNm;
        Query2 ===  "Update employee where set food="+ foodVariable +" where  employee = "+employeeNm;
0
Comment
Question by:FranklinRaj22
  • 4
  • 3
7 Comments
 
LVL 35

Expert Comment

by:mccarl
ID: 39640332
is there a way I can dynamically bind values into the query String ?
Unfortunately, no, not for this case of multiple different SQL statements.

Are you wanting to use bind variables because you are updating values over a number of employees at a time? If so, then you are probably best to break up your logic to execute a batch of updates that set the "hours" for a number of employees first. Then execute another batch of updates that set the "food" for a number of employees, etc.

Else, are you wanting to use bind variables because it really is the correct way to pass user supplied data to avoid things like SQL injection attacks, plus allow the database to properly cache the SQL text, etc, but you are only calling the above set of queries for one employee at a time?  If so, then it probably still is the more correct way to go, to separate out each query and just call jdbcTemplate.update(...) for each one. Maybe there would have been some benefit if the driver supported a batch of different SQL's but maybe also the fact that the DB has to reparse each and every SQL that you send now, the performance is worse. The only other option that I see here, is the possibility that the DB supports a multi-table update query, so you can use just the one SQL to update all the values that you need for each employee and then just use a normal .update() call   (or batchUpdate() if setting multiple employee details at once)
0
 

Author Comment

by:FranklinRaj22
ID: 39641919
Can we use same prepared statements in batch update ? if so can we set the limit on the update ?
As in every 100 records i need an update .
0
 
LVL 35

Accepted Solution

by:
mccarl earned 500 total points
ID: 39647657
Can we use same prepared statements in batch update ?
Yes, in fact, that is the typical use of batchUpdate. All the methods except the one you mentioned above, work in this way, ie. one sql statement (provided as a string, but compiled to a PreparedStatement internally) that is used multiple times with different sets of bind variables.

if so can we set the limit on the update ?
As in every 100 records i need an update .
Well, yes and no. There is no limit that can be set, but obviously it will only update using what ever data you supply. So if you supply 100 sets of bind variables, it will execute 100 updates. If you are asking whether you can supply it, say 1000 sets of data, and have it break that up into 10 lots of 100 records, then no. You would have to split the data up yourself before passing it to batchUpdate().
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:FranklinRaj22
ID: 39651219
There is an update method like below , What does the batch size stand for ??
Is this somethign that I can limit to have the update in batches ?

public <T> int[][] batchUpdate(String sql, final Collection<T> batchArgs, final int batchSize, final ParameterizedPreparedStatementSetter<T> pss)
0
 
LVL 35

Expert Comment

by:mccarl
ID: 39652849
Hmm, that method is in newer versions, so I hadn't seen that before, or used it. However, looking at the Javadoc I would say that batchSize needs to be passed in because the collection of batch arguments is only a 1 dimensional collection. ie, if you pass a collection containing 24 items, is that an update of 12 rows updating 2 fields, or 8 rows of 3 fields, or 6 rows of 4 fields, etc. The javadoc is not 100% clear so I would have to actually try it out for real to find out exactly how it operates.
0
 

Author Comment

by:FranklinRaj22
ID: 39667650
I am trying to implement this but unable to get desired results . Experimenting .
0
 
LVL 35

Expert Comment

by:mccarl
ID: 39667707
Feel free to ask further questions in this thread. Maybe we could help you get going sooner!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
groupNoAdj 7 84
reasons why a "inside Menu" would not appear for a web server 6 56
advertisement module in core php 4 144
table example 4 19
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now