Widget Servlets Documentation

The goal with AppliBuilder is to push most of the work of developing application to the widgets, and make it easier for developers to assemble applications with tehse widgets. The following describes how to create widgets with server components that make it easy for developers to work with database queries.

Many widgets, e.g. charts, use servlets to do some of the work for the component. These can use any server web techology, but in our case we use Java servlets and JSPs to implement such functionality. This document describes some of the utilities to help implement such servlets for AppliBuilder.

Developer Database Access

Each developer has her/his own database with AppliBuilder. Widgets that use the database will need to work with the developer database, using API keys obtained using the widget property forms.

To access the database a few steps are required:

1. Rewrite database access code to use the SqlExecutor class, instead of the usual JDBC connection. This allows prepared statement execution and returns JDBC result sets, so required change is limited. See example servlet below.
2. Add the dcwebapp.jar to compile classpath.
3. Add a file for your application to the Tomcat/conf/Catalina/localhost dir. For example, if your application is mywdigetapp, add the file mywidgetapp.xml with the following contents:

  <?xml version="1.0" encoding="UTF-8" ?> 
  <Context path="/mywidgetapp" docBase="Tomcat/webapps/mywidgetapp.war" privileged="true" reloadable="true" /> 

Note that the database access code in your servlet requires an access key to access the developer database. Your widget needs to get that from the developer using the widget property forms.

Servlet Example (Named Query with Query Parameters)

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.applibase.ajaxdc.common.SqlExecutor;

public class MyWidgetServlet extends HttpServlet {
   public void doGet(HttpServletRequest request, HttpServletResponse response)
         throws IOException, ServletException {
      SqlExecutor sqlExecutor = null;
      try {
         // Pass the EncodedAppkey (or database name and appkey)
         sqlExecutor = new SqlExecutor(
               "H4sIAAAAAAAAAHP1c/Z2jUxJzc2PT0kytPIwKfZ0hAHXEq+qZKOAEIPsyhBHj+CMdMcy/5SMKpdA kKStLQDo9O5JOwAAAA==");
         sqlExecutor.setup();

         String namedQuery = request.getParameter("NamedQuery");
         String queryParams = request.getParameter("queryParameters");

         PrintWriter out = new PrintWriter(response.getWriter());
         ResultSet rs = sqlExecutor.executeNamedQuery(namedQuery, queryParams);
         while (rs.next()) {
            out.println("Requested column value is "+rs.getObject("mycolumn"));
         }
      } catch (Exception ex) {
         ex.printStackTrace();
      } finally {
         if (sqlExecutor != null) {
            try {
               sqlExecutor.finish();
            } catch (SQLException sqlex) {
               sqlex.printStackTrace();
            }
         }
      }
   }
}

The query paremeters sent from the client needs to be in a specific format for this to work properly. The client component of your widget gets query parameters from the user as a String. The application developer provides a string to specify a set of query parameters, e.g.

"first", "second", "third"

var urlparams = (new Applibase.Core()).convertQueryParams(qstring);
var fullurl = '/servleturl?NamedQuery=myquery&queryParameters='+urlparams;

Servlet Example (Direct SQL)

package com.acme.mywidget;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.applibase.ajaxdc.common.SqlExecutor;

public class MyWidgetServlet extends HttpServlet {
   public void doGet(HttpServletRequest req, HttpServletResponse response)
         throws IOException, ServletException {
      SqlExecutor sqlExecutor = null;
      try {
         // Pass the EncodedAppkey (or database name and appkey)
         sqlExecutor = new SqlExecutor(
               "H4sIAAAAAAAAAHP1c/Z2jUxJzc2PT0kytPIwKfZ0hAHXEq+qZKOAEIPsyhBHj+CMdMcy/5SMKpdA kKStLQDo9O5JOwAAAA==");
         sqlExecutor.setup();

         PrintWriter out = new PrintWriter(response.getWriter());
         ResultSet rs = sqlExecutor.executeQuery("Select * from Users");
         while (rs.next()) {
            out.println(rs.getObject("usrid"));
         }
      } catch (Exception ex) {
         ex.printStackTrace();
      } finally {
         if (sqlExecutor != null) {
            try {
               sqlExecutor.finish();
            } catch (SQLException sqlex) {
               sqlex.printStackTrace();
            }
         }
      }
   }
}

SqlExecutor Methods

Create an SqlExecutor instance using encoded key, which encodes necessary information ncluding database name.

public SqlExecutor(String encodedAppkey) throws SQLException, IOException, ApplicationException

Create an SqlExecutor instance using a basic application key and the database name.

public SqlExecutor(String dbname, String appkey) throws SQLException, IOException, ApplicationException

Invoke setup before every use of the query executor. This opens a connection to the database.

public void setup() throws SQLException

Invoke finish after every use of the query executor. This closes the connection to the database.

public void finish() throws SQLException

Execute an SQL query string.

public boolean execute(String sql) throws SQLException

Execute a prepared statement instance.

public boolean execute(PreparedStatement ps) throws SQLException

Execute a query statement created from an SQL String and return the result set.

public ResultSet executeQuery(String sql) throws SQLException

Execute a prepared statement and return the result set.

public ResultSet executeQuery(PreparedStatement ps) throws SQLException

Execute an update statement created from an SQL String and return the number of rows changed.

public int executeUpdate(String sql) throws SQLException

Execute an update prepared statement and return the number of rows changed.

public int executeUpdate(PreparedStatement ps) throws SQLException

Execute named query statement(s) with string values query parameters obtained from the client (which is encoded) (See example aboove) and return result of last statement.

public ResultSet executeNamedQuery(String nqname, String queryParams) throws SQLException, ApplicationException, IOException

Execute named query statement(s) with string values query parameters obtained from the client (See example aboove) and return result of last statement. Set paramEncoded to true if you are passing the queryParams that are encoded completely or else use use false.

public ResultSet executeNamedQuery(String nqname, String queryParams, boolean paramEncoded) throws SQLException, ApplicationException, IOException

Execute named query statement(s) with map of query parameters and return result of last statement.

public ResultSet executeNamedQuery(String nqname, Map params) throws SQLException, ApplicationException, IOException

Get named query prepared statement, and in case of multiple statements execute all but the last statement, which is returned.

public PreparedStatement getNamedQueryStmt(String nqname, Map params) throws SQLException, ApplicationException, IOException

Get a prepared statement instance from an SQL String.

public PreparedStatement getPreparedStatement(String sql) throws SQLException