How to get first group of results in sorted results in hibernate?

Question

I have following Author entity. I wanted to print first group of (authors grouped by their age in descending order).

Eg,

Author

id name age

1   JK   20

2  NRN  25

3  APJ  35

4  RK  20

5  NRN  25

6 JK  20

7  RK  20

After sorting (ASC) it will be:

1  JK  20

4  RK  20

6 JK  20

7  RK  20

2  NRN  25

3  APJ  35

5  NRN  25

I wanted to have first set of grouping as the result, i.e,

I tried with following query but it returns all the results.

String hql = "FROM Author A ORDER BY A.age ASC";
Query query = session.createQuery(hql);
List results = query.list();

Please let me know what could be modified in the query.

@Entity
public class Author {

@Id
private int id;

private String name;

private int age;

public Author() {
}

public Author(int id, String name, int age) {
    super();
    this.setId(id);
    this.setName(name);
    this.setAge(age);
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public int getAge() {
    return age;
}

public void setAge(int age) {
    this.age = age;
}

@Override
public String toString() {
    return "Author [id=" + id + ", name=" + name + ", age=" + age + "]";
}

}

Thanks.

Have a look at setMaxResult This post solves your problem https://stackoverflow.com/questions/1239723/how-do-you-do-a-limit-query-in-hql — Diversity, May 17 '18 at 11:17
But in my case, I am not sure of count of first group of results... — Omkar Shetkar, May 17 '18 at 11:31

Robert Niestroj · Answer 1 · 2018-05-17T18:57:58.087

1

I assume you want the people with the lowest age. Try it with a simple subquery

SELECT a FROM Author a WHERE a.age = (SELECT min(a.age) FROM Author a)

edited May 17 '18 at 18:57

answered May 17 '18 at 11:55

Robert Niestroj

15,299
14
76
119

Diversity · Answer 2 · 2018-05-17T12:46:09.973

If i understand you correct than a group is a range between ascending ages.

E.g Group 1 Age 20 - 30 Group 2 Age 30 - 40 a.s.o

In this case you can query the amount of authors belonging to one group in the following way.

   String hql = "SELECT count(A.id) FROM Author A WHERE
                "A.age >= 20 AND A.age < 30 ORDER BY A.age ASC";

The result will be the amount of authors in group 1. To query the complete author set belonging to group one you can use this statement.

String hql = "FROM Author A WHERE
                "A.age >= 20 AND A.age < 30 ORDER BY A.age ASC";

Now you know how many authors belong to group one an can use your query with setFirstResult and setMaxResult.

The grouping can also be done using named parameters within the HQL-Statement

int minAge = 20;
int maxAge = 30;

String hql = "SELECT count(A.id) FROM Author A WHERE
                "A.age >= :minAge AND A.age < :maxAge ORDER BY A.age ASC";

You have to assign the values of the named parameters to the query instance. Have look here: http://www.java2s.com/Code/Java/Hibernate/HQLwithNamedParameters.htm

Now you have the possibility to loop over your queries. For setting the range of the resultset please refer to How do you do a limit query in HQL?

Thanks for the detailed answer. Actually, I needed first set of results (of equal values) after sorting but not the range of values. — Omkar Shetkar, May 17 '18 at 13:05

score 1 · Accepted Answer · answered May 17 '18 at 12:42

1

Once try this code, it may help

 List<Author> ages = session
                .createNativeQuery("SELECT * FROM Author a WHERE a.age = (SELECT min(a.age) FROM Author a)")
                .addEntity(Author.class).getResultList();

answered May 17 '18 at 12:42

jangachary sriramadasu

138
8

score 0 · Answer 4 · answered May 17 '18 at 11:16

0

use group by,

String hql = "FROM Author A  GROUP BY A.age ORDER BY A.age ASC ";
Query query = session.createQuery(hql);
List results = query.list();

answered May 17 '18 at 11:16

Balasubramanian

700
7
26

With this query, I get exception as "org.h2.jdbc.JdbcSQLException: Column "AUTHOR0_.ID" must be in the GROUP BY list; SQL statement:" – Omkar Shetkar May 17 '18 at 11:32

How to get first group of results in sorted results in hibernate?

4 Answers4