Databases
StackOverflow2013
Postsdbo
POCan I write criteria query joining view and a table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCan I write criteria query joining view and a table
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPramil
    1. USPramil
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload