Writing a simple SQL data source for the free LGPL version of SmartGWT

While travelling back from a vacation I cleaned up some old experimental code for writing a fairly generic SmartGWT data source with the required server side support code. The commercial versions of SmartGWT have support for connecting client side grid and other components to server side databases. For the free version of SmartGWT you have to roll your own and in this post I'll show you a simple way to do this that should get you started. Copy the sample web app that is included in the free LGPL version of SmartGWT and make the modifications listed below.

I also set up a Github project that contains everything ready to run in IntelliJ.

The goal is to support defining client side grids connected to a database using a simple SQL statement to fetch the required data using a custom class SqlDS. I had to strangely format the following code snippets to get them to fit the content width for my blog:

    ListGrid listGrid = new ListGrid();
    listGrid.setDataSource(
      new SqlDS(
         "select title, content, uri from news where " +
         "content like '%Congress%'"));
    listGrid.setAutoFetchData(true);

The following datasource looks for the column names (i.e., "title", "content", and "uri") in the SQL query and creates fields in the constructed SqlDS instance with those column names. I also assume that there is a servlet defined to process the HTTP GET fetch at the bottom of the constructor:

package com.markwatson.client;

import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.data.DataSourceField;
import com.smartgwt.client.types.DSDataFormat;
import com.smartgwt.client.types.FieldType;

import java.util.Arrays;
import java.util.List;

public class SqlDS extends DataSource {
  public SqlDS(String sql) {
    setID(id);
    setDataFormat(DSDataFormat.JSON);

    List<String> tokens =
        Arrays.asList(sql.toLowerCase()
           .replaceAll(",", " ").split(" "));
    int index1 = tokens.indexOf("select");
    int index2 = tokens.indexOf("from");
    for (int i=index1+1; i<index2; i++) {
      if (tokens.get(i).length() > 0) {
         addField(new DataSourceField(tokens.get(i),
               FieldType.TEXT, tokens.get(i)));
      }
    }
    // should do a better job at UUENCODEing SQL:
    setDataURL("/news?query="+ sql.replaceAll(" ","20%"));
  }
}

The only thing left to do is write a servlet that processes web wervice requests like /news?query=... and returns JSON data with fields from the SQL query for each returned row for display in the list grid:

package com.markwatson.server;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;

public class DbRestServlet extends HttpServlet {
    @Override
    public void doGet(HttpServletRequest req,
         HttpServletResponse resp) throws IOException {
      PrintWriter out = resp.getWriter();
      try {
          // remove "query="
          String sql = req.getQueryString().substring(6); 
          int index = sql.indexOf("&");
          sql = sql.substring(0, index);
          out.println(
             DbUtils.doQuery(sql.replaceAll("20%", " ")));
      } catch (Exception ex) {
        ex.printStackTrace(System.err);
        out.println("[]");
      }
    }
}

The utility class DbUtils returns JSON data which is what the client side SqlDS DataSource class expects from the server:

package com.markwatson.server;

import org.codehaus.jackson.map.ObjectMapper;

import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DbUtils {
  static String dbURL;
  static Connection dbCon;

  static {
    try {
      Class.forName("org.postgresql.Driver");
      // Define the data source for the driver
      dbURL = "jdbc:postgresql://localhost/test_database";
      dbCon = DriverManager.getConnection(
                     dbURL, "postgres", "password");
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  public static String doQuery(String sql)
                               throws Exception {
    ObjectMapper mapper =
       new ObjectMapper(); // should cache and reuse this!
    List<Map<String, String>> ret =
        new ArrayList<Map<String, String>>();
    Statement statement = dbCon.createStatement();
    ResultSet rs = statement.executeQuery(
                            sql.replaceAll("20%", " "));
    java.sql.ResultSetMetaData meta = rs.getMetaData();
    int size = meta.getColumnCount();
    while (rs.next()) {
      Map<String, String> row =
         new HashMap<String, String>();
      for (int i = 1; i <= size; i++) {
        String column = meta.getColumnName(i);
        Object obj = rs.getObject(i);
        row.put(column, "" + obj);
      }
      ret.add(row);
    }
    StringWriter sw = new StringWriter();
    mapper.writeValue(sw, ret);
    return sw.toString();
  }
}

I had to add three JAR files to the SmartGWT sample project:

jackson-core-lgpl-1.8.1.jar
jackson-mapper-lgpl-1.8.1.jar
postgresql-9.0-801.jdbc4.jar

SmartGWT's DataSource abstraction is a real improvement over how I connect to databases in GWT apps where I tend to write a lot of small RPC services to fetch and save data as required. My simple DataSource subclass SqlDS does not support writing data back to the database from the client; it can either be extended or you can use a RPC service call to save edited data.