Java

Hibernate temporary tables

I was surprised to discover that Hibernate makes use of temporary tables when performing deletes. The creation of the table was triggering an alert in a database monitor for an application. The monitor logged the creation of tables beginning with the prefix HT_ and a name that mirrored that of an entity tablename.

Initially I must admit that I was thrown very far off track and started looking through the schema creation scripts and codebase looking for something, even checking HT as initials against the developer names. Naturally we tried deleting the tables and soon enough they were recreated.

As with a lot of modern programming bugs the answer came through some frustrated Googling and the realisation that HT might stand for Hibernate Table. With that insight in place I was then able to find this excellent explanation of the situation and I get another self-righteous data point in my dislike of ORM.

This temporary table is required for two reasons, firstly because ORM’s do not actually do a proper mapping between the domain concepts of a relational store and objects and secondly because Hibernate wants to create a verisimilitude around the concept of a class hierarchy.

I don’t think there is a better solution than what Hibernate is offering here but I do think it is the wrong problem being solved in a clever way.

Standard

4 thoughts on “Hibernate temporary tables

  1. Ankit Patel says:

    What if my database doesn’t allow to create Temp Table?

    it gives below error.

    could not insert/select ids for bulk update
    org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:151)
    at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:421)
    at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:283)
    at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1288)
    at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:117)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3657)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:142)
    … 48 more

    • There isn’t really a workaround, you need the permissions or Hibernate is not going to work.

      If you can control the schema details then one thing you can do is not delete records but instead add a column to represent a “deletion marker”. However that will cause performance problems if the deleted rows go beyond about 10% of your data. You could also try using archive or active tables that simply contain the primary key of the entries you want to query against. That will create a join obviously but should be performant in most cases.

      If you can’t make any changes to the database tier then you are out of luck and should probably use some other ORM layer or write the SQL yourself and use Hibernate’s direct execution features.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s