There are two beans : Dept and User. They map respectively table department and table employees :
@Entity
@Table(name = "HR.EMPLOYEES")
public class User {
@Id
@SequenceGenerator(name="EMPLOYEES_SEQ", sequenceName="EMPLOYEES_SEQ", allocationSize=1)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="EMPLOYEES_SEQ")
@Column(name = "EMPLOYEE_ID")
private int uid;
@Formula(value="FIRST_NAME || ' ' || LAST_NAME")
private String username;
@Column(name = "FIRST_NAME")
private String firstname;
@Column(name = "LAST_NAME")
private String lastname;
@Column(name="EMAIL")
private String email;
@Column(name="HIRE_DATE")
@DateTimeFormat(pattern = "dd/MM/yyyy")
private Date hireDate;
private String job_id;
@Column(name = "SALARY")
private double salary;
@ManyToOne
@JoinColumn(name = "DEPARTMENT_ID")
private Dept dept;
public int getUid() {
return uid;
}
public void setUid(int id) {
this.uid = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getFirstname() {
return firstname;
}
public void setFirstname(String firstname) {
this.firstname = firstname;
}
public String getLastname() {
return lastname;
}
public void setLastname(String lastname) {
this.lastname = lastname;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getHireDate() {
return hireDate;
}
public void setHireDate(Date hireDate) {
this.hireDate = hireDate;
}
public String getJob_id() {
return job_id;
}
public void setJob_id(String job_id) {
this.job_id = job_id;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
}
@Entity
@Table(name = "HR.DEPARTMENTS")
public class Dept {
@Id
@Column(name = "DEPARTMENT_ID")
private int id;
@Column(name = "DEPARTMENT_NAME")
private String dname;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "dept")
@Transient
private Set<User> users = new HashSet<User>(0);
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public Set<User> getUsers() {
return users;
}
public void setUsers(Set<User> users) {
this.users = users;
}
}
Service method :
@Override
@Transactional
public List<User> list(int start, int length, String search, int triIdx, String ordreTri) {
String hql = "from User u join u.dept as d "; // here is the join condition
if (search != null && !search.equals("")) {
hql = hql.concat(" where ");
if (NumberUtils.isNumber(search))
hql = hql.concat(" u.salary ");
else
hql = hql.concat(" lower(u.username) ");
hql = hql.concat(" like '%").concat(search.toLowerCase()).concat("%'");
}
if (ordreTri.equals("asc")) {
switch (triIdx) {
case 0:
hql = hql.concat(" order by u.username ");
break;
case 1:
hql = hql.concat(" order by u.email ");
break;
case 2:
hql = hql.concat(" order by u.salary ");
break;
case 3:
hql = hql.concat(" order by d.dname ");
break;
default:
hql = hql.concat(" order by u.username ");
break;
}
} else {
switch (triIdx) {
case 0:
hql = hql.concat(" order by u.username desc");
break;
case 1:
hql = hql.concat(" order by u.email desc");
break;
case 2:
hql = hql.concat(" order by u.salary desc");
break;
case 3:
hql = hql.concat(" order by d.dname desc");
break;
default:
hql = hql.concat(" order by u.username desc");
break;
}
}
Query query = sessionFactory.getCurrentSession().createQuery(hql);
query = query.setFirstResult(start);
query = query.setMaxResults(length);
@SuppressWarnings("unchecked")
List<User> listUser = (List<User>) query.list();
return listUser;
}
dataTable :
...
"columns" : [
{"data" : "username"},
{"data" : "email"},
{"data" : "salary"},
{"data" : "dname"},
{"data" : "uid"}
],
...
I want to retrieve user columns and department name ; in standard SQL we can write : select e.first_name , e.salary, d.department_name from emp e join dept d on e.dept_id = d.dept_id
But how to write the join in Hibernate here ?
EDIT :
At runtime there is alert error : DataTables warning: table id=t_list - Requested unknown parameter 'username' for row 0, column 0. For more information about this error, please see http://datatables.net/tn/4
