• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Need to generate a complex hibernate query

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 662
Eclipse IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm not going to ask on the rationale behind using hibernate.
Can you not use AliasToEntity Mapping?
 
reply
    Bookmark Topic Watch Topic
  • New Topic