Over a million developers have joined DZone.

Writing effective custom queries in Hibernate

· Big Data Zone

Learn how you can maximize big data in the cloud with Apache Hadoop. Download this eBook now. Brought to you in partnership with Hortonworks.

There are many instances where we will have to write custom queries with hibernate.

I always hated writing custom queries due to following reasons
  1. Hibernate returns List of Object arrays (List<Object>.)
  2. Lots of ugly mapping code.
  3. Every Object has to be casted.
  4. 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();
    }
  }

Hortonworks DataFlow is an integrated platform that makes data ingestion fast, easy, and secure. Download the white paper now.  Brought to you in partnership with Hortonworks

Topics:

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}