Writing effective custom queries in Hibernate
There are many instances where we will have to write custom queries with hibernate.
I always hated writing custom queries due to following reasons
- Hibernate returns List of Object arrays (List<Object>.)
- Lots of ugly mapping code.
- Every Object has to be casted.
- Maintain indexes in code, so if I add a new column, I have to ensure I use the right index.
and here is an example how the code generally looks like
public List<SalaryByDepartment> getSalaryByDepartment() {
Session session = HibernateUtil.getSessionFactory().openSession();
try {
List<Object> contactList = session.createQuery("select " +
"department.id, " +
"department.departmentName, " +
"sum(employee.salary) from Employee employee, Department department " +
"where employee.department.id = department.id group by department.id")
.list();
List<SalaryByDepartment> salaryByDepartments = new ArrayList<SalaryByDepartment>();
for (Object object : contactList) {
Object[] result = (Object[]) object;
SalaryByDepartment salaryByDepartment = new SalaryByDepartment();
salaryByDepartment.setDeptId((Integer) result[0]);
salaryByDepartment.setDepartmentName((String) result[1]);
salaryByDepartment.setSalary((Double) result[2]);
salaryByDepartments.add(salaryByDepartment);
}
return salaryByDepartments;
} catch (HibernateException e) {
e.printStackTrace();
return null;
} finally {
session.close();
}
}
There is a better mechanism for writing the custom queries called 'select new' but it is not widely used for some reason.
It might be an overkill to write all custom queries using this mechanism, but is good for queries which return more than 2 columns.
public List<SalaryByDepartment> getNewSalaryByDepartment() {
Session session = HibernateUtil.getSessionFactory().openSession();
try {
List<SalaryByDepartment> salaryByDept = session.createQuery("select " +
"new hsqldb.results.SalaryByDepartment(department.id, department.departmentName, sum(employee.salary)) " +
"from Employee employee, Department department " +
"where employee.department.id = department.id group by department.id")
.list();
return salaryByDept;
} catch (HibernateException e) {
e.printStackTrace();
return null;
} finally {
session.close();
}
}
Database
Hibernate
Opinions expressed by DZone contributors are their own.