I need to convert this query to hql to use it my code :
SELECT DISTINCT
certificate_id , last_scan
FROM
bcs_certificate_instance
ORDER BY
last_scan
LIMIT 5 ;
Who can help please?
Asked
Active
Viewed 189 times
1
-
It should work as it is in ```hql```. Have you tried just running it in ```hive```? – Grzegorz Skibinski Dec 26 '19 at 08:14
-
1No it won't. There is not LIMIT keyword in hql. – Osama Khalid Dec 26 '19 at 08:25
-
1Sorry, you're right I kind of assumed ```hql``` = ```hive querying language```. Which apparently is not the only way this acronym is used. – Grzegorz Skibinski Dec 26 '19 at 08:34
-
Check this out [How do you do a limit query in HQL?](https://stackoverflow.com/questions/1239723/how-do-you-do-a-limit-query-in-hql) – Arun Palanisamy Dec 26 '19 at 08:36
-
I agreed with @OsamaKhalid Hibernate does not support the LIMIT keyword because that's only recognized by MySQL and PostgreSQL. For handling limit please use`setFirstResult` `setMaxResults` – Naresh Kumar Dec 26 '19 at 09:41
1 Answers
1
select distinct bci.certificate_id , bci.last_scan
from bcs_certificate_instance bci
order by last_scan asc;
And for limit you have to use setMaxResults() funtion on query object before executing it.
A code snippet for this would look like:
Query query = em.createQuery(“select distinct bci.certificate_id , bci.last_scan
from bcs_certificate_instance bci
order by last_scan asc;”);
query.setMaxResults(10);
List resultData = query.getResultList();
Thats it. So that is how you limit the number of records returned by a query in HQL.
Example with @Query annotation:
public interface PersonDao extends EntityDao<Person, Long> {
@Query("select p from Person p where p.age between ?1 and ?2")
QueryResult<Person> findAllByAge(int minAge, int maxAge);
}
Once you have obtained a QueryResult, you can apply further options and restrictions to the query:
List<Person> result = personDao.findAllByAge(18, 65)
.sortAsc(Person_.lastName)
.sortDesc(Person_.age).
.firstResult(10)
.maxResults(10)
.getResultList();
}
Osama Khalid
- 307
- 4
- 14
-
I can't use the set max result option because i am using the @Query annotation .. so i have to set the max value in the query – Hana_CH Dec 26 '19 at 10:24
-
I have added another example with @Query annotation. See if this is helpful to you. Thanks – Osama Khalid Dec 26 '19 at 16:08