| 知乎专栏 |
Repository： 仅仅是一个标识，没有任何方法，方便Spring自动扫描识别 CrudRepository： 继承Repository，实现了一组CRUD相关的方法 PagingAndSortingRepository： 继承CrudRepository，实现了一组分页排序相关的方法 JpaRepository： 继承PagingAndSortingRepository，实现一组JPA规范相关的方法
Spring Data JPA 为此提供了一些表达条件查询的关键字：
Keyword Sample JPQL snippet 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 中的 "<"，比如 findBySalaryLessThan(int max) 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<String> userList) ，方法的参数可以是 Collection 类型，也可以是数组或者不定长参数 NotIn --- 等价于 SQL 中的 "not in"，比如 findByUsernameNotIn(Collection<String> userList) ，方法的参数可以是 Collection 类型，也可以是数组或者不定长
Modifier and Type Method and Description void deleteAllInBatch() Deletes all entities in a batch call. void deleteInBatch(Iterable<T> entities) Deletes the given entities in a batch which means it will create a single Query. List<T> findAll() <S extends T> List<S> findAll(Example<S> example) <S extends T> List<S> findAll(Example<S> example, Sort sort) List<T> findAll(Sort sort) List<T> findAllById(Iterable<ID> ids) void flush() Flushes all pending changes to the database. T getOne(ID id) Returns a reference to the entity with the given identifier. <S extends T> List<S> saveAll(Iterable<S> entities) <S extends T> S saveAndFlush(S entity) Saves an entity and flushes changes instantly.
CrudRepository 接口提供了最基本的对实体类的添删改查操作
T save(T entity); //保存单个实体 Iterable<T> save(Iterable<? extends T> entities);//保存集合 T findOne(ID id); //根据id查找实体 boolean exists(ID id); //根据id判断实体是否存在 Iterable<T> findAll(); //查询所有实体,不用或慎用! long count(); //查询实体数量 void delete(ID id); //根据Id删除实体 void delete(T entity); //删除一个实体 void delete(Iterable<? extends T> entities); //删除一个实体的集合 void deleteAll(); //删除所有实体,不用或慎用!
接口实现 PagingAndSortingRepository
package api.repository.h5;
import org.springframework.data.repository.PagingAndSortingRepository;
import api.domain.User;
public interface GatherRepository extends PagingAndSortingRepository<User, Integer> {
}
控制器添加 Pageable pageable 参数
@RequestMapping("/browse")
public ModelAndView browse(Pageable pageable) {
Page<User> 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;
}
如果 Repository 继承了 PagingAndSortingRepository 你会发 CrudRepository 中的 save 等方法不能使用了，我的解决方法是写两个 Repository
一个 CURD 的 ChatRepository 放在 cn.netkiller.repository
package cn.netkiller.repository;
import cn.netkiller.domain.Chat;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface ChatRepository extends CrudRepository<Chat, String> {
List<Chat> findAllBySession(String session);
Chat findOneBySession(String session);
}
另一个分页的 PagingAndSortingRepository 放在 cn.netkiller.repository.pageable
package cn.netkiller.repository.pageable;
import cn.netkiller.domain.Chat;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface ChatPageableRepository extends PagingAndSortingRepository<Chat, String> {
Page<Chat> findAllByDevice(String device, Pageable pageable);
}
@RequestMapping(value = "/list", method=RequestMethod.GET)
public Page<Blog> getEntryByPageable1(@PageableDefault( sort = { "id" }, direction = Sort.Direction.DESC)
Pageable pageable) {
return blogRepository.findAll(pageable);
}
@RequestMapping(value = "/blog", method=RequestMethod.GET)
public Page<Blog> getEntryByPageable(@PageableDefault(value = 15, sort = { "id" }, direction = Sort.Direction.DESC)
Pageable pageable) {
return blogRepository.findAll(pageable);
}
@RequestMapping(value = "/list", method=RequestMethod.GET)
public Page<Blog> getEntryByPageable2(@PageableDefault Pageable pageable) {
return blogRepository.findAll(pageable);
}
@ModelAttribute("users")
public Page<User> users(@PageableDefault(size = 5) Pageable pageable) {
return userManagement.findAll(pageable);
}
我们只需要在方法的参数中直接定义一个pageable类型的参数，当Spring发现这个参数时，Spring会自动的根据request的参数来组装该pageable对象，Spring支持的request参数如下：
page，第几页，从0开始，默认为第0页
size，每一页的大小，默认为20
sort，排序相关的信息，以property,property(,ASC|DESC)的方式组织，例如sort=firstname&sort=lastname,desc表示在按firstname正序排列基础上按lastname倒序排列
这样，我们就可以通过url的参数来进行多样化、个性化的查询，而不需要为每一种情况来写不同的方法了。
通过url来定制pageable很方便，但唯一的缺点是不太美观，因此我们需要为pageable设置一个默认配置，这样很多情况下我们都能够通过一个简洁的url来获取信息了。
Spring提供了@PageableDefault帮助我们个性化的设置pageable的默认配置。例如@PageableDefault(value = 15, sort = { "id" }, direction = Sort.Direction.DESC)表示默认情况下我们按照id倒序排列，每一页的大小为15。
@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<Article> tmp = articleRepository.findBySearch(1L);
tmp.forEach((temp) -> {
System.out.println(temp.toString());
});
return tmp.get(0).getTitle();
}
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, String> {
Fcoin findOneByAddress(String address);
int countByAirdropFalse();
List<Fcoin> findByAirdrop(boolean airdrop, Pageable pageable);
}
package cn.netkiller.api.repository;
import org.springframework.data.repository.CrudRepository;
import cn.netkiller.api.domain.StatisticsHistory;
public interface StatisticsHistoryRepostitory extends CrudRepository<StatisticsHistory, Long> {
public StatisticsHistory findByMemberIdAndStatisticsIdAndType(long member_id, long statistics_id,
StatisticsHistory.StatisticsType type);
}
@Autowired private StatisticsHistoryRepostitory statisticsHistoryRepostitory; statisticsHistoryRepostitory.findByMemberIdAndStatisticsIdAndType(uid, id, type);
public interface UserRepository extends CrudRepository<User, Long> {
Long countByFirstName(String firstName);
}
@Transactional
Long deleteByFirstName(String firstName);
@Transactional
List<User> removeByFirstName(String firstName);
Iterable<PicturePsychoanalysis> findByAnalysisIsNull();
使用 And / Or 链接多个条件
Iterable<Chat> findByAnswerIsNullOrAudioIsNullOrPsychoanalysisIsNull();
IsNotNull
Page<Picture> findByImageIsNotNullAndStoryIsNotNullAndAudioIsNotNull(Pageable pageable);
package cn.netkiller.repository;
import cn.netkiller.domain.Lora;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import java.util.Optional;
import java.util.Set;
@Repository
public interface LoraRepository extends CrudRepository<Lora, Integer> {
Optional<Lora> findByKeywordIn(Set<String> keyword);
Iterable<Lora> findAllByKeywordIn(Set<String> keyword);
}
查询接口
@GetMapping("one")
public Optional<Lora> keywordTest(@RequestParam("key") Set<String> keyword) {
Optional<Lora> lora = loraRepository.findByKeywordIn(keyword);
return lora;
}
@GetMapping("all")
public Iterable<Lora> keywordTest1(@RequestParam("key") Set<String> keyword) {
Iterable<Lora> lora = loraRepository.findAllByKeywordIn(keyword);
return lora;
}
Page<Picture> findByShareTrueAndImageIsNotNullAndStoryIsNotNullAndAudioIsNotNull(Pageable pageable);
// DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime now = LocalDateTime.now();
// LocalDateTime lastDay = now.minusDays(1);
LocalDateTime start = now.minusDays(1);
LocalDateTime end = now.minusMinutes(5);
log.info("Query picture from {} to {}", start, end);
// iterable = pictureService.findBySrtIsNullAndCtimeAfter(end);
iterable = pictureService.findBySrtIsNullAndCtimeBefore(end);
iterable.forEach(picture -> {
String appId = businessService.queryAppIdByDevice(picture.getDevice());
aigcPipeline.setAigc(Aigc.builder().appId(appId).device(picture.getDevice()).session(picture.getSession()).prompt(picture.getPrompt()).story(picture.getStory()).audio(picture.getAudio()).build());
aigcPipeline.subtitles(srt -> {
pictureService.updateSrt(aigcPipeline.getAigc().getSession(), srt);
sessionStatusService.progress(aigcPipeline.getAigc().getSession(), "识别字幕", srt);
});
});
public Iterable<Picture> findBySrtIsNullAndCtimeBefore(LocalDateTime lastDay) {
Iterable<Picture> iterable = pictureRepository.findBySrtIsNullAndCtimeBefore(lastDay);
return iterable;
}
package cn.netkiller.repository;
import cn.netkiller.domain.story.Picture;
import cn.netkiller.repository.model.PictureGroupList;
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 java.time.LocalDateTime;
import java.util.Optional;
@Repository
public interface PictureRepository extends CrudRepository<Picture, Long> {
Picture findAllBySession(String session);
Optional<Picture> findBySession(String session);
Page<Picture> findAll(Pageable pageable);
Iterable<Picture> findBySrtIsNullAndCtimeBefore(LocalDateTime lastDay);
}
public List<StudentEntity> findAllByOrderByIdAsc(); public List<StudentEntity> findAllByOrderByIdDesc(); List<RecentRead> findByMemberIdOrderByIdDesc(int memberId, Pageable pageable);
package schedule.repository;
import java.util.Date;
import org.springframework.data.repository.CrudRepository;
import common.domain.CmsTrash;
public interface CmsTrashRepository extends CrudRepository<CmsTrash, Integer> {
Iterable<CmsTrash> findBySiteIdAndTypeOrderByCtimeASC(int siteId, String string);
Iterable<CmsTrash> findBySiteIdAndTypeAndCtimeGreaterThanOrderByCtimeASC(int siteId, String string, Date date);
}
复杂的 GreaterThan / LessThan 应用
@Cacheable(value = "picture:share:next", key = "#cursor", unless = "#result == null")
public Optional<Picture> shareNext(int cursor) {
return pictureRepository.findFirstByShareTrueAndImageIsNotNullAndStoryIsNotNullAndAudioIsNotNullAndIdGreaterThan(cursor);
}
@Cacheable(value = "picture:share:previous", key = "#cursor", unless = "#result == null")
public Optional<Picture> sharePrevious(int cursor) {
return pictureRepository.findFirstByShareTrueAndImageIsNotNullAndStoryIsNotNullAndAudioIsNotNullAndIdLessThanOrderByIdDesc(cursor);
}
List<UserModel> findByName(String name, Sort sort);
Sort sort = new Sort(Direction.DESC, "id");
repostitory.findByName("Neo", sort);
userRepository.findAll(Sort.by(Sort.Direction.ASC, "name"));
userRepository.findAll(Sort.by("LENGTH(name)"));
userRepository.findAll(Sort.by(Sort.Direction.ASC, "name"));
userRepository.findAll(Sort.by("LENGTH(name)"));
Page 返回数据和页码等数据
PageRequest(int page, int size, Sort sort) Deprecated. use PageRequest.of(int, int, Sort) instead.
package cn.netkiller.repository;
import cn.netkiller.domain.Picture;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import java.util.Optional;
@Repository
public interface PictureRepository extends JpaRepository<Picture, Long> {
Picture findAllBySession(String session);
Optional<Picture> findOneBySession(String session);
Page<Picture> findAll(Pageable pageable);
}
public Page<Picture> page(Pageable pageable) {
return pictureRepository.findAll(pageable);
}
@GetMapping("/{device}/page")
public Mono<Page<Picture>> page(@PathVariable String device, Pageable pageable) {
return Mono.just(pictureService.page(pageable));
}
排序 /picture/test/page?sort=id,desc 每页返回数量 /picture/test/page?size=10 返回第二页5条数据 /picture/test/page?size=5&page=1 返回第二页5条数据，ID倒序排序 /picture/test/page?size=5&page=1&sort=id,desc
curl -X 'GET' \ 'http://localhost:8080/picture/test/page?page=0&size=1&sort=id' \ -H 'accept: */*'
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<RecentRead, Long> {
List<RecentRead> findByMemberId(long id, Pageable pageable);
}
Top 10 实例
@RequestMapping("/recent/read/list/{id}")
public List<RecentRead> recentList(@PathVariable long id) {
int page = 0;
int limit = 10;
List<RecentRead> recentRead = recentReadRepostitory.findByMemberId(id, new PageRequest(page, limit));
return recentRead;
}
翻页返回数据可以选择 Iterable/List 或者 Page。
Iterable/List 只返回数据，不含页码等数据
注意 PageRequest(int page, int size) 在新版 Spring boot 2.x 中已经废弃请使用 PageRequest.of(page, size) 替代
List<Fcoin> fcoins = fcoinRepository.findByAirdrop(false, PageRequest.of(0, size));
public interface MemberRepository extends JpaRepository<User, Integer>, UserRepository {
...
}
我们并不希望每次返回所有字段，JPA默认查询等价 “SELECT * FROM TABLE”
package cn.netkiller.repository.model;
public interface PictureGroupList {
Integer getId();
String getImage();
String getThumbnail();
String getAudio();
String getStory();
}
public Iterable<PictureGroupList> findByPictureGroupId(int pictureGroupId) {
return pictureRepository.findByPictureGroupId(pictureGroupId);
}
package cn.netkiller.domain;
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 java.io.Serializable;
@Entity
@Table(uniqueConstraints = {@UniqueConstraint(name = "UK_device_hobby", columnNames = {"device_id", "hobby"})})
@DynamicUpdate
@DynamicInsert
@Data
@Comment("用户画像")
public class UserPersona implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", unique = true, nullable = false, insertable = false, updatable = false, columnDefinition = "int unsigned")
@Comment("主键")
private Integer id;
@ManyToOne
@Comment("设备")
@JoinColumn(name = "device_id", unique = false, nullable = false, insertable = true, updatable = false)
@JsonIgnore
private Device device;
@Comment("兴趣爱好")
private String hobby;
@Comment("权重")
private Integer weight;
public interface UserPersonaNativeQuery {
// String getId();
String getHobby();
String getWeight();
}
}
解放方法是，定一个接口 UserPersonaNativeQuery
package cn.netkiller.repository;
import cn.netkiller.domain.UserPersona;
import jakarta.transaction.Transactional;
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.List;
@Repository
public interface UserPersonaRepository extends CrudRepository<UserPersona, Integer> {
@Transactional
@Modifying
@Query(nativeQuery = true, value = "INSERT INTO user_persona (device_id, hobby,weight) VALUES ((SELECT id FROM device where sn=:device) , :hobby, 1) ON DUPLICATE KEY UPDATE weight = weight + 1;")
int updateUserPersona(@Param("device") String device, @Param("hobby") String hobby);
List<UserPersona.UserPersonaNativeQuery> findByDeviceId(Integer id);
}
在仓库中指定该接口
[
{
"weight": "10",
"hobby": "小朋友"
},
{
"weight": "10",
"hobby": "妈妈"
},
{
"weight": "10",
"hobby": "自行车"
},
{
"weight": "5",
"hobby": "校园"
},
{
"weight": "5",
"hobby": "欢声笑语"
},
{
"weight": "6",
"hobby": "雪景"
},
{
"weight": "4",
"hobby": "皮筋"
},
{
"weight": "4",
"hobby": "同学们"
},
{
"weight": "4",
"hobby": "爬杆"
},
{
"weight": "4",
"hobby": "女同学"
},
{
"weight": "4",
"hobby": "羽毛球"
},
{
"weight": "4",
"hobby": "歌曲"
},
{
"weight": "1",
"hobby": "冰雪"
},
{
"weight": "1",
"hobby": "包饺子"
},
{
"weight": "1",
"hobby": "哈尔滨"
},
{
"weight": "1",
"hobby": "冻梨"
},
{
"weight": "1",
"hobby": "冰雕"
}
]
@ManyToOne 字段的巧妙处理，正常情况 @ManyToOne 会返回一个对象，如果我们返回List列表，这个对象就会重复出现，首先使用 @JsonIgnore 将其屏蔽，然后创建两个方法 private void setDeviceId(Integer deviceId) 和 private Integer getDeviceId()
package cn.netkiller.domain;
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 java.io.Serializable;
@Entity
@Table(uniqueConstraints = {@UniqueConstraint(name = "UK_device_hobby", columnNames = {"device_id", "hobby"})})
@DynamicUpdate
@DynamicInsert
@Data
@Comment("用户画像")
public class UserPersona implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", unique = true, nullable = false, insertable = false, updatable = false, columnDefinition = "int unsigned")
@Comment("主键")
private Integer id;
@ManyToOne
@Comment("设备")
@JoinColumn(name = "device_id", unique = false, nullable = false, insertable = true, updatable = false)
@JsonIgnore
private Device device;
@Comment("兴趣爱好")
private String hobby;
@Comment("权重")
private Integer weight;
private void setDeviceId(Integer deviceId) {
this.setDevice(Device.builder().id(deviceId).build());
}
private Integer getDeviceId() {
return this.getDevice().getId();
}
public interface UserPersonaNativeQuery {
String getId();
String getHobby();
Integer getWeight();
Integer getDeviceId();
}
}