Transaction use on additional data store

Support
#1

Hi Support
I have a problem to use transaction.

I created a bean for manage update operation with sql command on additional database db1.

In @SpringBootApplication java file I created this function

 @Bean
 TransactionTemplate db1TransactionTemplate(
        @Qualifier("db1TransactionManager")
        PlatformTransactionManager transactionManager) {
    return new TransactionTemplate(transactionManager);
 }

In my bean I declare Entity Manager and TransactionTemplate

@PersistenceContext(unitName = "db1") // store name
private EntityManager entityManager;

@Autowired
@Qualifier("db1TransactionTemplate")
private TransactionTemplate db1TransactionTemplate;

 // creates new transaction
@Transactional("db1TransactionManager")
public TransactionTemplate getRequiresNewTransactionTemplate() {
    TransactionTemplate tt = new TransactionTemplate(transactionManager);
tt.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
    return tt;
}

My method is this:

@Transactional("db1TransactionManager")
private boolean nativeQuery(String nativeQuery) {
    int result = -1;
    List<Object > result1 = null;
    TransactionTemplate tx = getRequiresNewTransactionTemplate();
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();

    def.setName("db1");
    def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);


    try {

        Query query = entityManager.createNativeQuery(nativeQuery);
        result = query.executeUpdate();
        entityManager.getTransaction().commit();


    } catch (Exception e ){
        e.printStackTrace();
        log.error(e.getMessage());
        entityManager.getTransaction().rollback();
    }finally {
         entityManager.close();
    }
    return (result == 0) ? true : false;
}

on execute I receive this error:
jakarta.persistence.TransactionRequiredException: Exception Description: No transaction is currently active

What did I do wrong?
Thank you for response

#4

Hi @infonuvola2015

The following should be enough to invoke EntityManager on an additional data store:

@PersistenceContext(unitName = "db1")
private EntityManager entityManager;

@Transactional("db1TransactionManager")
public boolean nativeQuery(String nativeQuery) {
	Query query = entityManager.createNativeQuery(nativeQuery);
	result = query.executeUpdate();
	return (result == 0) ? true : false;
}

Pay attention that for the @Transactional to have an effect the method must be called on a bean instance, for example:

@Autowired
private MyQueryService myQueryService;

private void execute() {
	myQueryService.nativeQuery("update ...");
	// ...
}

That’s why I changed the method to public. See for example this explanation.

If you need to start transactions inside a bean in a private method, use TransactionTemplate as follows:

@Autowired
@Qualifier("db1TransactionTemplate")
private TransactionTemplate db1TransactionTemplate;

private boolean nativeQuery(String nativeQuery) {
	int result = db1TransactionTemplate.execute(status -> {
		Query query = entityManager.createNativeQuery(nativeQuery);
        return query.executeUpdate();
    });
	return (result == 0) ? true : false;
}

Regards,
Konstantin

#6

Thank’s Konstantin for clarifications