I have got service class which executes two db transactions as a single transaction. If an exception occurs, I want to do some other db insert. I want all the above operations as a single transaction.
Follwing is service class
package edu.unomaha.ist.bioinformatics.serviceimpl;
import org.apache.log4j.Logger;
import org.hibernate.exception.ConstraintViolationException;
import org.springframework.beans.factory.annotation.Autowired;
@Service
public class DbOPerationsImpl implements DbOperations {
static Logger log = Logger.getLogger(SequenceValidation.class.getName());
@Autowired
private SequencesRestRepository seqrepo;
@Autowired
private SequenceNamesRepo seqnamerepo;
@Transactional(propagation = Propagation.REQUIRED,noRollbackFor = Exception.class)
public void insertsequence(int dbid, String active, String sequence, String hash, int i3bio, int regid, int version,String genename) {
int seqid=0;
SequencesRest seqobj=null;
boolean isDuplicate=false;
try {
System.out.println("before insert");
try{
seqrepo.insertsequence(dbid,active,sequence , hash, i3bio, regid, version);
}
catch(DataIntegrityViolationException ex){
isDuplicate=true;
System.out.println("entered DataIntegrityViolationException blok");
System.out.println("exception occured");
//seqobj=seqrepo.findByData(sequence);
seqobj=seqrepo.getSequenceRestData(sequence);
seqid=seqobj.getSequencesId();
seqnamerepo.insertsequencename(genename, version, seqid);
System.out.println("After printing cause");
log.error("Exception for the sequence " + sequence);
}
if(!isDuplicate){
seqobj=seqrepo.findByData(sequence);
seqid=seqobj.getSequencesId();
seqnamerepo.insertsequencename(genename, version, seqid);
}
}
catch(DataIntegrityViolationException ex){
System.out.println("entered DataIntegrityViolationException blok");
System.out.println("exception occured");
seqobj=seqrepo.getSequenceRestData(sequence);
seqid=seqobj.getSequencesId();
seqnamerepo.insertsequencename(genename, version, seqid);
System.out.println("After printing cause");
log.error("Exception for the sequence " + sequence);
}
catch(ConstraintViolationException ex){
System.out.println("entered ConstraintViolationException blok");
System.out.println("exception occured");
seqobj=seqrepo.findByData(sequence);
seqobj=seqrepo.findByData(sequence);
seqid=seqobj.getSequencesId();
seqnamerepo.insertsequencename(genename, version, seqid);
System.out.println("After printing cause");
log.error("Exception for the sequence " + sequence);
//log.error("Excpetion occured whle inserting into DB", ex);
// throw ex;
}
catch (Exception ex){
System.out.println("After printing cause");
log.error("Exception for the sequence " + sequence);
// log.error("Excpetion occured whle inserting into DB", ex);
}
}
}
This is my repository class
package edu.unomaha.ist.bioinformatics.repository;
@Repository public interface SequenceNamesRepo extends JpaRepository<SequencesNamesRest,Serializable>{
@Modifying
@Query(nativeQuery=true, value=ApplicationQueries.INSERTSEQUENCENAMEREST)
public void insertsequencename(String name,int version,int id);
}
My logic is, I had a unique column in a table which may throw DataIntigrityException. Hence when it occurs with the method seqrepo.insertsequence , I try to get a sequence and data insert in another table. But the insertion in another table is not happenning. When there no exception, everything works fine, data is getting inserted in two tables (please check code under if(!isDuplicate)). Only when I get an exception, data is not getting committed in second table.
I am getting the following exception
before insert
Hibernate: INSERT INTO sequences_rest(dbs_id,active,data,seq_hash,i3bio,regions_regions_id,crtn_d ate,lst_updt,version)VALUES (?,?,?,md5(?),?,?,SYSDATE(),SYSDATE(),?);
2017-02-09 18:04:18.089 WARN 21470 --- [nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1062, SQLState: 23000
2017-02-09 18:04:18.089 ERROR 21470 --- [nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper : Duplicate entry 'b3e9f55904def1290e14209c370f6a08' for key 'seq_hash_UNIQUE'
entered DataIntegrityViolationException blok
exception occured
Hibernate: SELECT * FROM sequences_rest where seq_hash=md5(?)
Hibernate: INSERT INTO sequences_names_rest(name,crtn_date,lst_updt,version,sequences_id)VALUES(? ,SYSDATE(),SYSDATE(),?,?);
After printing cause
2017-02-09 18:04:46.074 ERROR 21470 --- [nio-8080-exec-6] e.u.i.b.utils.SequenceValidation : Exception for the sequence GGGAGCTGTCGAAGGTGGGATCGGCGATTGGGACGAAGTCGTAACAAGGTAGCCGTACCGGAAGGTGCGGCTGG ATCACCTCCTTTCTAAGGAGCACCATTTCCCAGTCGAATGAACTAGGGAACATAAAGTAGGCATCTGTAGTGGA TATCTACTTGGTGAATATGTTTTGTAAATCCTGTCCACCCCGTGGATGGGTAGTCGGCAAAACGTCGGACTGTC ATAAGAATTGAAACGCTGGCACACTGTTGGGTCCTGAGGCAACACGTTGTGTTGTCACCCTGCTTGGTGGTGGG GTGTGGACTTTGACTTCTGAATAGTGGTTGCGAGCATCTAAACATAGCCTCGCTCGTTTTCGAGTGGGGCTGGT TTTTGCAATTTTATTAGCTAAGTTCTTAAGGGCGCATGGTGAATGCCTTGGCACTAGAAGCCGAAGAAGGA
2017-02-09 18:04:46.158 ERROR 21470 --- [nio-8080-exec-6] e.u.i.b.utils.SequenceValidation : Exception for the sequence GGGAGCTGTCGAAGGTGGGATCGGCGATTGGGACGAAGTCGTAACAAGGTAGCCGTACCGGAAGGTGCGGCTGGATCACCTCCTTTCTAAGGAGCACCATTTCCCAGTCGAATGAACTAGGGAACATAAAGTAGGCATCTGTAGTGGA TATCTACTTGGTGAATATGTTTTGTAAATCCTGTCCACCCCGTGGATGGGTAGTCGGCAAAACGTCGGACTGTCATAAGAATTGAAACGCTGGCACACTGTTGGGTCCTGAGGCAACACGTTGTGTTGTCACCCTGCTTGGTGGTGGGGTGTGGACTTTGACTTCTGAATAGTGGTTGCGAGCATCTAAACATAGCCTCGCTCGTTTTCGAGTGGGGCTGGTTTTTGCAATTTTATTAGCTAAGTTCTTAAGGGCGCATGGTGAATGCCTTGGCACTAGAAGCCGAAGAAGGA
2017-02-09 18:04:46.417 ERROR 21470 --- [nio-8080-exec-6] e.u.i.b.utils.SequenceValidation : Excpetion occured whle inserting into DB
org.springframework.transaction.TransactionSystemException: Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Transaction marked as rollbackOnly
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionM anager.java:526) ~[spring-orm-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager .processCommit(AbstractPlatformTransactionManager.java:761) ~[spring- tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager .commit(AbstractPlatformTransactionManager.java:730) ~[spring-tx- 4.3.3.RELEASE.jar:4.3.3.RELEASE]
at
There are some spaces in showing the exception above. PLease ignore. Thanks for the help in advance.
