JPA Join Query(本地查询)自定义dto [英] JPA Join Query(native query) custom dto
问题描述
我正在尝试加入3个表并使用以下查询获取集体结果,即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;
此查询在mysql工作台中工作正常,当我尝试将此查询添加到@Query
批注中时,我遇到了AbstractJpaQuery$TupleConverter$TupleBackedMap
异常,对此的解决方法是使用ContructorExpression
我添加了new
表达式,但我无法正常工作,我的数据库实体如下所示:
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<Like> likes;
@OneToMany(mappedBy = "feed", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
@Getter
@Setter
List<Comment> comments;
@Override
public String toString() {
return "Feed{" +
"id=" + id +
", user=" + user +
'}';
}
}
上述SQL查询的自定义DTO对象:
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
方法如下:
@Query("**sql_query**")
List<FeedDetails> findAllFeedsByUserId(Long userId);
我不确定我在sql_query
中写了什么叫什么,以便得到正确的FeedDetails
List
结果.
I am not sure what shout i write in sql_query
such that i get proper FeedDetails
List
result.
推荐答案
您不能对本机查询使用构造函数表达式(NEW运算符).
You cannot use constructor expression (NEW operator) with Native Queries.
您可以使用JPA构造函数结果.看起来像:
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)})
})
或者您可以将FeedDetails转换为接口并尝试使用Spring Data JPA接口投影:
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
这篇关于JPA Join Query(本地查询)自定义dto的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!