How to convert this mysql query to JPA Query?

[Castiel Snow]

How to convert this mysql query to JPA Query?
I tried following an example and I ended up with the query below.

SELECT * FROM informativos
    INNER JOIN informativos_usuarios
    ON informativos_usuarios.user_id = user.id
    AND
    informativos_usuarios.informativo_id = i.id;

    @Query("SELECT i FROM informativos i INNER JOIN informativos_usuarios u ON u.user_id = %1 AND u.informativo_id = i.id")
    public List<Informativo> findAll(User user);

Additional information:

@Entity
@Table(name = "informativos")
public class Informativo {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @NotNull
    @Column(nullable = false)
    private String titulo;

    @Column(nullable = true, unique = false, columnDefinition = "TEXT")
    private String numeroContrato;

    @Column(nullable = true, unique = false, columnDefinition = "TEXT")
    private String nomeEmpresa;

    @Column(name = "observacoes", nullable = true, unique = false, columnDefinition = "TEXT")
    private String observacoes = "";

    @Column(name = "data_ultima_alteracao")
    private ZonedDateTime dataUltimaAlteracao;

    @Fetch(value = FetchMode.SELECT)
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
            name = "informativos_usuarios",
            joinColumns = @JoinColumn(name = "informativo_id"),
            inverseJoinColumns = @JoinColumn(name = "user_id")
    )
    private List<User> usuarios = new ArrayList<>();

@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

....

[Tim Holloway]

Well, one of the big advantages of JPA over raw SQL/JDBC is that you don't code JOIN statement in JPQL.

JPA is based on Entity classes, so if you define informativos  as having a relation such as @OneToMany to informativos_usuarios, then a simple

SELECT i FROM informativos

is all the JPQL you need. Depending on whether you've defined the relation as LAZY_FETCH or EAGER_FETCH, the informativos_usuario(s) linked to the informativo in question  will be immediately fetched as well.

[Castiel Snow]

Tim Holloway wrote:Well, one of the big advantages of JPA over raw SQL/JDBC is that you don't code JOIN statement in JPQL.

JPA is based on Entity classes, so if you define informativos  as having a relation such as @OneToMany to informativos_usuarios, then a simple

SELECT i FROM informativos

is all the JPQL you need. Depending on whether you've defined the relation as LAZY_FETCH or EAGER_FETCH, the informativos_usuario(s) linked to the informativo in question  will be immediately fetched as well.

Hi, Thanks for the reply.
I understand however, I need to get a list of informativos ,"i", if i contains user u;

[Campbell Ritchie]

Please don't add new information to old posts which have been replied to; it is likely that the new text will not be noticed.

[Castiel Snow]

Campbell Ritchie wrote:Please don't add new information to old posts which have been replied to; it is likely that the new text will not be noticed.

Sorry. I thought would be considered spam. Wont happen again.

[Tim Holloway]

Your list of Users in informativos is not annotated as a related Entity with @OneToMany or @ManyToMany.

And while you can query primarily on informativos, you would find it simpler for User to back-reference to informativos and select from User.

[Tim Holloway]

Sorry. I'm blind. So many annotations stacked up I didn't see what they were annotating!

You do need a reverse @ManyToMany back from User, though. As I said, it's much simpler to retrieve a User and read its list of informativos.