今天遇到个有意思的问题:findAll查出的数据竟然很数据库里存储的数据不一样
本来数据库里有全国所有省份的数据但是查出来只有河南和浙江的数据条数一样,数据大量重复
经过分析是Entity的ID字段在数据库中并不是唯一的,而且重复的很多,后来改成联合主键就正常了。
选ID的时候一定要确认是否是唯一的,因为JPA底层会根据这个ID判断俩个Entity是不是一样的。
以下是配置联合主键的代码:
/**
* @Description:
* @Author: tianlang
* @Email: tianlangstudio@aliyun.com
* @Date: 20-5-6 下午6:19
*/
package email.tianlangstudio.aliyun.com.datav.model;
import javax.persistence.*;
import java.math.BigDecimal;
/**
* 员工地域信息表
* 包含省、市、员工个数信息
*
SELECT gsdm as 单位,
nd as 年份,
province as 省,
city as 市,
SUM(rgcb) AS 金额,
SUM(sl) AS 数量 FROM map GROUP BY gsdm,nd,province,city
* ***/
@Entity
@Table(name = "map")
@IdClass(EmployeeRegionInfoKey.class)
public class EmployeeRegionInfo {
/*
* 公司代码
* **/
@Id
@Column(name = "gsdm")
private String companyCode;
/**
* 年份
* **/
@Id
@Column(name = "nd")
private Integer year;
/*
* 省名称
* **/
@Id
@Column(name = "province")
private String province;
/**
* 市名称
* **/
@Id
@Column(name = "city")
private String city;
/**
* 金额
* **/
@Column(name = "rgcb")
private BigDecimal moneyAmount;
/**
* 数量
* **/
@Column(name = "sl")
private Integer employeeAmount;
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getCompanyCode() {
return companyCode;
}
public void setCompanyCode(String companyCode) {
this.companyCode = companyCode;
}
public Integer getYear() {
return year;
}
public void setYear(Integer year) {
this.year = year;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
public BigDecimal getMoneyAmount() {
return moneyAmount;
}
public void setMoneyAmount(BigDecimal moneyAmount) {
this.moneyAmount = moneyAmount;
}
public Integer getEmployeeAmount() {
return employeeAmount;
}
public void setEmployeeAmount(Integer employeeAmount) {
this.employeeAmount = employeeAmount;
}
}
/**
* @Description:
* @Author: tianlang
* @Email: tianlangstudio@aliyun.com
* @Date: 20-5-7 下午1:47
*/
package email.tianlangstudio.aliyun.com.datav.model;
import org.springframework.context.annotation.Primary;
import javax.persistence.Column;
import javax.persistence.Embeddable;
import java.io.Serializable;
import java.math.BigDecimal;
//@Embeddable
//@Primary
public class EmployeeRegionInfoKey implements Serializable {
/*
* 公司代码
* **/
//@Column(name = "gsdm")
private String companyCode;
/**
* 年份
* **/
//@Column(name = "nd")
private Integer year;
/*
* 省名称
* **/
//@Column(name = "province")
private String province;
/**
* 市名称
* **/
//@Column(name = "city")
private String city;
public String getCompanyCode() {
if(companyCode == null) {
companyCode = "";
}
return companyCode;
}
public void setCompanyCode(String companyCode) {
this.companyCode = companyCode;
}
public Integer getYear() {
return year;
}
public void setYear(Integer year) {
this.year = year;
}
public String getProvince() {
if(province == null) {
province = "";
}
return province;
}
public void setProvince(String province) {
this.province = province;
}
public String getCity() {
if(city == null) {
city = "";
}
return city;
}
public void setCity(String city) {
this.city = city;
}
@Override
public boolean equals(Object obj) {
EmployeeRegionInfoKey other = (EmployeeRegionInfoKey)obj;
return other.getYear() == this.getYear() &&
other.getCompanyCode().equals(this.getCompanyCode()) &&
other.getProvince().equals(this.getProvince()) &&
other.getCity().equals(this.getCity());
}
@Override
public int hashCode() {
return (this.getCity() + this.getProvince() + this.getYear() + this.getCompanyCode()).hashCode();
}
}