How can I create simple count queries inside Repository with Micronaut Data JDBC?

Question

I'm trying to create a count native query inside a Micronaut Data JDBC repository. I'm using:

@JdbcRepository(dialect = Dialect.POSTGRES)
public abstract class BookRepository implements GenericRepository<Book, Long> {
    @Transactional(Transactional.TxType.MANDATORY)
    @Query(value = "select count(*) FROM book WHERE registration_date > :date", nativeQuery = true)
    public abstract long countNow(@NotNull Timestamp date);
}

And I get the following compilation error:

error: Unable to implement Repository method: BookRepository.countNow(Timestamp date). Query results in a type [my.app.db.Book] whilst method returns an incompatible type: long

How may I fix this ?

score 1 · Answer 1 · answered Oct 27 '19 at 12:27

I suspect that native queries must return the entity of that repository. In your case it is the Book.

But have you tried to use a query method instead of writing the query yourself. Something like

public abstract long countByRegistrationDateGreatherThan(Timestamp t);

For further explanation see the documentation of Micronaut Data.

score 0 · Answer 2 · answered May 30 '21 at 23:07

Here is another option with JdbcOperations:

fun getCount(whereClause: String): Int {
    @Language("SQL")
    val sql = """SELECT COUNT(*) AS rowcount FROM book $whereClause"""
    return jdbcOperations.prepareStatement(sql) { statement ->
        val resultSet = statement.executeQuery()
        resultSet.next()
        resultSet.getInt("rowcount")
    }
}

score 0 · Answer 3 · answered Jul 07 '21 at 18:48

Here's an example with CriteriaBuilder:

val criteriaBuilder = entityManager.criteriaBuilder
val criteriaQuery = criteriaBuilder.createQuery(Long::class.java)
val root = criteriaQuery.from(EntityClass::class.java)
criteriaQuery.select(criteriaBuilder.count(root.get<Int>("id")))

How can I create simple count queries inside Repository with Micronaut Data JDBC?

3 Answers3