Monday, November 07, 2011

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.

7 comments:

Tom_B said...

Hi Mark,

Thanks - I have been thinking about doing this for some time for various reasons.

I'm not using GWT, but this will be a nice thing to borrow from.

-Tom

Adel said...

Hello,

I like this helpful post but I have a question, you are passing the sql query as GET parameter, so basically the user will have information about the sql Table and its columns. Isn't this a security issue because you are exposing your database model to the end-user?

Mark Watson, author and consultant said...

Hello Adel,

You are correct: in a real app, you would want to hide the database access. For example, passing parameters via the GET call and build the SQL query on the server side.

-Mark

Mark Watson, author and consultant said...

Hello Adel, sorry, I lost your comment; here it is:

"""
Hello,

Thank you for the helpful post.

I have a question, you are passing your sql query as a GET parameter in the url. Isn't this considered a security issue because you are exposing your database model (table names, column names) to the end-user?
"""

Sean Rasmussen said...

Do you offer this as a free resource? It would be great if we can use this provided that you permit others in doing so.

Mark Watson, author and consultant said...

Hello Sean,

My code is on github:

https://github.com/mark-watson/SmargGWT_LGPL_SQL_DATASOURCE

I hope you find it useful. You can use the code either under the Apache 2, MIT, or LGPL licenses - whichever works best for you.

-Mark

Venkatesh Sundaramoorthy said...

Hi,

When I try the sample in eclipse IDE, I am getting the following error:

2013-09-30 15:43:55.485:WARN::EXCEPTION
java.lang.ClassNotFoundException: com.markwatson.server.DbRestServlet
at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)


But, the same I tried it using IntelliJ it works well on the App Engine.

I would like to run the sample on Tomcat, when I do the deployment on to Tomcat Server (v7). I am running into trouble again. I am getting "Transport Error".

Appreciate your guidance and inputs on where I am going wrong.

Thanks,
Venkatesh