javahibernatehql

How to use hibernate query on two tables (join table)?


I have these tables: cont(id_cont, user, pass) emp(emp_id, name, cont_id_cont (fk))

@Entity
@Table(name = "emp", catalog = "", uniqueConstraints = {
      @UniqueConstraint(columnNames = "cont_id_cont") })
public class Emp implements java.io.Serializable{
  private int id_emp;
  private ContUser contUser; 
  private String name;

and

@Entity
@Table(name = "cont", catalog = "", uniqueConstraints = {
        @UniqueConstraint(columnNames = "pass") })
public class Cont implements java.io.Serializable{

    private int id_cont;
    private String user;
    private String pass;
private Set<Emp> empForCont = new HashSet<Emp>(0);
}

Now: I want this query: select cont.user, emp.name, emp.cont_id_cont from cont inner join emp on cont.id_cont= emp.cont_id_cont where cont.user = 'gbs04405';


Solution

  • This style of accessing data is not what ORM is intended for. If you are using Hibernate, you should (usually) access data through objects. To make this work, instead of embedding SQL like constraints, define relations @OneToMany, @ManyToOne, and/or @ManyToMany between objects where necessary.

    In addition, you should consider using HQL (or JPQL) instead of pure SQL, to achieve what you want.


    It should be something like this:

    SELECT e FROM Emp e JOIN e.contUser u WHERE u.user = :userstring
    

    You can check here for further JPQL syntax.