Spring JdbcDaoSupport - Binding parameters in Batch update .

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;
FranklinRaj22Asked:
Who is Participating?
 
mccarlConnect With a Mentor IT Business Systems Analyst / Software DeveloperCommented:
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
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
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
 
FranklinRaj22Author Commented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
FranklinRaj22Author Commented:
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
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
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
 
FranklinRaj22Author Commented:
I am trying to implement this but unable to get desired results . Experimenting .
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
Feel free to ask further questions in this thread. Maybe we could help you get going sooner!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.