Is there any way to use OrmLite with htores Postgres?

Question

We are currently using the PostgreSQL and OrmLite database. Now we have a use case for using Postgres hstore , but cannot find a way to access this table through OrmLite. I would prefer not to open a separate database connection to select and paste into this single table, but I do not see any other parameters.

At least I need a handle to an existing connection that uses OrmLite, so I can reuse it to create a prepared statement, but I have not found a way to get java.sql.Connection , starting with OrmLite ConnectionSource . I see that OrmLite has a JdbcCompiledStatement , but this is just a wrapper around the PreparedStatement and requires that the PreparedStatement be passed to the constructor. (Not sure if this will be for use.)

I tried to use DatabaseConnection.compileStatement(...) , but it requires knowledge of the types of fields used, and OrmLite does not seem to know what hstore is.

I tried using updateRaw() , but this function exists only on dao OrmLite, which I do not have, because the table to which I attached dao has an OrmLite field type that it does not recognize. Is there a way to get a generic dao for issuing raw requests?

I understand that hstores are database specific and probably will not be supported by OrmLite, but I would really like to find a way to transfer data to and from the database using unsupported fields instead of just unsupported queries.

Answer 1

At least I need a handle to an existing connection that uses OrmLite, so I can reuse it to create a prepared statement ...

Well, that’s pretty easy. As mentioned in @jsight, ORMLite ConnectionSource for JDBC is JdbcConnectionSource . When you get a connection from this class using connectionSource.getReadOnlyConnection() , you will get a DatabaseConnection , which is really a JdbcDatabaseConnection and can be passed to it. There is a JdbcDatabaseConnection.getInternalConnection() method that returns the associated java.sql.Connection .

I tried using updateRaw (), but this function exists only on an OrmLite object that I don't have ...

You can really use any DAO class to perform a raw function in any table. This is conveniently considered as an unstructured update for the DAO object table. But if you have a DAO, you can do the raw update on any other table.

find a way to transfer data to and from the database using unsupported fields instead of just unsupported queries

If you use unsupported fields, you will have to do this as a raw statement — either SELECT or UPDATE . If you edit the post to show the original expression you tried, I can help more specifically.

Answer 2

It looks like ConnectionSource could be implemented by JdbcConnectionSource and most likely will return JdbcDatabaseConnection. This object has a getInternalConnection method that looks like what you are looking for.

Answer 3

@Gray I posted an ORMLite patch to SourceForge that might use the Other data type. The patch identifier is 3566779. With this patch, hstores can be supported.

Users will need to add the PGHStore class to their projects. The code for this class is here .

Users also need to add the persister class, as shown below:

 package com.mydomain.db.persister; import com.mydomain.db.PGHStore; import com.j256.ormlite.field.FieldType; import com.j256.ormlite.field.SqlType; import com.j256.ormlite.field.types.BaseDataType; import com.j256.ormlite.support.DatabaseResults; import java.sql.SQLException; public class PGHStorePersister extends BaseDataType { private static final PGHStorePersister singleton = new PGHStorePersister(); public static PGHStorePersister getSingleton() { return singleton; } protected PGHStorePersister() { super(SqlType.OTHER, new Class<?>[] { PGHStore.class }); } protected PGHStorePersister(SqlType sqlType, Class<?>[] classes) { super(sqlType, classes); } @Override public Object parseDefaultString(FieldType ft, String string) throws SQLException { return new PGHStore(string); } @Override public Object resultToSqlArg(FieldType fieldType, DatabaseResults results, int columnPos) throws SQLException { return results.getString(columnPos); } @Override public Object sqlArgToJava(FieldType fieldType, Object sqlArg, int columnPos) throws SQLException { return new PGHStore((String) sqlArg); } @Override public boolean isAppropriateId() { return false; } } 

Finally, users will need to annotate their data in order to use persister.

 @DatabaseField(columnName = "myData", persisterClass=PGHStorePersister.class)