java - tables - join hibernate criteria

HQL Hibernate INNER JOIN

Question

¿Cómo puedo escribir esta consulta SQL en Hibernate? Me gustaría usar Hibernate para crear consultas, no crear la base de datos.

SELECT * FROM Employee e INNER JOIN Team t ON e.Id_team=t.Id_team

Creé clases de entidad en 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
}

Intenté crear una consulta de selección de trabajo de muchas maneras, pero todavía no funciona.

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(); 

¿Tal vez algo está mal con las entidades?

Answer 1

Las combinaciones solo se pueden utilizar cuando existe una asociación entre entidades. Su entidad de empleado no debe tener un campo llamado id_team , de tipo int , asignado a una columna. Debe tener una asociación ManyToOne con la entidad del Equipo, asignada como JoinColumn:

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

Entonces, la siguiente consulta funcionará perfectamente:

select e from Employee e inner join e.team

Que cargará a todos los empleados, excepto aquellos que no estén asociados a ningún equipo.

Lo mismo ocurre con todos los otros campos que son una clave externa a alguna otra tabla asignada como una entidad, por supuesto ( id_boss , id_profession ).

Es hora de que lea la documentación de Hibernate, porque se perdió una parte extremadamente importante de lo que es y cómo funciona.

Answer 2

Puede hacerlo sin tener que crear un mapeo real de Hibernate. Prueba esto:

SELECT * FROM Employee e, Team t WHERE e.Id_team=t.Id_team

Answer 3

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<Address> addList=new ArrayList<Address>();
@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<Address> getAddList() {
return addList;
}
public void setAddList(List<Address> addList) {
this.addList = addList;
}
}

Tenemos dos entidades Empleado y Dirección con relación One to Many.

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;
}
}

De esta manera podemos implementar la unión interna entre dos tablas.

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<Employee> list=query.list();
list.stream().forEach((p)->{System.out.println(p.getEmpName());});
session.close();
}catch(Exception e){
e.printStackTrace();
}
}
}

He usado Java 8 para bucle para editar los nombres. Asegúrese de tener jdk 1.8 con tomcat 8. También agregue algunos registros más para una mejor comprensión.

 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;
}
}