Saturday 13 June 2015

JPA Queries containing count

A simple howto and reference regarding the usage of the count() function in your JPA code.

First thing is correct syntax. Counting the number of rows is not done using the syntax count(*) but rather count(entity). So a proper named query declaration would be something like :

@NamedQueries({
    @NamedQuery(
            name = "PixelStation.pixelStationExists",
            query = "SELECT COUNT(p) FROM PixelStation p WHERE p.pixel = :pixel AND p.station = :station"
    )
})

Next thing to remember is that the class returned by the query is the Java Long. So getting a value would be like:

        Long count = _entityManager.createNamedQuery("PixelStation.pixelStationExists", Long.class)
                .setParameter("station", station)
                .setParameter("pixel", pixel)
                .getSingleResult();

        // record exists no need to do anything
        if (count >= 1)
            return;

        // insert new record
        ...