• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

RFI | Native Query | Hibernate

 
Alok Sharan
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,


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

Tbl_Product (Table - 1)
-----------------------
Product_Id (PK)
Product_Title
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)
Person_Name

My Entities are -

1) Product.java

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


2) ProductPersonMap.java

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;

3) Person.java

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