CODEWITHSUNDEEP
javasqlhibernatepostgresqlmaterialized-views
Peter Jurkovic
Peter Jurkovic

Reputation: 2896

Mapping entity to a materialized view using Hibernate

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.

Upvotes: 6

Views: 12989

Answers (3)

Ternsip
Ternsip

Reputation: 11

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

Upvotes: 1

Peter Jurkovic
Peter Jurkovic

Reputation: 2896

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;

Upvotes: 4

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153800

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).

Upvotes: 3

Related Questions