SpringBoot无主键QueryDSL查询数据重复
问题
- 实体类无主键，启动报错，需要在某个字段上添加
@Id注解，应用场景：视图查询，表数据只做插入、查询，无需更新（类似日志） - 表中无主键，在任意字段上添加
@Id后，数据记录重复，全部都是一样的结果，以下操作可以解决记录重复
将实体类中组合不重复的字段单独抽离到一个类中，必须实现序列化接口
Serializable
package com.example.springbootclickhousenoneid.entity;
import java.io.Serializable;
import java.util.Date;
public class DwsWebSummaryCountsKey implements Serializable {
private String si;
private Date eventTime;
}
实体类添加注解
@IdClass(DwsWebSummaryCountsKey.class)，且必须添加@Id(加载任意字段都行)
package com.example.springbootclickhousenoneid.entity;
import com.fasterxml.jackson.annotation.JsonFormat;
import java.io.Serializable;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.Table;
import lombok.Data;
@Data
@Entity
@Table(name = "dws_web_summary_counts")
@IdClass(DwsWebSummaryCountsKey.class)
public class DwsWebSummaryCounts implements Serializable {
private static final long serialVersionUID = 5478600893211632322L;
@Id
@Column(name = "si")
private String si;
@Column(name = "event_time")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date eventTime;
@Column(name = "pv")
private Integer pv;
@Column(name = "uv")
private Integer uv;
@Column(name = "ip_counts")
private Integer ipCounts;
}
查询(数据不重复了)
package com.example.springbootclickhousenoneid.controller;
import com.example.springbootclickhousenoneid.base.ResultDTO;
import com.example.springbootclickhousenoneid.entity.DwsWebSummaryCounts;
import com.example.springbootclickhousenoneid.entity.QDwsWebSummaryCounts;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import java.util.List;
import javax.annotation.Resource;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class MainController {
@Resource
private JPAQueryFactory jpaQueryFactory;
@GetMapping("/")
public ResultDTO index() {
QDwsWebSummaryCounts entity = QDwsWebSummaryCounts.dwsWebSummaryCounts;
JPAQuery<DwsWebSummaryCounts> query = jpaQueryFactory.selectFrom(entity);
List<DwsWebSummaryCounts> list = query.fetch();
return ResultDTO.ok(list);
}
}