Best way to join 2 tables using hibernate

Question

I have 2 hibernate entities/tables and need to combine information from both for use in a view. The tables are

Table Client:
  clientId,
  firstName,
  lastName,
  phone,
  cellPhone

Table Appointment:
  apptTime,
  clientId (and some other fields I don't need now)

There is a one-to-many relationship between client and Appointment, based on clientID. In regular SQL I'd just say something like:

Select 
    client.clientId, 
    appt.apptTime,
    client.firstName,
    client.lastName 
from 
    Client  client, 
    Appointment app 
where 
    client.clientId = appt.clientId

and use the recordset that was returned.

I'm not sure how to do this in Hibernate. Should I create a ClientAppt entity and then do something like the above select (modified somewhat for HQL)?

Note, I thought of using SecondaryTable approach, but I think that requires a 1 to 1 relationship? I suppose I could map a one to many, but is there an alternative? This is a one time change and mapping a one to many relationship might be a bit expensive for something this small? What is the best approach? Thanks

Answer 1

You can model this many ways. What I would do is have the Client be an Entity, and have the various appointments be a element collection. You didn't specify language, but in Java it would be like:

@Entity
@Table(name="person")
public class ClientAppts {
    ...

    @OneToMany(mappedBy="client")
    Set<Appointment> appointments;
}

@Entity
@Table(name="person")
public class Appointment {
    ....

    @ManyToOne
    @JoinColumn(name="person_id")
    private ClientAppts client;

    ....
}     

You can then fetch Clients using whatever method you prefer, with the simplest being:

    session.get(Client.class, clientId);

Hibernate will do the join and return a Client object with a set of appointments. You can go further make the set a SortedSet, or add an index to it to make it a List.

Answer 2

The "Best" way (imho) is not to think of the normal SQL way. Don't think of selecting certain fields and there are tables you are joining. You should try to make meaningful business entities and you should work on those business entities.

For example, here is what I would do:

(not actual code, just show the idea)

@Entity
public class Client {
  @Id
  @Column(name="clientId")
  private Long id;

  @Column
  private String longName;
}

@Entity
public class Appointment {
  @Id
  private Long id;

  @ManyToOne
  @Column(name="clientId")
  private Client client;
}

With the above mapping, what you need to do is nothing more than a HQL from Appointment a where a.client.id = :whatEverIdYouWant, or, to save the extra query to DB, from Appointment a join fetch a.client where a.client.id = :whatEverIdYouWant

You may also add @sharakan's mapping if your design doesn't mind Client depending on Appointment

Answer 3

You should use Projections.alias to specify the list of columns you are interested in. See the hibernate manual here for example.

Answer 4

public class Appoinment {

@Override
public List<Appointment > methodName(String search) {
    String hql =" from Appointment a join fetch a.client where a.client.id = 
                   :search";

    Query<Appointment > query=this.getSession().createQuery(hql,Appointment 
           .class);
    Long id=-1L;
    try {
        id=Long.parseLong(search);
    }catch (NumberFormatException ex) {
        ex.printStackTrace();
    }
    query.setParameter("id", id);
    query.setParameter("search", "%" +search+ "%");
    return query.getResultList();
}

}