springmv mysql_Spring Data with MySQL (增删插改)

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.comnetkiller：Spring Data with MySQL (实体定义)​zhuanlan.zhihu.comnetkiller：Spring Data with MongoDB (四)​zhuanlan.zhihu.comnetkiller：Spring Data with MongoDB (三)​zhuanlan.zhihu.comnetkiller：Spring Data with MongoDB (二)​zhuanlan.zhihu.comnetkiller：Spring Data with MongoDB (一)​zhuanlan.zhihu.comnetkiller：Spring Data with Redis​zhuanlan.zhihu.comnetkiller：Spring boot with i18n​zhuanlan.zhihu.com