Using spwrap Inside Spring Transactions

spwrap is a tiny framework that simplifies calls to database stored procedures in Java. Read this introduction for an idea of what spwrap is and does before continuing on with this post.

We have talked before about how to use spwrap in Spring Boot applications. Today, we will talk about a new feature just released in version 0.0.18 — spwrap can participate in Spring transactions.

spwrap itself doesn't allow spanning transactions across DAO method calls, but as part of 0.0.18, it will participate in Spring transactions if spwrap is used inside Spring and there's an active transaction.

Suppose we have a Spring project where a datasource transaction manager is enabled. And we have SupplierDAO, which is a spwrap DAO defined like this:

public interface SupplierDAO {

    @StoredProc("insert_new_supplier")
    void insertSupplier(@Param(VARCHAR) String name);
}

And we have a domain object supplier and its spring-data-jpa repository 

@Entity
public class Supplier {

    @Id    @GeneratedValue(strategy = AUTO)
    private Long id;

    @Column(unique = true)
    private String name;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}
// -- repository
public interface SupplierRepo extends JpaRepository<Supplier, Long> {
}

And here's the service class: 

@Service
public class SupplierService {

    private SupplierDAO supplierDAO;
    private SupplierRepo supplierRepo;

    public SupplierService(SupplierDAO supplierDAO, SupplierRepo supplierRepo) {
        this.supplierDAO = supplierDAO;
        this.supplierRepo = supplierRepo;
    }

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

        supplierDAO.insertSupplier(supplierName);   // << will rolled-back

        Supplier s2 = new Supplier();
        s2.setName(supplierName);
        supplierRepo.save(s2);      // throws exception
    }

    public List<Supplier> listSuppliers(){
        return supplierRepo.findAll();
    }
}

Now, because the supplierDAO.insertSupplier(supplierName) inserts a supplier with the name "Abdullah", and supplierRepo.save(s2) inserts a supplier with the same name, then the Spring Framework will throw a DataAccessException subclass and roll back the entire transaction.

Tha means the stored procedure that is executed as a result of calling supplierDAO.insertSupplier(supplierName) will be rolled back as well. Again, this is a new feature as part of spwrap 0.0.18.  

You can see the full example on GitHub.