Meaningless Drivel is fun!
The moose likes Object Relational Mapping and the fly likes JPA data retrieval query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA data retrieval query" Watch "JPA data retrieval query" New topic

JPA data retrieval query

satish bodas
Ranch Hand

Joined: Jun 19, 2008
Posts: 116
Hello ,
I have a search screen on the UI where user is expected to enter the loginId( varchar )

Based on the entered loginId I need to get the employee details from DB

I intend to use JPA ( provider = Hibernate )

My db design is as follows ::

As you can see the db is designed to use a surrogate key as the primary key .

In such a case do I have to use Query to get the details from db since the search is not based on Primary key field ?

Thanks ,
Paul Michael
Ranch Hand

Joined: Jul 02, 2001
Posts: 697
Yes that's correct, you can use Query to retrieve values based on any of the object's properties (PK or non-PK).

What other approach do you have in mind?

SCJP 1.2 (89%), SCWCD 1.3 (94%), IBM 486 (90%), SCJA Beta (96%), SCEA (91% / 77%), SCEA 5 P1 (77%), SCBCD 5 (85%)
satish bodas
Ranch Hand

Joined: Jun 19, 2008
Posts: 116
Thanks Paul for the reply .

I forgot to mention an important req from my end

This user_lov has a FK in a separate table ( user_address )

Relationship from user_lov to user_address is a One to many

I was wondering if I need to write a sql query with inner joins ?

If I do the necessary annotations in my User & Address class
the "find" method is not returning me the details even if I do use the primary key .

Thanks ,
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 17278

If you have defined your one-to-many relationship in JPA like so:

@OneToMany(fetch=FetchType.EAGER, mappedBy="schedule", cascade=CascadeType.MERGE)
@OrderBy("stopSequence ASC")
private List<ScheduleStops> scheduleStops = new ArrayList<ScheduleStops>();

Then you do not need to code a join. Just use the Java relationships. For example:

Integer stopId = scheduleX.getScheduleStops().get(1);


for (ScheduleStops stop : scheduleX.getScheduleStops() ) {

You don't have to use OrderBy, but I needed an ordered collection of children, so this saved SQL coding a sort clause.

The default behavior for JPA is lazy loading. I was using this code in an app that detaches the ORM objects from the EntityManager, so I made it do an eager fetch instead. If I hadn't, the collection object would have been null after I detached. If I'd remained attached, a stub would have caused the child SELECT to be done on first access. In either case, eager loading causes an SQL join to be created instead of separate SELECTs for parent and children.

An IDE is no substitute for an Intelligent Developer.
satish bodas
Ranch Hand

Joined: Jun 19, 2008
Posts: 116
Thanks Tim for the response .
Using the logic you have specified I have got it to work .

Here is the complete code ::
First code base is for the master table in db :: userLov

The second codebase is for the child table ( the many part )

Here is the db piece :: ( I prefer having my ddl separate

Sample code for the client ::

Things worked - thanks Tim

My query is - now in case I dont want to a search based on the Primary key .

Lets say I want to search the user table on the basis of "loginId"
( IN above table ddl its not there - but if I were to add a unique constraint on this newly added field )

how do I go about extracting results along with addresses

I dont want to use the query or SQL syntax - since I feel its such a waste of time using all these annotations if they cannot support me here !

Thanks ,
I agree. Here's the link:
subject: JPA data retrieval query
jQuery in Action, 3rd edition