• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9373
  • Last Modified:

Is there a example of using Spring JDBCTemplate to use batch insert with transaction around it

Is there a example of using Spring JDBCTemplate to use batch insert with transaction around it. I have two tables one parent table and other child table. The parent and child tables have 1 to many relationship. I want to do batch insert for parent table and at the same time that should also do batch insert in child tables with the Foreign Key generated from parent table. I want to enclose that insert in transaction so that if anything in child table fails I can roll back that particular insert from parent.
0
VikasMahajan
Asked:
VikasMahajan
  • 4
3 Solutions
 
Gibu GeorgeChief Technology OfficerCommented:
For transaction management use the declarative transaction management which will affect the code leaset. To understand transaction manaagement
http://static.springframework.org/spring/docs/2.0.x/reference/transaction.html

The sample code to do the transaction management is like this(not declarative)
public void saveMt940(final Mt940 mt940) throws DataAccessException {
	    TransactionTemplate tt = new TransactionTemplate();
	    tt.setTransactionManager(new DataSourceTransactionManager(getDataSource()));
	    tt.execute(new TransactionCallbackWithoutResult() {
            protected void doInTransactionWithoutResult(TransactionStatus status) {
                OracleSequenceMaxValueIncrementer inc = new OracleSequenceMaxValueIncrementer(getDataSource(),"TRANSAKCIJE_940_SEQ");
        	    JdbcTemplate jt = getJdbcTemplate();
        	    int key = inc.nextIntValue();
        	    if (logger.isDebugEnabled()) {
        	        logger.debug("key is : " + key);
        	    }
        	    jt.update(mt940.toHeadInsertSQL(key));
        	    for (int i = 0; i < mt940.getLineCount(); i++) {
                	if (logger.isDebugEnabled()) {
            	        logger.debug("sql is : " + mt940.toLineInsertSQL(i,key));
            	    }
                	jt.update(mt940.toLineInsertSQL(i,key));
                }
            }
	    });
	}

Open in new window

0
 
Gibu GeorgeChief Technology OfficerCommented:
You can perform batch inserts via batchUpdate() as well. Also there are convenient overloaded executeBatch() method at SimpleJdbcInsert
0
 
Gibu GeorgeChief Technology OfficerCommented:
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
VikasMahajanAuthor Commented:
Thanks gibu!!
Yes, I was thinking of using Spring transaction management. We do not yet have that setup in application so first I will have to do that. At this point I am not sure what kind of transaction (declarative or programable) will be suitable for my application. We have services and those services call the DAO and I was thinking of taking care of transaction in DAO due to the batch inserts and dependencies of the parent and child table. What would you recommend.
0
 
Gibu GeorgeChief Technology OfficerCommented:
The declarative one is a lot cleaner. If you are doing the transaction management by writing code it should be done at the DAO layer, not at the Service layer.
0
 
sheepfestCommented:
IMHO, translactions should be managed at the Service layer, not at the DAO. What about services that require multiple DAO objects to be used within the same transaction? Far easier and cleaner to wrap the service layer method in a transaction.
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now