Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
73.8. JPQL @Query
上一页 第 73 章 Spring Data with MySQL 下一页
知乎专栏

73.8. JPQL @Query

73.8.1. @Modifying 更新/删除

更新/删除操作需要加上 @Modifying 注解

			
@Modifying
@Query("update Money m set m.isDeleted=?2 where  m.money=?1")
void updateStateByMoney(Long money, Byte state);			
			
			
			

  @Modifying(clearAutomatically=true, flushAutomatically = true)

73.8.2. 参数传递

				
package api.repository.oracle;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import api.domain.oracle.Member;

@Repository
public interface MemberRepository extends CrudRepository<Member, Long> {
	public Page<Member> findAll(Pageable pageable);

	// public Member findByBillno(String billno);

	public Member findById(String id);

	@Query("SELECT m FROM Member m WHERE m.status = 'Y' AND m.id = :id")
	public Member findFinishById(@Param("id") String id);

}
				
			
				
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface PersonRepository extends JpaRepository<Person, Long> {
    @Query("SELECT p FROM Person p WHERE LOWER(p.lastName) = LOWER(:lastName)")
    public List<Person> find(@Param("lastName") String lastName);
}

73.8.3. 原生 SQL 操作

73.8.3.1. 查询

				
public interface UserRepository extends JpaRepository<User, Long> {

	@Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?0", nativeQuery = true)
	User findByEmailAddress(String emailAddress);
}

73.8.3.2. 忽略插入

insert ignore

				
	@Modifying
    @Query(value = "insert ignore into emp(create, modified, user_id, user_name, user_nickname, user_mail) values(?1, ?2, ?3, ?4, ?5, ?6)", nativeQuery = true)
    void insertIgnoreEmployee(Timestamp create, Timestamp modified, String userId, String name, String nickname, String mail);

73.8.3.3. 数据更新

这里的nativeQuery=true代表在执行这个方法的时候使用原生sql语句，直接写数据库中的实际表名和表中的字段名，而不是实体表名。 

				
    @Modifying
    @Query(nativeQuery = true, value = "UPDATE project p, (SELECT MIN(start) AS start, MAX(finish) AS finish FROM project WHERE parent_id = :id) t SET p.start = t.start, p.finish = t.finish WHERE p.id = :id")
    public void updateStartAndFinishById(@Param("id") Long id);

在什么情况下使用呢？例如上面，同时操作两张表，做更新，如果不使用 nativeQuery = true 无法实现。

73.8.4. @Query 与 Pageagble

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#_native_queries 

			
@Query(value = "SELECT u FROM User u ORDER BY id")
Page<User> findAllUsersWithPagination(Pageable pageable);			
			
			
				
package api.domain;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Index;
import javax.persistence.Table;

@Entity
@Table(indexes = { @Index(name = "address", columnList = "from_address,to_address"), @Index(name = "contractAddress", columnList = "contractAddress") })

public class TransactionHistory implements Serializable {
	private static final long serialVersionUID = 6710992220657056861L;
	@Id
	@Column(name = "blockNumber", unique = true, nullable = false, insertable = true, updatable = false)
	private int blockNumber;
	private String timeStamp;
	private String hash;
	@Column(name = "from_address")
	private String from;
	@Column(name = "to_address")
	private String to;
	private String value;
	private String gas;
	private String gasPrice;
	private String isError;
	private String contractAddress;
	private String gasUsed;
	private String symbol;

	public TransactionHistory() {
		// TODO Auto-generated constructor stub
	}

	public int getBlockNumber() {
		return blockNumber;
	}

	public void setBlockNumber(int blockNumber) {
		this.blockNumber = blockNumber;
	}

	public String getTimeStamp() {
		return timeStamp;
	}

	public void setTimeStamp(String timeStamp) {
		this.timeStamp = timeStamp;
	}

	public String getHash() {
		return hash;
	}

	public void setHash(String hash) {
		this.hash = hash;
	}

	public String getFrom() {
		return from;
	}

	public void setFrom(String from) {
		this.from = from;
	}

	public String getTo() {
		return to;
	}

	public void setTo(String to) {
		this.to = to;
	}

	public String getValue() {
		return value;
	}

	public void setValue(String value) {
		this.value = value;
	}

	public String getGas() {
		return gas;
	}

	public void setGas(String gas) {
		this.gas = gas;
	}

	public String getGasPrice() {
		return gasPrice;
	}

	public void setGasPrice(String gasPrice) {
		this.gasPrice = gasPrice;
	}

	public String getIsError() {
		return isError;
	}

	public void setIsError(String isError) {
		this.isError = isError;
	}

	public String getContractAddress() {
		return contractAddress;
	}

	public void setContractAddress(String contractAddress) {
		this.contractAddress = contractAddress;
	}

	public String getGasUsed() {
		return gasUsed;
	}

	public void setGasUsed(String gasUsed) {
		this.gasUsed = gasUsed;
	}

	public static long getSerialversionuid() {
		return serialVersionUID;
	}

	public String getSymbol() {
		return symbol;
	}

	public void setSymbol(String symbol) {
		this.symbol = symbol;
	}

	@Override
	public String toString() {
		return "TransactionHistory [blockNumber=" + blockNumber + ", timeStamp=" + timeStamp + ", hash=" + hash + ", from=" + from + ", to=" + to + ", value=" + value + ", gas=" + gas + ", gasPrice=" + gasPrice + ", isError=" + isError + ", contractAddress=" + contractAddress + ", gasUsed=" + gasUsed + ", symbol=" + symbol + "]";
	}

}
				
				
			
				
package api.repository;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import api.domain.TransactionHistory;

@Repository
public interface TransactionHistoryRepository extends CrudRepository<TransactionHistory, Integer> {

	@Query(value = "SELECT * FROM transaction_history th WHERE (th.from_address = :address or th.to_address = :address) and contract_address is NULL",
			countQuery = "SELEÇT count(*) FROM transaction_history th WHERE (th.from_address = :address or th.to_address = :address) and contract_address is NULL",
			nativeQuery = true)
	public Page<TransactionHistory> findEthByAddress(@Param("address") String address, Pageable pageable);

}

73.8.5. 返回指定字段

通过实体返回数据有时结果集非常庞大，可能会影响性能，这时我们只需要返回指定字段即可。

				
@Query(value = "select u.userName, ui.name, ui.gender, ui.description from UserInfo ui, User u where u.id = ui.userId")
public List<Object> getCustomField();
				
			
			
@Query(value = "select new map(u.userName, ui.name, ui.gender, ui.description) from UserInfo ui, User u where u.id = ui.userId")
public List<Map<String, Object>> getCustomField();

73.8.6. 返回指定的模型

临时写一个新的模型

				
public class MyModel implements Serializable {

    private String userName;
    private String name;
    private String gender;
    private String description;

    public MyModel() {};

    public MyModel(String userName, String name, String gender, String description) {
        this.userName = userName;
        this.name = name;
        this.gender = gender;
        this.description = description;
    }
}

使用构造方法赋值

				
@Query(value = "select new cn.netkiller.model.MyModel(u.userName, ui.name, ui.gender, ui.description) from UserInfo ui, User u where u.id = ui.userId")
public List<MyModel> getAllRecord();

73.8.7. @Query 与 @ManyToOne

			
package cn.netkiller.domain;

import jakarta.persistence.*;
import lombok.Data;
import org.hibernate.annotations.Comment;
import org.hibernate.annotations.DynamicInsert;
import org.hibernate.annotations.DynamicUpdate;

import java.io.Serial;
import java.io.Serializable;

@Entity
@DynamicInsert
@DynamicUpdate
@Table
@Data
public class Follow implements Serializable {

    @Serial
    public static final long serialVersionUID = -1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, nullable = false, insertable = false, updatable = false, columnDefinition = "int unsigned")
    @Comment("主键")
    private Integer id;

    @ManyToOne
    @JoinColumn(name = "follower")
    private Consumer follower;

    @ManyToOne
    @JoinColumn(name = "followed")
    private Consumer followed;
}
			
			
			
			
package cn.netkiller.domain;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonIgnore;
import jakarta.persistence.*;
import lombok.Data;
import org.hibernate.annotations.Comment;
import org.hibernate.annotations.DynamicInsert;
import org.hibernate.annotations.DynamicUpdate;
import org.springframework.format.annotation.DateTimeFormat;

import java.io.Serial;
import java.io.Serializable;
import java.util.Date;

@Entity
@Table
@Data
@DynamicUpdate
@DynamicInsert
@Comment("客户信息表")
public class Consumer implements Serializable {
    @Serial
    public static final long serialVersionUID = -1L;

    @Id
//    @GeneratedValue(strategy = GenerationType.IDENTITY)
//    @Column(name = "id", unique = true, nullable = false, insertable = false, updatable = false, columnDefinition = "int unsigned")
    @Comment("唯一ID")
//    @Column(name = "id", nullable = false, columnDefinition = "int unsigned")
    private Integer id;

    @OneToOne(cascade = CascadeType.MERGE)
    @MapsId
    @JoinColumn(name = "id", insertable = true, updatable = false, columnDefinition = "int unsigned", foreignKey = @ForeignKey(name = "device_id"))
    @JsonIgnore
    private Device device;

    @Comment("姓名")
    @Column(length = 8)
    private String name;
    @Comment("昵称")
    @Column(length = 16)
    private String nickname;
    @Comment("头像")
    private String avatar;
    @Comment("性别")
    private Boolean gender;
    @Comment("年龄")
    private Integer age;
    @Comment("生日")
    @JsonFormat(pattern = "yyyy-MM-dd")
    @Temporal(TemporalType.DATE)
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date birthday;
    @Comment("地址")
    private String address;
    @Comment("电话")
    @Column(length = 15)
    private String mobile;

    @Column(insertable = false, updatable = false, columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
    @Comment("创建时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
//    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date ctime;

    @Column(nullable = true, insertable = false, updatable = false, columnDefinition = "TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP")
    @Comment("修改时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
//    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date mtime;

    public Consumer() {
    }

}
			
			
			
			
package cn.netkiller.service;

import cn.netkiller.domain.Consumer;
import cn.netkiller.domain.Follow;
import cn.netkiller.repository.FollowRepository;
import jakarta.transaction.Transactional;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class FollowService {
    @Autowired
    private FollowRepository followRepository;

    public Iterable<Follow> following() {
        Iterable<Follow> follow = followRepository.findAll();
        return follow;
    }

    public void following(Integer follower, Integer followed) {
        Follow follow = new Follow();
        Consumer me = new Consumer();
        me.setId(follower);

        Consumer their = new Consumer();
        me.setId(followed);

        followRepository.findByFollowerAndFollowed(me, their);

        follow.setFollower(me);
        follow.setFollower(their);

        followRepository.save(follow);

    }

    @Transactional
    public boolean follow(Integer follower, Integer followed) {

//        Consumer follower = new Consumer();
//        follower.setId(follow.getFollower().getId());
//
//        Consumer followed = new Consumer();
//        followed.setId(follow.getFollowed().getId());

        followRepository.followByFollowerAndFollowed(follower, followed);
        return true;
    }

    @Transactional
    public boolean unfollow(Integer follower, Integer followed) {

//        Consumer follower = new Consumer();
//        follower.setId(follow.getFollower().getId());
//
//        Consumer followed = new Consumer();
//        followed.setId(follow.getFollowed().getId());

        followRepository.deleteByFollowerAndFollowed(follower, followed);
        return true;
    }
}
			
			
			
			
package cn.netkiller.controller;

import cn.netkiller.ai.AigcJsonResponse;
import cn.netkiller.domain.Follow;
import cn.netkiller.service.FollowService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@RestController
@Slf4j
@RequestMapping("/follow/{appid}/{device}")
public class FollowController {
    @Autowired
    private FollowService followService;

    @GetMapping("following")
    public Iterable<Follow> following() {
        Iterable<Follow> follow = followService.following();
        return follow;
    }

    @PutMapping("follow/{follower}/{followed}")
    public AigcJsonResponse follow(@PathVariable("follower") Integer follower, @PathVariable("followed") Integer followed) {

        followService.follow(follower, followed);
        return new AigcJsonResponse(true);
    }

    @DeleteMapping("unfollow/{follower}/{followed}")
    public AigcJsonResponse unfollow(@PathVariable("follower") Integer follower, @PathVariable("followed") Integer followed) {
        boolean status = followService.unfollow(follower, followed);
        return new AigcJsonResponse(status);
    }
}			
			
			
			
package cn.netkiller.repository;

import cn.netkiller.domain.Consumer;
import cn.netkiller.domain.Follow;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Optional;

@Repository
public interface FollowRepository extends CrudRepository<Follow, Integer> {

    Optional<Follow> findByFollowerAndFollowed(Consumer me, Consumer their);

    @Modifying
//    @Query(value = "DELETE FROM Follow WHERE follower=:follower and followed=:followed", nativeQuery = true)
    @Query(value = "DELETE FROM Follow WHERE follower.id=:follower and followed.id=:followed")
    void deleteByFollowerAndFollowed(@Param("follower") Integer follower, @Param("followed") Integer followed);


    @Query(value = "INSERT INTO Follow(follower,followed) VALUES( :follower,:followed)", nativeQuery = true)
    void followByFollowerAndFollowed(Integer follower, Integer followed);
}

原生写法：@Query(value = "DELETE FROM Follow WHERE follower=:follower and followed=:followed", nativeQuery = true)

JPQL写法：@Query(value = "DELETE FROM Follow WHERE follower.id=:follower and followed.id=:followed")

两种写法结果相同

73.8.8. Collection

返回集合

				
@Query("SELECT u FROM User u WHERE u.status = 1")
Collection<User> findAllActiveUsers();

处理子查询 IN

			
@Query(value = "SELECT u FROM User u WHERE u.name IN :names")
List<User> findUserByNameList(@Param("names") Collection<String> names);

73.8.9. Sort

		
	@Query(value = "SELECT u FROM User u")
	List<User> findAllUsers(Sort sort);

73.8.10. 更新数据，返回值

返回 0 表示更新失败，返回 1 表示更新成功

			
    @Query("UPDATE Picture SET share = :status WHERE id=:id")
    @Modifying
    int updateShareStatus(@Param("id") Long id, @Param("status") boolean status);

73.8.11. 锁 @Lock

			
interface UserRepository extends Repository<User, Long> {

  // Plain query method
  @Lock(LockModeType.READ)
  List<User> findByLastname(String lastname);
上一页 上一级 下一页
73.7. TransactionTemplate 起始页 73.9. 事务 @Transactional