Sping Boot实现JPA自定义更新SQL语句

1.更新语句分析

Notice 是实体类 在jpa需要参数来替换 ，noticeState、noticeReceiveUserId、noticeType都在该实体类中的属性，在这里要用n.xx来引用，n.noticeState=0 ：是指定更新的字段，n.noticeReceiveUserId =?1：?1 表示方法中的第一个参数如下面的int userId，如我们要应用方法中的第6个参数就用?6表示。

注意

jpa中使用自定义更新或删除语句必须加
@Modifying
@Transactional//事务的注解
没加会报错：Not supported for DML operations
导入注解要注意包的引用
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.transaction.annotation.Transactiona

    @Transactional//事务的注解

    @Query("update Notice n set n.noticeState=0 where 	n.noticeReceiveUserId =?1  and n.noticeType=?2")
    @Modifying
    int updateNoticeByNoticeReceiveUserIdAndNoticeType(int userId,int type);

2.完整代码如下

1. notice实体类

package com.cwq.bnsp_behind.entity;

import lombok.Data;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.*;
import java.util.Date;

@Data
@Entity
@EntityListeners(AuditingEntityListener.class)
public class Notice {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer noticeId;

    private Integer noticeSendUserId;

    private Integer noticeReceiveUserId;

    private String noticeContent;

    @CreatedDate
    private Date noticeTime;

    //通知类型0系统通知，1评论，2收藏，3点赞
    private Integer  noticeType;

    //消息状态0未读，1已读
    private Integer noticeState;

}

2.NoticeRepository 接口

package com.cwq.bnsp_behind.repository;
import com.cwq.bnsp_behind.entity.Notice;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface NoticeRepository extends JpaRepository<Notice,Integer> {
    @Transactional//事务的注解
    @Query("update Notice n set n.noticeState=0 where n.noticeReceiveUserId =?1  and n.noticeType=?2")
    @Modifying
    int updateNoticeByNoticeReceiveUserIdAndNoticeType(int userId,int type);

}

3.NoticeController 控制类,R是我自定义的一个统一返回类，可以根据需要定义适合的返回类型

package com.cwq.bnsp_behind.controller;
import com.cwq.bnsp_behind.config.R;
import com.cwq.bnsp_behind.entity.Notice;
import com.cwq.bnsp_behind.repository.NoticeRepository;
import io.swagger.models.auth.In;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.repository.query.Param;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/notices")
public class NoticeController {
    @Autowired
    private NoticeRepository noticeRepository;
    @PutMapping("updateNotice")
    public R updateNotice(@RequestBody Notice notice){
        int i = noticeRepository.updateNoticeByNoticeReceiveUserIdAndNoticeType(notice.getNoticeReceiveUserId(), notice.getNoticeType());
        return R.ok().data("更新几条",i);
    }

}