Spring Data with MySQL多维度架构 - 知乎www.zhihu.com
Repository
Repository: 仅仅是一个标识,没有任何方法,方便Spring自动扫描识别
CrudRepository: 继承Repository,实现了一组CRUD相关的方法
PagingAndSortingRepository: 继承CrudRepository,实现了一组分页排序相关的方法
JpaRepository: 继承PagingAndSortingRepository,实现一组JPA规范相关的方法
Spring Data JPA 为此提供了一些表达条件查询的关键字:
And findByLastnameAndFirstname … where x.lastname = ?1 and x.firstname = ?2
Or findByLastnameOrFirstname … where x.lastname = ?1 or x.firstname = ?2
Is,Equals findByFirstnameIs,findByFirstnameEquals … where x.firstname = ?1
Between findByStartDateBetween … where x.startDate between ?1 and ?2
LessThan findByAgeLessThan … where x.age < ?1
LessThanEqual findByAgeLessThanEqual … where x.age ⇐ ?1
GreaterThan findByAgeGreaterThan … where x.age > ?1
GreaterThanEqual findByAgeGreaterThanEqual … where x.age >= ?1
After findByStartDateAfter … where x.startDate > ?1
Before findByStartDateBefore … where x.startDate < ?1
IsNull findByAgeIsNull … where x.age is null
IsNotNull,NotNull findByAge(Is)NotNull … where x.age not null
Like findByFirstnameLike … where x.firstname like ?1
NotLike findByFirstnameNotLike … where x.firstname not like ?1
StartingWith findByFirstnameStartingWith … where x.firstname like ?1 (parameter bound with appended %)
EndingWith findByFirstnameEndingWith … where x.firstname like ?1 (parameter bound with prepended %)
Containing findByFirstnameContaining … where x.firstname like ?1 (parameter bound wrapped in %)
OrderBy findByAgeOrderByLastnameDesc … where x.age = ?1 order by x.lastname desc
Not findByLastnameNot … where x.lastname <> ?1
In findByAgeIn(Collection ages) … where x.age in ?1
NotIn findByAgeNotIn(Collection age) … where x.age not in ?1
TRUE findByActiveTrue() … where x.active = true
FALSE findByActiveFalse() … where x.active = false
IgnoreCase findByFirstnameIgnoreCase … where UPPER(x.firstame) = UPPER(?1)
常用如下:
And --- 等价于 SQL 中的 and 关键字,比如 findByUsernameAndPassword(String user, Striang pwd)
Or --- 等价于 SQL 中的 or 关键字,比如 findByUsernameOrAddress(String user, String addr)
Between --- 等价于 SQL 中的 between 关键字,比如 findBySalaryBetween(int max, int min)
LessThan --- 等价于 SQL 中的 "
GreaterThan --- 等价于 SQL 中的">",比如 findBySalaryGreaterThan(int min)
IsNull --- 等价于 SQL 中的 "is null",比如 findByUsernameIsNull()
IsNotNull --- 等价于 SQL 中的 "is not null",比如 findByUsernameIsNotNull()
NotNull --- 与 IsNotNull 等价
Like --- 等价于 SQL 中的 "like",比如 findByUsernameLike(String user)
NotLike --- 等价于 SQL 中的 "not like",比如 findByUsernameNotLike(String user)
OrderBy ---等价于 SQL 中的 "order by",比如 findByUsernameOrderBySalaryAsc(String user)
Not --- 等价于 SQL 中的 "! =",比如 findByUsernameNot(String user)
In --- 等价于 SQL 中的 "in",比如 findByUsernameIn(Collection userList) ,方法的参数可以是 Collection 类型,也可以是数组或者不定长参数
NotIn --- 等价于 SQL 中的 "not in",比如 findByUsernameNotIn(Collection userList) ,方法的参数可以是 Collection 类型,也可以是数组或者不定长
CrudRepository
CrudRepository 接口提供了最基本的对实体类的添删改查操作
T save(T entity); //保存单个实体
Iterable save(Iterable extends T> entities);//保存集合
T findOne(ID id); //根据id查找实体
boolean exists(ID id); //根据id判断实体是否存在
Iterable findAll(); //查询所有实体,不用或慎用!
long count(); //查询实体数量
void delete(ID id); //根据Id删除实体
void delete(T entity); //删除一个实体
void delete(Iterable extends T> entities); //删除一个实体的集合
void deleteAll(); //删除所有实体,不用或慎用!
JpaRepository
Modifier and Type Method and Description
void deleteAllInBatch()
Deletes all entities in a batch call.
void deleteInBatch(Iterable entities)
Deletes the given entities in a batch which means it will create a single Query.
List findAll()
List findAll(Example example)
List findAll(Example example, Sort sort)
List findAll(Sort sort)
List findAllById(Iterable ids)
void flush()
Flushes all pending changes to the database.
T getOne(ID id)
Returns a reference to the entity with the given identifier.
List saveAll(Iterable entities)
S saveAndFlush(S entity)
Saves an entity and flushes changes instantly.
findByXXX
@Autowired
private ArticleRepository articleRepository;
@RequestMapping("/mysql")
@ResponseBody
public String mysql() {
articleRepository.save(new Article("Neo", "Chen"));
for (Article article : articleRepository.findAll()) {
System.out.println(article);
}
Article tmp = articleRepository.findByTitle("Neo");
return tmp.getTitle();
}
@RequestMapping("/search")
@ResponseBody
public String search() {
for (Article article : articleRepository.findBySearch(1)) { System.out.println(article); }
List tmp = articleRepository.findBySearch(1L);
tmp.forEach((temp) -> {
System.out.println(temp.toString());
});
return tmp.get(0).getTitle();
}
传 Boolean 参数
package cn.netkiller.wallet.repository.fcoin;
import java.util.List;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.CrudRepository;
import cn.netkiller.wallet.domain.fcoin.Fcoin;;
public interface FcoinRepository extends CrudRepository {
Fcoin findOneByAddress(String address);
int countByAirdropFalse();
List findByAirdrop(boolean airdrop, Pageable pageable);
}
Eunm 传递枚举参数
package cn.netkiller.api.repository;
import org.springframework.data.repository.CrudRepository;
import cn.netkiller.api.domain.StatisticsHistory;
public interface StatisticsHistoryRepostitory extends CrudRepository {
public StatisticsHistory findByMemberIdAndStatisticsIdAndType(long member_id, long statistics_id,
StatisticsHistory.StatisticsType type);
}
@Autowired
private StatisticsHistoryRepostitory statisticsHistoryRepostitory;
statisticsHistoryRepostitory.findByMemberIdAndStatisticsIdAndType(uid, id, type);
count 操作
public interface UserRepository extends CrudRepository {
Long countByFirstName(String firstName);
@Transactional
Long deleteByFirstName(String firstName);
@Transactional
List removeByFirstName(String firstName);
}
OrderBy
public List findAllByOrderByIdAsc();
public List findAllByOrderByIdDesc();
List findByMemberIdOrderByIdDesc(int memberId, Pageable pageable);
GreaterThan
package schedule.repository;
import java.util.Date;
import org.springframework.data.repository.CrudRepository;
import common.domain.CmsTrash;
public interface CmsTrashRepository extends CrudRepository {
Iterable findBySiteIdAndTypeOrderByCtimeASC(int siteId, String string);
Iterable findBySiteIdAndTypeAndCtimeGreaterThanOrderByCtimeASC(int siteId, String string, Date date);
}
PageRequest 翻页操作
翻页返回数据可以选择 Iterable/List 或者 Page。
Iterable/List 只返回数据,不含页码等数据
Page 返回数据和页码等数据
PageRequest(int page, int size, Sort sort) Deprecated.
use PageRequest.of(int, int, Sort) instead.
PageRequest.of
package cn.netkiller.api.repository;
import java.util.List;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.CrudRepository;
import cn.netkiller.api.domain.RecentRead;
public interface RecentReadRepostitory extends CrudRepository {
List findByMemberId(long id, Pageable pageable);
}
Top 10 实例
@RequestMapping("/recent/read/list/{id}")
public List recentList(@PathVariable long id) {
int page = 0;
int limit = 10;
List recentRead = recentReadRepostitory.findByMemberId(id, new PageRequest(page, limit));
return recentRead;
}
注意 PageRequest(int page, int size) 在新版 Spring boot 2.x 中已经废弃请使用 PageRequest.of(page, size) 替代
List fcoins = fcoinRepository.findByAirdrop(false, PageRequest.of(0, size));
Pageable
接口实现 PagingAndSortingRepository
package api.repository.h5;
import org.springframework.data.repository.PagingAndSortingRepository;
import api.domain.User;
public interface GatherRepository extends PagingAndSortingRepository {
}
控制器添加 Pageable pageable 参数
@RequestMapping("/browse")
public ModelAndView browse(Pageable pageable) {
Page users = userRepository.findAll(pageable);
System.out.println(users.toString());
ModelAndView mv = new ModelAndView();
mv.addObject("users", users.getContent());
mv.addObject("number", users.getNumber());
mv.addObject("size", users.getSize());
mv.addObject("totalPages", users.getTotalPages());
mv.setViewName("table");
return mv;
}
排序 /browse?sort=id,desc
每页返回数量 /browse?size=10
返回第二页5条数据 /browse?size=5&page=1
返回第二页5条数据,ID倒序排序 /browse?size=5&page=1&sort=id,desc
Sort 排序操作操作
List findByName(String name, Sort sort);
Sort sort = new Sort(Direction.DESC, "id");
repostitory.findByName("Neo", sort);
Query
参数传递
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 {
public Page 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 {
@Query("SELECT p FROM Person p WHERE LOWER(p.lastName) = LOWER(:lastName)")
public List find(@Param("lastName") String lastName);
}
原生 SQL
public interface UserRepository extends JpaRepository {
@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);
@Query 与 Pageagble
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 {
@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 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 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 getAllRecord();
@Transactional
下面介绍一下@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 注解。
删除更新需要 @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 {
Page 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("注释");
}
锁 @Lock
interface UserRepository extends Repository {
// Plain query method
@Lock(LockModeType.READ)
List findByLastname(String lastname);netkiller:Spring Data with MySQL (实体关系)zhuanlan.zhihu.comnetkiller:Spring Data with MySQL (索引)zhuanlan.zhihu.com
netkiller:Spring Data with MySQL (实体定义)zhuanlan.zhihu.com
netkiller:Spring Data with MongoDB (四)zhuanlan.zhihu.com
netkiller:Spring Data with MongoDB (三)zhuanlan.zhihu.com
netkiller:Spring Data with MongoDB (二)zhuanlan.zhihu.com
netkiller:Spring Data with MongoDB (一)zhuanlan.zhihu.com
netkiller:Spring Data with Rediszhuanlan.zhihu.com
netkiller:Spring boot with i18nzhuanlan.zhihu.com