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

user_12280817 0
Viewed 710

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 ?

micronaut-data micronaut
3 Answers

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.

user_9608750

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")
    }
}
user_14309910

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")))
user_61066410
Related