ScrollableResults size gives repeated value

Question

I am working on application using hibernate and spring. I am trying to get count of result got by query by using ScrollableResults but as query contains lots of join(Inner joins), the result contains id repeated many times. this creates problem for ScrollableResults when i am using it to know total no of unique rows(or unique ids) returned from database. please help. Some part of code is below :

        StringBuffer queryBuf = new StringBuffer("Some SQL query with lots of Joins");

        Query query = getSession().createSQLQuery(queryBuf.toString());

        query.setReadOnly(true);


        ScrollableResults results = query.scroll();
        if (results.isLast() == false)
            results.last();
        int total = results.getRowNumber() + 1;

        logger.debug(">>>>>>TOTAL COUNT<<<<<< = {}", total);

It gives total count 1440 but actual unique rows in database is 504. Thanks in Advance.

score 1 · Accepted Answer · answered Jul 26 '13 at 08:38

1

You can try

Integer count= ((Long)query.uniqueResult()).intValue();

answered Jul 26 '13 at 08:38

Marginean Vlad

329
1
6

1

I tried the same, but it takes very long. It seems to me that uniqueResult() goes through all the records, in which case I gain nothing from using ScrollableResults... – IntelliData Jun 21 '16 at 15:06

score 1 · Answer 2 · edited Nov 12 '20 at 06:05

1

getRowNumber() gives the number of the current row. Call last() and afterwards getRowNumber()+1 will give the total number of results.

edited Nov 12 '20 at 06:05

tgallei

827
3
13
22

answered Nov 11 '20 at 14:53

Pilso

61
3

Andreas Retter · Answer 3 · 2020-04-22T10:59:40.637

0

Unfortunately, getRowNumber does not give you the size, or the number of results, but the current position in the results. ScrollableResults does not provide a way to get the number of results out-of-the-box.

I am referring to ScrollableResults Hibernate Version 5.4.

As a workaround, you can try

Long l_resultsCount = 0L;
while(results.next()) {
    l_resultsCount++;
}

edited Apr 22 '20 at 10:59

answered Apr 22 '20 at 10:54

Andreas Retter

1
1

ScrollableResults size gives repeated value

3 Answers3