0

I have created domain objects using annotations and now I need some sql queries to be written to fetch/insert/update data from/to more than one table.

Is there a way to keep only the sql queries in the hbm.xml file as named query because the domain objects are already created using annotations and I do not need to do it in xml way. If this is possible then what are the steps need to be followed, please advice.

Thanks in advance.

Improve this question

2 Answers 2

Reset to default
2

Yes you can use the bellow tag in your xml file

<sql-query name="Myquery">
   select * from YourTableName
</sql-query>

Check this example http://www.java4s.com/hibernate/example-on-hibernate-named-queries/

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

My aim is to use annotated domain object with a named query xml, how to achieve this? Please help.
Solved. You need to put all the columns of the table in the select clause even if you need few because it populates the complete domain object.
2

Firstly define a sql-query in hbm.xml like:

<sql-query name="persons"> 
  <return alias="person"  class="eg.Person"/> 
 Select person.NAME AS {person.name},person.AGE AS {person.age},person.SEX AS {person.sex} FROM PERSON person Where person.NAME LIKE :namePattern
</sql-query>

and then fetch it by the statment's name:

List people = sess.getNamedQuery("persons").setString("namePattern" ,namePattern)
.setMaxResults( 50 )
.list();

Another way, the sql-query element is unneeded, write java code directly:

List cats = sess.createSQLQuery(

"select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id ")
.addEntity("cat" , Cat. class)         // add entity
.addJoin("kitten" ,  "cat.kittens ")   // add related entity 
.list();

Maybe help you:)

Improve this answer

4 Comments

I have to use named query in a xml file and here is the query: SELECT appl.applicant_id as applicantId, appl.airport_id as airportId, appl.first_name as firstName, appl.ssn as ssn FROM applicant appl, enroll_request enroll WHERE enroll.applicant_id = appl.applicant_id and enroll.application_id = :applicationId. How my xml file should look like?
this is my entry in the xml which throws error: <hibernate-mapping> <import class="com.dtis.dac.domain.Applicant"/> <import class="com.dtis.dac.domain.EnrollmentRequest"/> <query name="getUniqueApplicant"> <![CDATA[ SELECT appl.applicant_id as applicantId, appl.airport_id as airportId, appl.first_name as firstName, appl.ssn as ssn FROM applicant appl, enroll_request enroll WHERE enroll.applicant_id = appl.applicant_id and enroll.application_id = :applicationId ]]> </query> </hibernate-mapping>
I have this query printed on consode: Hibernate: SELECT appl.applicant_id as applicantId, appl.airport_id as airportId, appl.first_name as firstName, appl.ssn as ssn FROM applicant appl, enroll_request enroll WHERE enroll.applicant_id = appl.applicant_id and enroll.application_id = ? this works when I test it using a database editor like SQL server mngmnt studio but fails saying: org.hibernate.exception.SQLGrammarException: Invalid column name applicant_id. PLEASE HELP ME.
I want a named query for the two Hibernate domain objects, Applicant and EnrollRequest. Right now I have some Applicant column selected but I actually want the Applicant object to be returned. The query is: SELECT appl.applicant_id as {applicant.applicantId}, appl.airport_id as {applicant.airportId}, appl.first_name as {applicant.firstName}, appl.ssn as {applicant.ssn} FROM applicant appl, enroll_request enroll WHERE enroll.applicant_id = appl.applicant_id and enroll.application_id = :applicationId How my xml file should look like? Please help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.