Win a copy of Java 9 Modularity: Patterns and Practices for Developing Maintainable Applications this week in the Java 9 forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

RFI | Native Query | Hibernate  RSS feed

Alok Sharan
Posts: 5
  • Mark post as helpful
  • send pies
  • 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?
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!