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.

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;