| 知乎专栏 |
更新/删除操作需要加上 @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)
下面介绍一下@Transactional注解的参数以及使用：
事物传播行为介绍:
@Transactional(propagation=Propagation.REQUIRED) ：如果有事务, 那么加入事务, 没有的话新建一个(默认情况下)
@Transactional(propagation=Propagation.NOT_SUPPORTED) ：容器不为这个方法开启事务
@Transactional(propagation=Propagation.REQUIRES_NEW) ：不管是否存在事务,都创建一个新的事务,原来的挂起,新的执行完毕,继续执行老的事务
@Transactional(propagation=Propagation.MANDATORY) ：必须在一个已有的事务中执行,否则抛出异常
@Transactional(propagation=Propagation.NEVER) ：必须在一个没有的事务中执行,否则抛出异常(与Propagation.MANDATORY相反)
@Transactional(propagation=Propagation.SUPPORTS) ：如果其他bean调用这个方法,在其他bean中声明事务,那就用事务.如果其他bean没有声明事务,那就不用事务.
事物超时设置:
@Transactional(timeout=30) //默认是30秒
事务隔离级别:
@Transactional(isolation = Isolation.READ_UNCOMMITTED)：读取未提交数据(会出现脏读, 不可重复读) 基本不使用
@Transactional(isolation = Isolation.READ_COMMITTED)：读取已提交数据(会出现不可重复读和幻读)
@Transactional(isolation = Isolation.REPEATABLE_READ)：可重复读(会出现幻读)
@Transactional(isolation = Isolation.SERIALIZABLE)：串行化 MYSQL: 默认为REPEATABLE_READ级别 SQLSERVER: 默认为READ_COMMITTED
@Transactional注解中常用参数说明
注意的几点:
@Transactional 只能被应用到public方法上, 对于其它非public的方法,如果标记了@Transactional也不会报错,但方法没有事务功能.
用 spring 事务管理器,由spring来负责数据库的打开,提交,回滚.默认遇到运行期例外(throw new RuntimeException("注释");)会回滚，即遇到不受检查（unchecked）的例外时回滚；而遇到需要捕获的例外(throw new Exception("注释");)不会回滚,即遇到受检查的例外（就是非运行时抛出的异常，编译器会检查到的异常叫受检查例外或说受检查异常）时，需我们指定方式来让事务回滚要想所有异常都回滚,要加上 @Transactional( rollbackFor={Exception.class,其它异常}) .如果让unchecked例外不回滚： @Transactional(notRollbackFor=RunTimeException.class)
@Transactional 注解应该只被应用到 public 可见度的方法上。 如果你在 protected、private 或者 package-visible 的方法上使用 @Transactional 注解，它也不会报错， 但是这个被注解的方法将不会展示已配置的事务设置。
@Transactional 注解可以被应用于接口定义和接口方法、类定义和类的 public 方法上。然而，请注意仅仅 @Transactional 注解的出现不足于开启事务行为，它仅仅 是一种元数据，能够被可以识别 @Transactional 注解和上述的配置适当的具有事务行为的beans所使用。上面的例子中，其实正是 元素的出现 开启 了事务行为。
Spring团队的建议是你在具体的类（或类的方法）上使用 @Transactional 注解，而不要使用在类所要实现的任何接口上。你当然可以在接口上使用 @Transactional 注解，但是这将只能当你设置了基于接口的代理时它才生效。因为注解是不能继承的，这就意味着如果你正在使用基于类的代理时，那么事务的设置将不能被基于类的代理所识别，而且对象也将不会被事务代理所包装（将被确认为严重的）。因此，请接受Spring团队的建议并且在具体的类上使用 @Transactional 注解。
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() {
...
...
}
}
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);
}
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 无法实现。
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);
}
通过实体返回数据有时结果集非常庞大，可能会影响性能，这时我们只需要返回指定字段即可。
@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();
临时写一个新的模型
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();
返回集合
@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);