Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Writing effective custom queries in Hibernate

DZone's Guide to

Writing effective custom queries in Hibernate

· Big Data Zone ·
Free Resource

Hortonworks Sandbox for HDP and HDF is your chance to get started on learning, developing, testing and trying out new features. Each download comes preconfigured with interactive tutorials, sample data and developments from the Apache community.

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 Community Connection (HCC) is an online collaboration destination for developers, DevOps, customers and partners to get answers to questions, collaborate on technical articles and share code examples from GitHub.  Join the discussion.

Topics:

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}