Hibernate & postgreSQL with Grails

Question

Is there an easy way to set hibernate to use different primary key identifiers for each table with postgres? I tried using postgres dialogs in a DataSource:

dialect = org.hibernate.dialect.PostgreSQLDialect or dialect = net.sf.hibernate.dialect.PostgreSQLDialect 

But that will not work. Thanks

Answer 1

The short answer is no, there is no easy way. However, I found a solution that really works. Basically you need to implement your own dialect. Here is the implementation (note the source of the implementation in the comments).

 package com.my.custom; import java.util.Properties; import org.hibernate.dialect.Dialect; import org.hibernate.dialect.PostgreSQLDialect; import org.hibernate.id.PersistentIdentifierGenerator; import org.hibernate.id.SequenceGenerator; import org.hibernate.type.Type; /** * Creates a sequence per table instead of the default behavior of one sequence. * * From <a href='http://www.hibernate.org/296.html'>http://www.hibernate.org/296.html</a> * @author Burt */ public class TableNameSequencePostgresDialect extends PostgreSQLDialect { /** * Get the native identifier generator class. * @return TableNameSequenceGenerator. */ @Override public Class<?> getNativeIdentifierGeneratorClass() { return TableNameSequenceGenerator.class; } /** * Creates a sequence per table instead of the default behavior of one sequence. */ public static class TableNameSequenceGenerator extends SequenceGenerator { /** * {@inheritDoc} * If the parameters do not contain a {@link SequenceGenerator#SEQUENCE} name, we * assign one based on the table name. */ @Override public void configure( final Type type, final Properties params, final Dialect dialect) { if (params.getProperty(SEQUENCE) == null || params.getProperty(SEQUENCE).length() == 0) { String tableName = params.getProperty(PersistentIdentifierGenerator.TABLE); if (tableName != null) { params.setProperty(SEQUENCE, "seq_" + tableName); } } super.configure(type, params, dialect); } } } 

The above implementation should be stored as TableNameSequencePostgresDialect.java under src/java/com/my/custom in your Grails project.

Then update your DataSource.groovy to use this new custom dialect.

 dialect = com.my.custom.TableNameSequencePostgresDialect 

This is pretty much about it. Not easy , but it can be done.