spring jpa规则 JPA排序 分页 常用

spring data jpa版本 2.1.2

<dependency>
​    <groupId>org.springframework.data</groupId>
​    <artifactId>spring-data-jpa</artifactId>
​    <version>2.1.2.RELEASE</version>
</dependency>

官网链接：查询关键字说明：https://docs.spring.io/spring-data/jpa/docs/2.1.2.RELEASE/reference/html/#repository-query-keywords
官网链接：返回类型说明：https://docs.spring.io/spring-data/jpa/docs/2.1.2.RELEASE/reference/html/#repository-query-return-types

常用示例

// 根据调解查询排序的前几个，查询第一个
Person findFirstByUserIdOrderByCreateTimeDesc(String userId);
Person findTopByUserIdOrderByCreateTimeDesc(String userId);
Person findFirst10ByUserIdOrderByCreateTimeDesc(String userId);
Person findTop10ByUserIdOrderByCreateTimeDesc(String userId);

// 排序
List<Person> findByNameOrderByCreateTime(String name);
// 查询所有的，根据createTime排序
List<Person> findAllByOrderByCreateTime();

// 删除
long deleteByName(String name);
List<Person> removeByName(String name);

// 判断是否存在
boolean existsById(String id);

// 查询条数
long countByLastname(String lastname);

SQL查询操作

传参

?N 和 ?xxx不能混用
可以使用@Param 设置别名

Person getByUserId(@Param(“userId”) String userId);

示例

// 使用原生sql查询
@Query(value = "select * from person p where p.user_id = :userId", nativeQuery = true))
Person getByUserId(String userId);

// 分页，org.springframework.data.domain.Pageable
@Query(     value = "select * from person p where p.age = :age",
       countQuery = "select count(1) from person p where p.age = :age",
      nativeQuery = true))
Page<Person> getByAge(int age, Pageable pageable);

// 条件查询
@Query(value = "select * from person where if(?1 !='',x1=?1,1=1) and if(?2 !='',x2=?2,1=1) and if(?3 !='',x3=?3,1=1) ",nativeQuery = true)
List<XXX> find(String X1,String X2,String X3);

更新操作

// user.role.id并没有写错
@Modifying
@Query("delete from User u where user.role.id = ?1")
void deleteByRoleId(long roleId);

@Modifying
@Query("update User u set u.name=:name,age=18 where u.roleId = :roleId")
void updateNameByRoleId(String name, long roleId);

查询关键字说明

逻辑关键字	JPA表达式关键字	方法命名	sql where字句
AND	And	findByNameAndPwd	where name= ?1 and pwd =?2
OR	Or	findByNameOrSex	where name= ? 1or sex=?2
AFTER	After, IsAfter	findByIdAfter	where id > ?
BEFORE	Before, IsBefore	findByIdBefore	where id < ?
CONTAINING	Containing, IsContaining, Contains	findByNameContaining	where name like ‘%?%’
BETWEEN	Between, IsBetween	findByIdBetween	where id between ? and ?
ENDING_WITH	EndingWith, IsEndingWith, EndsWith	findByNameEndingWith	where name like ‘%?’
EXISTS	Exists
FALSE	False, IsFalse	findByAaaFalse	where aaa = false
GREATER_THAN	GreaterThan, IsGreaterThan	findByIdGreaterThan	where id > ?
GREATER_THAN_EQUALS	GreaterThanEqual, IsGreaterThanEqual	findByIdGreaterThanEquals	where id > = ?
IN	In, IsIn	findByIdIn(Collection<?> c)	where id in (?)
IS	Is, Equals, (or no keyword)	findByIdEquals	where id= ?
IS_EMPTY	IsEmpty, Empty
IS_NOT_EMPTY	IsNotEmpty, NotEmpty
IS_NOT_NULL	NotNull, IsNotNull	findByNameNotNull	where name is not null
IS_NULL	Null, IsNull	findByNameIsNull	where name is null
LESS_THAN	LessThan, IsLessThan	findByIdLessThan	where id < ?
LESS_THAN_EQUAL	LessThanEqual, IsLessThanEqual	findByIdLessThanEquals	where id <= ?
LIKE	Like, IsLike	findByNameLike	where name like ?
NEAR	Near, IsNear
NOT	Not, IsNot	findByNameNot	where name <> ?
NOT_IN	NotIn, IsNotIn	findByIdNotIn(Collection<?> c)	where id not in (?)
NOT_LIKE	NotLike, IsNotLike	findByNameNotLike	where name not like ?
REGEX	Regex, MatchesRegex, Matches
STARTING_WITH	StartingWith, IsStartingWith, StartsWith	findByNameStartsWith	where name like ‘?%’
TRUE	True, IsTrue	findByAaaTue	where aaa = true
WITHIN	Within, IsWithin

返回类型说明

Return type	Description
void	Denotes no return value.
Primitives	Java primitives.
Wrapper types	Java wrapper types.
T	An unique entity. Expects the query method to return one result at most. If no result is found, null is returned. More than one result triggers an IncorrectResultSizeDataAccessException.
Iterator<T>	An Iterator.
Collection<T>	A Collection.
List<T>	A List.
Optional<T>	A Java 8 or Guava Optional. Expects the query method to return one result at most. If no result is found, Optional.empty() or Optional.absent() is returned. More than one result triggers an IncorrectResultSizeDataAccessException.
Option<T>	Either a Scala or Javaslang Option type. Semantically the same behavior as Java 8’s Optional, described earlier.
Stream<T>	A Java 8 Stream.
Future<T>	A Future. Expects a method to be annotated with @Async and requires Spring’s asynchronous method execution capability to be enabled.
CompletableFuture<T>	A Java 8 CompletableFuture. Expects a method to be annotated with @Async and requires Spring’s asynchronous method execution capability to be enabled.
ListenableFuture	A org.springframework.util.concurrent.ListenableFuture. Expects a method to be annotated with @Async and requires Spring’s asynchronous method execution capability to be enabled.
Slice	A sized chunk of data with an indication of whether there is more data available. Requires a Pageable method parameter.
Page<T>	A Slice with additional information, such as the total number of results. Requires a Pageable method parameter.
GeoResult<T>	A result entry with additional information, such as the distance to a reference location.
GeoResults<T>	A list of GeoResult<T> with additional information, such as the average distance to a reference location.
GeoPage<T>	A Page with GeoResult<T>, such as the average distance to a reference location.
Mono<T>	A Project Reactor Mono emitting zero or one element using reactive repositories. Expects the query method to return one result at most. If no result is found, Mono.empty() is returned. More than one result triggers an IncorrectResultSizeDataAccessException.
Flux<T>	A Project Reactor Flux emitting zero, one, or many elements using reactive repositories. Queries returning Flux can emit also an infinite number of elements.
Single<T>	A RxJava Single emitting a single element using reactive repositories. Expects the query method to return one result at most. If no result is found, Mono.empty() is returned. More than one result triggers an IncorrectResultSizeDataAccessException.
Maybe<T>	A RxJava Maybe emitting zero or one element using reactive repositories. Expects the query method to return one result at most. If no result is found, Mono.empty() is returned. More than one result triggers an IncorrectResultSizeDataAccessException.
Flowable<T>	A RxJava Flowable emitting zero, one, or many elements using reactive repositories. Queries returning Flowable can emit also an infinite number of elements.

词汇表 Glossary

• AOP

Aspect oriented programming

面向切面编程

• Commons DBCP

Commons DataBase Connection Pools - a library from the Apache foundation that offers pooling implementations of the DataSource interface.

通用的数据库连接池 - 由Apache基金会的库为DataSource接口提供连接池的实现

• CRUD

Create, Read, Update, Delete - Basic persistence operations.

对数据库的增删改查 - 基本的持久化操作

• DAO

Data Access Object - Pattern to separate persisting logic from the object to be persisted

数据访问对象 - 一种将持久化逻辑和持久化对象分开的模式

• Dependency Injection 依赖注入

Pattern to hand a component’s dependency to the component from outside, freeing the component to lookup the dependent itself. For more information, see http://en.wikipedia.org/wiki/Dependency_Injection.

模式将组件的依赖关系从外部传递给组件，从组件本身查找从属依赖。

• JPA

Java Persistence API

Java 持久化 API