title: Can I write criteria query joining view and a table
body: <p>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,...............,..................,....................)</p>
  
  <p>Here application_survey table includes multiple entries corresponding to application_id.</p>
  
  <p>So I have created veiw</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Then I need query</p>
  
  <pre><code>select* from application
  left join v_application_survey 
  on(v_application_survey.application_id=application.application_id)
  </code></pre>
  
  <p>, using hibernate criteria query.Is it possible.If possible please reply with example.</p>
  
  <p>Corresponding pojo class for v_application_survey is as follows</p>
  
  <pre><code>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;
  }
  
  }
  </code></pre>
  
  <p>Corresponding mapping file for v_application_survey is as follows</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
  &lt;!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
  "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"&gt;
  &lt;!-- Generated Aug 1, 2013 10:36:46 AM by Hibernate Tools 3.4.0.CR1 --&gt;
  &lt;hibernate-mapping&gt;
  &lt;class name="nic.mams.model.VApplicationSurvey" table="v_application_survey" schema="public"&gt;
  &lt;composite-id name="id" class="nic.mams.model.VApplicationSurveyId"&gt;
  &lt;key-property name="applicationId" type="java.lang.Long"&gt;
  &lt;column name="application_id" /&gt;
  &lt;/key-property&gt;
  &lt;key-property name="surveyNo" type="string"&gt;
  &lt;column name="survey_no" /&gt;
  &lt;/key-property&gt;
  &lt;key-property name="resurveyNo" type="string"&gt;
  &lt;column name="resurvey_no" /&gt;
  &lt;/key-property&gt;
  &lt;/composite-id&gt;
  &lt;/class&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>The model class for application_survey table is as follows</p>
  
  <pre><code>@SuppressWarnings("serial")
  public class ApplicationSurvey implements java.io.Serializable {
  
   private long applicationSurveyId;
   private Application application;
   private Village village;
   private Direction direction;
   private Employee employee;
   private String surveyNo;
   private String resurveyNo;
   private Date updatedOn;
   private char surveyType;
   private String resurveyBlock;
   private Double area;
  
   public ApplicationSurvey() {
   }
  
   public ApplicationSurvey(long applicationSurveyId, char surveyType) {
   this.applicationSurveyId = applicationSurveyId;
   this.surveyType = surveyType;
   }
  
   public ApplicationSurvey(long applicationSurveyId, Application application,
    Village village, Direction direction, Employee employee,
    String surveyNo, String resurveyNo, Date updatedOn,
    char surveyType, String resurveyBlock, Double area) {
   this.applicationSurveyId = applicationSurveyId;
   this.application = application;
   this.village = village;
   this.direction = direction;
   this.employee = employee;
   this.surveyNo = surveyNo;
   this.resurveyNo = resurveyNo;
   this.updatedOn = updatedOn;
   this.surveyType = surveyType;
   this.resurveyBlock = resurveyBlock;
   this.area = area;
   }
  
   public long getApplicationSurveyId() {
   return this.applicationSurveyId;
   }
  
   public void setApplicationSurveyId(long applicationSurveyId) {
   this.applicationSurveyId = applicationSurveyId;
   }
  
   public Application getApplication() {
   return this.application;
   }
  
   public void setApplication(Application application) {
   this.application = application;
   }
  
   public Village getVillage() {
   return this.village;
   }
  
   public void setVillage(Village village) {
   this.village = village;
   }
  
   public Direction getDirection() {
   return this.direction;
   }
  
   public void setDirection(Direction direction) {
   this.direction = direction;
   }
  
   public Employee getEmployee() {
   return this.employee;
   }
  
   public void setEmployee(Employee employee) {
   this.employee = employee;
   }
  
   public String getSurveyNo() {
   return this.surveyNo;
   }
  
   public void setSurveyNo(String surveyNo) {
   this.surveyNo = surveyNo;
   }
  
   public String getResurveyNo() {
   return this.resurveyNo;
   }
  
   public void setResurveyNo(String resurveyNo) {
   this.resurveyNo = resurveyNo;
   }
  
   public Date getUpdatedOn() {
   return this.updatedOn;
   }
  
   public void setUpdatedOn(Date updatedOn) {
   this.updatedOn = updatedOn;
   }
  
   public char getSurveyType() {
   return this.surveyType;
   }
  
   public void setSurveyType(char surveyType) {
   this.surveyType = surveyType;
   }
  
   public String getResurveyBlock() {
   return this.resurveyBlock;
   }
  
   public void setResurveyBlock(String resurveyBlock) {
   this.resurveyBlock = resurveyBlock;
   }
  
   public Double getArea() {
   return this.area;
   }
  
   public void setArea(Double area) {
   this.area = area;
   }
  
  }
  </code></pre>
  
  <p>The mapping file for application_survey table is as follows</p>
  
  <pre><code>    &lt;?xml version="1.0"?&gt;
  &lt;!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
  "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"&gt;
  &lt;!-- Generated 14 Nov, 2011 5:11:45 PM by Hibernate Tools 3.4.0.CR1 --&gt;
  &lt;hibernate-mapping&gt;
      &lt;class name="nic.mams.model.ApplicationSurvey" table="application_survey" schema="public"&gt;
          &lt;id name="applicationSurveyId" type="long"&gt;
              &lt;column name="application_survey_id" /&gt;
              &lt;generator class="increment"/&gt;
          &lt;/id&gt;
          &lt;many-to-one name="application" class="nic.mams.model.Application" fetch="select"&gt;
              &lt;column name="application_id" /&gt;
          &lt;/many-to-one&gt;
          &lt;many-to-one name="village" class="nic.mams.model.Village" fetch="select"&gt;
              &lt;column name="village_id" length="6" /&gt;
          &lt;/many-to-one&gt;
          &lt;many-to-one name="direction" class="nic.mams.model.Direction" fetch="select"&gt;
              &lt;column name="direction" /&gt;
          &lt;/many-to-one&gt;
          &lt;many-to-one name="employee" class="nic.mams.model.Employee" fetch="select"&gt;
              &lt;column name="updated_by" /&gt;
          &lt;/many-to-one&gt;
          &lt;property name="surveyNo" type="string"&gt;
              &lt;column name="survey_no" length="10" /&gt;
          &lt;/property&gt;
          &lt;property name="resurveyNo" type="string"&gt;
              &lt;column name="resurvey_no" length="10" /&gt;
          &lt;/property&gt;
          &lt;property name="updatedOn" type="timestamp"&gt;
              &lt;column name="updated_on" length="29" /&gt;
          &lt;/property&gt;
          &lt;property name="surveyType" type="char"&gt;
              &lt;column name="survey_type" length="1" not-null="true" /&gt;
          &lt;/property&gt;
          &lt;property name="resurveyBlock" type="string"&gt;
              &lt;column name="resurvey_block" length="10" /&gt;
          &lt;/property&gt;
          &lt;property name="area" type="java.lang.Double"&gt;
              &lt;column name="area" precision="17" scale="17" /&gt;
          &lt;/property&gt;
      &lt;/class&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>Note that <strong>application</strong> table have one-to-many relationship to <strong>application_survey</strong> table</p>