0

I have a loop that executes a query N times through a DAO. In my case, I have an object with a property that has the value "123456789". In the first turn, it executes a query on a particular table to get all the records which have the column C1 equal to "123456789".

In the second turn, it executes a query on this same table to get all the records which have the column C1 equal to "12345678". And so on until it finds the record with the longest prefix.

I thought that instead of executing this query multiple times, why not to execute another query only once, which takes as parameter the string "123456789" and returns the record which has the column C1 which is the longest prefix of "123456789". But I don't know how to do that with Hibernate (if possible), or if you have another solution. I'm using MySQL.

(I've looked at this post but I wanted to know if there is a way to do that with Hibernate.)

Community
  • 1
  • 1
manash
  • 6,985
  • 12
  • 65
  • 125

1 Answers1

2

You can execute an sql like:

select * from table where 1234567 like concat(c1,'%') order by c1 desc limit 1;

Basically you will take the longest value returned from your SQL that matches the given parameter. If you want you can use the setMaxResults instead the limit and operator || to concat the columns, something like:

...
Query query = session.createQuery("from Prefixes where :parameter like Prefixes.prefix || '%' order by Prefixes.prefix desc");
query.setParameter("parameter", 1234567);
query.setMaxResults(1);
Prefixes p = (Prefixes)query.uniqueResult();
...
Francisco Spaeth
  • 23,493
  • 7
  • 67
  • 106
  • Thanks for the answer, it works! But, I have two questions : first, what is the difference between the use of concat() and ||? Also, is this HQL statement portable between different DBMS? – manash Oct 25 '11 at 09:20
  • As far as I know, || is hibernate, that means will be translated to each database with their own function. concat will be passed through and will work for a specific database. – Francisco Spaeth Oct 26 '11 at 09:08