JPA data retrieval query

[satish bodas]

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 ::

   
create table user_lov(
userid int,loginid varchar not null,firstname varchar not null,lastname varchar not null,status char(1) not null default 'A',
createdDate timestamp not null,createdBy varchar not null,updatedDate timestamp,updatedBy varchar);
 
alter table user_lov add constraint p_k_user_lov PRIMARY KEY (userid);
 
create unique index u_i_user_lov on user_lov(loginid);

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]

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?

[satish bodas]

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]

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.

[satish bodas]

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

   
@Entity
@Table(name="userLov")
public class User implements Serializable {
@Id 
@GeneratedValue(strategy=GenerationType.AUTO)
 
private Long id;
private String name;
 
@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL , targetEntity = Address.class , mappedBy = "user")
private Set addresses;

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

  
@Entity
@Table(name="userAddress")
public class Address implements Serializable {
@Id 
@GeneratedValue(strategy=GenerationType.AUTO)
private long aid;
private String street;

        @ManyToOne
@JoinColumn(name="id" , nullable = false)
private User user;

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

create table userLov(
id int,
name varchar
);
 
alter  table userLov add constraint p_k primary key (id);
 
create table userAddress(
aid int,
street varchar,
id int 
);
 
alter  table userAddress add constraint p_k_addid primary key (aid);
 
alter  table userAddress add constraint f_k_addid  foreign key (id) references userLov(id);   

Sample code for the client ::

   
User user = em.find(User.class,new Long(10));
java.util.Set addresses = user.getAddresses();
java.util.Iterator iter = addresses.iterator();
while(iter.hasNext()){
Address address = (Address)iter.next();
System.out.println(" address details " + address.toString());
}

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