7

I would like to get first row from database using JPA Criteria. I use JPA, Hibernate 4.2.7. In SQL, statement looks like:

SELECT * FROM houses WHERE rownum = 1;

My Java code to achive that looks like:

    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<House> query = builder.createQuery(House.class);
    Root<House> root = query.from(House.class);

    query.select(root).where(builder.equal(root.get("rownum"), 1));

    TypedQuery<House> tQuery = entityManager.createQuery(query); 
    House house = tQuery.getSingleResult();

But 'rownum' pseudocolumn cannot be resolved, I get exception:

 java.lang.IllegalArgumentException: Unable to resolve attribute [rownum] against path
at org.hibernate.ejb.criteria.path.AbstractPathImpl.unknownAttribute(AbstractPathImpl.java:120)
at org.hibernate.ejb.criteria.path.AbstractPathImpl.locateAttribute(AbstractPathImpl.java:229)
at org.hibernate.ejb.criteria.path.AbstractPathImpl.get(AbstractPathImpl.java:200)

Is is possible, and if so, how to get 'rownum' pseudocolumn with Criteria API? Thanks for any suggestions.

gadon
  • 253
  • 2
  • 7
  • 16

2 Answers2

11

You can accomplish this using setFirstResult and setMaxResults.

session.createCriteria(Foo.class)
   .setFirstResult(0)
   .setMaxResults(1);
bradleyfitz
  • 686
  • 4
  • 8
  • This is the beauty of JPA, doesn't matter how the database manages the limits, for JPA is always the same sintaxis – rekiem87 Jan 03 '14 at 20:41
  • For my example with entity manager it will be: tQuery.setFirstResult(0); tQuery.setMaxResults(1); Thanks for answer! – gadon Jan 03 '14 at 22:24
  • Does JPA create efficient queries using db specific dialect (rownum for Oracle, TOP/LIMIT for other supported DBs) ? – Amit Parashar Jul 15 '14 at 19:08
  • If the query is like this, then I think we cannt use the setMaxResults. Ex : Select 1 from Table where col1= 'someValue' and ROWNUM <2. – Alagammal P Aug 17 '18 at 15:10
4

you can set rownum or limits in property of query;

Query q =entityManager.createQuery("select * from employee_table");

q.setFirstResult(0);

q.setMaxResults(30);

djavaphp
  • 68
  • 7