MySql ANY_VALUE() method with CRUD repository

Question

SQL mode ONLY_FULL_GROUP_BY is enabled by default. So instead of disabling ONLY_FULL_GROUP_BY I am trying to use ANY_VALUE(arg)

The query fetches me the expected result.

I want this to be used with Spring Boot CrudRepository. So I had it on the @Query annotation something like below (just a sample). I want to use @Query annotation with a complex query

@Query("select any_value(c.id), c.number, any_value(c.type) from Call c group by c.number")
public List<Call> getCallsByType(Pageable pageable);

But it throws an exception

Caused by: java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
 +-[METHOD_CALL] MethodNode: '('
 |  +-[METHOD_NAME] IdentNode: 'any_value' {originalText=any_value}
 |  \-[EXPR_LIST] SqlNode: 'exprList'

How can it be done?

Did you try set [nativeQuery](https://stackoverflow.com/a/15949012/5594670) to true? — Javvano, Jul 18 '17 at 13:28
@Javvano Yes that works by setting nativeQuery to true. Thank you — Rajagopal, Jul 18 '17 at 13:42
`nativeQuery` absolutely defeats the purpose of JPA ... plus : native queries are the root of all evil. Do not use them. Ever. — specializt, Jul 18 '17 at 14:21

score 1 · Accepted Answer · answered Jul 18 '17 at 13:35

You have tell Spring to treat that query as native one. Otherwise it will try to validate it according to the JPA specification.

Try:

@Query(value = "select any_value(c.id), c.number, any_value(c.type) from Call c group by c.number"
       , nativeQuery = true)
public List<Object[]> getCallsByType(Pageable pageable);

Keep in mind that you cannot use the NEW operator syntax in this case and you would have to deal with result as an array of Object.

Alternatively

If you want to use map the results directly to a POJO class you would have to (assuming that you are using JPA 2.1+):

1) Define the mapping:

@SqlResultSetMapping(
    name="resultMapping",
    classes={
        @ConstructorResult(
            targetClass=ResultClass.class,
            columns={
                @ColumnResult(name="id"),
                @ColumnResult(name="number")
                // further mappings ...
            }
        )
    }
)

2) Define a native query

@NamedNativeQuery(name="getCallsByType"
    , query="select any_value(c.id), c.number, any_value(c.type) from Call c group by c.numbe")

3) Define this method in the CrudRepository without the @Query annotation:

public List<ResultClass> getCallsByType();

MySql ANY_VALUE() method with CRUD repository

1 Answers1