[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Spring JdbcDaoSupport - Binding parameters in Batch update .

Posted on 2013-11-11
7
Medium Priority
?
715 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
I had a project requirement for a displaying a user workbench .This workbench would consist multiple data grids .In each grid the user will be able to see a large number of data. These data grids should allow the user to 1. Sort 2. Export the …
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…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses

649 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