Mapping entity to a materialized view using Hibernate

Question

I need to map (PostgreSQL) a materialized view to @Entity, using Hibernate. If is hbm2ddl configured to update value, Hibernate always tries to create new SQL table. That only happens if the view is materialized, otherwise (with non-materialized views) it works without problems.

Mapped entity

@Entity
@Immutable
@Cache (usage=CacheConcurrencyStrategy.READ_ONLY)
@Table(name = "quasar_evaludated_function")
public class EvaluatedAuditor {

    private long id;

    private boolean qsAuditor;

    // getter setters ...

}

SQL MATERIALIZED VIEW

CREATE materialized VIEW quasar_evaludated_function
AS SELECT a.id AS id,
          (SELECT Count(code)
           FROM   quasar_qs_auditor_code code
           WHERE  code.auditor_id = a.id
                  AND code.is_granted = TRUE) > 0 AS is_qs_auditor
   FROM   quasar_auditor a;  

Log

ERROR 2015-01-14 21:16:17 SchemaUpdate:execute(line 261) - HHH000388: Unsuccessful: create table quasar_evaludated_function (id int8 not null, is_clinical_expert boolean, is_product_assessor_a boolean, is_product_assessor_r boolean, is_product_specialist boolean, is_qs_auditor boolean, is_responsible_clinician boolean, is_technical_expert boolean, primary key (id))
ERROR 2015-01-14 21:16:17 SchemaUpdate:execute(line 262) - ERROR: relation "quasar_evaludated_function" already exists

If is the hbm2ddl option configured to validate is thrown an Exception.

Thank you for help.

Modla

Answer 1

It's not nice solution, but it works. I've just created a new view, which is referencing to a materialized view. If you don't need automatic schema generation, you should see Vlad Mihalcea's solution.

CREATE MATERIALIZED VIEW quasar_evaludated_function_mv AS select 
                a.id as id,
                (select count(f) from quasar_qs_auditor_code f where  f.auditor_id = a.id and f.is_granted = true) >  0 as is_qs_auditor,
                (select count(f) from quasar_product_assessor_a_code f where  f.auditor_id = a.id and f.is_granted = true) >  0 as is_product_assessor_a,
                (select count(f) from quasar_product_assessor_r_code f where  f.auditor_id = a.id and f.is_granted = true) >  0 as is_product_assessor_r, 
                (select count(f) from quasar_product_specialist_code f where  f.auditor_id = a.id and f.is_granted = true) >  0 as is_product_specialist,
                (select count(f) from quasar_technical_expert_code f where  f.auditor_id = a.id and f.is_granted = true) >  0 as is_technical_expert,
                (select count(f) from quasar_clinical_expert_code f where  f.auditor_id = a.id and f.is_granted = true) >  0 as is_clinical_expert, 
                (select count(f) from quasar_responsible_clinician_code f where  f.auditor_id = a.id and f.is_granted = true) >  0 as is_responsible_clinician
                from quasar_auditor a;

    CREATE VIEW quasar_evaludated_function  AS SELECT mv.* from quasar_evaludated_function_mv mv;

Modla

It is not that bad, after all. Thank you! – Mueller

Answer 2

You shouldn't use the hibernate.hbm2ddl.auto in that case. Actually, you should always favor using incrementing database scripts while having FlywayDB automate the database update process.

Because you use a database specific materialize view, the hibernate schema generator won't help you at all. So, your only option is the database specific incremental scripts.

You can still maintain separate scripts for PostgreSQL and for your integration testing in-memory database (e.g. HSQLDB or H2).

Yongyoni

Of course in production is used validate value. But in this case validation failed. Do you think that to map PostgreSQL materialized view to @Entity is impossible? – Modla

It is possible. Hibernate has been supported entities mapped to views for over a decade. But you can't use the automatic schema generation, even for validate. Just replace it with incremental scripts. – Yongyoni

Yes, thank you. But l'll take a look for another possible solution (if any), because hdm2ddl is quite comfortable feature. – Modla

Answer 3

Instead of @Table use @Subselect("SELECT * FROM quasar_evaludated_function")

Hime