Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Need to generate a complex hibernate query

 
roshan rahul
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Arun Kumarr
Ranch Hand
Posts: 661
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not going to ask on the rationale behind using hibernate.
Can you not use AliasToEntity Mapping?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic