Hibernate hbm2ddl.auto create/update skip View

Question

I have a mapped entity to a database view e.g.

@Entity
@Immutable
@Table(name = "my_view")
public class MyView {
 // some properties and getters
}

and my sql pendant is like create view my_view as select * from thisAndThat and stupid logic;

In my production environment it works like a charme. Now in my integration tests I use Hibernate property hibernate.hbm2ddl.auto = update to create my database setup with DbUnit in HSQL on the fly. Because I did not mark the entity table as view directly hibernate tries to create the database.

I am able to fix this by using flyway and add an migration script

drop table if exists my_view;
create view my_view AS ....;

First at all: It works

BUT: While building my project I get a lot of exceptions

2019-11-29 14:03:43,023  WARN  ExceptionHandlerLoggedImpl:handleException:27 GenerationTarget encountered exception accepting command : Error executing DDL "alter table my_view add constraint FKj50dxtl46l99jfi90uf4df7vo foreign key (other_table_id) references fonds" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table my_view add constraint FKj50dxtl46l99jfi90uf4df7vo foreign key (other_table_id) references other_table" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applyForeignKeys(AbstractSchemaMigrator.java:433)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:249)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:315)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:708)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:724)
    at org.springframework.orm.hibernate5.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:615)
....

which is a) a bit disturbing and b) makes me nervous this could break in (near) future.

Do you know how I can skip creating a specific table dispite using hibernate.hbm2ddl.auto?

Thanks in advance!

Were you able to get this working? If so, how did you set up the dbUnit dataset to supply data for the view? I tried just including it like a normal table and end up with org.dbunit.dataset.NoSuchTableException errors. — pbuchheit, Mar 09 '21 at 15:51
As accepted answer suggested I use annotation \@Subselect("Select * from my_view"). That's the trick. FYI, I also use annotations \@Entity and \@Immutable — wolle, Mar 11 '21 at 09:14
I do the same thing. If you look at the database logs, is hibernate creating a 'my_view' table during the deployment process? If you clear out the .script that hibernate creates, does it make any difference? One of the issue I ran into was that the script originally included a create query for the table from before I changed it to a view so everything 'seemed' to be working. Once I deleted the script though, the view was no longer being added so dbunit could not find the table to populate. — pbuchheit, Mar 11 '21 at 13:42
hibernate isn't creating any table/view automatically. I added a init-script and create the view manually between compile and test-execution — wolle, Mar 12 '21 at 14:03

score 2 · Accepted Answer · answered Nov 29 '19 at 16:23

In this case I would use @Subselect instead of @Table (https://docs.jboss.org/hibernate/orm/5.2/javadocs/org/hibernate/annotations/Subselect.html)

As for documentation @Subselect

Map an immutable and read-only entity to a given SQL select expression.

Hibernate hbm2ddl.auto create/update skip View

1 Answers1