I need to implement SELECT with a many-to-many relationship in @QUERY. Perhaps I am misinterpreting the information written in the documentation. My query looks like this:

@Query("select massages.id from massages join string_massage on massages.id = string_massage.massage_id where string_massage.string_id = ?1")
List<MasageEntity>  findMassagesIdByStringId(@Param("strings_id") long strings_id);

In my example, I use table names. The names are underlined as an error (without compilation). Maybe I should use Entities. Then how do I do it with many-to-many relationship?

I will show a part of my Entities.

I have two Entities. MasageEntity and RstringEntity.

//MasageEntity
@Entity
@Table(name = "massages")
public class MasageEntity {
    @Id
    @GeneratedValue (strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "string_text")
    private String string_text;
    @Column(name = "string_speed")
    private Long string_speed;
    @Column(name = "string_color_type")
    private Long string_color_type;
    @Column(name = "string_color")
    private String string_color;
    @Column(name = "string_timing_type")
    private String string_timing_type;
    @Column(name = "string_timing")
    private String string_timing;
    @Column(name = "showed")
    private Long showed;

    

    @ManyToMany(fetch = FetchType.LAZY,
            cascade = {
                    CascadeType.PERSIST,
                    CascadeType.MERGE
            })
    @JoinTable(name = "string_massage",
            joinColumns = { @JoinColumn(name = "massage_id") },
            inverseJoinColumns = { @JoinColumn(name = "string_id") })

//RstringEntity
@Entity
@Table(name = "string")
public class RstringsEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "name")
    private String name;
    @Column(name="code")
    private String code;


    @ManyToMany(fetch = FetchType.LAZY,
            cascade = {
                    CascadeType.PERSIST,
                    CascadeType.MERGE
            }, mappedBy = "strings")
    @JsonIgnore
    private Set<MasageEntity> masagess = new HashSet<>();
    public  RstringsEntity() {}

CodePudding user response：

There are multiple ways how to perform queries in Spring Boot: native SQL and JPQL.
In case of native queries we are using pure SQL language, defining query on DB level.
In case of Java Persistence Query Language (JPQL) we define query via entity objects.

Solution 1, native queries
You created the native query in repository, but to use it we need mark it like SQL nativeQuery = true. It required for framework to understang what query laguage do you use. @Query annotation use JPQL by defult, so that's the reason of your errors.

@Repository
public interface MessageRepository extends JpaRepository<MassageEntity, Long> {
    //find MessageEntities by String ID via native query 
    @Query(value = "select massages.* from massages join string_massage on massages.id = string_massage.massage_id where string_massage.string_id = ?1", nativeQuery = true)
    List<MassageEntity>  findMassagesByStringIdNativeSQL(@Param("strings_id") long strings_id);

    //find Message IDs by String ID via native query
    @Query(value = "select massages.id from massages join string_massage on massages.id = string_massage.massage_id where string_massage.string_id = ?1", nativeQuery = true)
    List<Long>  findMassagesIdByStringIdNativeSQL(@Param("strings_id") long strings_id);
}

Solution 2, JPQL queries
Example how to define JPQL queries for your case. JPQL will be translated to SQL during execution.

@Repository
public interface MessageRepository extends JpaRepository<MassageEntity, Long> {

    //find MessageEntities by String ID via JPQL
    @Query("select message from MassageEntity message join message.strings string where string.id = :strings_id")
    List<MassageEntity>  findMassagesByStringIdJPQL(@Param("strings_id") long strings_id);

    //find Message IDs by String ID via JPQL
    @Query("select message.id from MassageEntity message join message.strings string where string.id = :strings_id")
    List<Long>  findMassagesIDByStringIdJPQL(@Param("strings_id") long strings_id);
}

Native query generated by Hibernate:

    select
        massageent0_.id as id1_3_,
        massageent0_.string_text as string_t2_3_ 
    from
        massages massageent0_ 
    inner join
        string_massage strings1_ 
            on massageent0_.id=strings1_.massage_id 
    inner join
        string rstringsen2_ 
            on strings1_.string_id=rstringsen2_.id 
    where
        rstringsen2_.id=?

Solution 3, Spring auto-generated queries
Spring can auto-generate queries by repository method definition.
Example for your case:

@Repository
public interface MessageRepository extends JpaRepository<MassageEntity, Long> {

    //find MessageEntities by String ID
    List<MassageEntity> findByStrings_Id(@Param("id") long strings_id);
}

Entries which I used fro sulutions:

@Entity
@Table(name = "massages")
public class MassageEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "string_text")
    private String string_text;

    @ManyToMany(fetch = FetchType.LAZY,
            cascade = {
                    CascadeType.PERSIST,
                    CascadeType.MERGE
            })
    @JoinTable(name = "string_massage",
            joinColumns = { @JoinColumn(name = "massage_id") },
            inverseJoinColumns = { @JoinColumn(name = "string_id") })
    private Set<RstringsEntity> strings = new HashSet<>();
}

@Entity
@Table(name = "string")
public class RstringsEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "name")
    private String name;
    @Column(name = "code")
    private String code;


    @ManyToMany(fetch = FetchType.LAZY,
            cascade = {
                    CascadeType.PERSIST,
                    CascadeType.MERGE
            }, mappedBy = "strings")
    @JsonIgnore
    private Set<MassageEntity> massages = new HashSet<>();
}