How do I use @Query in a Micronaut Data repository to do an aggregation query where the results have more than one column?

Question

I completed this tutorial: https://guides.micronaut.io/latest/micronaut-data-jdbc-repository-gradle-java.html

I then added code to the completed, working tutorial to try to do a custom SQL query for a use case involving reading the data with a GROUP BY clause. My code then looked like this: https://github.com/mattwelke/micronaut-jdbc-guide-complete/tree/rec-engine (note the branch name rec-engine)

When I run the script ./scripts/start_mysql.sh and then run curl http://localhost:8080/views/popular in another terminal while the application runs, I get the following error returned (and in the logs):

{
   "message":"Internal Server Error",
   "_embedded":{
      "errors":[
         {
            "message":"Internal Server Error: Cannot convert type [class java.lang.String] with value [def456] to target type: class example.micronaut.dtos.PopularProductResult. Consider defining a TypeConverter bean to handle this case."
         }
      ]
   },
   "_links":{
      "self":{
         "href":"/views/popular",
         "templated":false
      }
   }
}

I was under the impression that Micronaut Data would take care of this mapping. But I also understand if Micronaut Data isn't capable of doing this, and I'm required to do it myself. however, when I try to do it myself, I'm confused about how to follow the instructions to create some code that would map the query results to my class. The error describes mapping a String to PopularProductResult (my class). I don't want to map a string to that class. I want to map a string and a number to that class, since it represents an aggregation result.

How do I do this?

The GitHub repo shows the complete code, but for easy reference here, here's my domain model, repository (including the query in the @Query) and my custom class for my query results:

package example.micronaut.domain;

import io.micronaut.data.annotation.GeneratedValue;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;

import javax.validation.constraints.NotNull;

@MappedEntity
public class View {

    @Id
    @GeneratedValue(GeneratedValue.Type.AUTO)
    private Long id;

    @NotNull
    private String productId;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getProductId() {
        return productId;
    }

    public void setProductId(String productId) {
        this.productId = productId;
    }

    @Override
    public String toString() {
        return "View{" +
                "id=" + id +
                ", productId='" + productId + '\'' +
                '}';
    }
}

package example.micronaut;

import example.micronaut.domain.View;
import example.micronaut.dtos.PopularProductResult;
import io.micronaut.core.annotation.NonNull;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.Query;
import io.micronaut.data.exceptions.DataAccessException;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.PageableRepository;

import java.util.List;

import javax.transaction.Transactional;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;

@JdbcRepository(dialect = Dialect.MYSQL) 
public interface ViewRepository extends PageableRepository<View, Long> { 

    View save(@NonNull @NotBlank String productId);

    @Transactional
    default View saveWithException(@NonNull @NotBlank String productId) {
        save(productId);
        throw new DataAccessException("test exception");
    }

    long update(@NonNull @NotNull @Id Long id, @NonNull @NotBlank String productId);

    @Query("SELECT product_id AS productId, COUNT(*) AS count FROM `view` GROUP BY productId ORDER BY count DESC")
    List<PopularProductResult> popularViews();
}

package example.micronaut.dtos;

public record PopularProductResult(String productId, Long count) {
}

Matt Welke · Accepted Answer · 2022-05-19T21:37:37.333

My problem was that I didn't have the @Introspected annotation on my class representing query results. Being somewhat new to Java and very new to Micronaut, I wasn't aware of some of the requirements Micronaut has, like how you have to do things a certain way so that it does its work at compile time. Bean introspection is a part of that.

So I changed my query result class (which I'm actually using a record for) to:

package example.micronaut.dtos;

import io.micronaut.core.annotation.Introspected;

@Introspected
public record PopularProductResult(
        String productId,
        Long count) {
}

At this point, I had a different error:

Internal Server Error: Error reading object for name [product_id] from result set: Column 'product_id' not found.

It was caused by my query used with @Query:

@Query("SELECT product_id AS productId, COUNT(*) AS count FROM `view` GROUP BY productId ORDER BY count DESC")
List<PopularProductResult> popularViews();

I was under the impression my query result column names should match the fields defined in my Java class. Instead, Micronaut was doing its own name mapping where it was looking for a column name called product_id in my result set because my field name was productId. This behaviour is described in the Micronaut Data documentation at https://micronaut-projects.github.io/micronaut-data/latest/guide/#sqlNaming.

So I changed my query to:

@Query("SELECT product_id, COUNT(*) AS count FROM `view` GROUP BY product_id ORDER BY count DESC")
List<PopularProductResult> popularViews();

And I got the results I was looking for, without errors:

[
  {
    "productId": "abc123",
    "count": 85
  },
  {
    "productId": "ghi789",
    "count": 81
  },
  {
    "productId": "def456",
    "count": 78
  }
]

How do I use @Query in a Micronaut Data repository to do an aggregation query where the results have more than one column?

1 Answers1