I'm using JPA class-based projection for constructing a DTO. The DTO represents an article. It has a title field and an isWrittenByMe field. The isWrittenByMe field should hold true when the parameter author is one of the article's authors and false otherwise.

I made a JPA repository and added a method that executes a custom JPQL query.

   @Query("SELECT new com.example.learnjpql.dto.ArticleDTO(a.title, :author MEMBER OF a.authors) FROM Article a")
    List<ArticleDTO> getAllArticles(Author author);

Hibernate generated this query

Hibernate: select article0_.title as col_0_0_, ? in (authors1_.author_id) as col_1_0_ from article article0_ cross join article_authors2 authors1_ where article0_.id=authors1_.article_id

And this is the output of getAllArticles.

[ArticleDTO[title=Article 1, isAuthoredByMe=true], ArticleDTO[title=Article 1, isAuthoredByMe=false], ArticleDTO[title=Article 2, isAuthoredByMe=true], ArticleDTO[title=Article 3, isAuthoredByMe=false], ArticleDTO[title=Article 3, isAuthoredByMe=false], ArticleDTO[title=Article 4, isAuthoredByMe=false], ArticleDTO[title=Article 4, isAuthoredByMe=false], ArticleDTO[title=Article 5, isAuthoredByMe=false]]

It's outputting all entries in the join table, but I want the entries in the Article table

For instance, if the parameter author has written article 1 and article 3, I want a query that returns something like this:

I thought maybe instead of passing :author MEMBER OF a.authors to the constructor, I can pass a SELECT statement and that would run a seperate query for each entry, but I got an unexpected token error.

Please let me know if I'm missing something or using the wrong tool.

@Entity
@NoArgsConstructor
@AllArgsConstructor
@Getter
public class Author {
    @Id
    private Long id;

    private String firstname;

    private String lastname;

    @ManyToMany(fetch = FetchType.EAGER, mappedBy = "authors")
    private Set<Article> articles;

    @Override
    public boolean equals(Object o) {
        if (this == o)
            return true;
        if (!(o instanceof Author author))
            return false;
        return getId().equals(author.getId()) && getFirstname().equals(author.getFirstname()) && getLastname().equals(author.getLastname());
    }

    @Override
    public int hashCode() {
        return Objects.hash(getId(), getFirstname(), getLastname());
    }

    public String toString() {
        return "Author(id="   this.getId()   ", firstname="   this.getFirstname()   ", lastname="   this.getLastname();
    }
}

@Entity
@NoArgsConstructor
@AllArgsConstructor
@Getter
@ToString
public class Article {

    @Id
    private Long id;

    private String title;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "article_authors2",
            joinColumns = { @JoinColumn(name = "article_id") },
            inverseJoinColumns = { @JoinColumn(name = "author_id") })
    private Set<Author> authors;

    @Override
    public boolean equals(Object o) {
        if (this == o)
            return true;
        if (!(o instanceof Article article))
            return false;
        return getId().equals(article.getId()) && getTitle().equals(article.getTitle());
    }

    @Override
    public int hashCode() {
        return Objects.hash(getId(), getTitle());
    }
}

public record ArticleDTO(
        String title,
        boolean isAuthoredByMe
) { }

CodePudding user response：

Before Hibernate 6, predicates weren't usable as expressions, so you'd have to wrap your predicate into case when ... then true else false end i.e. your query becomes:

@Query("SELECT new com.example.learnjpql.dto.ArticleDTO(a.title, case when :author MEMBER OF a.authors then true else false end) FROM Article a")
List<ArticleDTO> getAllArticles(Author author);

alternatively, you can also use an explicit subquery:

@Query("SELECT new com.example.learnjpql.dto.ArticleDTO(a.title, case when exists (select 1 from a.authors sub where sub = :author)) FROM Article a")
List<ArticleDTO> getAllArticles(Author author);