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

Effortlessly power IoT, predictive analytics, and machine learning applications with an elastic, resilient data infrastructure. Learn how with Mesosphere DC/OS.

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();
    }
  }

Learn to design and build better data-rich applications with this free eBook from O’Reilly. Brought to you by Mesosphere DC/OS.

Topics:

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

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

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}