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?
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.