Trying to use Postgres UUID type with ResultSetMapper

[Kevin Morgan]

Hi All,

I'm trying to write a ResultSetMapper that deals with UUID's. I'm using org.jdbi:jdbi:2.55, and org.postgresql:postgresql:9.3-1102-jdbc41, and postgres 9.3.

I've got a table with a column named "id" and type "uuid". I'm trying to get that value out of the ResultSet.

    @Override

    public UserSession map(int index, ResultSet r, StatementContext ctx) throws SQLException {

        Object a = r.getObject(1);

        return new UserSession(

                UUID.fromString(r.getString("id")),

                r.getLong("user_id"));

    }

Here's the value of "a" as formatted by the eclipse variables panel. Hopefully this is clear to everyone.

a PGobject  (id=274)

type "record" (id=276)

value "(b84dae7e-83b4-4635-84f8-c7ea32335d68,7)" (id=275)

I saw someone else try to do something similar, where they called r.getObject(1) and cast the result to a UUID. That does not work for me, because the value that comes back is not a UUID, it's a PGobject. 

The value "(b84dae7e-83b4-4635-84f8-c7ea32335d68,7)" is annoyingly different than the String representation of a UUID, since the UUID will not have parenthesis or the 7 at the end.

Also r.getString("id") throws an error, something along the lines of:

! org.postgresql.util.PSQLException: The column name id was not found in this ResultSet.

! at org.postgresql.jdbc2.AbstractJdbc2ResultSet.findColumn(AbstractJdbc2ResultSet.java:2728) ~[postgresql-9.3-1102-jdbc41.jar:na]

! at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:2568) ~[postgresql-9.3-1102-jdbc41.jar:na]

! at com.kevinm416.report.user.UserSessionDBMapper.map(UserSessionDBMapper.java:15) ~[bin/:na]

! at com.kevinm416.report.user.UserSessionDBMapper.map(UserSessionDBMapper.java:1) ~[bin/:na]

Even though that column definitely exists, because I can see the value when I call r.getObject(1). I have also made very sure that the column is called "id" in the database.

Am I doing something wrong here? Does anyone have suggestions?

-Kevin

[Kevin Morgan]

I stepped through AbstractJdbc2ResultSet code, and the issue is that the postgres type is not coming back as a "uuid", it's coming back as "record".

Relevant code from AbstractJdbc2ResultSet:

    protected String getPGType( int column ) throws SQLException
    {
        return connection.getTypeInfo().getPGType(fields[column - 1].getOID());
    }

    protected Object internalGetObject(int columnIndex, Field field) throws SQLException {

        ...
        if (type.equals("uuid")) {    // <------ type = "record" at this point instead of "uuid"
            if (isBinary(columnIndex)) {
                return getUUID(this_row[columnIndex - 1]);
            }
            return getUUID(getString(columnIndex));
        }
    }

I think this is turning into more of a bug report. I'm not sure where the boundary between jdbi and the postgres driver is, so maybe someone here will be able to tell me if this should be reported as a bug to jdbi or somewhere else.

-Kevin

[Steven Schlansker]

What does your table definition and SELECT statement look like? I suspect you are returning a tuple of 2 values instead of just the UUID you expect. 

Also, the getObject and friends are all part of the JDBC driver and outside the control of JDBI. 

--
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.

[Kevin Morgan]

This is my query:

    @SqlQuery(
            " SELECT (id, user_id) " +
            " FROM user_sessions " +
            " WHERE id = :id "
    )
    public UserSession loadSession(@Bind("id") UUID id);

So the query itself should return tuple of two values, but ResultSet.getObject(1) should be a UUID, right?

-Kevin

[Kevin Morgan]

Thank you for getting me to reexamine my sql query. Putting parens around the columns that I selected made the return type some sort of record type. After removing the parens, everything works correctly.

-Kevin