0 Replies Latest reply on Aug 2, 2013 8:01 AM by pramilkprince

    Can I write criteria query joining view and a table

    pramilkprince Newbie

      I have two table

      1)application(application_id(pk),application_type,application_date,......,................,............)

      2)application_survey(application_survey_id,application_id(fk),survey_no,resurvey_no,...............,..................,....................)

       

      Here application_survey table includes multiple entries corresponding to application_id.

       

      So I have created veiw

       

      CREATE OR REPLACE VIEW v_application_survey AS

      SELECT application_survey.application_id, string_agg(application_survey.survey_no::text,

              CASE

                  WHEN btrim(application_survey.survey_no::text) = ''::text OR application_survey.survey_no IS NULL THEN ''::text

                  ELSE ','::text

              END) AS survey_no, string_agg(application_survey.resurvey_no::text,

              CASE

                  WHEN btrim(application_survey.resurvey_no::text) = ''::text OR application_survey.resurvey_no IS NULL THEN ''::text

                  ELSE ','::text

              END) AS resurvey_no

         FROM application_survey

        GROUP BY application_survey.application_id;

       

      ALTER TABLE v_application_survey OWNER TO postgres;

       

       

       

      Then I need query

       

      select* from application

      left join v_application_survey on(v_application_survey.application_id=application.application_id),

       

      using hibernate criteria query.Is it possible.If possible please reply with example.

       

      Corresponding pojo class for v_application_survey is as follows

       

      public class VApplicationSurvey implements java.io.Serializable {

       

      private VApplicationSurveyId id;

       

      public VApplicationSurvey() {

      }

       

      public VApplicationSurvey(VApplicationSurveyId id) {

      this.id = id;

      }

       

      public VApplicationSurveyId getId() {

      return this.id;

      }

       

      public void setId(VApplicationSurveyId id) {

      this.id = id;

      }

       

      }

       

      Corresponding mapping file for  v_application_survey is as follows

       

      <?xml version="1.0"?>

      <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"

      "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

      <!-- Generated Aug 1, 2013 10:36:46 AM by Hibernate Tools 3.4.0.CR1 -->

      <hibernate-mapping>

          <class name="nic.mams.model.VApplicationSurvey" table="v_application_survey" schema="public">

              <composite-id name="id" class="nic.mams.model.VApplicationSurveyId">

                  <key-property name="applicationId" type="java.lang.Long">

                      <column name="application_id" />

                  </key-property>

                  <key-property name="surveyNo" type="string">

                      <column name="survey_no" />

                  </key-property>

                  <key-property name="resurveyNo" type="string">

                      <column name="resurvey_no" />

                  </key-property>

              </composite-id>

          </class>

      </hibernate-mapping>