Hi,
I am new to hibernate and i am developing an application using
struts 2 and hibernate 3. I have minimal knowledge of hibernate.
I have an sql query. I need help in knowing which will be the best way to generate a model and controller class for the below query as the select query has only one table but within the select clause it makes use of multiple tables. there are no foreign key relationships within the tables, so the final query has to be this way and cannot be changed.
SELECT (
SELECT pname
FROM priority
WHERE ID = JI.PRIORITY
) AS 'Priority'
,JI.pkey AS 'IssueKey'
,(
SELECT stringvalue
FROM customfieldvalue
WHERE customfield = 10010 AND issue = JI.ID
) AS 'Legacy_ID'
,JI.SUMMARY AS 'Summary'
,(
SELECT pname
FROM issuetype
WHERE ID = JI.issuetype
) AS 'IssueType'
,(
SELECT CO.customvalue
FROM customfieldoption CO
,customfieldvalue CV
WHERE CO.ID = CV.STRINGVALUE AND CO.CUSTOMFIELD = CV.CUSTOMFIELD AND CV.CUSTOMFIELD = 12624 AND CV.ISSUE = JI.ID
) AS 'Release Timebox'
,CASE WHEN JI.TIMEORIGINALESTIMATE = NULL THEN NULL ELSE (JI.TIMEORIGINALESTIMATE / 28800) END AS 'Original Estimate'
,CASE WHEN JI.TIMEESTIMATE = NULL THEN NULL ELSE (JI.TIMEESTIMATE / 2880) END AS 'Remaining Estimate'
,(
SELECT pname
FROM project
WHERE ID = JI.PROJECT
) AS 'Project'
,(
SELECT CO.customvalue
FROM customfieldoption CO
,customfieldvalue CV
WHERE CO.ID = CV.STRINGVALUE AND CO.CUSTOMFIELD = CV.CUSTOMFIELD AND CV.CUSTOMFIELD = 12621 AND CV.ISSUE = JI.ID
) AS 'Investment Theme'
,(
SELECT CO.customvalue
FROM customfieldoption CO
,customfieldvalue CV
WHERE CO.ID = CV.STRINGVALUE AND CO.CUSTOMFIELD = CV.CUSTOMFIELD AND CV.CUSTOMFIELD = 11220 AND CV.ISSUE = JI.ID
) AS 'Team'
FROM jiraissue JI
WHERE
JI.PROJECT IN (SELECT P.ID FROM project P,projectcategory PC,nodeassociation NA WHERE P.ID = NA.SOURCE_NODE_ID AND NA.SINK_NODE_ID = PC.ID AND NA.sink_node_entity = 'ProjectCategory' AND PC.cname = 'IX~XYZ' )
AND EXISTS ( SELECT CO.customvalue FROM customfieldoption CO ,customfieldvalue CV WHERE CO.ID = CV.STRINGVALUE AND CO.CUSTOMFIELD = CV.CUSTOMFIELD AND CV.CUSTOMFIELD = 12624 AND CV.ISSUE = JI.ID AND CO.customvalue IN ('2015.4'))
AND JI.issuetype in (4, 23)
Order by 3,2.
Thank you in advance