SpringBoot学习之旅(七)---JPA进阶篇之自定义查询 修改 分页

文章目录

前言源码下载其他文章查询关键字自定义Select和Update分页及自定义分页自定义分页分页查询的业务代码

前言

前一节SpringBoot学习之旅(六)—JPA操作MySql（基础篇）讲述了通过默认的Repository方法，实现对数据库的一些基础性的操作，而且灰常的优雅，代码看起来也很简洁；但是实际的开发中，不可能只有简单的基础操作，必然会存在比较复杂的操作，使用默认的一些方法肯定是不能满足我们的需要的，因此，下面我们一起来探讨一下，如何使用Jpa进行复杂的、丰富的数据库操作，从而满足我们日常开发过程中的需要。本文基于前一节的进阶，对于基础操作不熟悉的，可以先去看看，如果对Jpa的基础操作已经掌握了，可以直接阅读本章。

源码下载

点击开源中国下载源码

由于代码内容比较多多，建议优先下载代码，对着代码读更加快捷

其他文章

SpringBoot学习之旅(八)—JPA进阶篇之联表操作

查询关键字

自定义Select和Update

说明

开发过程中，我们不可能只会使用到索引进行数据查询，可能还涉及到其他的字段对数据进行筛选，那么我们只要遵循以上的关键字规范，即可进行更对的数据的查询

编码

定义Repository

package com.lupf.springbootjpa.repository;import com.lupf.springbootjpa.dbobject.UserInfo;import org.springframework.data.jpa.repository.JpaRepository;import org.springframework.data.jpa.repository.JpaSpecificationExecutor;import org.springframework.data.jpa.repository.Modifying;import org.springframework.data.jpa.repository.Query;import org.springframework.stereotype.Repository;import org.springframework.transaction.annotation.Transactional;import java.util.List;@Repositorypublic interface UserAdvRepository extends JpaRepository<UserInfo, Integer>, JpaSpecificationExecutor<UserInfo> {//根据电话号码查询用户UserInfo findUserInfoByTelphone(String telPhone);//根据用户名或者电话号码查询用户UserInfo findUserInfoByNameAndTelphone(String name, String telPhone);//根据用户id 名称 及电话号码查询用户UserInfo findUserInfoByIdAndNameAndTelphone(Integer id, String name, String telPhone);//根据名称 或者 电话号码查询用户List<UserInfo> findUserInfoByNameOrTelphone(String name, String telPhone);//根据id修改用户的头像@Transactional@Modifying(clearAutomatically = true)@Query(value = "update user_info set avatar =?1 where id = ?2", nativeQuery = true)int updateAvatarById(String avatar, Integer id);}

定义Service

package com.lupf.springbootjpa.service;import com.lupf.springbootjpa.service.model.UserInfoModel;import org.springframework.transaction.annotation.Transactional;import java.util.List;public interface UserAdvService {//根据电话号码查询用户UserInfoModel findUserInfoByTelphone(String telPhone);//根据用户名或者电话号码查询用户UserInfoModel findUserInfoByNameAndTelphone(String name, String telPhone);//根据用户id 名称 及电话号码查询用户UserInfoModel findUserInfoByIdAndNameAndTelphone(Integer id, String name, String telPhone);//根据名称 或者 电话号码查询用户List<UserInfoModel> findUserInfoByNameOrTelphone(String name, String telPhone);//根据id修改用户的邮箱@TransactionalUserInfoModel updateEmailById(String email, Integer id);//根据id修改用户的头像int updateAvatarById(String avatar, Integer id);}

service实现

注：这里并不是实际的业务开发，只是做的demo，因此，很多参数并没有进行详细的校验，实际开发中，请完善校验，增强代码的健壮性

package com.lupf.springbootjpa.service.impl;import com.lupf.springbootjpa.dbobject.UserInfo;import com.lupf.springbootjpa.repository.UserAdvRepository;import com.lupf.springbootjpa.service.UserAdvService;import com.lupf.springbootjpa.service.model.UserInfoModel;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import java.util.List;import java.util.Optional;import java.util.stream.Collectors;@Servicepublic class UserAdvServiceImpl extends UserServiceBase implements UserAdvService {@AutowiredUserAdvRepository userAdvRepository;@Overridepublic UserInfoModel findUserInfoByTelphone(String telPhone) {UserInfo userInfo = userAdvRepository.findUserInfoByTelphone(telPhone);return dbo2model(userInfo);}@Overridepublic UserInfoModel findUserInfoByNameAndTelphone(String name, String telPhone) {UserInfo userInfo = userAdvRepository.findUserInfoByNameAndTelphone(name, telPhone);return dbo2model(userInfo);}@Overridepublic UserInfoModel findUserInfoByIdAndNameAndTelphone(Integer id, String name, String telPhone) {UserInfo userInfo = userAdvRepository.findUserInfoByIdAndNameAndTelphone(id, name, telPhone);return dbo2model(userInfo);}@Overridepublic List<UserInfoModel> findUserInfoByNameOrTelphone(String name, String telPhone) {List<UserInfo> userInfos = userAdvRepository.findUserInfoByNameOrTelphone(name, telPhone);if (null != userInfos) {List<UserInfoModel> userInfoModels = userInfos.stream().map(userInfo -> dbo2model(userInfo)).collect(Collectors.toList());return userInfoModels;}return null;}@Overridepublic UserInfoModel updateEmailById(String email, Integer id) {Optional<UserInfo> userInfoOptional = userAdvRepository.findById(id);if (null != userInfoOptional && userInfoOptional.isPresent()) {UserInfo userInfo = userInfoOptional.get();userInfo.setEmail(email);UserInfo updateUserInfo = userAdvRepository.save(userInfo);return dbo2model(updateUserInfo);}return null;}@Overridepublic int updateAvatarById(String avatar, Integer id) {int updateNum = userAdvRepository.updateAvatarById(avatar, id);return updateNum;}}

单元测试

package com.lupf.springbootjpa.service.impl;import com.alibaba.fastjson.JSON;import com.lupf.springbootjpa.service.UserAdvService;import com.lupf.springbootjpa.service.model.UserInfoModel;import lombok.extern.slf4j.Slf4j;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.stereotype.Repository;import org.springframework.test.context.junit4.SpringRunner;import java.util.List;import static org.junit.Assert.*;@RunWith(SpringRunner.class)@SpringBootTest@Slf4jpublic class UserAdvServiceImplTest {@AutowiredUserAdvService userAdvService;@Testpublic void updateEmailById() {UserInfoModel userInfoModel = userAdvService.updateEmailById("123@", 18);log.info(JSON.toJSONString(userInfoModel));}@Testpublic void updateAvatarById() {int updateNum = userAdvService.updateAvatarById("/a.png", 18);log.info(String.valueOf(updateNum));}@Testpublic void findUserInfoByTelphone() {UserInfoModel userInfoModel = userAdvService.findUserInfoByTelphone("13800000000");log.info(JSON.toJSONString(userInfoModel));}@Testpublic void findUserInfoByNameAndTelphone() {UserInfoModel userInfoModel = userAdvService.findUserInfoByNameAndTelphone("张三", "13800000000");log.info(JSON.toJSONString(userInfoModel));}@Testpublic void findUserInfoByIdAndNameAndTelphone() {UserInfoModel userInfoModel = userAdvService.findUserInfoByIdAndNameAndTelphone(18, "张三", "13800000000");log.info(JSON.toJSONString(userInfoModel));}@Testpublic void findUserInfoByNameOrTelphone() {List<UserInfoModel> userInfoModels = userAdvService.findUserInfoByNameOrTelphone("张三", "13612345678");log.info(JSON.toJSONString(userInfoModels));}}

测试

这里为了节省篇幅，就不做一一的截图展示了，需要测试的，可自行下载代码，跑测试用例即可

分页及自定义分页

自定义分页

默认的分页，在使用起来，会降低代码的可读性和优雅性，为了能更好的满足开发需要，我们对部分分页操作进行自定义，所谓的自定义，更多的就是简化Specification的组装，让其更加的简便；

第一种方式：自定义执行操作类型的Specification帮助类

package com.lupf.springbootjpa.repository.custom;import org.springframework.data.jpa.domain.Specification;import javax.persistence.EntityManager;import javax.persistence.criteria.*;import javax.persistence.metamodel.Attribute;import javax.persistence.metamodel.EntityType;import java.util.*;/*** 根据这个类，定义我们需要操作的字段及操作方式*/public class CustomerOperators {private Map<String, CustomField> map = new HashMap<>();public CustomerOperators addOpes(String fieldName, CustomField.Operator operator, Object... value) {CustomField opes = map.get(fieldName);if (null == opes) {opes = new CustomField(fieldName, operator, value);map.put(fieldName, opes);}return this;}/*** 根据addName方法加入进来的对象 这里将其转换为Specification对象，用于参与查询** @param entityManager* @param example* @param <T>* @return*/public <T> Specification<T> byAuto(final EntityManager entityManager, final T example) {//获取实体的类型final Class<T> type = (Class<T>) example.getClass();return new Specification<T>() {@Overridepublic Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {//创建一个Predicate用于保存查询条件List<Predicate> predicates = new ArrayList<>();if (null != map && map.size() > 0) {//获取实体类的EntityType 可以从这个EntityType中获取实体中的数据EntityType<T> entity = entityManager.getMetamodel().entity(type);//对实体的数据做循环for (Attribute<T, ?> attr : entity.getDeclaredAttributes()) {String name = attr.getName();CustomField customPredicate = map.get(name);if (null != customPredicate) {Path p = root.get(name);Object[] vas = customPredicate.value;if (null != vas && vas.length > 0) {switch (customPredicate.operator) {case EQ:predicates.add(cb.equal(p, vas[0]));break;case LIKE://只有String类型的时候才去做模糊匹配if (vas[0] instanceof String && attr.getJavaType() == String.class) {predicates.add(cb.like(p, "%" + customPredicate.value[0] + "%"));}break;}}}}}// 将所有条件用 and 联合起来if (!predicates.isEmpty()) {return cb.and(predicates.toArray(new Predicate[predicates.size()]));}return cb.conjunction();}};}}

第二种方式：根据传递的对象中属性值作为查询条件的帮助类

package com.lupf.springbootjpa.repository.custom;import org.springframework.data.jpa.domain.Specification;import org.springframework.util.ReflectionUtils;import org.springframework.util.StringUtils;import static mon.collect.Iterables.toArray;import javax.persistence.EntityManager;import javax.persistence.criteria.CriteriaBuilder;import javax.persistence.criteria.CriteriaQuery;import javax.persistence.criteria.Predicate;import javax.persistence.criteria.Root;import javax.persistence.metamodel.Attribute;import javax.persistence.metamodel.EntityType;import javax.persistence.metamodel.SingularAttribute;import java.lang.reflect.Field;import java.util.ArrayList;import java.util.List;/*** 根据传递的T对象中的属性值 组装对应的predicates 用于作为分页的查询条件* String类型的是进行迷糊查询 其他类型的均使用equal*/public class CustomerSpecs {//这里定义一个返回值为Specification的方法byAuto,由于参数是泛型T,所以这个Specification适合任何数据类型//传递的EntityManager和T对象中包含的值作为查询条件public static <T> Specification<T> byAuto(final EntityManager entityManager, final T example) {//获取实体的类型final Class<T> type = (Class<T>) example.getClass();return new Specification<T>() {@Overridepublic Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {//创建一个Predicate用于保存查询条件List<Predicate> predicates = new ArrayList<>();//获取实体类的EntityType 可以从这个EntityType中获取实体中的数据EntityType<T> entity = entityManager.getMetamodel().entity(type);//对实体的数据做循环for (Attribute<T, ?> attr : entity.getDeclaredAttributes()) {//获取属性的属性值Object attrValue = getValue(example, attr);//判断属性值是否为null 是空的话就直接过掉if (attrValue != null) {//获取属性的的类型为java的String的时候if (attr.getJavaType() == String.class) {//判断值是否为null或者空字符串if (!StringUtils.isEmpty(attrValue)) {//如果是字符串的话,就在字符串的前后加上%，并将其添加到条件列表中//构造的属性为like，用于进行模糊查询predicates.add(cb.like(root.get(attrubute(entity, attr.getName(), String.class)), pattern((String) attrValue)));}} else {//如果是非字符串类型，那么就直接添加到条件列表中//构造的属性为equal，用于做相等查询predicates.add(cb.equal(root.get(attrubute(entity, attr.getName(), attr.getJavaType())), attrValue));}}}//将条件列表转换为Predicatereturn predicates.isEmpty() ? cb.conjunction() : cb.and(toArray(predicates, Predicate.class));}//通过反射获取实体对应属性的属性值private <T> Object getValue(T example, Attribute<T, ?> attr) {return ReflectionUtils.getField((Field) attr.getJavaMember(), example);}//获取当前属性的SingularAttribute，SingularAttribute包含了实体类中的某个单独的属性private <E, T> SingularAttribute<T, E> attrubute(EntityType<T> entityType, String fieldName, Class<E> fieldClass) {return entityType.getDeclaredSingularAttribute(fieldName, fieldClass);}};}static private String pattern(String str) {return "%" + str + "%";}}

帮助类

第一种方式的辅助工具类

package com.lupf.springbootjpa.repository.custom;/*** 需要进行筛选的属性、属性值、操作类型*/public class CustomField {/*** 操作类型*/public enum Operator {EQ,//相等LIKE,//模糊匹配//...//这里还可以存在更多的操作，可以根据具体的请求添加//添加之后需要在CustomerOperators.byAuto.toPredicate的switch方法中添加枚举对应的动作即可 ;}//属性名称public String fieldName;//操作类型public CustomField.Operator operator;//待匹配的值 可能需要传递多个值，因此这里使用数组public Object[] value;/*** @param fieldName* @param operator* @param value*/public CustomField(String fieldName, CustomField.Operator operator, Object... value) {this.fieldName = fieldName;this.operator = operator;this.value = value;}}

自定义Repository

CustomRepository

package com.lupf.springbootjpa.repository.custom;import org.springframework.data.domain.Page;import org.springframework.data.domain.Pageable;import org.springframework.data.jpa.repository.JpaRepository;import org.springframework.data.jpa.repository.JpaSpecificationExecutor;import org.springframework.data.repository.NoRepositoryBean;import java.io.Serializable;@NoRepositoryBean//标识当前接口不是领域类的接口public interface CustomRepository<T, ID extends Serializable> extends JpaRepository<T, ID>, JpaSpecificationExecutor<T> {//查找对象 根据传递的对象中的值查找，如果是String的数据，会进行模糊匹配Page<T> findByAuto(T example, Pageable pageable);Page<T> findByAuto(T example, CustomerOperators ops, Pageable pageable);}

CustomRepositoryImpl

package com.lupf.springbootjpa.repository.custom;import org.springframework.data.domain.Page;import org.springframework.data.domain.Pageable;import org.springframework.data.jpa.repository.support.SimpleJpaRepository;import javax.persistence.EntityManager;import java.io.Serializable;import static com.lupf.springbootjpa.repository.custom.CustomerSpecs.*;public class CustomRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements CustomRepository<T, ID> {private final EntityManager entityManager;/*** 构造方法** @param domainClass* @param emEntityManager对象*/public CustomRepositoryImpl(Class<T> domainClass, EntityManager em) {super(domainClass, em);this.entityManager = em;}/*** 匹配对象值的方法 除了String模糊匹配，其他有值的数据绝对匹配** @param example 数据对象* @param pageable 分页* @return*/@Overridepublic Page<T> findByAuto(T example, Pageable pageable) {return findAll(byAuto(entityManager, example), pageable);}/*** 根据Operators中添加的要操作的属性进行操作** @param example 数据对象* @param odu操作方式及属性值* @param pageable 分页* @return*/@Overridepublic Page<T> findByAuto(T example, CustomerOperators opes, Pageable pageable) {return findAll(odu.byAuto(entityManager, example), pageable);}}

RepositoryFactory工厂类

package com.lupf.springbootjpa.repository.custom;import org.springframework.data.jpa.repository.JpaRepository;import org.springframework.data.jpa.repository.support.JpaRepositoryFactory;import org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean;import org.springframework.data.jpa.repository.support.JpaRepositoryImplementation;import org.springframework.data.repository.core.RepositoryInformation;import org.springframework.data.repository.core.RepositoryMetadata;import org.springframework.data.repository.core.support.RepositoryFactorySupport;import javax.persistence.EntityManager;import java.io.Serializable;public class CustomRepositoryFactoryBean<T extends JpaRepository<S, ID>, S, ID extends Serializable> extends JpaRepositoryFactoryBean<T, S, ID> {public CustomRepositoryFactoryBean(Class<? extends T> repositoryInterface) {super(repositoryInterface);}@Overrideprotected RepositoryFactorySupport createRepositoryFactory(EntityManager entityManager) {return new CustomRepositoryFactory(entityManager);}/*** 自定义CustomRepository工厂类 继承自JpaRepositoryFactory*/private static class CustomRepositoryFactory extends JpaRepositoryFactory {//构造方法public CustomRepositoryFactory(EntityManager entityManager) {super(entityManager);}/*** 获取自定义的Repository** @param information* @param entityManager* @return*/@Overrideprotected JpaRepositoryImplementation<?, ?> getTargetRepository(RepositoryInformation information, EntityManager entityManager) {//实例化返回CustomRepositoryImplreturn new CustomRepositoryImpl<>(information.getDomainType(), entityManager);}/*** 重写getRepositoryBaseClass 指明当前Repository实现的类型** @param metadata* @return*/@Overrideprotected Class<?> getRepositoryBaseClass(RepositoryMetadata metadata) {return CustomRepositoryImpl.class;}}}

项目入口添加自定义Repository的指向

@EnableJpaRepositories(repositoryFactoryBeanClass = CustomRepositoryFactoryBean.class)@SpringBootApplicationpublic class SpringBootJpaApplication {public static void main(String[] args) {SpringApplication.run(SpringBootJpaApplication.class, args);}}

分页查询的业务代码

UserPageRepository

package com.lupf.springbootjpa.repository;import com.lupf.springbootjpa.dbobject.UserInfo;import com.lupf.springbootjpa.repository.custom.CustomRepository;public interface UserPageRepository extends CustomRepository<UserInfo, Integer> {}

定义Service

指定对应的查询操作

package com.lupf.springbootjpa.service;import com.lupf.springbootjpa.repository.custom.CustomerOperators;import com.lupf.springbootjpa.service.model.UserInfoModel;import org.springframework.data.domain.Page;import org.springframework.data.domain.Pageable;public interface UserPageService {//使用自定义的CustomerSpecs进行对象属性匹配查询Page<UserInfoModel> getUserByPage(UserInfoModel userInfoModel, Pageable pageable);//使用自带的Specification 通过new对象组装查询条件查询Page<UserInfoModel> getUserByAge(UserInfoModel userInfoModel, Pageable pageable);// 使用自定义的CustomerOperators 指定需要查询的参数及动作进行查询Page<UserInfoModel> getUserByPage(UserInfoModel userInfoModel, CustomerOperators ops, Pageable pageable);}

实现Service接口

package com.lupf.springbootjpa.service.impl;import com.lupf.springbootjpa.dbobject.UserInfo;import com.lupf.springbootjpa.repository.UserPageRepository;import com.lupf.springbootjpa.repository.custom.CustomField.Operator;import com.lupf.springbootjpa.repository.custom.CustomerOperators;import com.lupf.springbootjpa.service.UserPageService;import com.lupf.springbootjpa.service.model.UserInfoModel;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.data.domain.Page;import org.springframework.data.domain.Pageable;import org.springframework.data.jpa.domain.Specification;import org.springframework.stereotype.Service;import javax.persistence.criteria.*;@Servicepublic class UserPageServiceImpl extends UserServiceBase implements UserPageService {@AutowiredUserPageRepository userPageRepository;@Overridepublic Page<UserInfoModel> getUserByPage(UserInfoModel userInfoModel, Pageable pageable) {UserInfo userInfo = model2dbo(userInfoModel);Page<UserInfo> userInfos = userPageRepository.findByAuto(userInfo, pageable);return pagedbo2model(userInfos);}@Overridepublic Page<UserInfoModel> getUserByAge(UserInfoModel userInfoModel, Pageable pageable) {Specification<UserInfo> specification = (Specification<UserInfo>) (root, query, cb) -> {Path<Integer> agePath = root.get("age");Predicate agep = cb.gt(agePath, userInfoModel.getAge());//这里还可以添加更多的查询条件//....Predicate p = cb.and(agep);return p;};Page<UserInfo> userInfos = userPageRepository.findAll(specification, pageable);return pagedbo2model(userInfos);}@Overridepublic Page<UserInfoModel> getUserByPage(UserInfoModel userInfoModel, CustomerOperators ops, Pageable pageable) {ops.addOpes("name", Operator.LIKE, userInfoModel.getName());ops.addOpes("age", Operator.EQ, userInfoModel.getAge());UserInfo userInfo = new UserInfo();Page<UserInfo> userInfos = userPageRepository.findByAuto(userInfo, ops, pageable);return pagedbo2model(userInfos);}}

创建测试用例

package com.lupf.springbootjpa.service.impl;import com.alibaba.fastjson.JSON;import com.lupf.springbootjpa.repository.custom.CustomerOperators;import com.lupf.springbootjpa.service.UserPageService;import com.lupf.springbootjpa.service.model.UserInfoModel;import lombok.extern.slf4j.Slf4j;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.data.domain.Page;import org.springframework.data.domain.PageRequest;import org.springframework.test.context.junit4.SpringRunner;@RunWith(SpringRunner.class)@SpringBootTest@Slf4jpublic class UserPageServiceImplTest {@AutowiredUserPageService userPageService;@Testpublic void getUserByPage() {PageRequest pageRequest = new PageRequest(0, 3);UserInfoModel userInfoModel = new UserInfoModel();// userInfoModel.setSex(new Byte("2"));userInfoModel.setAge(12);userInfoModel.setName("测试添加1");Page<UserInfoModel> userInfoModels = userPageService.getUserByPage(userInfoModel, pageRequest);log.info(JSON.toJSONString(userInfoModels));}@Testpublic void getUserByAge() {PageRequest pageRequest = new PageRequest(0, 3);UserInfoModel userInfoModel = new UserInfoModel();userInfoModel.setAge(12);Page<UserInfoModel> userInfoModels = userPageService.getUserByAge(userInfoModel, pageRequest);log.info(JSON.toJSONString(userInfoModels));}@Testpublic void getUserByPage1() {PageRequest pageRequest = new PageRequest(0, 3);UserInfoModel userInfoModel = new UserInfoModel();CustomerOperators opu = new CustomerOperators();userInfoModel.setAge(12);userInfoModel.setName("测试添加1");Page<UserInfoModel> userInfoModels = userPageService.getUserByPage(userInfoModel, opu, pageRequest);log.info(JSON.toJSONString(userInfoModels));}}

测试结果

到此，自定义查询、修改、分页操作完成。

下一节会讲解JPA进行联表操作。