SpringBoot无主键QueryDSL查询数据重复

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);
  }
}
1 Like

靖哥哥已经把QueryDsl理解透彻了。