Hibernate version: 2.1.6

Mapping documents: Partial definition follows.


Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Data base server: Postgres 7.4.3

The generated SQL (show_sql=true):



Is there a way to force the generated SQL for an update to include an explicit cast on the columns used in the where clause?

For instance, I need the above update SQL to look like this:


Notice the cast of the id column in the where clause. The reason I ask has to do with the way Postgres uses (or, in my cast, does not use) indexes. If the value assigned to id in the update statement is an int (int4, the default) a sequential scan results. If the value assigned to id is casted as a bigint (int8) an index scan results.

Since, no one has responded I'll assume this cannot be done without reinstrumenting hibernate.

Has anyone else come across this problem with Postgres?

Rob

If you're not in a hurry, maybe Hibernate 3 helps. See Gavins blog at http://blog.hibernate.org/cgi-bin/blosxom.cgi/Gavin%20King/customsql.html.

The reference manual of the alpha 3 release has also some information.

HTH
Ernst

Thanks for the information. I'll look at the Hibernate3 docs.

For the record, the SQL for the update with explicit cast should have been:


Where the value assigned to column 'id' is explicitly casted to a bigint. Being able to turn on explicit casting of columns that have an index would be a boon for me.

Rob

OK, with further research it looks like I can accomplish what I need by writing my own custom ClassPersister that generates the update sql as I need it. The ClassPersister is associated with the entity class by the

persister attribute in the Class element of the Hibernate mapping file for the entity class.

Does anyone know any other way of doing this?

Rob

In HB3, it can be done in the mapping document. In HB2, you need a custom persister.