how to batch update using stored procedure with spring jdbc

[etharo]

Need a bit help here. Couldn't find any good info in doc, forum or google.
I got a list, and I need to run some sql + a stored procedure for each item in list.

Im using spring(2.5) jdbc, oracle 10 db, java web app.

This is how I do it today:

Some config stuff:
<jee:jndi-lookup id="dataSource" jndi-name="java:comp/env/dbsource"/> 

<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource"/>
</bean>

<tx:annotation-driven transaction-manager="txManager" />

// how stored procedure is called
@Transactional
public void updateProcedure( final List<Person> list) 
{
	try
	{
		getJdbcTemplate().batchUpdate("{call myschema.myProc( ?, ? null, ? )}",
			new BatchPreparedStatementSetter() 
			{					
				public int getBatchSize()
				{
					return list.size();
				}
	
			public void setValues(PreparedStatement ps, int i) throws SQLException 
			{
				Person p = list.get(i);
														
				ps.setString(1, p.getName());
				ps.setLong(2, p.getHeight());
				ps.setInt(3, p.getAge());
			}
		});
	}
	catch( Exception e )
	{
		e.printStackTrace();
	}
}

// from base class
private JdbcTemplate jdbcTemplate;

public void setDataSource(DataSource dataSource) 
{
    this.jdbcTemplate = new JdbcTemplate(dataSource);
}

public JdbcTemplate getJdbcTemplate() 
{
	return jdbcTemplate;
}

Few comments:
spring jdbc works, batching works, i got tons of stuff that works, the web app works.

Did i mention I also run a plain sql on each item? yes I do, in another method. That one works splendid.

The issue is this:
In 1 out of 10 times this doesn't work.
I recieve no exception.
I know the method is run.
I assume the stored procedure is called, but I got no way to be 100% sure. My co-worker have added comments to the SP to see what the input params are, but they never trigger ... like the SP was never called.

I was thinking of redoing the entire method, maybe batchUpdate isn't the right thing to do. Maybe this way of catching errors (with exception) is wrong.
But I could not find any examples of how to use StoredProcedure class to batch update a list. I assume iterate and call .execute() is not the way to do it.
Also I would like to move the sql thats on the outside, into this method. Because its a bit silly when the sql is successfully, but the SP fails. If one fail, both should fail - but i haven't looked at this yet since I couldn't get my head around SP batching with spring.

Does anyone have an example of using StoredProcedure class to batch update a list? Or how are you guys doing this kind of stuff?

[Marten Deinum]

Start with removing the try/catch that will screw up transaction management. Next your transactions are defined on the wrong layer they should really be on the service layer not the dataaccees layer.

Also I'm not entirly sure if batchupdate is the correct method to use here, its intended use is to generate 1 sql statement to update/insert ( a batched sql statement). Not sure if that works with a call to a stored procedure.

[etharo]

- remove try/catch. I will. But how can I figure out when there was a problem? Logging error etc. Will it toss an exception, or fail silently?

Next your transactions are defined on the wrong layer they should really be on the service layer not the dataaccees layer.

Do you mean:
@transactional should be added to the method in service layer where I call the different methods for updating the list?
or..?

Also I'm not entirly sure if batchupdate is the correct method to use here, its intended use is to generate 1 sql statement to update/insert ( a batched sql statement). Not sure if that works with a call to a stored procedure.

I assume too..
I think I was thinking like "calling the SP as sql, and then batch it" because I didn't knew how else to do this.

[Marten Deinum]

I strongly suggest you read chapter 9 of the reference guide and how springs exception handling works.

- remove try/catch. I will. But how can I figure out when there was a problem? Logging error etc. Will it toss an exception, or fail silently?

And how are you going to figure it out now? Checking the system.out log of tomcat? Next to that your transaction is screwed because you are trying to execute a sql statement, transaction handling sees no exception (you catch and swallow) and tries to commit which can lead to pretty nasty situations. Don't catch it, spring will handle it (ie. rollback transaction, log exception and re-throw it after converting it into a DataAccessException subclasss)

The @Transactional should be indeed on your service layer, you want that whole method to be in 1 transaction. Now you can have the situatiion (if you have multiple dao calls in that service method) that 1 sql statement commits while the other doesn't and presto you have invalid data.

[etharo]

I strongly suggest you read chapter 9 of the reference guide and how springs exception handling works.

Thx. It actually made more sence this time (recently learned a bit aspectj).

When I removed the try/catch, and we forced a failure in the procedure, I got a nice freakish chrash (which i didn't get before). Which also puzzels me a bit since chap9 talked about that spring handles the exception, and I the dev can "choose" to deal with it ... which in my head says "does not toss an exception unless you want to". I must have misunderstood something.
No biggie - i got an exception, its nice.

I now moved the @Transactional to my "service layer". Struts action class actually.
made a nice new method.

@Transactional
private void update( List<Person> list )
{
    myRepository.update( list ); //my other sql
    myRepository.updateProcedure( list );
}

Ok, end of my messy day. So this was tossed together quite fast... and it doesn't work. update() updates, updateProcedure() fails, no rollback for update(). Which doesn't supprice me, but i don't understand it either.

.update() is an getJdbcTemplate().batchUpdate("some sql" ... etc
Guess it has something to do with this.

Gotta run. I'm back tomorrow. Got to read a bit more on this..

[Marten Deinum]

I also suggest you read chapter 6 of the reference guide which explains how AOP with spring works. Basically it uses a Proxy with struts1 there is only 1 method you can intercept with struts2 you probably have the issue that the class isn't known by spring. Also I really believe it belongs in the service layer and your struts action is part of your web layer not the service layer. But that is all IMHO ofcourse .

When I removed the try/catch, and we forced a failure in the procedure, I got a nice freakish chrash (which i didn't get before). Which also puzzels me a bit since chap9 talked about that spring handles the exception, and I the dev can "choose" to deal with it ... which in my head says "does not toss an exception unless you want to". I must have misunderstood something.

Spring does handle the exception (if it is a RuntimeException it rollsback the transaction) however it doesn't swallow the exception. After handling it it simply rethrows/propagates it up the stack.

Ok, end of my messy day. So this was tossed together quite fast... and it doesn't work. update() updates, updateProcedure() fails, no rollback for update(). Which doesn't supprice me, but i don't understand it either

There is no transaction (@Transactional doesn't work here probably) so each method executes in its own transaction.

[etharo]

I also suggest you read chapter 6 of the reference guide which explains how AOP with spring works.

Did a new run through 6,9 and 11 .. and I stil learn new stuff.

Also I really believe it belongs in the service layer

Yes yes - i made the service class. I just hate creating a new class that are just calling 2 methods. Bloat.
But I did, cause after reading(and your comment) I was suspecting that spring maybe wasn't able to create the proxy on the action class, since Its not created by spring.
My Dao, and Service classes implements their own interfaces and are injected(ioc) with spring here and there.
So I created this new service class, and injects it, and hoped spring would find things easier.

// config
<bean id="myService" class="com.service.impl.MyServiceImpl">
	<property name="myRepository" ref="myRepository"/>		
</bean>

//code
public class MyServiceImpl implements MyService
{
	private MyRepository myRepository;

	@Transactional
	public void update( List<Person> list )
	{
		myRepository.update( list ); //my other sql
		myRepository.updateProcedure( list );	
	}

	public void setMyRepository( MyRepository myRepository )
	{
		this.myRepository = myRepository;
	}
}

result:
update are updating and commited.
updateProcedure gets an exception, and are not run.

Thats not supposed to happen...

I can't se in doc that JdbcTemplate().batchUpdate() explisitly commits, which I for a moment suspected it to do.

Then I turned logging on(somehow not managed that before, so I have always coded in blind).

What I do see in the log is that the proxy is added with JdkDynamicAopProxy, I see the TransactionInterceptor, and in the log I do see that rollback is done. But I don't find anything that should tell me that the first sql is commited. On normal sql the autocommit is on, so I don't see any explisit commit done there either.

org.springframework.jdbc.datasource.DataSourceTransactionManager - Using transaction object [org.springframework.jdbc.datasource.DataSourceTransactionManager$DataSourceTransactionObject@1da0a1]
org.springframework.jdbc.datasource.DataSourceTransactionManager - Creating new transaction with name [com.service.MyService.update]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
org.springframework.jdbc.datasource.DataSourceTransactionManager - Acquired Connection [jdbc:oracle:thin:@*******, UserName=****, Oracle JDBC driver] for JDBC transaction
org.springframework.jdbc.datasource.DataSourceTransactionManager - Switching JDBC Connection [jdbc:oracle:thin:@******, UserName=******, Oracle JDBC driver] to manual commit
org.springframework.transaction.support.TransactionSynchronizationManager - Bound value [org.springframework.jdbc.datasource.ConnectionHolder@125f027] for key [org.apache.tomcat.dbcp.dbcp.BasicDataSource@9830bc] to thread [http-8081-Processor25]
org.springframework.transaction.support.TransactionSynchronizationManager - Initializing transaction synchronization
org.springframework.jdbc.core.JdbcTemplate - Executing SQL batch update [update sql goes here]
org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [update sql goes here]
org.springframework.transaction.support.TransactionSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@125f027] for key [org.apache.tomcat.dbcp.dbcp.BasicDataSource@9830bc] bound to thread [http-8081-Processor25]
org.springframework.transaction.support.TransactionSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@125f027] for key [org.apache.tomcat.dbcp.dbcp.BasicDataSource@9830bc] bound to thread [http-8081-Processor25]
org.springframework.jdbc.support.JdbcUtils - JDBC driver supports batch updates
org.springframework.transaction.support.TransactionSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@125f027] for key [org.apache.tomcat.dbcp.dbcp.BasicDataSource@9830bc] bound to thread [http-8081-Processor25]

org.springframework.jdbc.core.JdbcTemplate - Executing SQL batch update [{call myschema.myProc( ?, ? null, ? )}]
org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [{call myschema.myProc( ?, ? null, ? )}]
org.springframework.transaction.support.TransactionSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@125f027] for key [org.apache.tomcat.dbcp.dbcp.BasicDataSource@9830bc] bound to thread [http-8081-Processor25]
org.springframework.transaction.support.TransactionSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@125f027] for key [org.apache.tomcat.dbcp.dbcp.BasicDataSource@9830bc] bound to thread [http-8081-Processor25]
org.springframework.jdbc.support.JdbcUtils - JDBC driver supports batch updates
org.springframework.transaction.support.TransactionSynchronizationManager - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@125f027] for key [org.apache.tomcat.dbcp.dbcp.BasicDataSource@9830bc] bound to thread [http-8081-Processor25]
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator - Using nested SQLException from the BatchUpdateException
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator - Unable to translate SQLException with Error code '1476', will now try the fallback translator
org.springframework.jdbc.datasource.DataSourceTransactionManager - Triggering beforeCompletion synchronization
org.springframework.jdbc.datasource.DataSourceTransactionManager - Initiating transaction rollback
org.springframework.jdbc.datasource.DataSourceTransactionManager - Rolling back JDBC transaction on Connection [jdbc:oracle:thin:@*****, UserName=***, Oracle JDBC driver]
org.springframework.jdbc.datasource.DataSourceTransactionManager - Triggering afterCompletion synchronization
org.springframework.transaction.support.TransactionSynchronizationManager - Clearing transaction synchronization
org.springframework.transaction.support.TransactionSynchronizationManager - Removed value [org.springframework.jdbc.datasource.ConnectionHolder@125f027] for key [org.apache.tomcat.dbcp.dbcp.BasicDataSource@9830bc] from thread [http-8081-Processor25]
org.springframework.jdbc.datasource.DataSourceTransactionManager - Releasing JDBC Connection [jdbc:oracle:thin:@******, UserName=******, Oracle JDBC driver] after transaction
org.springframework.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [{call myschema.myProc( ?, ? null, ? )}]; ORA-01476: divisor is equal to zero
ORA-06512: at "*********", line 263
ORA-06512: at line 1
; nested exception is java.sql.SQLException: ORA-01476: divisor is equal to zero
ORA-06512: at "***********", line 263
ORA-06512: at line 1

	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:111)
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:582)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:594)
	at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:841)
	at com.dao.impl.MyDao.updateProcedure(MyDao.java:294)
	at com.service.impl.MyServiceImpl.update(MyServiceImpl.java:20)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:301)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy24.update(Unknown Source)
	at com.actions.MyAction.save(MyAction.java:111)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

It turns of autocommit, does it stuffs, on failure it rollback(I belive), and the exception is an runtime exception which should trigger rollback if I got that correct

If the error is removed from the SP, and it runs nice, then I see the commit beeing done in the log.

What am I missing...

[Marten Deinum]

I can't se in doc that JdbcTemplate().batchUpdate() explisitly commits, which I for a moment suspected it to do.

No it doesn't. The TransactionManager controls your transaction it start a transaction before your service method and commits afterwards (or does a rollback). How are you executing your other updates?

[etharo]

simple - no fancy fuzz

public void update( final List<Person> list) 
{
	String sql = "update myschema.sometable set name = ?, age = ? where id = ? ";
	
	getJdbcTemplate().batchUpdate( sql,
		new BatchPreparedStatementSetter() {
			public int getBatchSize() {
				return list.size();
			}

			public void setValues(PreparedStatement ps, int i) throws SQLException 
			{		
				Person p = list.get(i);
																
				ps.setString(1, p.getName());
				ps.setInt( 2, p.getAge());
				ps.setString( 3, p.getId());				
			}
		});
}

[etharo]

question:
If an checked exception is thrown, then it commits whats done untill exception.
If an unchecked excetion is thrown, then it rollback the entire stuff.

On top of stacktrace is:
org.springframework.dao.DataIntegrityViolationExce ption
which is sub class of RunTimeException, so thats an unchecked.

But right under:
java.sql.SQLException
Which is an checked exception...

I do assume its not the sqlexception that spring are using to decide?
Anyhow I could try define rollbackFor=Exception.class just to check.

Another point that I was thinking of... what if the SP is doing transactional stuff in itself... could that mess up? I did a quick check of the SP, but couldn't see any. I'll check tomorrow with my coworker.