Writing effective custom queries in Hibernate

Amar Mattey

Apr. 21, 14 · Interview

Likes (0)

Comment

Save

26.7K Views

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.

Related

Trending

Writing effective custom queries in Hibernate

Related

Partner Resources