Transactions - mhewedy/spwrap GitHub Wiki

Like stored-procedure-proxy, spwrap provides no support for transactions, if transaction should be done, it is better to be DB-Level in the stored procedure code itself.

spwrap allow to pass other parameters to the JDBC Object like fetch size, Transaction isolation, 'Query Timeout` and more, see this wiki page about Configuring JDBC Objects.

Spring Transactions

Starting from version 0.0.18 if a stored procedure call is a part of Spring framework transaction, then it will participate in the transaction, i.e. affected by the commit/rollback and other properties of it.

Example:

@Transactional
public void add2Suppliers(){
    final String supplierName = "Abdullah";

    supplierDAO.insertSupplier(supplierName);
	
    Supplier s2 = new Supplier();
    s2.setName(supplierName);
    supplierRepo.save(s2);
}

Suppose that, the supplier name column has a unique constraint, then the above code snippet will not cause any changes to the database at all. (the insertion happened by supplierDAO.insertSupplier(supplierName) will be rollbacked)

In version 0.0.17 and earlier, the call to add2Suppliers will insert a new row in the database as the result of calling supplierDAO.insertSupplier(supplierName).

For a complete example showing how spwrap joining spring transactions, see spring-boot-transactions example.

EJB Transactions

Staring from version 0.0.19, spwrap can participates in both Container-managed transactions and Bean-managed transactions.

In the following snippets, insertSupplier DAO method inserts supplier into a table that doesn't allow NULL in supplier name

CMT example:

@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void insertCoupleOfSuppliers() {
	supplierDAO.insertSupplier("Abdullah");// will rollbacked
	supplierDAO.insertSupplier("Wael");// will rollbacked
	supplierDAO.insertSupplier(null);// will throws CallException exception
}

BMT example:

public void insertCoupleOfSuppliers() throws Exception {
	UserTransaction utx = context.getUserTransaction();
	try {
		utx.begin();
		supplierDAO.insertSupplier("Abdullah");// will rollbacked
	        supplierDAO.insertSupplier("Wael");// will rollbacked
	        supplierDAO.insertSupplier(null);// will throws CallException exception
		utx.commit();
	} catch (Throwable ex) {
		utx.rollback();
		throw new Exception(ex.getMessage());
	}
}

See This issue for test example.