Use Prepared Statement with Dropwizard JDBI and PostgreSQL UUID column

dropwizard, java, jdbc, postgresql, uuid

Solution

Thanks to Steven Schlansker it was pointed out that the reason I was having trouble trying to utilize PreparedStatement.setObject was because it was not the right solution to the problem.

AnglerMapper needs to get the UUID from Postgres, not set it. Revisiting the error I was receiving:

java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc4.Jdbc4ResultSet.getObject(int, Class) is not yet implemented.

Steven grep'd through the JDBC source and confirmed that it did not support the method but it did support getting a UUID with `(UUID)rs.getObject(1)`. Which is close to what I originally tried `rs.getObject(1, UUID.class)`.

JDBC Exception

public <T> T getObject(int columnIndex, Class<T> type) throws SQLException { 
    throw org.postgresql.Driver.notImplemented(this.getClass(), "getObject(int, Class<T>)"); 
}

org/postgresql/test/jdbc4/UUIDTest.java

public void testUUID() throws SQLException {
    UUID uuid = UUID.randomUUID();
    PreparedStatement ps = _conn.prepareStatement("INSERT INTO uuidtest VALUES (?)");
    ps.setObject(1, uuid, Types.OTHER);
    ps.executeUpdate();
    ps.close();

    Statement stmt = _conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT id FROM uuidtest");
    assertTrue(rs.next());

    UUID uuid2 = (UUID)rs.getObject(1);
    assertEquals(uuid, rs.getObject(1));
    assertEquals(uuid.toString(), rs.getString(1));

    rs.close();
    stmt.close();
}

The corrected and working version of AnglerMapper:

AnglerMapper.java

package org.sachmo.hooktale.db;

import org.sachmo.hooktale.core.Angler;
import org.skife.jdbi.v2.StatementContext;
import org.skife.jdbi.v2.tweak.ResultSetMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;

/**
 * Created by jason on 5/27/14.
 * angler mapper
 */
public class AnglerMapper implements ResultSetMapper<Angler> {
  @Override
  public Angler map(int i, ResultSet rs, StatementContext statementContext) throws     SQLException {
    return new Angler((UUID)rs.getObject("angler_id"), rs.getString("first_name"),
            rs.getString("last_name"), rs.getString("email"), rs.getString("password"));
  }
}

Problem

Quick overview, I'm using the Java web framework Dropwizard and the PostgreSQL database to learn how to create API's. Dropwizard provides the JDBI JDBC wrapper to interact with the database. TLDR: Need to bind native Postgres UUID type with JDBI. SRC: https://github.com/jasonshell/hooktale-api The problem I am having is JDBI requires either ResultSetMapper or ResultSetMapperFactory for more sophisticated mappings. Which in my case involves having a PostgreSQL uuid for the angler_id. In AnglerMapper.java `rs.getObject("angler_id", java.util.UUID.class)` is throwing a method not implemented in JDBC error. AnglerMapper.java (causes error) ``` package org.sachmo.hooktale.db; import org.sachmo.hooktale.core.Angler; import org.skife.jdbi.v2.StatementContext; import org.skife.jdbi.v2.tweak.ResultSetMapper; import java.sql.ResultSet; import java.sql.SQLException; /** * Created by jason on 5/27/14. * angler mapper */ public class AnglerMapper implements ResultSetMapper<Angler> { @Override public Angler map(int i, ResultSet rs, StatementContext statementContext) throws SQLException { return new Angler(rs.getObject("angler_id", java.util.UUID.class), rs.getString("firstName"), rs.getString("lastName"), rs.getString("email"), rs.getString("password")); } } ``` After some searching I read that the ability to accept UUID parameters for binding is not an attribute of JDBI but with the underlying JDBC driver. In my case org.postgresql.9.3-1100-jdbc41, which is capable of accepting UUIDs using `PreparedStatement.setObject()` and results in native binding of UUIDs working without an ArgumentFactory. My problem is I do not know how/where to implement the PreparedStatement.setObject() properly. Any advice on accepting UUID parameters for binding with JDBI would be greatly appreciated. In case more info is needed, including supporting classes/interface. Angler.java ``` package org.sachmo.hooktale.core; import io.dropwizard.jackson.JsonSnakeCase; import java.util.UUID; /** * Created by jason on 5/20/14. * angler */ @JsonSnakeCase public class Angler { private UUID id; private String firstName; private String lastName; private String email; private String password; public Angler(UUID id, String firstName, String lastName, String email, String password) { this.id = id; this.firstName = firstName; this.lastName = lastName; this.email = email; this.password = password; } public UUID getId() { return id; } public void setFirstName(String firstName) { this.firstName = firstName; } public String getFirstName() { return firstName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getLastName() { return lastName; } public void setEmail(String email) { this.email = email; } public String getEmail() { return email; } public void setPassword(String password) { this.password = password; } public String getPassword() { return password; } } ``` AnglerDAO.java ``` package org.sachmo.hooktale.db; import org.sachmo.hooktale.core.Angler; import org.skife.jdbi.v2.sqlobject.Bind; import org.skife.jdbi.v2.sqlobject.SqlQuery; import org.skife.jdbi.v2.sqlobject.customizers.RegisterMapper; import java.util.List; import java.util.UUID; /** * Created by jason on 5/20/14. * angler dao */ @RegisterMapper(AnglerMapper.class) public interface AnglerDAO { @SqlQuery("SELECT * FROM angler ORDER BY created_at") List<Angler> findAll(); @SqlQuery("SELECT * FROM angler WHERE id = :it") Angler findById(@Bind UUID id); } ``` AnglerResource.java ``` package org.sachmo.hooktale.resources; import com.codahale.metrics.annotation.Timed; import org.sachmo.hooktale.core.Angler; import org.sachmo.hooktale.db.AnglerDAO; import javax.ws.rs.*; import javax.ws.rs.core.MediaType; import javax.ws.rs.core.Response; import java.util.List; import java.util.UUID; /** * Created by jason on 5/20/14. * angler resource */ @Path("/angler") @Produces(MediaType.APPLICATION_JSON) @Consumes(MediaType.APPLICATION_JSON) public class AnglerResource { private final AnglerDAO anglerDAO; public AnglerResource(AnglerDAO anglerDAO) { this.anglerDAO = anglerDAO; } @Path("/{id}") @GET @Timed public Angler getAngler(@PathParam("id")UUID uuid) { Angler angler = anglerDAO.findById(uuid); if (angler != null) { return angler; } else { throw new WebApplicationException(Response.Status.NOT_FOUND); } } @GET @Timed public List<Angler> listAngler() { return anglerDAO.findAll(); } } ```

Original source