Win a copy of Java Challengers this week in the Java in General forum!
  • 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 ...
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • paul wheaton
  • Devaka Cooray
  • Jeanne Boyarsky
  • Tim Cooke
  • Liutauras Vilda
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Piet Souris
  • salvin francis
  • Mikalai Zaikin
  • Himai Minh

RFI | Native Query | Hibernate

Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

I am using hibernate for interacting with database. I have three tables

Tbl_Product (Table - 1)
Product_Id (PK)
Product_Number (Unique)

Tbl_Product_Person_Map(Table - 2)
Map_Id (PK)
Product_Id (FK)
Person_Id (FK)

Tbl_Person (Table - 3)
Person_Id (PK)

My Entities are -


Integer productID;
String productNumber; //Being alphanumeric
String productTitle;
Collection productPersonMap; //Tbl_Product has one to many relation with table Tbl_Product_Person_Map on product_id


Integer productID;
Integer personID;
Person personObj; //Tbl_Product_Person_Map is having many to one mapping with table Tbl_Person on person_id
Product productObj;


Integer personID;
String personName;

What I am looking for:
I am trying to get list of products such that product number starts with(?) and person name starts with (?), being ? to be replaced with user's input.

*) What I am using to get it :
At the first stage, I am using HQL to get the desired result. My HQL looks like -
Select distinct prod from Product prod, ProductPersonMap prodMap, Person pers
Where prod.productID = prodMap.productObj.productID
AND prodMap.personObj.personID = pers.personID
AND UPPER(prod.productNumber) LIKE (?)
AND UPPER(pers.personName) LIKE UPPER(?)

*) Problem :
This query is not giving me the desired result.
When I tried to debug the actual query generated by Hibernate, I found three queries are getting fired internally -
i) select distinct product0_.Product_Id, product0_.Title, product0_.Product_Number
from Tbl_Product product0_, Tbl_Product_Person_Map productpers1_, Tbl_Person person2_
Where product0_.Product_Id = productpers1_.Product_Id
AND person2_.Person_Id = productpers1_.Person_Id
AND UPPER(product0_.Product_Number) LIKE (?)
AND UPPER(person2_.Person_Name) LIKE UPPER(?)

ii) select productpers1_.Map_Id, productpers1_.Product_Id, productpers1_.Person_Id
from Tbl_Product_Person_Map productpers1_
where productpers1_.productID = ?

iii) select person2_.personID, person2_.Person_Name
from Tbl_Person person2_
where person2_.personID = ?

As a result of this I am getting some additional rows from table Tbl_Person in the final result collection.

*) SQL Query that is working for me -
Select *
from Tbl_Product prod, Tbl_Product_Person_Map map, Tbl_Person person
Where prod.Product_Id = map.Product_Id
AND map.Person_Id = person.Person_Id
AND UPPER(prod.Product_Number) LIKE UPPER('12a%')
AND UPPER(person.Person_Name) LIKE UPPER('sharon%')

*) What I am looking for :
I read somewhere that we can use Native SQL to fire SQL queries.
I tried googling it but the samples are not looking sufficient as of now.

Could someone help me resolving this issue?
    Bookmark Topic Watch Topic
  • New Topic