Groovy SQL: More Groovy Goodness

Ladies and gentlemen, today, I want to share with you how, in the context of the development of my progressive web applications, the Apache Groovy language is making my life a breeze, particularly the groovy-sql module, which provides a higher-level abstraction over Java’s JDBC technology, when it is hard for me to understand why I have to use JPA and an ORM, like Hibernate, to connect to my MySQL database. Then, let us not waste any more time creating a new connection for each user, when interacting with a relational database is time-consuming, it would be a huge mistake to not use a database connection pool library, like the Apache Commons DBCP, to set up our data source.

Maven Dependencies

<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-dbcp2</artifactId>
  <version>2.2.0</version>
</dependency>

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.41</version>
</dependency>

<dependency>
  <groupId>org.codehaus.groovy</groupId>
  <artifactId>groovy-sql</artifactId>
  <version>2.4.13</version>
</dependency>

DataSource SetUp

This following article might be of interest to you if you want to manually configure your JDBC connection pool for your application server, but you must be aware that a change of web server or of a hosting provider will force you to repeat the configuration when it can be managed at once and automatically at startup using a  ServletContextListener, like this :

AppListener.java

package app; 

import java.sql.SQLException;
import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;

@WebListener
public class AppListener implements ServletContextListener {

    @Override
    public void contextInitialized(ServletContextEvent event) {
       ServletContext context = event.getServletContext();
       context.setAttribute("datasource", setupDataSource());
    }

    @Override
    public void contextDestroyed(ServletContextEvent event) {
       ServletContext context = event.getServletContext();
       BasicDataSource ds = (BasicDataSource) context.getAttribute("datasource");
       try {
         ds.close();
       } catch(SQLException e) {
       }
    }

    private DataSource setupDataSource() {
      BasicDataSource ds = new BasicDataSource();
      ds.setDriverClassName("com.mysql.jdbc.Driver");
      ds.setUrl(System.getenv("db.url"));
      ds.setUsername(System.getenv("db.user"));
      ds.setPassword(System.getenv("db.password"));
      ds.setInitialSize(3);
      ds.setMaxTotal(10);
      return ds;
    }
}

And on your next migration to your chosen cloud provider, all you need to do is to set up the appropriate environment variables to be back on track when you can go for a distributed deployment architecture with your database hosted elsewhere and on another provider with a remote access.

Using our DataSource

In my article, Using your favorite dynamic language with Apache Struts 2, I showed that the integration with the Apache Groovy language and the development of a web application with the Apache Struts web framework can be made fast and easy with a hot reloading and just an overloading of the ActionSupport  class. I'm using this technique in my Rapid Application Development framework Metamorphosis built on top of it to make it modular, multi-template like the Joomla CMS, microservices oriented, and highly scalable, and it has no dependency yet with the groovy-sql module nor with the awesome groovy-templates module, which we will cover later when sending emails with the JavaMail API. The groovy-json module is in the core of the framework for parsing and producing JSON. One use case is to parse the request payload and to replicate somehow the behavior of the Node.js body-parser module.

ActionSupport.java

package org.metamorphosis.core;

import org.apache.struts2.ServletActionContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;
import groovy.json.JsonSlurper;
import static groovy.json.JsonOutput.toJson;

public class ActionSupport extends com.opensymphony.xwork2.ActionSupport {

    public HttpServletRequest getRequest() {
      return ServletActionContext.getRequest();
    }

    public HttpServletResponse getResponse() {
      return ServletActionContext.getResponse();
    }

    public ServletContext getContext() {
      return ServletActionContext.getServletContext();
    }

    public DataSource getDataSource() {
      return (DataSource) getContext().getAttribute("datasource");
    }

    public HttpSession getSession() {
      return getRequest().getSession(true);
    }

    public Object getUser() {
      return getSession().getAttribute("user");
    }

    public Object parse(HttpServletRequest request) throws IOException {
      return new JsonSlurper().parse(request.getInputStream());
    }

    public void json(Object object) throws IOException {
      getResponse().setHeader("Content-Type", "application/json");
      getResponse().getWriter().write(toJson(object));
    }

}

With the overloaded ActionSupport class, which is transparently imported by the Groovy scripting engine, we can now create our SQL connection, like this, in our Groovy script with no JNDI lookup :

MyAction.groovy

import groovy.sql.Sql

class MyAction extends ActionSupport {

    def showCustomers() {
       def connection = getConnection()
       def customers = connection.rows("select id, firstName, lastName, dateOfBirth from customers", [])
       request.setAttribute("customers",customers)  
       connection.close() 
       SUCCESS
    }

    def getConnection()  {
      new Sql(dataSource)
    }

}

And, we can go further in our simplification with another overloading of the ActionSupport class, which we will provide to the Groovy scripting engine with the help of the ImportCustomizer class and using a context param in our web.xml file to avoid a code duplication. We will also store the SQL connection in the request to save one line of code in our scripts.

ActionSupport.java

package app; 

import groovy.sql.Sql

public class ActionSupport extends org.metamorphosis.core.ActionSupport {

  public Sql getConnection()  {
    HttpServletRequest request = getRequest();
    Sql connection = (Sql) request.getAttribute("connection");
    if(connection == null) {
      connection = new Sql(getDataSource());
      request.setAttribute("connection",connection);
    }
    return connection;
  }

}

<context-param>
   <param-name>groovy.imports</param-name>
   <param-value>app.ActionSupport</param-value>
</context-param>

MyAction.groovy

class MyAction extends ActionSupport {

  def showCustomers() {
       def customers = connection.rows("select id, firstName, lastName, dateOfBirth from customers", [])
       request.setAttribute("customers",customers)  
       connection.close() 
       SUCCESS
   }

   def saveCustomer() {
       def customer = parse(request) 
       def params = [customer.firstName,customer.lastName,customer.dateOfBirth]
       def result = connection.executeInsert "insert into customers values (?,?,?)", params
       connection.close()
       json([id: result[0][0]])
   }

}

<table>
  <thead>
    <tr>
      <th>First Name</th>
      <th>Last Name</th>
      <th>Date Of Birth</th>                           
   </tr>
   </thead>
   <tbody>
      <s:iterator value="#request.customers" var="customer">
       <tr id="${customer.id}">
         <td>${customer.firstName}</td>
         <td>${customer.lastName}</td>
         <td><s:date name="dateOfBirth" format="MM/dd/yyyy"/></td>                                        
       </tr>
     </s:iterator>
    </tbody>
 </table>

And, as you can see, adding or removing a field is easy, and we need no POJO or DAO in order to build a working and maintainable CRUD web application with no Java class explosion, and in terms of simplicity and efficiency, there can be no comparison with the new Java Spring Data JDBC module when truth first, or why you should mostly implement database first designs to avoid a long road of pain and suffering, once your project grows.

Servlet API

The servlet API comes with the ServletRequestListener interface for receiving notification events about requests coming into and going out of the scope of a web application and from our perspective, it can be used to create and close the SQL connection automatically to make things better and the code shorter.

RequestListener.java

package app;

import javax.servlet.ServletContext;
import javax.servlet.ServletRequest;
import javax.servlet.ServletRequestEvent;
import javax.servlet.ServletRequestListener;
import javax.servlet.annotation.WebListener;
import javax.sql.DataSource;
import groovy.sql.Sql;

@WebListener
public class RequestListener implements ServletRequestListener {

    @Override
    public void requestInitialized(ServletRequestEvent event)  {
       ServletRequest request = event.getServletRequest();
       ServletContext context = event.getServletContext();
       request.setAttribute("connection",new Sql((DataSource) context.getAttribute("datasource")));
    }

    @Override
    public void requestDestroyed(ServletRequestEvent event) {
       ServletRequest request = event.getServletRequest();
       Sql connection = (Sql) request.getAttribute("connection");
       connection.close();
    }

}

ActionSupport.java

package app; 

import groovy.sql.Sql

public class ActionSupport extends org.metamorphosis.core.ActionSupport {

  public Sql getConnection()  {
    return (Sql) getRequest().getAttribute("connection");
  }

}

MyAction.groovy

class MyAction extends ActionSupport {

  def showCustomers() {
       def customers = connection.rows("select id, firstName, lastName, dateOfBirth from customers", [])
       request.setAttribute("customers",customers)  
       SUCCESS
   }

   def saveCustomer() {
       def customer = parse(request) 
       def params = [customer.firstName,customer.lastName,customer.dateOfBirth]
       def result = connection.executeInsert "insert into customers values (?,?,?)", params
       json([id: result[0][0]])
   }

   def getCustomer() {
       def customer = connection.firstRow "select * from customers where id = ?", [request.getParameter("id")]
       json(customer)
   }

}

The servlet API comes also with the HttpServletRequestWrapper class which provides a convenient implementation of the HttpServletRequest interface that can be subclassed by developers. In our case, subclassing it, will help us to set or get a request attribute or parameter, using the dot notation in our scripts, since Groovy supports the concept of propertyMissing for intercepting otherwise failing property resolution attempts and request.body is definitely a really nice way to get the JSON request body when receiving a POST or a PUT request.

RequestWrapper.java

package app;

import java.io.IOException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletRequestWrapper;
import groovy.json.JsonSlurper;

public class RequestWrapper extends HttpServletRequestWrapper {

  public RequestWrapper(HttpServletRequest request) {
     super(request);
  }

  public void propertyMissing(String property,Object value) {
    setAttribute(property,value);
  }

  public Object propertyMissing(String property) throws IOException {
    if(property.equals("body")) return new JsonSlurper().parse(getInputStream());
    Object value = getAttribute(property);
    return value != null ? value : getParameter(property);
  }

}

ActionSupport.java

package app; 

import javax.servlet.http.HttpServletRequest;
import groovy.sql.Sql

public class ActionSupport extends org.metamorphosis.core.ActionSupport {

  public HttpServletRequest getRequest() {
    HttpServletRequest request = super.getRequest();
    HttpServletRequest wrapper = (HttpServletRequest) request.getAttribute("requestWrapper");
    if(wrapper == null) {
      wrapper = new RequestWrapper(request);
      request.setAttribute("requestWrapper",wrapper);
    }
    return wrapper;
  }

  public Sql getConnection()  {
    return (Sql) super.getRequest().getAttribute("connection");
  }

}

MyAction.groovy

class MyAction extends ActionSupport {

  def showCustomers() {
       request.customers = connection.rows "select id, firstName, lastName, dateOfBirth from customers", []
       SUCCESS
   }

   def saveCustomer() {
       def customer = request.body 
       def params = [customer.firstName,customer.lastName,customer.dateOfBirth]
       def result = connection.executeInsert "insert into customers values (?,?,?)", params
       json([id: result[0][0]])
   }

   def getCustomer() {
       def customer = connection.firstRow "select * from customers where id = ?", [request.id]
       json(customer)
   }

}

To use the MySQL date_format function to format the date of birth of the customers is also more efficient than to use the Struts 2  <s:date/> tag to do it on a server-side rendering and when you can't use it in your SQL query,  you can make the Java SimpleDateFormat class as your fallback.

MyAction.groovy

class MyAction extends ActionSupport {

  def showCustomers() {
       request.customers = connection.rows "select id, firstName, lastName, date_format(dateOfBirth,'%m/%d/%Y') as dateOfBirth from customers", []
       SUCCESS
   }

   def saveCustomer() {
       def customer = request.body 
       def params = [customer.firstName,customer.lastName,customer.dateOfBirth]
       def result = connection.executeInsert "insert into customers values (?,?,?)", params
       json([id: result[0][0]])
   }

   def getCustomer() {
       def customer = connection.firstRow "select * from customers where id = ?", [request.id]
       customer.dateOfBirth = new SimpleDateFormat("MM/dd/yyyy").format(customer.dateOfBirth)
       json(customer)
   }

}

<table>
  <thead>
    <tr>
      <th>First Name</th>
      <th>Last Name</th>
      <th>Date Of Birth</th>                           
   </tr>
   </thead>
   <tbody>
      <s:iterator value="#request.customers" var="customer">
       <tr id="${customer.id}">
         <td>${customer.firstName}</td>
         <td>${customer.lastName}</td>
         <td>${customer.dateOfBirth}</td>                                        
       </tr>
     </s:iterator>
    </tbody>
 </table>

<module>
  <name>dashboard</name>
  <url>dashboard</url>
  <type>back-end</type>
  <actions>
    <action url="customers" method="showCustomers" page="customers" script="MyAction.groovy"/>
    <action url="customers/create"  method="saveCustomer" script="MyAction.groovy"/>
    <action url="customers/info"  method="getCustomer" script="MyAction.groovy"/>
  </actions>
</module>

One trick to automatically handle the browser back button once your users have successfully logout and their HTTP sessions invalidated, is to do it with an interceptor and in this context, you can easily understand that a back-end module can only be accessed by an authenticated user and that it can't be cached.

ModuleInterceptor.java

package org.metamorphosis.core;

import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.struts2.ServletActionContext;
import com.opensymphony.xwork2.ActionInvocation;
import com.opensymphony.xwork2.interceptor.AbstractInterceptor;

public class ModuleInterceptor extends AbstractInterceptor {

  @Override
  public String intercept(ActionInvocation invocation)  {
      try {
        ModuleManager moduleManager = ModuleManager.getInstance();
        Module module = moduleManager.getCurrentModule();
        if(module!=null && module.isBackend()) {
          HttpSession session = ServletActionContext.getRequest().getSession();
          Object user = (Object) session.getAttribute("user");
          if(user==null) return "error";
          HttpServletResponse response = ServletActionContext.getResponse();
          response.setHeader("Cache-control","no-cache, no-store, must-revalidate");
        }
        return invocation.invoke();
      }catch(Exception e) {
      }
      return "error";
  }

}

On the other hand, when you are on production and for high scalability, the instantiated object of an action script which is stateless, can be cached for reuse for all of the users and on development and for a live coding, we can just let it be reloaded on each request using an environment variable.

ActionFactory.java

package org.metamorphosis.core;

import java.util.Map;
import com.opensymphony.xwork2.config.entities.ActionConfig;
import com.opensymphony.xwork2.factory.DefaultActionFactory;

public class ActionFactory extends DefaultActionFactory {

  @Override
  public Object buildAction(String url, String namespace, ActionConfig config, Map<String, Object> extraContext) throws Exception {
    ModuleManager moduleManager = ModuleManager.getInstance();
    Module module = moduleManager.getCurrentModule();
    String reload = System.getenv("metamorphosis.reload");
    return "true".equals(reload) ? moduleManager.buildAction(module,url) : moduleManager.buildAndCacheAction(module,url);
  }

}