一 首先熟悉几种jpa中的接口Repository,CrudRepository,PagingAndSortingRepository,JpaRepository,JpaSpecificationExecutor

Repository是一个空接口:

@Indexed
public interface Repository<T, ID> { }

  

CrudRepository继承Repository:

@NoRepositoryBean
public interface CrudRepository<T, ID extends Serializable> extends Repository<T, ID> {
<S extends T> S save(S var1); //保存
<S extends T> Iterable<S> save(Iterable<S> var1);//批量保存
T findOne(ID var1);//根据id 查询一个对象。返回对象本身,当对象不存在时,返回null
boolean exists(ID var1)//根据id 判断对象是否存在
Iterable<T> findAll();;//查询所有的对象
Iterable<T> findAllById(Iterable<ID> var1);//根据id列表查询所有的对象
long count();//计算对象的总个数
void delete(ID var1);//根据id 删除
void delete(T var1);//删除一个对象
void delete(Iterable<? extends T> var1);//批量删除,集合对象(后台执行时,一条一条删除)
void deleteAll();//删除所有(后台执行时,一条一条删除)
}

  

PagingAndSortingRepository继承CrudRepository,另提供了两个方法,实现了分页和排序的功能:

@NoRepositoryBean
public interface PagingAndSortingRepository<T, ID extends Serializable> extends CrudRepository<T, ID> {
Iterable<T> findAll(Sort var1);// 仅排序
Page<T> findAll(Pageable var1);// 分页和排序
}

  

JpaRepository继承PagingAndSortingRepository:

@NoRepositoryBean
public interface JpaRepository<T, ID extends Serializable> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {
List<T> findAll(); //查询所有对象,返回List
List<T> findAll(Sort var1);//查询所有对象,并排序,返回List
List<T> findAll(Iterable<ID> var1);//根据id列表查询所有的对象,返回List
<S extends T> List<S> save(Iterable<S> var1);//批量保存,并返回对象List
void flush();//强制缓存与数据库同步
<S extends T> S saveAndFlush(S var1);//保存并强制同步数据库
void deleteInBatch(Iterable<T> var1);//批量删除集合对象(后台执行时,生成一条语句执行,用多个or条件)
void deleteAllInBatch();//删除所有(执行一条语句,如:delete from user)
T getOne(ID var1);//根据id 查询一个对象,返回对象的引用(区别于findOne),当对象不存时,返回引用不是null,但各个属性值是null
<S extends T> List<S> findAll(Example<S> var1);//根据实例查询
<S extends T> List<S> findAll(Example<S> var1, Sort var2);//根据实例查询,并排序
}

  

JpaSpecificationExecutor做动态SQL查询L:

/**
* Interface to allow execution of {@link Specification}s based on the JPA criteria API.
*/
public interface JpaSpecificationExecutor<T> {
T findOne(Specification<T> var1); //通过Specification查询单个对象
List<T> findAll(Specification<T> var1); //通过Specification查询集合
Page<T> findAll(Specification<T> var1, Pageable var2); //通过Specification分页查询
List<T> findAll(Specification<T> var1, Sort var2); //通过Specification排序查询
long count(Specification<T> var1);//通过Specification统计
}

  

二 常用使用方式

1 用JpaRepository接口进行单表命名规范查询:

And

findByLastnameAndFirstname

… where x.lastname = ?1 and x.firstname = ?2

Or

findByLastnameOrFirstname

… where x.lastname = ?1 or x.firstname = ?2

Is,Equals

findByFirstname,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<Age> ages)

… where x.age in ?1

NotIn

findByAgeNotIn(Collection<Age> ages)

… 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)

 

2 @Query自定义注解使用

public interface UserRepository extends JpaRepository<User, Long> {
//这种"?1"占位符要求和参数位置对应,推荐使用别名
//nativeQuery=true说明为原生SQL
//@Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true)
@Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = :emailAddress", nativeQuery = true)
User findByEmailAddress(String emailAddress); //delete、update example
@Modifying
@Query("update User u set u.firstname = :firstname where u.lastname = :lastname")
int setFixedFirstnameFor(String firstname, String lastname); @Modifying
@Query("delete from User u where user.role.id = :roleId")
void deleteInBulkByRoleId(long roleId);
}

  

3 利用JpaSpecificationExecutor实现动态SQL

Page<DemoUser> result = userRepository.findAll(
new Specification<DemoUser>(){
@Override
public Predicate toPredicate(Root<DemoUser> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
List<Predicate> predicates = new ArrayList<>(); //关联查询 inner
Join<DemoUser, DemoDept> join = root.join("demoDept", JoinType.INNER);
//主表条件 like 动态条件
if (StringUtils.isNotBlank(demoUserDtoQueryDto.getUserName())) {
predicates.add(builder.like(root.get("userName"), //属性
"%" + demoUserDtoQueryDto.getUserName() + "%")); //值
} //in使用测试
CriteriaBuilder.In<Long> in = builder.in(root.get("id"));
Arrays.asList(1L, 2L, 3L).forEach(e -> {
in.value(e);
});
predicates.add(in); //从表条件 id=1
predicates.add(builder.equal(join.get("id"), 1)); //从表条件查询 这里测试 //or使用 (password=4 or sex=1)
predicates.add(builder.or(builder.equal(root.get("password"), "44"),
builder.equal(root.get("sex"), "1"))); //对象类型比较要求比较器
//<Y extends Comparable<? super Y>> Predicate greaterThanOrEqualTo(Expression<? extends Y> x, Expression<? extends Y> y);
// builder.greaterThanOrEqualTo();
// builder.lessThanOrEqualTo();
// builder.greaterThan();
// builder.lessThan();
//直接用与数值类型的值进行比较
//Predicate gt(Expression<? extends Number> x, Expression<? extends Number> y);
// builder.gt();
// builder.le();
return builder.and(predicates.toArray(new Predicate[predicates.size()]));
// query.where(predicates.toArray(new Predicate[predicates.size()]));
// return null;
}
},
new PageRequest(page, pageSize); //分页参数 还可以添加排序字段
); // ---------------------------------------------------------------------------------------
// Entity
@Entity
@Table(name = "demo_user")
@Data
public class DemoUser extends BaseDomain implements Serializable { @Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String userName;
private String password;
private Short sex; @ManyToOne
@JoinColumn(name = "dept_id")
@NotFound(action = NotFoundAction.IGNORE)
private DemoDept demoDept; } // ---------------------------------------------------------------------------------------
// DAO
public interface UserRepository extends JpaRepository<DemoUser, Long>, JpaSpecificationExecutor<DemoUser> {}

  

05-11 22:07