Spring data jpa 查询出来的数据总是重复

jpa 主键重复导致查询list的数据总是重复第一条数据


··· Java疑难杂症、技术学习、免费问答请加QQ群:695654335 Java技术交流 ···


1.现象

在使用Spring data jpa 利用 EntityManager 写自定义原生 sql 语句的时候。
因为需要返回匹配的结果集的自定义视图。

例如关联查询之后,将返回结果封装成某个与数据映射的实体类对象,这时候需要在这个对象中使用@Id 注解标明这个对象的主键ID。但是这么做了之后会有一个问题,那就是如果数据形式是如下表所展现的数据形式的话。

order_noorder_num
2018010100011
2018010100012
2018010100013
2018010100021
2018010100022
2018010100031

结果返回json如下

[{
	"orderNo": "201801010001",
	"orderNum": "1"
}, {
	"$ref": "$[0]"
}, {
	"$ref": "$[0]"
}, {
	"orderNo": "201801010002",
	"orderNum": "1"
}, {
	"$ref": "$[3]"
}, {
	"orderNo": "201801010003",
	"orderNum": "1"
}]

看到这里,就很奇怪了,为何序列化出来是"$ref": "$[0]" 这种数据


2.原始代码

下面是我的实体类以及查询自定义查询使用的代码
实体类代码

@Entity
public class TempVM implements Serializable {
    private static final long serialVersionUID = 5853023367468231088L;

    private String orderNo;
    private String orderNum;

    @Id
    @Column(name = "order_no")
    public String getOrderNo() {
        return orderNo;
    }

    public void setOrderNo(String orderNo) {
        this.orderNo = orderNo;
    }

    @Column(name = "order_num")
    public String getOrderNum() {
        return orderNum;
    }

    public void setOrderNum(String orderNum) {
        this.orderNum = orderNum;
    }
}

下面是自定义查询代码

@Repository
public class TempDaoPlusImpl implements TempDaoPlus {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<TempVM> findAll() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select order_no,order_num from tb_temp");
        Query query = entityManager.createNativeQuery(sql.toString(), TempVM.class);
        return query.getResultList();
    }
}

3.解决方案

修改后的实体类

@Entity
public class TempVM implements Serializable {
    private static final long serialVersionUID = 5853023367468231088L;

    private Long id;
    private String orderNo;
    private String orderNum;

    @Id
    @Column(name = "id")
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @Column(name = "order_no")
    public String getOrderNo() {
        return orderNo;
    }

    public void setOrderNo(String orderNo) {
        this.orderNo = orderNo;
    }

    @Column(name = "order_num")
    public String getOrderNum() {
        return orderNum;
    }

    public void setOrderNum(String orderNum) {
        this.orderNum = orderNum;
    }
}

修改后的自定义查询

@Repository
public class TempDaoPlusImpl implements TempDaoPlus {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<TempVM> findAll() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select @rownum \\:= @rownum +1 AS id, t.order_no, t.order_num from " +
                " tb_temp t,(select @rownum \\:= 0) r" +
                "");
        Query query = entityManager.createNativeQuery(sql.toString(), TempVM.class);
        return query.getResultList();
    }
}

修改后执行结果

[{
	"id": 1,
	"orderNo": "201801010001",
	"orderNum": "1"
}, {
	"id": 2,
	"orderNo": "201801010001",
	"orderNum": "2"
}, {
	"id": 3,
	"orderNo": "201801010001",
	"orderNum": "3"
}, {
	"id": 4,
	"orderNo": "201801010002",
	"orderNum": "1"
}, {
	"id": 5,
	"orderNo": "201801010002",
	"orderNum": "2"
}, {
	"id": 6,
	"orderNo": "201801010003",
	"orderNum": "1"
}]
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值