Support for pgsql array, hstore?

[Steve Kradel]

Hello group,

Any tips for supporting postgresql varchar[] and hstore column types?  At the moment I have Sql2o 1.5.1 filling in a POJO with a public String[] field, but that String[] at runtime turns out to be a java.sql.Array requiring the following ugly contortions to use properly:

public static String[] convertArray(String[] sqlArray) throws SQLException {
       
        if (sqlArray == null) {
            return null;
        }
       
        java.sql.Array arr = (java.sql.Array) ((Object) sqlArray);
       
        String[] result = (String[]) arr.getArray();
       
        try {
            arr.free();
        } catch (SQLException e) {}
       
        return result;
}

Likewise I haven't seen any mention of hstore... will do some of my own testing but thought I'd ask first.

--Steve

[Lars Aaberg]

Hi Steve,

It might be possible to support these data types through a converter. Sql2o converters are so far not documented on the web page, but converters allows sql2o to convert between data from the database and types in your domain model. In this example it would be to convert from java.sql.Array to an ordinary string array.

Something like this:

public class StringArrayConverter implements Converter<String[]> {
    @Override
    public String[] convert(Object val) throws ConverterException {

        // convert from database value (val) to a String[]

        // throw a ConverterException if the converter doesn't know how to convert the given value.

    }

    @Override
    public Object toDatabaseParam(String[] val) {
        // Convert from a String array to the data type the database expects when adding parameters.
    }
}

you register the converter by passing it to the constructor of the PostgresQuirks when creating your Sql2o instance:

Map<Class, Converter> converterMap = new HashMap<Class, Converter>() {{
    put(String.class, new StringArrayConverter());
}};

Sql2o sql2o = new Sql2o(myDataSource, new PostgresQuirks(converterMap));

If you get this to work and you don't mind sharing your result, I would very much like to add your converter to the sql2o code base. It would be really awesome if you choose to share the result :)

Regards

Lars Aaberg

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

[Steve Kradel]

Hi Lars,

Certainly, I will take a crack at it.  It will take some experimentation to discover what pgsql-specific class pops out; not sure you will want to integrate it into sql2o directly as it will mean a hard dependency on pgsql jdbc4, or else a lot of reflection.

Also, run-time class tokens for arrays might be a no-go, although admittedly I've done a lot more with CLR generics and typing than Java's.

--Steve

[Lars Aaberg]

Hi Steve,

I agree that it should not be included into sql2o directly. From version 1.6 of sql2o I all the quirks and db specific functionality will be moved to sub-projects. So, if you are using postgres, you would need sql2o and sql2o-postgres dependencies to get the full functionality.

~lars

--

[Steve Kradel]

The good news: the pgsql JDBC driver appears to convert hstore to java.util.Map automatically, no converter needed.

A converter for pgsql Arrays (database -> java.util.List) is easy to implement.

The bad news: It appears that a properly-implemented sql2o Converter for java.util.List -> database ought to have a handle to the base connection to create arrays as per http://stackoverflow.com/questions/10555782/how-to-pass-array-of-timestamps-to-postgres-function-using-java-jdbc

I am loath to bodge it in as a field on the converter class; perhaps we should have another Converter-compatible interface that supplies the active connection to both convert and toDatabaseParam methods?

Another issue is that I do not see any way to indicate arrays or their types to the Converter registration on a Quirks.

--Steve

[Lars Aaberg]

Hmm, I see. it looks like there is no quick-fix for this. Some of the internals of sql2o will need some refactoring to be able to use the Connection object from the converter.

Right now I don't have a good suggestion for a solution. I will experiment a little and get back to you.

~lars