Hibernate and Postgres – a custom UserType to handle arrays

Here is a custom Hibernate UserType that will work to persist Java array types (string[], int[], boolean[], etc..) in a single column. I have seen Oracle implementations, but here is one that uses Java.sql.array and works for Postgres. If you’d like to know more about how this works, Andrew Phillips has written a great explanation of custom Hibernate UserTypes. Enjoy! _______________________ import java.io.Serializable; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.sql.Array; import org.hibernate.*; import org.hibernate.usertype.UserType; /* * Custom UserType implemented to store java array (ie; String[]) types */ public class CustomArrayType implements UserType { public int[] sqlTypes() { … Continue reading Hibernate and Postgres – a custom UserType to handle arrays

Resolving Postgres "returning auto generated keys no supported"

I discovered Jean-Pol Landrain’s, “TriggerAssignedIdentityGenerator” to address the issue of the Hibernate ID Generator not supporting an Id created by the database via a BEFORE INSERT trigger. However the TriggerAssignedIdentityGenerator design for Oracle did not work for the older JDBC PostgreSQL driver which doesn’t support the return of auto-generated keys. The probem was resolved by changes to the ‘prepare’ and ‘executeAndExtract’ methods: protected PreparedStatement prepare(String insertSQL, SessionImplementor session) throws SQLException { return session.getBatcher().prepareStatement(insertSQL + ” RETURNING *”);} protected Serializable executeAndExtract(PreparedStatement insert) throws SQLException { insert.execute(); // get the new id back ResultSet rs = insert.getResultSet(); if (rs.next()) { return new … Continue reading Resolving Postgres "returning auto generated keys no supported"

Postgres create trigger error: relation does not exist 42P01

PostgreSQL 8.4: When attempting to create a simple trigger the executes a function, for a table that exists, I was getting a 42P01 error: relation “table_name” does not exist CREATE TRIGGER genUuid BEFORE INSERT ON table_name FOR EACH ROW EXECUTE PROCEDURE generate_function_v1(); This was simple resolved by including the schema name along with the table as follows: CREATE TRIGGER genUuid BEFORE INSERT ON my_schma.table_name FOR EACH ROW EXECUTE PROCEDURE generate_function_v1(); While there is a default_schema property in the hibernate.cfg.xml that is set to “my_schma”, hibernate seems to only use this for schema generation, but not for queries. Is this a … Continue reading Postgres create trigger error: relation does not exist 42P01