Spring data JPA, hql query joining @ManyToMany relation in select request

Question

Income details:

Account table

@Entity
@Table(name = "account_table")
public class Account {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "account_seq_gen")
    @SequenceGenerator(name = "account_seq_gen", sequenceName = "ACCOUNT_SEQ")
    private Long id;
    @ManyToMany(cascade = CascadeType.REMOVE)
    @JoinTable(name = "account_calendar_relation",
            joinColumns = @JoinColumn(name = "account_id"),
            inverseJoinColumns = @JoinColumn(name = "calendar_id")
    )
    private List<Calendar> calendars = new ArrayList<>();

    @ManyToOne
    @JoinColumn(columnDefinition = "user_id")
    private User user;

//...
}

is related to Calendar

@Entity
@Table(name = "calendar")
public class Calendar {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "calendar_seq_gen")
    @SequenceGenerator(name = "calendar_seq_gen", sequenceName = "CALENDAR_SEQ")
    private Long id;
    @ManyToMany(mappedBy = "calendars")
    private List<Account> accounts;
//...
}

as manyToMany.

I need implement request to load light calendar collection from DB using spring data JPA repository by userId (userId is account field).

I tried:

@Query(value = "SELECT new com.nextiva.calendar.model.CalendarsCoreInfo(c.id, c.email, c.originalCalendar.id," +
        " c.readOnly, c.deleted)" +
        " FROM Calendar c " +
        " JOIN Account a ON c.accounts IN (a)" +
        " WHERE a.user.id = :userId")
Set<CalendarsCoreInfo> findCalendarsCoreInfoByUserId(@Param("userId") Long usedId);

but application did not start with exception:

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.Set com.nextiva.calendar.dao.jpa.CalendarRepository.findCalendarsCoreInfoByUserId(java.lang.Long)!

I tried apply native query:

@Query(value = "SELECT new com.nextiva.calendar.model.CalendarsCoreInfo(c.id, c.email, c.originalCalendar.id," +
        " c.readOnly, c.deleted)" +
        " FROM {h-schema}.calendar c" +
        " JOIN {h-schema}.account_calendar_relation atr ON atr.calendar_id = c.id" +
        " JOIN {h-schema}.account_table account ON account.id = atr.account_id" +
        " WHERE account.user_id = :userId", nativeQuery = true)
Set<CalendarsCoreInfo> findCalendarsCoreInfoByUserId(@Param("userId") Long usedId);

but native query doesn't support customized result (CalendarsCoreInfo in my case).

Question

As I know I can resolve this issue using jdbcTemplate or replacing ManyToMany entity mapping with OneToMany and ManyToOne. But I hope somebody knows elegant solution for current structure fixing HQL only.

Answer 1

My query part:

@NamedNativeQueries( {
        @NamedNativeQuery(name = "findCalendarsCoreInfoById",
                query = "SELECT c.id, c.email, c.original_id, c.read_only, c.deleted" +
                        " FROM {h-schema}calendar c" +
                        " JOIN {h-schema}account_calendar_relation atr ON atr.calendar_id = c.id" +
                        " JOIN {h-schema}account_table a ON a.id = atr.account_id" +
                        " WHERE a.user_id = ? ",
                resultSetMapping = "calendarsCoreInfoMapping"
        ),
})

with correspond mapper to target object for the query:

@SqlResultSetMappings( {
        @SqlResultSetMapping(
                name = "calendarsCoreInfoMapping",
                classes = {
                        @ConstructorResult(
                                targetClass = CalendarsCoreInfo.class,
                                columns = {
                                        @ColumnResult(name = "id", type = Long.class),
                                        @ColumnResult(name = "email", type = String.class),
                                        @ColumnResult(name = "original_id", type = String.class),
                                        @ColumnResult(name = "read_only"),
                                        @ColumnResult(name = "deleted")
                                }
                        )
                }
        ),
})

Both of its I've put to Calendar entity (some recommendations because of different behavior for different JPA vendors).

To make my repository working I declared Query specifying prepared query:

@Query(name = "findCalendarsCoreInfoById", nativeQuery = true)
Set<CalendarsCoreInfo> findCalendarsCoreInfoByUserId(Long userId);

Please pay attention on @Query. If method name is the same with query name and it marked like nativeQuery = true it is not enough to map method on query (this way haven't worked for me). It was fixed if I add name = "findCalendarsCoreInfoById" into @Query also.