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:
|
1 2 3 4 5 6 7 |
// Example taken directly from sql2o documentation try (Connection con = sql2o.open()) { int insertedId = con.createQuery(sql, true) .addParameter("val", someValue) .executeUpdate() .getKey(); } |
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:
|
1 2 3 4 |
postgres_db=# ALTER TABLE thistable DROP COLUMN id; ALTER TABLE postgres_db=# ALTER TABLE thistable ADD COLUMN id BIGSERIAL PRIMARY KEY; ALTER TABLE |
And when I made another request to my microservice, I got an exception:
|
1 2 |
java.lang.IllegalArgumentException: Cannot convert type class org.postgresql.util.PGobject to class java.lang.Long |
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:
|
1 2 3 4 |
Long insertedId = Long.parseLong(connection.createQuery(sql, true) .addParameter("something", value) .executeUpdate() .getKey(String.class)); |
Which fortunately came back with a much more explicit exception:
|
1 2 |
java.lang.NumberFormatException: For input string: "{"giant": {"json": {"object": {"hierarchy": ... "}" |
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.
Pingback: sql2o’s speedy vindication | Kaiser Leib