Solved

Spring JdbcDaoSupport - Binding parameters in Batch update .

Posted on 2013-11-11
7
601 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:FranklinRaj22
Comment Utility
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
Comment Utility
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
Comment Utility
I am trying to implement this but unable to get desired results . Experimenting .
0
 
LVL 35

Expert Comment

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…

744 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

14 Experts available now in Live!

Get 1:1 Help Now