?
Solved

Spring JdbcDaoSupport - Binding parameters in Batch update .

Posted on 2013-11-11
7
Medium Priority
?
680 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 36

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 36

Accepted Solution

by:
mccarl earned 1000 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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 36

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 36

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
A solution for Fortify Path Manipulation.
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Suggested Courses
Course of the Month12 days, 6 hours left to enroll

752 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