File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA data retrieval query" Watch "JPA data retrieval query" New topic
Author

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 ,
~satish
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 ,
~satish
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16250
    
  21

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);

Or

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.


Customer surveys are for companies who didn't pay proper attention to begin with.
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 ,
~satish
 
Consider Paul's rocket mass heater.
 
subject: JPA data retrieval query