Bind PostgreSQL UUID column with @SqlQuery

[Jason Shell]

I have a PostgreSQL database that uses UUIDs for user ids. I'm attempting to use JDBI to create the findById method.

Stack Overflow question: https://stackoverflow.com/questions/24067663/use-prepared-statement-with-dropwizard-jdbi-and-postgresql-uuid-column

Source: https://github.com/jasonshell/hooktale-api

My first attempt was to just bind the PostgreSQL UUID to the Java version. 

public interface AnglerDAO {

  @SqlQuery("SELECT * FROM angler WHERE id = :it")

  Angler findById(@Bind UUID id);

}

After that failed I read that sometimes JDBI requires a ResultSetMapper class to be created. Therefore, I created a new class that implements ResultSetMapper which looks like:

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"));

  }

}

Which does not work because ResultSet does not have a .getUUID method and trying .getObject ... returns a not implemented JDBC error.

After more Googling, I have read: 

If you are using a relatively recent PgJDBC driver, you should be able to just use the UUID as an argument to PreparedStatement.setObject, which should mean that if you use the UUID object directly through JDBI it should all "just work" 

This has led me here in the hopes I could get a clearer explanation on how I can implement PreparedStatement.setObject. Not sure if it goes in the DAO file, ResultSetMapper or somewhere else.

[Steven Schlansker]

On Jun 9, 2014, at 10:05 AM, Jason Shell <jason.sh...@gmail.com> wrote:

> I have a PostgreSQL database that uses UUIDs for user ids. I'm attempting to use JDBI to create the findById method.
>

> Which does not work because ResultSet does not have a .getUUID method and trying .getObject ... returns a not implemented JDBC error.
>
> After more Googling, I have read:
>
> If you are using a relatively recent PgJDBC driver, you should be able to just use the UUID as an argument to PreparedStatement.setObject, which should mean that if you use the UUID object directly through JDBI it should all "just work"
>
> This has led me here in the hopes I could get a clearer explanation on how I can implement PreparedStatement.setObject. Not sure if it goes in the DAO file, ResultSetMapper or somewhere else.

I think you are confusing two things:
* There is a setObject for setting parameters into the database
* There is a getObject for retrieving results from the database

These are different operations with different rules governing them.

You claim that JDBC supports setObject with UUID, but what you are actually doing is getObject expecting a UUID.
That said, this test case leads me to believe that the JDBC driver has supported both of these for a while now:
https://github.com/pgjdbc/pgjdbc/blob/159e525cbad13dc0c0c49174230638d8e5d1e7c4/org/postgresql/test/jdbc4/UUIDTest.java

So perhaps you should share exactly what the exception you are getting is. Is it a JDBC exception or a JDBI exception?

[Jason Shell]

My apologies, I was definitely confusing setObject and getObject.

error:

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

I should of prefaced my question with I'm new to Java web development and am using Dropwizard, JDBI and PostgreSQL to learn. If I omit any information you need to help, please let me know.

[Steven Schlansker]

On Jun 9, 2014, at 10:31 AM, Jason Shell <jason.sh...@gmail.com> wrote:

> My apologies, I was definitely confusing setObject and getObject.
>
> error:
>
> java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc4.Jdbc4ResultSet.getObject(int, Class<T>) is not yet implemented.
>

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

So this particular method is not implemented in the JDBC driver.
What happens if you try it the way they do it in the test case, namely
replacing

rs.getObject(1, UUID.class)
with
(UUID)rs.getObject(1)

Maybe that will work?

> I should of prefaced my question with I'm new to Java web development and am using Dropwizard, JDBI and PostgreSQL to learn. If I omit any information you need to help, please let me know.
>
> On Monday, June 9, 2014 1:12:59 PM UTC-4, Steven Schlansker wrote:
>
> On Jun 9, 2014, at 10:05 AM, Jason Shell <jason.sh...@gmail.com> wrote:
>
> > I have a PostgreSQL database that uses UUIDs for user ids. I'm attempting to use JDBI to create the findById method.
> >
> > Which does not work because ResultSet does not have a .getUUID method and trying .getObject ... returns a not implemented JDBC error.
> >
> > After more Googling, I have read:
> >
> > If you are using a relatively recent PgJDBC driver, you should be able to just use the UUID as an argument to PreparedStatement.setObject, which should mean that if you use the UUID object directly through JDBI it should all "just work"
> >
> > This has led me here in the hopes I could get a clearer explanation on how I can implement PreparedStatement.setObject. Not sure if it goes in the DAO file, ResultSetMapper or somewhere else.
>
> I think you are confusing two things:
> * There is a setObject for setting parameters into the database
> * There is a getObject for retrieving results from the database
>
> These are different operations with different rules governing them.
>
> You claim that JDBC supports setObject with UUID, but what you are actually doing is getObject expecting a UUID.
> That said, this test case leads me to believe that the JDBC driver has supported both of these for a while now:
> https://github.com/pgjdbc/pgjdbc/blob/159e525cbad13dc0c0c49174230638d8e5d1e7c4/org/postgresql/test/jdbc4/UUIDTest.java
>
> So perhaps you should share exactly what the exception you are getting is. Is it a JDBC exception or a JDBI exception?
>
>

> --
> You received this message because you are subscribed to the Google Groups "jDBI" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Jason Shell]

(UUID)rs.getObject(1) worked perfectly. Thank you. 

When I started to trouble shoot this error, my first step lead me to the JDBI docs where I learned I needed the ResultSetMapper. With this knowledge getting all the fields except for the UUID was trivial. 

After getting the error for the UUID field, where should I of looked first for help? My particular JDBC driver docs? Aside from learning these fundamental libs, I definitely need to improve my troubleshooting ability. 

[Steven Schlansker]

Yes, for better or worse JDBI does not completely hide JDBC from you. Understanding how JDBC works is core to working with JDBI (or realistically any Java database library). The Postgres JDBC driver is fairly well written and reading it isn’t too hard.

But as usual I think the best way to get better at troubleshooting is experience and practice.

Here’s how I diagnosed this:

“java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc4.Jdbc4ResultSet.getObject(int, Class<T>) is not yet implemented.”: read “org.postgresql.jdbc4” -> Okay, the driver is saying it’s not supported

Look at driver source code. Indeed, it’s not supported.
You hypothesized that there actually is UUID support, and I happen to have used it extensively at $OLDJOB, so I knew it probably existed. I grepped through the driver source code for UUID and found the test I linked you to.
The test does it a slightly different way. That’s what I suggested and it works :)

Hope that helps,
Steven