Monthly Archives: March 2016

sql2o’s speedy vindication

Yesterday I wrote about a problem I’d run into with sql2o’s getKey() method. The sql2o developer responded to me on twitter, and pointed out that since sql2o is just calling the JDBC PreparedStatement.getGeneratedKeys() method, this isn’t wrong with sql2o per se: it’s a problem with the underlying JDBC driver and ultimately with postgres itself. Even people using straight JDBC have to do workarounds, and many of those workarounds end up being slower (requiring multiple queries) than simply guaranteeing that the leftmost column contains the generated value we’re after.

Much thanks to sql2o for the speedy response!

sql2o’s getKey() and postgres

sql2o is not the cause of this issue. It’s a problem with postgres’s JDBC driver. I retract any expressed or implied criticism of the sql2o project represented by this post’s previous title or the paragraphs below.

I’m using sql2o for persistence on my current project, a microservice written in Spark. Sql2o is a good fit both because it’s fast and tiny and because it minimizes configuration. It’s great (and I’ll write about how to use it later), but it has at least one really frustrating caveat: the Connection.getKey() method.

getKey() is supposed to give us the generated primary key of a newly-inserted record. In ORMs like Hibernate, we don’t need to do that: the object’s field corresponding to the key gets populated when we persist it. But it’s not so bad:

And for several hours today, it worked wonderfully.

My issue happened when I realized that I’d set up my table’s ID column as an integer instead of a bigint. This might be one of the occasions where we’d blow past MAX_INT. So I dropped and re-added the column, as you do:

And when I made another request to my microservice, I got an exception:

That right there is pretty darn un-google-able. There’s a stackoverflow answer that’s specific to PostGIS, but nothing that had to do with the apparent discrepancy between a Java Long and what should have been Postgres’s direct equivalent.

Thinking I was very clever, I decided to hammer a very duct-tape converter in there:

Which fortunately came back with a much more explicit exception:

I thought at first that the giant JSON object was the entire record, serialized, and that somehow I’d lost track of what the key should be and postgres or sql2o or an intermediate gremlin was relying on some mishmash of the entire record as a composite key. I re-created the key as an integer, making sure to specify that yes it was the primary key and yes the generation strategy and so forth. That didn’t help.

Staring at the exception, I realized that the String we were failing to parse as a Long wasn’t the entire object: it was the json representation of one of the child objects I’m persisting for this model. In fact, it was the second field specified in the model, and the second column I’d specified in my original CREATE statement. This leaves one very promising conclusion: that the leftmost column is the “key,” at least for sql2o’s “getKey()” call. Dropping and re-creating the primary key column the way I did moved it all the way to the right, so it wasn’t being returned by getKey() any more.

Dropping the entire table and re-creating it re-ordered the columns appropriately, and since the leftmost column was now the primary key again, everything worked.

Touch Events: Doodling from Mobile Browsers

Mouse events are not exactly the same as touch events, although they’re similar. That meant that my websocket canvas drawing app only did half its job on mobile pbrowsers: you could see the lines drawn by people on desktop browsers, but you couldn’t draw anything back. Here’s how to hook up a very simple touch event handler. MDN has a more complete reference.

First, we need to hook up the event listeners:

Then we write the event handlers. Since we want touch events to do the same thing mouse events are already doing, I extracted the mouse event handlers’ method bodies, and had both the touch event and the mouse event call the same function:


Note that our handleTouchMove function refers to an array of touches, e.changedTouches, using only the 0th element. The changedTouches array supports multi-touch, which is incompatible with our single-pen doodling paradigm.

Deploying a Spark application built with SBT to Heroku

Heroku is a cloud application platform that takes care of many operational concerns – since I have to serve as my own devops for extracurricular projects, that’s pretty appealing. Spark has a tutorial for Heroku deployment, but Spark’s tutorials assume you’re using Maven, and I’m using SBT. Fortunately, Heroku provides an SBT plugin and a reference. Between the two of ’em, we can figure out what to do to deploy a Spark app (in this case, my websocket canvas doodler) to Heroku.

  1. Sign up for a Heroku account, and install the Heroku toolbelt.
  2. Set up your SBT and Spark app.
  3. Add the sbt-heroku and sbt-native-packager plugins to plugins.sbt. Note that the version of sbt-native-packager is different from that described in the Heroku reference:
  4. Add this method to your Spark app, and call port(getHerokuAssignedPort()); in your main() method to determine the port assigned by Heroku:
  5. Create your Heroku app, using the -n flag to avoid adding a git remote:
  6. Add enablePlugins(JavaAppPackaging) and herokuAppName to build.sbt, replacing “your-app-123” with the name Heroku assigned your app:
  7. Finally, deploy the app:

Having completed these steps, you can navigate to your app at, replacing “your-app-123” with the name assigned in step 4.

Spark Doodle: <canvas> and websockets

The source code for this post is on github here. You can play with the app on Heroku here. This app was based on David Ase’s chat tutorial and on Code and Coffee’s node.js example.

Websockets allow duplex communication over a single TCP connection – in other words, they let the web browser constantly speak and listen to a server instead of doing everything through atomized HTTP requests. Before websockets, if you wanted (for instance) a chat app in the web browser, you’d use polling to constantly ask the server whether anything changed. Websockets allow the server to push information to the browser without waiting to be asked.

In this post, I’ll show you how to use websockets to build a simple shared doodling app. Users will see all the lines that other users draw while they are logged in, but they won’t see lines that were drawn before they arrived, nor will they see their own lines reappear if they close and re-open the browser window. The server will use Spark, and the client will be pure, no-library-having Javascript.

The main() method of a Spark application traditionally sets up the routes, but here, all our requests go to the index.html page in /src/main/resources/public, as indicated by the staticFileLocation() call. The webSocket() call sets up the websocket, running at “/doodle” and handling responses using the DoodleWebSocket class. Finally, I put the list of sessions and the broadcastMessage() methods inside this Application class.

Our websocket handler implementation is spartan indeed! Spark uses Jetty’s websocket implementation directly, including Jetty’s annotations. All we do here is add and remove sessions and call the Application controller to broadcast our message out to the clients.

That’s all the java code for this application!

We need some HTML to give ourselves a canvas to draw on:

We have a <canvas> and we include the js source.

Finally, the javascript:

The conceptual implementation is directly from Code and Coffee’s post: if the mouse has moved in the past 25 milliseconds and the mouse button is clicked, we broadcast the instruction to draw a line between our mouse pointer’s current position and the previous position.

The meat of my change is:

  • Switch from a socket to a plain vanilla websocket
  • add the line to re-create the websocket when the mouse is clicked
  • add the proxy function to wait for the socket to be ready before sending a message.

Re-creating the websocket on mouse clicks is to resolve an issue where the websockets would time out on heroku. The proxy function is there because otherwise we’d be trying to broadcast messages to a socket that wasn’t open yet.

Writing a Java Enum quickly using Excel

Today I had to translate a large code table supplied in a PDF, something like this:

A123 Require more vespene gas
A124 PC Load Letter
A125 You got your swagger back

Hundreds and hundreds of rows, into a Java enum:

That’s tedious to type. If I was strong with macros in a real text editor, maybe, I’d be able to do it quickly in Real ProgrammerTM fashion. But I’m not. I am pretty comfortable with Excel, so I did the following:

  • Copy-paste the table page by page from the PDF into an Excel spreadsheet (right click -> paste special -> plain text), cleaning up any formatting irregularities and extra row breaks.
  • Write a formula, =LEFT(A1, 4) to get the enum constant, and drag the formula cell down the table.
  • Another formula, =MID(A1, 5, 999) to get the String member for the enum value.
  • Copy the resulting cells, and right click -> paste special -> values over the top of themselves, so the next step works with the text instead of the code used to generate it.
  • Finally, =CONCAT(B1, "(", CHAR(34), C1, CHAR(34), ")," to build the line of Java code.
  • Copy the resulting cells and paste directly into Eclipse.

It’s not an especially clever hack, but it sure saves some typing.

Hello World in Spark with SBT

The code for this post appears in this github repository.

Spark is a promising little Java web framework, suggested to me by my boss after I wrote a microservice in Play!. I believe his exact phrase was “Why?” and that the question was legitimate because Play! is heavier than you need for just spitting out JSON.

I still like SBT over just Maven, because I am lazy and do not want to have to manage my pom.xml. Spark’s tutorials assume you’re using Maven, so here’s what I did instead, just for posterity.

First, build.sbt:

The important difference from what you’d get by doing “activator init” to get a new Play! app is the last line, which tells sbt where to find the project’s main method.

I also wanted /project/plugins.sbt in order to make the “sbt eclipse” command available:

Finally, the Java source for the application:

That’s it!