jpa+mysql分组查询,JPA Join Query（本地查询）自定义dto

Hi I am trying to join 3 table and fetch the collective result i.e dto using following query

SELECT f.id, u.email,

count(distinct l.id) as likes_count,

count(distinct c.id) as comments_count

FROM feeds f

INNER JOIN users u ON f.user_id = u.id

INNER JOIN likes l on l.feed_id = f.id

left join comments c on c.feed_id = f.id

WHERE u.id = 12055

group by f.id order by comments_count asc;

This query is working fine in mysql workbench, when i try to add this query to @Query annotation i am geeting AbstractJpaQuery$TupleConverter$TupleBackedMap exception, solution to this was to use ContructorExpression i added new expression but i am not getting it worked, my db entities are as below

@Entity

@Table(name = "feeds")

@NoArgsConstructor

public class Feed {

@Id

@GeneratedValue(strategy = GenerationType.AUTO)

@Getter

@Setter

Long id;

@Getter

@Setter

@Column(columnDefinition = "TEXT")

String content;

public Feed(String content, User user) {

this.content = content;

this.user = user;

}

@ManyToOne(optional = false, cascade = CascadeType.ALL, fetch = FetchType.LAZY)

@Getter

@Setter

User user;

@OneToMany(mappedBy = "feed", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)

@Getter

@Setter

List likes;

@OneToMany(mappedBy = "feed", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)

@Getter

@Setter

List comments;

@Override

public String toString() {

return "Feed{" +

"id=" + id +

", user=" + user +

'}';

}

}

Custom DTO Object for above sql query:

@NoArgsConstructor

@AllArgsConstructor

public class FeedDetails {

@Getter

@Setter

private Long id;

@Getter

@Setter

private Long likes;

@Getter

@Setter

private Long comments;

}

Repository method as below:

@Query("**sql_query**")

List findAllFeedsByUserId(Long userId);

I am not sure what shout i write in sql_query such that i get proper FeedDetails List result.

解决方案

You cannot use constructor expression (NEW operator) with Native Queries.

You could use JPA constructor result. This looks like:

Query q = em.createNativeQuery(

"SELECT c.id, c.name, COUNT(o) as orderCount, AVG(o.price) AS avgOrder " +

"FROM Customer c " +

"JOIN Orders o ON o.cid = c.id " +

"GROUP BY c.id, c.name",

"CustomerDetailsResult");

@SqlResultSetMapping(name="CustomerDetailsResult",

classes={

@ConstructorResult(targetClass=com.acme.CustomerDetails.class,

columns={

@ColumnResult(name="id"),

@ColumnResult(name="name"),

@ColumnResult(name="orderCount"),

@ColumnResult(name="avgOrder", type=Double.class)})

})

Or you could convert FeedDetails to an interface and try Spring Data JPA Interface projection: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections

Or if you don't like these option there is a little library called QLRM: https://github.com/simasch/qlrm/blob/master/ConstructorResult.md