Get min and max column values with single query

Question

How to get min and max value by using jpa and not using native query?

Result must be fetched with single transaction.

Relative sql query:

SELECT min(price), max(price) FROM product

I tried using this code

criteria.setProjection(Projections.min("price"));
Integer min = (Integer) criteria.uniqueResult();
...
criteria.setProjection(Projections.max("price"));
Integer max = (Integer) criteria.uniqueResult();

but this seems to be too odd to execute it twice.

Answer 1

Well you need to use a ProjectionList with your Criteria.

Your code would look like this:

criteria.setProjection(
     Projections.projectionList()
    .add(Projections.min("price"))
    .add(Projections.max("price"))
);
Object[] minMax = criteria.uniqueResult();
Integer min = (Integer) minMax[0];
Integer max = (Integer) minMax[1];

Another option is to use HQL, with min and max Aggregate functions:

Query q = session.createQuery("select min(prd.price), max(prd.price) from Product prd");
Object[] minMax = q.getSingleResult();
Integer min = (Integer) minMax[0];
Integer max = (Integer) minMax[1];