0

I'd like to convert this SQL Statement into either Criteria or Projections. I'm sorry I don't know which one to use since I'm new to Hibernate. I've done some research, and it looks like both are needed to achieve what I wanted.

Before I always use SQL Statements in my programs but when I saw a sample code which allows you to create queries without writing SQL Statements by using Criteria, I wanted to use it since it gives me cleaner code.

Thanks in advance.

String query = "SELECT MAX(employeeNo) "
    + "FROM Profile "
    + "WHERE SUBSTRING(employeeNo,1,2)='" + yy + "'";

empNo = session.createQuery(query).list().get(0).toString();

This query gets the highest employee number in the database for a given year yy. Sample result would be 14-229

khakiout
  • 2,372
  • 25
  • 32
lxcky
  • 1,668
  • 2
  • 13
  • 26
  • What is the `SUBSTRING(employeeNo,1,2)` part trying to achieve? Its a fairly straightforward request, but I don't get that part. – JamesENL Aug 15 '14 at 04:07
  • I *think* the first two digits of the employeeNo represent the year. – Greg Case Aug 15 '14 at 04:22
  • I Greg Case is right, then this design is flawed. You should never store more than one information inside a single column (and the problem you have is directly related to that fact) –  Aug 15 '14 at 05:16
  • The `empNo` is generated based on the current year and the number of employees hired on that year. `yy-nnnn` I used the substring so that I will be able to know what would be the next `empNo`. – lxcky Aug 15 '14 at 05:31

1 Answers1

1

This is assuming that you've already defined a mapping for Profile entity.

Session session = sessionFactory.getCurrentSession();
String empNo = (String) session
     .createCriteria(Profile.class)
     .add(Restrictions.sqlRestriction("substring(employeeNo,1,2) = ?", yy, StringType.INSTANCE)
     .setProjection(Projections.max("employeeNo"))
     .uniqueResult();

Now, this I don't know if I'd agree with your statement that this is cleaner. The nice thing about using Criteria is the allow you to build a criteria dynamically, but it's very verbose, and it isn't clear exactly what you are trying to do at first glance. Plus, because of the substring function, forces you to mix in a sqlRestriction. Instead, you could use HQL:

Session session = sessionFactory.getCurrentSession();
String empNo = (String) session.createQuery("SELECT MAX(employeeNo) FROM Profile WHERE substring(employeeNo,1,2) = :year")
     .setString("year", yy)
     .uniqueResult();

Personally, I think the HQL or even SQL approach is a little cleaner, and expresses intent better.

Greg Case
  • 3,200
  • 1
  • 19
  • 17
  • Yes, I think in this case the SQL approach is easier to read so I'm going to stick with it. I guess I just had the wrong impression of having pure Java code to be better than mixing it with SQL Statements. Thanks anyways. :) – lxcky Aug 15 '14 at 09:08
  • You should use "sqlRestriction("substr(employeeNo,1,2) = ?" insted of "sqlRestriction("substring(employeeNo,1,2) = ?"" – E A Feb 15 '16 at 11:50