Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

RFI | Native Query | Hibernate

 
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?
 
I need a new interior decorator. This tiny ad just painted every room in my house purple.
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic