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 {

    @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) 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.

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 {

    @StoredProc("SHOW_SUPPLIERS")
    List<SupplierCoffee> showSuppliers();

    @Scalar(VARCHAR)
    @StoredProc("GET_SUPPLIER_OF_COFFEE")
    String getSupplierOfCoffee(@Param(VARCHAR) String coffeeName);

    @Scalar(NUMERIC)
    @StoredProc("RAISE_PRICE")
    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;

    @Override
    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;
    }

    @Override
    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))
        .build();

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

List<SupplierCoffee> supplierCoffees = columbianDAO.showSuppliers();
supplierCoffees.forEach(System.out::println);

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 :)

spwrap: Stored Procedure call wrapper

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

Example:

public interface CustomerDAO {

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

    @StoredProc("get_customer")
    Customer getCustomer(@Param(INTEGER) Integer id);   

    @StoredProc("list_customers")
    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) {
        super();
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
    }

    public Integer id() {
        return id;
    }

    public String firstName() {
        return firstName;
    }

    public String lastName() {
        return lastName;
    }

    @Override
    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
    @Override
    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 

Home-grown ConnectionPool

I've written some code that act as a simple connection Pool. This code is not a production code, you need to test it carefully before using it. First, the DAO interface:

package com.forat.model;

import java.util.List;

public interface DAO<T> {
    public void persist(T o);
    public T find (int id);
    public List<T> findAll();
    public void remove(int id);
    public void update(T o);
}

And a domain object represents a User:

package com.forat.model;

import java.io.Serializable;

public class User implements Serializable {
    private int id;
    private String username;
    private String password;
    private String fullName;
    private String email;
    
    public User() {
    }
    
    public int getId() {
        return id;
    }
    private void setId(int id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getFullName() {
        return fullName;
    }
    public void setFullName(String fullName) {
        this.fullName = fullName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
}

And here's the connection Pool code (3 Classes)

package com.forat.db;

import java.sql.Connection;

public class PooledConnection {
    
    private Connection connection;
    private boolean inUse = false;
    
    public PooledConnection(Connection connection) {
        this.connection = connection;
    }
    
    /**
     * 
     * @return
     */
    public synchronized Connection getConnection() {
        if (!inUse) {
            inUse = true;
            return connection;
        }
        return null;
    }
    
    
    Connection _getUnderlyingConn() {
        return connection;
    }
    
    public synchronized void releaseConnection() {
        inUse = false;
    }
}

package com.forat.db;

import java.sql.Connection;
import java.sql.DriverManager;

class ConnectionFactory {
    static Connection createConnection() {
        try {
            return DriverManager.getConnection("jdbc:mysql://localhost:3306/register", "root", "system");
        }catch(Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex.getMessage(), ex);
        }
    }
    
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        }catch(Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex.getMessage(), ex);
        }
    }
}

package com.forat.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.forat.model.User;

/**
 * This class needs extensive testing before used in real applications
 * @author mohammed
 *
 */
public class Manager {
    
    private static List<PooledConnection> connectionPool = new ArrayList<PooledConnection>();
    
    public static synchronized Connection getConnection() {
        for (PooledConnection pconn : connectionPool) {
            Connection conn = pconn.getConnection() ; 
            if (conn != null)
                return conn;
        }
        
        PooledConnection pconn = new PooledConnection(ConnectionFactory.createConnection()); 
        connectionPool.add(pconn);
        return pconn.getConnection();
    }
    
    public static synchronized void closeConnection(Connection conn) {
        for (PooledConnection pconn : connectionPool) {
            if (pconn._getUnderlyingConn() == conn) {
                pconn.releaseConnection();
                return;
            }
        }
        System.err.println("Connection " + conn + " Cannot be release!");
    }
    
    static {
        for (int i=0; i < 10; i++) {
            connectionPool.add(new PooledConnection(ConnectionFactory.createConnection()));
        }
    }
    
}

And here's the UserDAO; a DAO for the User Object

package com.forat.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import com.forat.db.Manager;

public class UserDAO implements DAO<User> {

    @Override
    public void persist(User o) {
        Connection conn = null;
        try {        
            conn = Manager.getConnection();
            String sql = "INSERT INTO user (username, password, fullname, email) VALUES (?, ?, ?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, o.getUsername());
            pstmt.setString(2, o.getPassword());
            pstmt.setString(3, o.getFullName());
            pstmt.setString(4, o.getEmail());
            int result = pstmt.executeUpdate();
            
            if (result != 1)
                throw new SQLException("no row inserted!");
        }catch (Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException("Cannot add new User", ex);
        }finally {
            Manager.closeConnection(conn);
        }
    }
    @Override
    public User find(int id) {
        return null;
    }

    @Override
    public List<User> findAll() {
        return null;
    }

    @Override
    public void remove(int id) {
        
    }

    @Override
    public void update(User o) {
        
    }

    public static void main(String[] args) {
        User u = new User();
        u.setUsername("Ahmed");
        u.setPassword("system");
        u.setEmail("ahmed.hewedy@yahoo.com");
        new UserDAO().persist(u);
    }
    
}

That's all. Note, I've write this code in ArabTeam2000 forum a long time ago.

Using DataSource in non-managed (standalone) java applications

I've wrote bunch of utility classes that facilitate working with java datasource interface in standalone java applications

1- the XML file : "datasource.xml"

 <?xml version="1.0" encoding="UTF-8"?>  
 <datasource jndi="jdbc/testds" >  
   <initail-context-factory>com.sun.jndi.rmi.registry.RegistryContextFactory</initail-context-factory>  
   <provider-url>rmi://localhost:4099</provider-url>  
   <datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource</datasource-class>  
   <username>root</username>  
   <password>system</password>  
   <connection-url>jdbc:mysql://localhost:3306/test</connection-url>  
 </datasource>  

2- a wrapper class that wraps this XML file "DataSourceWrapper"

 package org.daz.ds;  
 import java.io.InputStream;  
 import javax.xml.parsers.DocumentBuilder;  
 import javax.xml.parsers.DocumentBuilderFactory;  
 import org.w3c.dom.Document;  
 import org.w3c.dom.Node;  
 import org.w3c.dom.NodeList;  
 public class DataSourceWrapper {  
   private static Document datasourceDoc;  
   private static DatasourceXmlMapping dsXmlMapping = null;  
   public static String getJndi() {  
     return dsXmlMapping.getJndi();  
   }  
   public static String getInitialContextFactory() {  
     return dsXmlMapping.getInitialContextFactory();  
   }  
   public static String getProviderUrl() {  
     return dsXmlMapping.getProviderUrl();  
   }  
   public static String getDataSourceClass() {  
     return dsXmlMapping.getDatasourceClass();  
   }  
   public static String getUsername() {  
     return dsXmlMapping.getUsername();  
   }  
   public static String getPassword() {  
     return dsXmlMapping.getPassword();  
   }  
   public static String getConnectionUrl() {  
     return dsXmlMapping.getConnectionUrl();  
   }  
   static {  
     try {  
       init();  
       initDatasourceXmlMapping();  
     }catch (Exception ex) {  
       throw new RuntimeException(ex.getMessage(), ex);  
     }  
   }  
   private static void init() throws Exception{  
     InputStream is = DataSourceWrapper.class.getClassLoader().getResourceAsStream("org/daz/ds/datasource.xml");  
     DocumentBuilder docBuilder = DocumentBuilderFactory.newInstance().newDocumentBuilder();  
     datasourceDoc = docBuilder.parse(is);  
     is.close();  
   }  
   private static void initDatasourceXmlMapping() {  
     final String datasource = "datasource";  
     final String jndi="jndi";  
     final String initailContextFactory = "initail-context-factory";  
     final String providerUrl = "provider-url";  
     final String datasourceClass = "datasource-class";  
     final String username = "username";  
     final String password = "password";  
     final String connectionUrl = "connection-url";  
     dsXmlMapping = new DatasourceXmlMapping();  
     Node datasourceNode = datasourceDoc.getDocumentElement();  
     Node jndiAttr = datasourceNode.getAttributes().getNamedItem(jndi);  
     if (jndiAttr == null)  
       throw new RuntimeException(jndi + " Attribute of " + datasource + " Element is null!");  
     dsXmlMapping.setJndi(jndiAttr.getNodeValue());  
     NodeList list = datasourceNode.getChildNodes();  
     for (int i=0; i <list.getLength(); i++) {  
       Node currentNode = list.item(i);  
       if (initailContextFactory.equals(currentNode.getNodeName()))  
         dsXmlMapping.setInitialContextFactory(currentNode.getTextContent());  
       if (providerUrl.equals(currentNode.getNodeName()))  
         dsXmlMapping.setProviderUrl(currentNode.getTextContent());  
       if (datasourceClass.equals(currentNode.getNodeName()))  
         dsXmlMapping.setDatasourceClass(currentNode.getTextContent());  
       if (username.equals(currentNode.getNodeName()))  
         dsXmlMapping.setUsername(currentNode.getTextContent());  
       if (password.equals(currentNode.getNodeName()))  
         dsXmlMapping.setPassword(currentNode.getTextContent());  
       if (connectionUrl.equals(currentNode.getNodeName()))  
         dsXmlMapping.setConnectionUrl(currentNode.getTextContent());  
     }  
   }  
   public static class DatasourceXmlMapping{  
     private String jndi;  
     private String initialContextFactory;  
     private String providerUrl;  
     private String datasourceClass;  
     private String username;  
     private String password;  
     private String connectionUrl;  
     public String getInitialContextFactory() {  
       return initialContextFactory;  
     }  
     public void setInitialContextFactory(String initialContextFactory) {  
       this.initialContextFactory = initialContextFactory;  
     }  
     public String getProviderUrl() {  
       return providerUrl;  
     }  
     public void setProviderUrl(String providerUrl) {  
       this.providerUrl = providerUrl;  
     }  
     public String getDatasourceClass() {  
       return datasourceClass;  
     }  
     public void setDatasourceClass(String datasourceClass) {  
       this.datasourceClass = datasourceClass;  
     }  
     public String getUsername() {  
       return username;  
     }  
     public void setUsername(String username) {  
       this.username = username;  
     }  
     public String getPassword() {  
       return password;  
     }  
     public void setPassword(String password) {  
       this.password = password;  
     }  
     public String getConnectionUrl() {  
       return connectionUrl;  
     }  
     public void setConnectionUrl(String connectionUrl) {  
       this.connectionUrl = connectionUrl;  
     }  
     public String getJndi() {  
       return jndi;  
     }  
     public void setJndi(String jndi) {  
       this.jndi = jndi;  
     }  
   }  
 }  

3- a class that you have to extend to support more Database vendors "DataSourceVendor"

 package org.daz.ds;  
 public class DataSourceVendor {  
   public static MethodNames getMethodNames(String vendor) {  
     MethodNames mn = null;  
     if (vendor.toLowerCase().contains("mysql")) {  
       mn = new MethodNames();  
       mn.setUser = "setUser";  
       mn.setPassword = "setPassword";  
       mn.setURL = "setUrl";  
       return mn;  
     }  
     throw new RuntimeException("Non-supported DBMS");  
   }  
   public static class MethodNames{  
     public String setUser;  
     public String setPassword;  
     public String setURL;  
   }  
 }  

4- a class that manage the whole last staff : "DataSourceManager"

 package org.daz.ds;  
   
 import java.lang.reflect.Method;  
 import java.rmi.registry.LocateRegistry;  
 import java.util.Hashtable;  
 import java.util.logging.Logger;  
   
 import javax.naming.InitialContext;  
 import javax.sql.DataSource;  
   
 import org.daz.ds.DataSourceVendor.MethodNames;  
   
 public class DataSourceManager {  
     
   private Logger logger = Logger.getLogger(DataSourceManager.class.getSimpleName());  
     
   public void startRegistry() throws Exception{  
     final String providerUrl = DataSourceWrapper.getProviderUrl();  
     final char separator = ':';  
     int rmiPort = Integer.parseInt(providerUrl.substring(providerUrl.lastIndexOf(separator) + 1));  
     System.out.println(rmiPort);  
     LocateRegistry.createRegistry(rmiPort);  
   }  
     
   public void storeDatasource() throws Exception{  
     Hashtable<string,> env = new Hashtable<string,>();  
     env.put(InitialContext.INITIAL_CONTEXT_FACTORY, DataSourceWrapper.getInitialContextFactory());  
     env.put(InitialContext.PROVIDER_URL, DataSourceWrapper.getProviderUrl());  
     InitialContext ctx = new InitialContext(env);  
       
     DataSource ds = (DataSource) Class.forName(DataSourceWrapper.getDataSourceClass()).newInstance();  
     logger.info("Using Datasource class : " + DataSourceWrapper.getDataSourceClass());  
       
     MethodNames mNames = DataSourceVendor.getMethodNames(DataSourceWrapper.getDataSourceClass());  
       
     Method m1 = ds.getClass().getMethod(mNames.setUser, String.class);  
     m1.invoke(ds, DataSourceWrapper.getUsername());  
     Method m2 = ds.getClass().getMethod(mNames.setPassword, String.class);  
     m2.invoke(ds, DataSourceWrapper.getPassword());  
     Method m3 = ds.getClass().getMethod(mNames.setURL, String.class);  
     m3.invoke(ds, DataSourceWrapper.getConnectionUrl());  
     logger.info("Using Connection Url : " + DataSourceWrapper.getConnectionUrl());  
     ctx.rebind(DataSourceWrapper.getJndi(), ds);  
   }  
     
   public DataSource getDatasource()throws Exception{  
     Hashtable<string,> env = new Hashtable<string,>();  
     env.put(InitialContext.INITIAL_CONTEXT_FACTORY, DataSourceWrapper.getInitialContextFactory());  
     env.put(InitialContext.PROVIDER_URL, DataSourceWrapper.getProviderUrl());  
     InitialContext ctx = new InitialContext(env);  
     return (DataSource) ctx.lookup(DataSourceWrapper.getJndi());  
   }  
 }  

5- a Test class :

 package org.daz.ds;  
 import java.sql.Statement;  
 import javax.sql.DataSource;  
 public class DatasourceTest {  
   public static void main(String[] args) throws Exception{  
     DataSourceManager dsm = new DataSourceManager();  
     dsm.startRegistry();  
     dsm.storeDatasource();  
     DataSource ds = dsm.getDatasource();  
     Statement stmt = ds.getConnection().createStatement();  
     stmt.executeQuery("select * from test");  
   }  
 }  

Hope you find this helpful.

Thanks.