HQL Hibernate INNER JOIN

Question

How can I write this SQL query in Hibernate  I would like to use Hibernate to create queries  not create the database   SELECT   FROM Employee e INNER JOIN Team t ON e Id team t Id team   I created entity classes in SQLServer2008     Entity  Table name  EMPLOYEE   public class Employee        Id  GeneratedValue      Column name  ID EMPLOYEE       private int id employee       Column name  SURNAME       private String surname       Column name  FIRSTNAME       private String firstname       Column name  ID PROFESSION       private int id profession       Column name  ID BOSS       private int id boss       Column name  HIRED DATE       private Date hired       Column name  SALARY       private double salary       Column name  SALARY ADD       private double salary add       Column name  ID TEAM       private int id team        setters and getters     Entity  Table name  TEAM   public class Team        Id  GeneratedValue      Column name  ID TEAM       private int id team       Column name  TEAMNAME       private String teamname       Column name  ADDRESS       private String address        setters and getters     I tried to build working select query in many ways but it still doesn t work   SessionFactory sessionFactory   new Configuration   configure   buildSessionFactory    Session session   sessionFactory openSession                     session beginTransaction    String select    FROM Employee e INNER JOIN Team t ON e Id team t Id team   Query query   session createQuery select   List elist   query list    session getTransaction   commit    session close          Maybe something is wrong with entities

User · Accepted Answer

Joins can only be used when there is an association between entities. Your Employee entity should not have a field named id_team, of type int, mapped to a column. It should have a ManyToOne association with the Team entity, mapped as a JoinColumn:

@ManyToOne
@JoinColumn(name="ID_TEAM")
private Team team;

Then, the following query will work flawlessly:

select e from Employee e inner join e.team

Which will load all the employees, except those that aren't associated to any team.

The same goes for all the other fields which are a foreign key to some other table mapped as an entity, of course (id_boss, id_profession).

It's time for you to read the Hibernate documentation, because you missed an extremely important part of what it is and how it works.

User · Answer

import java io Serializable  import java util ArrayList  import java util List  import javax persistence CascadeType  import javax persistence Column  import javax persistence Entity  import javax persistence GeneratedValue  import javax persistence GenerationType  import javax persistence Id  import javax persistence OneToMany  import javax persistence Table    Entity  Table name  empTable   public class Employee implements Serializable  private static final long serialVersionUID   1L  private int id  private String empName   List lt Address gt  addList new ArrayList lt Address gt        Id  GeneratedValue strategy GenerationType IDENTITY   Column name  emp id   public int getId         return id    public void setId int id        this id   id    public String getEmpName         return empName    public void setEmpName String empName        this empName   empName      OneToMany mappedBy  employee  cascade CascadeType ALL  public List lt Address gt  getAddList         return addList     public void setAddList List lt Address gt  addList        this addList   addList        We have two entities Employee and Address with One to Many relationship   import java io Serializable  import javax persistence Entity  import javax persistence GeneratedValue  import javax persistence GenerationType  import javax persistence Id  import javax persistence JoinColumn  import javax persistence ManyToOne  import javax persistence Table    Entity  Table name  address   public class Address implements Serializable   private static final long serialVersionUID   1L   private int address id  private String address  Employee employee    Id  GeneratedValue strategy GenerationType IDENTITY  public int getAddress id         return address id    public void setAddress id int address id        this address id   address id    public String getAddress         return address    public void setAddress String address        this address   address      ManyToOne  JoinColumn name  emp id   public Employee getEmployee         return employee    public void setEmployee Employee employee        this employee   employee        By this way we can implement inner join between two tables  import java util List  import org hibernate Query  import org hibernate Session   public class Main    public static void main String   args        saveEmployee         retrieveEmployee        private static void saveEmployee         Employee employee new Employee        Employee employee1 new Employee        Employee employee2 new Employee        Employee employee3 new Employee         Address address new Address        Address address1 new Address        Address address2 new Address        Address address3 new Address         address setAddress  1485 Sector 42 b        address1 setAddress  1485 Sector 42 c        address2 setAddress  1485 Sector 42 d        address3 setAddress  1485 Sector 42 a         employee setEmpName  Varun        employee1 setEmpName  Krishan        employee2 setEmpName  Aasif        employee3 setEmpName  Dut         address setEmployee employee       address1 setEmployee employee1       address2 setEmployee employee2       address3 setEmployee employee3        employee getAddList   add address       employee1 getAddList   add address1       employee2 getAddList   add address2       employee3 getAddList   add address3        Session session HibernateUtil getSessionFactory   openSession         session beginTransaction         session save employee       session save employee1       session save employee2       session save employee3       session getTransaction   commit        session close       private static void retrieveEmployee         try       String sqlQuery  select e from Employee e inner join e addList        Session session HibernateUtil getSessionFactory   openSession         Query query session createQuery sqlQuery        List lt Employee gt  list query list          list stream   forEach  p - gt  System out println p getEmpName                 session close         catch Exception e           e printStackTrace                I have used Java 8 for loop for priting the names  Make sure you have jdk 1 8 with tomcat 8  Also add some more records for better understanding     public class HibernateUtil    private static SessionFactory sessionFactory    static       Configuration configuration   new Configuration         configuration addAnnotatedClass Employee class       configuration addAnnotatedClass Address class                     configuration setProperty  connection driver class   com mysql jdbc Driver        configuration setProperty  hibernate connection url    jdbc mysql   localhost 3306 hibernate                                        configuration setProperty  hibernate connection username    root             configuration setProperty  hibernate connection password    root        configuration setProperty  dialect    org hibernate dialect MySQLDialect        configuration setProperty  hibernate hbm2ddl auto    update        configuration setProperty  hibernate show sql    true        configuration setProperty   hibernate connection pool size    10            configuration     StandardServiceRegistryBuilder builder   new StandardServiceRegistryBuilder   applySettings configuration getProperties         sessionFactory   configuration buildSessionFactory builder build        public static SessionFactory getSessionFactory         return sessionFactory

User · Answer

You can do it without having to create a real Hibernate mapping  Try this   SELECT   FROM Employee e  Team t WHERE e Id team t Id team

[java] HQL Hibernate INNER JOIN

Examples related to java

Examples related to hibernate

Examples related to hql