EoD SQL Applied – Part 4 / 5 (JavaScript)

JavaScript vs. Web Applications

So far in this series we’ve discussed using DataSets for Swing applications and DataIterators for web-applications. Why would I now bring in JavaScript as something outside of “web-application”? JavaScript applications have very different requirements to a normal web-application. Where a normal web-application has little ability to do things like preload data (like the next page), a JavaScript application may (for example) download the entire data-set and then display it in pages. This next section is about binding to JSON for JavaScript applications.

First thing to remember here is that an EoD SQL DataSet is a List and thus compatible with the Collections API. For this example we’re going to be working with the outstanding GSON API from our friends at Google. Our objective here is to minimize the amount of time spent between the Database and pushing the data to the client. Because GSON doesn’t appear to support Iterable object out-of-the-box, we’re going to start off using a DataSet.

Using a DataSet with GSON

The advantage with this approach is that you don’t need any additional binding classes in GSON. The approach should work well with any JSON API. The DataSet class has the additional advantage of being able to perform “paged” requests on databases that don’t have a LIMIT statement (by using absolute get() invocations or DataSet.subList()).

public class UserQuery extends BaseQuery {
@Select(sql="SELECT * FROM users ORDER BY name LIMIT ?2 OFFSET ?1",cache=NullDataSetCache.class)
DataSet<User> selectUserPage(int start, int count);

@Select("SELECT COUNT(*) FROM users")
int getUserCount();
}

public class PageOfUsersServlet extends HttpServlet {

private static final UserQuery USER_QUERY = QueryTool.getQuery(UserQuery.class);

protected void doGet(
final HttpServletRequest request,
final HttpServletResponse response)
throws ServletException,
IOException {

final int start = Integer.parseInt(request.getParameter("start"));
final int count = Integer.parseInt(request.getParameter("count"));

final DataSet<User> users = USER_QUERY.selectUserPage(start, count);

try {
response.setContentType("application/json");
final Gson gson = new Gson();
gson.toJSON(new PageResponse<User>(
start,
USER_QUERY.getUserCount(),
users),
response.getWriter());
} finally {
users.close();
}
}

private static class PageResponse<E> {

private final int startItem;

private final int totalItems;

private final Collection<E> items;

PageResponse(
final int start,
final int total,
final Collection<E> items) {

this.startItem = start;
this.totalItems = total;
this.items = items;
}

public int getStartItemIndex() {
return startItem;
}

public int getTotalItemCount() {
return totalItems;
}

public Collection<E> getItems() {
return items;
}
}
}

Some important things about the above code:

  1. We use the NullDataSetCache in the @Select annotation. This stops the returned DataSet caching the objects for identity equality, and since JSON has no back-referencing support, such caching would be a waste of time
  2. We wrap the Users returned in a PageResponse object and serialize that to the client. This object allows for a single callback function to handle the responses from any request for a page of users (since the response contains the index to insert the first user in). So we avoid the need for a closure on the JavaScript side.
  3. We send the total number of users in the database back to the client with each request, allowing us to determine the number of pages that can be fetched, even when the number of users is constantly changing.
  4. Finally: we have GSON pipe the JSON directly to the responses Writer. If the response is small it’ll be buffered by the container and then sent, but if we overflow the buffer boundary, the response will be chunked into blocks allowing us to free up some server-side memory.

DataIterators and Rubberstamping

Now for some black-magic. This part has only been tested with GSON, it may work with other JSON api’s but it strongly depends on how they treat Collection objects. The idea here is to avoid writing special binding objects and to leave the selection of data from the database until as late as possible. We rely on the fact the GSON never uses the size() or toArray() methods of a Collection object, only iterator(). This means that we can have a Collection implementation that doesn’t have a size() method, and returns a DataIterator for it’s iterator() method.

The example is virtually identical to the one above, except that it will run much faster, and use much less memory.

public class UserQuery extends BaseQuery {
@Select(sql="SELECT * FROM users ORDER BY name LIMIT ?2 OFFSET ?1",rubberstamp=true)
DataIterator<User> selectUserPage(int start, int count);

@Select("SELECT COUNT(*) FROM users")
int getUserCount();
}

public class PageOfUsersServlet extends HttpServlet {

private static final UserQuery USER_QUERY = QueryTool.getQuery(UserQuery.class);

protected void doGet(
final HttpServletRequest request,
final HttpServletResponse response)
throws ServletException,
IOException {

final int start = Integer.parseInt(request.getParameter("start"));
final int count = Integer.parseInt(request.getParameter("count"));

final Collection<User> lazyUserCollection = new AbstractCollection<User>() {

public Iterator<User> iterator() {
// we perform the query as late as possible
return USER_QUERY.selectUserPage(start, count);
}

public int size() {
// we could return the "count" field here,
// however the "count" is simply the maximum number
// of rows that could be returned, not the actual number
throw new UnsupportedOperationException();
}

};

response.setContentType("application/json");
final Gson gson = new Gson();
gson.toJSON(new PageResponse<User>(
start,
USER_QUERY.getUserCount(),
lazyUserCollection),
response.getWriter());
}

private static class PageResponse<E> {

private final int startItem;

private final int totalItems;

private final Collection<E> items;

PageResponse(
final int start,
final int total,
final Collection<E> items) {

this.startItem = start;
this.totalItems = total;
this.items = items;
}

public int getStartItemIndex() {
return startItem;
}

public int getTotalItemCount() {
return totalItems;
}

public Collection<E> getItems() {
return items;
}
}
}

Conclusion

We’ve run through using both DataSets and DataIterators for turning your objects into JSON data for a client. Bare in mind that you should test the DataIterator technique well before using it with any other JSON API. In the final part of this series we will look at using EoD SQL with GWT, and take a sneak peak into future of EoD SQL and the upcoming 2.1 releases features.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: