Unable to use rowCount() in Hibernate where-criteria

Question

I'm trying to create a simple query in Grails using Hibernate. I want to return the number of elements in each status. The query in sql looks like this:

select status, count(*) from UDTRAEKDATA
where UDTRAEK_JOB_ID='41'
group by status;

which produces the following table

STATUS	COUNT(*)
FAERDIG	12023
FEJL	434

The domain class is defined as follows:

class Udtraekdata {

    String jsonData
    UdtraekdataStatus status
    UdtraekEntitetType entitetType
    Date lastUpdated
    int antalLaasNulstillinger

    static hasOne = [udtraekdatafil: Udtraekdatafil, udtraekJob: UdtraekJob]

    static constraints = {
        udtraekdatafil nullable: true
        jsonData nullable: true
        faerdigbehandlet nullable: true
    }

    static mapping = {
        status index: 'idx_udtraekdata'
        udtraekJob index: 'idx_udtraekdata'
        udtraekdatafil index: 'Udtraekdata_fil_idx'
        id generator: 'sequence', params:[sequence: 'udtraekdata_sequence']
    }

}

I believe I should be able to create the corresponding query in Hibernate like this (Done here f.ex. grails 3.3 gorm where query with projection count() different than list().size()):

    Udtraekdata.where {
            udtraekJob.jobId == "job1"

            projections {
                groupProperty 'status'
                rowCount()
            }
        }.list()

However this resulst in a GroovyCastException with the message:

"Cannot cast object '10' with class 'java.lang.Integer' to class 'java.util.List'"

What am I missing? I want to do this lazily, as there will be millions of rows and I don't ever want to fetch them into memory.

Answer 1

I finally found my error. Apparently Spock or Grails has historically had problems with Hibernate in unit tests and one has been forced to use integration tests. This still seems to be a problem (even if I'm using Grails 4) when it comes to counting rows in unit test. The solution for me was thus simply to setup an integration test instead.

My final service method:

UdtraekStatusInfo hentUdtraekStatus(Long udtraekId) {
        Map<UdtraekdataStatus, Integer> statusMap = Udtraekdata.where {
            udtraekJob.id == udtraekId

            projections {
                groupProperty "status"
                rowCount()
            }
        }.list().collectEntries { row ->
            return [row[0], row[1]]
        }

        return new UdtraekStatusInfo(udtraekId, statusMap)
    }

My final test class:

@Integration
@Rollback
class UdtraekStatusServiceIntSpec extends Specification {

    UdtraekStatusService udtraekStatusService

    def "hentUdtraekStatus"() {
        given:
            UdtraekJob job1 = DomainTestHelper.getUdtraekJob().save(flush: true)
            UdtraekJob job2 = DomainTestHelper.getUdtraekJob().save(flush: true)

            [job1, job2].each { job ->
                UdtraekdataStatus.values().eachWithIndex { status, index ->
                    (0..index).each {
                        DomainTestHelper.getCompleteUdtraekData(udtraekJob: job, status: status).save(flush: true)
                    }
                }
            }
        when:
            UdtraekStatusInfo info = udtraekStatusService.hentUdtraekStatus(job1.id)
        then:
            UdtraekJob.count == 2
            Udtraekdata.count == 30
            // More test statements...
    }
}