public interface UserRepository extends JpaRepository<User, Long> {

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

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);
				
				
				

这里的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 无法实现。

			
    @GetMapping("discovery/group")
//    @Deprecated(since = "v4.0.0", forRemoval = true)
    public AigcResponse group(Pageable pageable) {

        Page<Map<String, Object>> page = pictureGroupService.discoveryPictureGroup(pageable);
        List<Map<String, Object>> newList = new ArrayList<Map<String, Object>>();
        List<Map<String, Object>> contents = page.getContent();
        contents.forEach(content -> {
            Map<String, Object> map = new HashMap<String, Object>(content);
            map.put("thumbnail", url.concat((String) map.get("thumbnail")));
            newList.add(map);
        });
        Page<Map<String, Object>> newPage = new PageImpl<>(newList, pageable, page.getTotalElements());

        return new AigcResponse(newPage);
    }			
			
			

			
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")

两种写法结果相同

返回集合

				
@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);			
			
				

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

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

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

			
interface UserRepository extends Repository<User, Long> {

  // Plain query method
  @Lock(LockModeType.READ)
  List<User> findByLastname(String lastname);			
			
				

			
package cn.netkiller.repository;

import cn.netkiller.domain.PictureClick;
import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.Optional;

@Repository
public interface PictureClickRepository extends CrudRepository<PictureClick, Integer> {

    @Query("SELECT SUM(favorites) AS favorites FROM PictureClick WHERE device.id = :deviceId")
    Optional<Integer> query(@Param("deviceId") Integer deviceId);
		
	@Query(value = "SELECT sum(quantity) FROM Product")
    public Long sumQuantities();

	@Query(value = "SELECT sum(quantity * price) FROM Product")
    public BigDecimal total();	
}	
			
				

			
    @Query("SELECT SUM(favorites) AS favorites, SUM(likes) AS likes FROM PictureClick WHERE device.id = :deviceId")
    Optional<List<Integer[]>> query(@Param("deviceId") Integer deviceId);
    
    @Query("SELECT new map(SUM(favorites) AS favorites, SUM(likes) AS likes) FROM PictureClick WHERE device.id = :deviceId")
    Optional<Map<String, Integer>> query(@Param("deviceId") Integer deviceId);
			
				

			
    @Query("SELECT new map(IFNULL(SUM(favorites),0) AS favorites, IFNULL(SUM(likes),0) AS likes) FROM PictureClick WHERE device.id = :deviceId")
    Optional<Map<String, Integer>> query(@Param("deviceId") Integer deviceId);
			
				

在SimpleJpaRepository中save()已经帮我们实现

					
@Repository
@Transactional(readOnly = true)
public class SimpleJpaRepository<T, ID> implements JpaRepositoryImplementation<T, ID> {
​
    private static final String ID_MUST_NOT_BE_NULL = "The given id must not be null!";
​
    private final JpaEntityInformation<T, ?> entityInformation;
    private final EntityManager em;
    private final PersistenceProvider provider;
​
    private @Nullable CrudMethodMetadata metadata;
​
    /**
     * Creates a new {@link SimpleJpaRepository} to manage objects of the given {@link JpaEntityInformation}.
     *
     * @param entityInformation must not be {@literal null}.
     * @param entityManager must not be {@literal null}.
     */
    public SimpleJpaRepository(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
​
        Assert.notNull(entityInformation, "JpaEntityInformation must not be null!");
        Assert.notNull(entityManager, "EntityManager must not be null!");
​
        this.entityInformation = entityInformation;
        this.em = entityManager;
        this.provider = PersistenceProvider.fromEntityManager(entityManager);
    }
​
    /**
     * Creates a new {@link SimpleJpaRepository} to manage objects of the given domain type.
     *
     * @param domainClass must not be {@literal null}.
     * @param em must not be {@literal null}.
     */
    public SimpleJpaRepository(Class<T> domainClass, EntityManager em) {
        this(JpaEntityInformationSupport.getEntityInformation(domainClass, em), em);
    }
​
    /**
     * Configures a custom {@link CrudMethodMetadata} to be used to detect {@link LockModeType}s and query hints to be
     * applied to queries.
     *
     * @param crudMethodMetadata
     */
    public void setRepositoryMethodMetadata(CrudMethodMetadata crudMethodMetadata) {
        this.metadata = crudMethodMetadata;
    }
​
    @Nullable
    protected CrudMethodMetadata getRepositoryMethodMetadata() {
        return metadata;
    }
​
    //......
​
    /*
     * (non-Javadoc)
     * @see org.springframework.data.repository.CrudRepository#delete(java.io.Serializable)
     */
    @Transactional
    public void deleteById(ID id) {
​
        Assert.notNull(id, ID_MUST_NOT_BE_NULL);
​
        delete(findById(id).orElseThrow(() -> new EmptyResultDataAccessException(
                String.format("No %s entity with id %s exists!", entityInformation.getJavaType(), id), 1)));
    }
​
    /*
     * (non-Javadoc)
     * @see org.springframework.data.repository.CrudRepository#delete(java.lang.Object)
     */
    @Transactional
    public void delete(T entity) {
​
        Assert.notNull(entity, "The entity must not be null!");
        em.remove(em.contains(entity) ? entity : em.merge(entity));
    }
​
    /*
     * (non-Javadoc)
     * @see org.springframework.data.repository.CrudRepository#delete(java.lang.Iterable)
     */
    @Transactional
    public void deleteAll(Iterable<? extends T> entities) {
​
        Assert.notNull(entities, "The given Iterable of entities not be null!");
​
        for (T entity : entities) {
            delete(entity);
        }
    }
​
    /*
     * (non-Javadoc)
     * @see org.springframework.data.jpa.repository.JpaRepository#deleteInBatch(java.lang.Iterable)
     */
    @Transactional
    public void deleteInBatch(Iterable<T> entities) {
​
        Assert.notNull(entities, "The given Iterable of entities not be null!");
​
        if (!entities.iterator().hasNext()) {
            return;
        }
​
        applyAndBind(getQueryString(DELETE_ALL_QUERY_STRING, entityInformation.getEntityName()), entities, em)
                .executeUpdate();
    }
​
    /*
     * (non-Javadoc)
     * @see org.springframework.data.repository.Repository#deleteAll()
     */
    @Transactional
    public void deleteAll() {
​
        for (T element : findAll()) {
            delete(element);
        }
    }
​
    /*
     * (non-Javadoc)
     * @see org.springframework.data.jpa.repository.JpaRepository#deleteAllInBatch()
     */
    @Transactional
    public void deleteAllInBatch() {
        em.createQuery(getDeleteAllQueryString()).executeUpdate();
    }
​
    //......
​
    /*
     * (non-Javadoc)
     * @see org.springframework.data.repository.CrudRepository#save(java.lang.Object)
     */
    @Transactional
    public <S extends T> S save(S entity) {
​
        if (entityInformation.isNew(entity)) {
            em.persist(entity);
            return entity;
        } else {
            return em.merge(entity);
        }
    }
​
    /*
     * (non-Javadoc)
     * @see org.springframework.data.jpa.repository.JpaRepository#saveAndFlush(java.lang.Object)
     */
    @Transactional
    public <S extends T> S saveAndFlush(S entity) {
​
        S result = save(entity);
        flush();
​
        return result;
    }
​
    /*
     * (non-Javadoc)
     * @see org.springframework.data.jpa.repository.JpaRepository#save(java.lang.Iterable)
     */
    @Transactional
    public <S extends T> List<S> saveAll(Iterable<S> entities) {
​
        Assert.notNull(entities, "The given Iterable of entities not be null!");
​
        List<S> result = new ArrayList<S>();
​
        for (S entity : entities) {
            result.add(save(entity));
        }
​
        return result;
    }
​
    /*
     * (non-Javadoc)
     * @see org.springframework.data.jpa.repository.JpaRepository#flush()
     */
    @Transactional
    public void flush() {
        em.flush();
    }
​
    //......
​
    /*
     * (non-Javadoc)
     * @see org.springframework.data.repository.CrudRepository#findById(java.io.Serializable)
     */
    public Optional<T> findById(ID id) {
​
        Assert.notNull(id, ID_MUST_NOT_BE_NULL);
​
        Class<T> domainType = getDomainClass();
​
        if (metadata == null) {
            return Optional.ofNullable(em.find(domainType, id));
        }
​
        LockModeType type = metadata.getLockModeType();
​
        Map<String, Object> hints = getQueryHints().withFetchGraphs(em).asMap();
​
        return Optional.ofNullable(type == null ? em.find(domainType, id, hints) : em.find(domainType, id, type, hints));
    }
​
    /*
     * (non-Javadoc)
     * @see org.springframework.data.jpa.repository.JpaRepository#getOne(java.io.Serializable)
     */
    @Override
    public T getOne(ID id) {
​
        Assert.notNull(id, ID_MUST_NOT_BE_NULL);
        return em.getReference(getDomainClass(), id);
    }
​
    /**
     * Applies the given {@link Specification} to the given {@link CriteriaQuery}.
     *
     * @param spec can be {@literal null}.
     * @param domainClass must not be {@literal null}.
     * @param query must not be {@literal null}.
     * @return
     */
    private <S, U extends T> Root<U> applySpecificationToCriteria(@Nullable Specification<U> spec, Class<U> domainClass,
            CriteriaQuery<S> query) {
​
        Assert.notNull(domainClass, "Domain class must not be null!");
        Assert.notNull(query, "CriteriaQuery must not be null!");
​
        Root<U> root = query.from(domainClass);
​
        if (spec == null) {
            return root;
        }
​
        CriteriaBuilder builder = em.getCriteriaBuilder();
        Predicate predicate = spec.toPredicate(root, query, builder);
​
        if (predicate != null) {
            query.where(predicate);
        }
​
        return root;
    }
    //......
}			
					
				

					
@Transactional(isolation = Isolation.SERIALIZABLE)					
					
				

					
@Transactional(propagation = Propagation.SUPPORTS, readOnly = true)			
					
				

				
package cn.netkiller.api.repository;

import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
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 cn.netkiller.api.domain.RecentRead;

@Repository
public interface RecentReadRepostitory extends CrudRepository<RecentRead, Integer> {

	Page<RecentRead> findByMemberIdOrderByIdDesc(int memberId, Pageable pageable);

	int countByMemberId(int memberId);
	
	@Transactional
	@Modifying
	@Query("DELETE FROM RecentRead r WHERE r.memberId = ?1 AND r.articleId = ?2")
	void deleteByMemberIdAndArticleId(int memberId, int articleId);
	
	@Transactional
	@Modifying
	@Query("delete from RecentRead where member_id = :member_id")
	public void deleteByMemberId(@Param("member_id") int memberId);

	int countByMemberIdAndArticleId(int memberId, int articleId);

}				
				
				

				
	// 指定Exception回滚
	@Transactional(rollbackFor=Exception.class)
    public void methodName() {
       // 不会回滚
       throw new Exception("...");
    }

	//指定Exception回滚，但其他异常不回滚
	@Transactional(noRollbackFor=Exception.class)
    public ItimDaoImpl getItemDaoImpl() {
        // 会回滚
        throw new RuntimeException("注释");
    }
				
				

				
@Service
public class UserService {
    @Autowired
    private UserRepostitory userRepostitory;
    
    @Transactional
    public void add(User user) {
        try {
            userRepostitory.save(user);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        // 不会回滚
    }
    @Transactional
    public void add(User user) throws Exception {
        try {
             userRepostitory.delete(user);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            // 抛出异常才会回滚
            throw new Exception(e);
        }
    }
}
				
				
				

@Transactional 必须与 public 一起使用，不能定义为 private、default、protected

				
@Service
public class UserService {
    @Transactional
    private void add(User user) {
         save(user);
    }
}
				
				

this 调用不支持事物

				
@Service
public class UserService {

    @Autowired
    private UserRepostitory userRepostitory;

    @Transactional
    public void add(User user) {
        userRepostitory.save(user);
        this.update(user);
    }

    @Transactional
    public void update(User user) {
        userRepostitory.update(user);
    }
}				
				
				

解决方案

				
@Servcie
public class UserService {
   @Autowired
   prvate ProfileService profileService;

   public void save(User user) {
         profileService.save(user);
   }
}

 @Servcie
 public class ProfileService {

    @Transactional(rollbackFor=Exception.class)
    public void save(User user) {

    }

}				
				
				

@Transactional 需要在 @Controller、@Service、@Component、@Repository 等注解下才能使用

				
// @Servcie
public class UserService {
	@Autowired
	prvate ProfileService profileService;
   
	@Transactional
	public void save(User user) {
         profileService.save(user);
	}
}				
				
				

屏蔽 @Servcie 后观察 save 的 @Transactional 是不生效的。

				
@Service
public class UserService {

    @Autowired
    private UserRepostitory userRepostitory;
    @Autowired
    private RoleService roleService;

    @Transactional
    public void add(User user) throws Exception {
        userRepostitory.save(user);
        new Thread(() -> {
            roleService.doOtherThing();
        }).start();
    }
}

@Service
public class RoleService {

    @Transactional
    public void doOtherThing() {
        ...
        ...
    }
}				
				
				

				
    @Transactional
    public void test(SysConfigEntity entity) throws Exception {
        //不会回滚
        this.saveSysConfig(entity);
        throw new Exception("rollback test");
         
    }
     
    @Transactional(rollbackFor = Exception.class)
    public void test1(SysConfigEntity entity) throws Exception {
        //会回滚
        this.saveSysConfig(entity);
        throw new Exception("rollback test");
         
    }
     
    @Transactional
    public void test2(SysConfigEntity entity) throws Exception {
        //会回滚
        this.saveSysConfig(entity);
        throw new RuntimeException("rollback test");
         
    }
     
    @Transactional
    public void test3(SysConfigEntity entity) throws Exception {
        //事务仍然会被提交
        this.test4(entity);
        throw new Exception("rollback test");
    }
     
    @Transactional(rollbackFor = Exception.class)
    public void test4(SysConfigEntity entity) throws Exception {
         
        this.saveSysConfig(entity);
    }