18 March 2017

Using spwrap inside spring transactions

spwrap is a little library that simplify calls to database stored procedures in java.

Read this introduction to talk an idea about spwrap before continue reading this post.

We have talked before about how to use spwrap in spring boot application.

Today we will talk about a new feature just release in version 0.0.18, which is spwrap now can participate in spring transactions.

spwrap itself doesn't allow spanning transaction 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 with Datasource transaction manager is enabled.

And we have  SupplierDAO which is a spwrap DAO defined like this:

public interface SupplierDAO {

    void insertSupplier(@Param(VARCHAR) String name);

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

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:

public class SupplierService {

    private SupplierDAO supplierDAO;
    private SupplierRepo supplierRepo;

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

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

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

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

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

Now because the supplierDAO.insertSupplier(supplierName) insert supplier with name "Abdullah", and supplierRepo.save(s2) insert supplier with the same name, then spring frameowkr will throw DataAccessException subclass and rollback the entire transaction.

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

You can see the full example at github: https://github.com/mhewedy/spwrap-examples/tree/master/spring-boot-transactions-mysql Don't forget, if you like the spwrap library, don't forget to like the project page at github.

12 March 2017

Local port forward

How to connect on a remote service as if it is existed on your localhost

ssh -L <The New Port on your machine>:localhost:<The original port on the remote machine> <Remote machine user name>@<The remote machine>

Example for sql on a linux machine on port 1433:

ssh -L 1433:localhost:1433 username@my-linux-machine


02 March 2017

Using spwrap with Spring boot

spwrap is a tiny framework to make stored procedures call from java code an easy and fun task.

I've written a couple of posts about it.

Today, I am gonna show you how to use it from spring boot project.

The main point I want to emphasis here is to register DAO interfaces as Spring Bean so that I would be reused across your application:

public class Config {

    private DataSource dataSource;

    public DAO dao(){
        return new DAO.Builder(dataSource)
                .config(new spwrap.Config().useStatusFields(false))

    public CustomerDAO customerDAO(DAO dao){
        return dao.create(CustomerDAO.class);
See the github page https://github.com/mhewedy/spwrap-examples/tree/master/spring-boot-mysql for the complete project.

Learn more: https://github.com/mhewedy/spwrap/wiki/Using-with-Spring-Boot-other-DI-frameworks
Thanks to stored-procedure-proxy at: https://github.com/marschall/stored-procedure-proxy/wiki/Object-Lifetime

28 February 2017

Using spwrap to simplify calling stored procedures from java

spwrap is a tiny framework to simplify the call to stored procedures from Java code.

In this post, I'll implement the Columbian coffee example from Java Tutorial at oracle.

This example uses mysql database to store coffee and suppliers data. see the install script at github.

The script installs 2 Tables: suppliers and coffees fill them with data and create 3 stored procedures: 
show_suppliers: To list all coffee names with supplier names (takes no parameters and return as result set)
get_supplier_of_coffee: To get a supplier name of coffee (takes 1 input parameter and return 1 output parameter)
raise_price: To raise the price of coffee (take 3 input parameters and return 1 output parameter)
Note: the original  raise_price stored procedure take 2 input parameters and 1 in/out parameter, but spwrap doesn't support INOUT parameters, so I split it into 1 input and 1 output parameter.

We will use spwrap to simplify the call to these 3 stored procedures.

First, We need to Create a Java interface to represent these 3 stored procedures:
public interface ColumbianDAO {

    List<SupplierCoffee> showSuppliers();

    String getSupplierOfCoffee(@Param(VARCHAR) String coffeeName);

    BigDecimal raisePrice(@Param(VARCHAR)String coffeeName,
                          @Param(FLOAT)float maximumPercentage,
                          @Param(NUMERIC) BigDecimal newPrice);
The interface contains 3 methods to represent the 3 stored procedures, the annotation @StoredProc uses to mark method as a stored procedure.

The annotation @Scalar to represent the return type of stored procedure so that the output parameter mapped correctly to the method return type.

For the stored procedures that return its result as result set, you need to provide result set mapper to map the result set object to your domain object (SupplierCoffee), here's the mappers implementation:
public class SupplierCoffee implements ResultSetMapper<SupplierCoffee> {
    private String supplierName, coffeeName;

    public SupplierCoffee map(Result<?> result) {
        // convert the result into SupplierCoffee        
        SupplierCoffee supplierCoffee = new SupplierCoffee();
        supplierCoffee.supplierName = result.getString(1);
        supplierCoffee.coffeeName = result.getString(2);
        return supplierCoffee;

    public String toString() {
        return "SupplierCoffee{" +
                "supplierName='" + supplierName + '\'' +
                ", coffeeName='" + coffeeName + '\'' +
And now you can call the stored procedures using the following code:
DAO dao = new DAO.Builder("jdbc:mysql://localhost:3306/columbian", "root", "")
        .config(new Config().useStatusFields(false))

ColumbianDAO columbianDAO = dao.create(ColumbianDAO.class);

List<SupplierCoffee> supplierCoffees = columbianDAO.showSuppliers();

String coffee = "Colombian";
String supplier = columbianDAO.getSupplierOfCoffee(coffee);
System.out.printf("Supplier of the coffee '%s' is '%s'\n", coffee, supplier);

BigDecimal newPrice = columbianDAO.raisePrice(coffee, 0.10f, BigDecimal.valueOf(19.99));
System.out.printf("new price of '%s' is '%s'\n", coffee, newPrice);
Download the complete source code of the complete example at github.

If you want to know more about spwrap visit the project page at github.  If you like the project, please start it at github :)

22 February 2017

Using Spockframework to unroll donzs of test cases from a few of them

Part of working on spwrap, I am working to writing unit and integration tests form this tiny framework.

I've used spockframework to write some test cases on a project in my current company, I find it very handy and complete framework.

Because spockframework is written in Groovy, it provides very dynamic features that is hard to achieve in other Java-based testing frameworks, at least the syntax of other testing/mocking frameworks will not as good as Spock.

Besides Spock is provides basic testing functionality, it provides what they called "interaction-based testing" (a.k.a. Mocking) and one amazing feature what is "Data Driven testing"

In this post I'll talk about how I used both of them to write 8 test cases and got more than 150 unit test generated.

One of the basic spwrap features is to let user call stored procedures that return result sets and output parameters, and it is user responsibility to extract the data from these JDBC interfaces.

This mapping is done in Mappers, where the user have to implement one of two interfaces either ResultSetMapper or TypedOutputParameterMapper.

example of class implements both interfaces:

public class Customer implements TypedOutputParamMapper<Customer>, ResultSetMapper<Customer> {

    private Integer id;
    private String firstName, lastName;

    public Customer map(Result<?> result) {
        if (result.isResultSet()) {// for ResultSetMapper
            return new Customer(result.getInt(1), result.getString(2), result.getString(3));
        } else { // for TypedOutputParamMapper
            return new Customer(null, result.getString(1), result.getString(2));

The map function above have one parameter of Type Result, which is a wrapper for both java.sql.ResultSet and java.sql.CallableStatement

The Result class has two subclasses (ResultSetWrapper and CallableStatementWrapper) that delegate the call to ResultSet and CallableStatement respectively and re-throw SQLException as non-checked CallException.

Each class of the Wrapper classes (ResultSetWrapper and CallableStatementWrapper) has about 40 methods like getString, getBoolean, getByte, getShort, getInt, getLong, getFloat, etc.

So, I need to write about 40 * 2 (1 success path and 1 fail path) * 2 (2 classes to test) ~= 160 method.

so let's see how we accomplish this using spock:

def callableStatementWrapper
def callableStatementMock = Mock(CallableStatement)

@Shared METHOD_NAMES = ["getString", "getBoolean", "getByte", "getShort", "getInt",
                        "getLong", "getFloat", "getDouble", "getBytes", "getDate", "getTime",
                        "getTimestamp", "getObject", "getBigDecimal", "getRef", "getBlob", "getClob",
                        "getArray", "getURL"];
void setup() {
    callableStatementWrapper = new CallableStatementWrapper(callableStatementMock, 1)

def "calling #methodName(int) on CallableStatementWrapper calls the same method name on CallableStatement" (String methodName){
        1 * callableStatementMock./get.*/(_)
        methodName << METHOD_NAMES

The first line is just a definition for a reference that will hold the object that we need to test which is a CallableStatementWrapper.

The second line is mocking the java.sql.CallableStatement into an variable named callableStatementMock

then we have a static (shared) field of type array of string, actually these are method names on CallableStatementWrapper we need to test.

The setup method instantiate the CallableStatementWrapper using the callableStatementMock mocked object.

the test method do 2 important things:

1. In the when block we say: whenever the user calls callableStatementWrapper. (some dynamic method name that we will supply later in the where block)

2. in the then block: we say, expect 1 call to the mocked object getXXX method that takes any parameter (see interaction-based testing for more details)

3. in the where block, we substitute the methodName in step 1 (when step) by the method name from the static array of method names METHOD_NAMES Array.

When run this test,  and because of the class annotate by @Unroll, we got about 20 test case runs for us.

see the test cases on github for CallableStatementWrapper and ResultSetWrapper.

Hope you can find spock and spwrap helpful!

19 February 2017

spwrap: Stored Procedure call wrapper

spwrap is a Stored Procedure caller; simply execute stored procedure from java code.

public interface CustomerDAO {

    void createCustomer(@Param(VARCHAR) String firstName, @Param(VARCHAR) String lastName);

    Customer getCustomer(@Param(INTEGER) Integer id);   

    List<Customer> listCustomers();
public class Customer implements TypedOutputParamMapper<Customer>, ResultSetMapper<Customer> {

    private Integer id;
    private String firstName, lastName;

    public Customer() {

    public Customer(Integer id, String firstName, String lastName) {
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;

    public Integer id() {
        return id;

    public String firstName() {
        return firstName;

    public String lastName() {
        return lastName;

    public Customer map(Result<?> result) {
        if (result.isResultSet()) {// for ResultSetMapper
            return new Customer(result.getInt(1), result.getString(2), result.getString(3));
        } else { // for TypedOutputParamMapper
            return new Customer(null, result.getString(1), result.getString(2));

    // for TypedOutputParamMapper
    public List<Integer> getTypes() {
        return Arrays.asList(VARCHAR, VARCHAR);
DAO dao = new DAO.Builder(dataSource).build();
CustomerDAO customerDao = dao.create(CustomerDAO.class);

customerDao.createCustomer("Abdullah", "Muhammad");
Customer abdullah = customerDao.getCustomer(0);
// ......
Learn more at github: https://github.com/mhewedy/spwrap 

08 February 2017

Google recaptcha behind a SSL Forward Proxy

If your machine/mobile device behind a SSL Forward Proxy, then there's a possibility that google recaptcha might not work on some browsers.

The reason is, the recaptcha tries to connect to 2 domains to do its work, and one of them is not so famous that you might not ever hit by the browser so there's a possibility that you can see such a message if you ever hit it:

But recaptcha connects to this server using AJAX calls, so it will not prompt.

So, you need to allow the link by opening the domain inside the browser (that you have problem not displaying recaptcha ) then take the risk and accept the new certificate.

The two domains are: https://www.google.com/ and https://www.gstatic.com/

Just opening the above 2 links in the browser that have the problem and accept the certificate will solve it.