Spring data Jpa查询

1.继承自JpaRepository<T,ID>后，可以直接在service使用父接口的查询方法。如findOne,findById,findAll等方法进行查询一个，多个或分页查询操作

父接口自带方法可看：https://blog.csdn.net/a972669015/article/details/88610480

	@Override
	public UserAccount findUserAccountById(Long id) {
		Optional<UserAccount> opt = userAccountRepository.findById(id);
		return opt.get();
	}


	@Override
	public Page<UserAccount> findAllUserAccount(Pageable pageable) {
		return userAccountRepository.findAll(pageable);
	}

2.根据实体类属性进行查询某个实体类，在Repository接口中建立对应的findByXXX方法（XXX为属性名称，首字母大写），不用写方法体。

UserAccount findByUserName(String username);
	
UserAccount findByAlertMobile(String alertMobile);

3.自己写sql语句进行自定义查询，在方法上加@Query（）注释。比如根据手机号查询用户名，可以在方法上加@Query（）注释，在括号里写要执行的sql，需要传入参数的字段可以用占位符：表示。

nativeQuery=true表示使用原生sql,不写的话就表示不使用原生sql，表名就是实体类名，表字段名就是实体类属性名 

@Query("SELECT id FROM useraccount WHERE userName = :userName")
Long findIdByUsername(@Param("userName")String userName);

4.使用Specification进行动态查询，有时遇到的业务场景，查询条件不固定，需要进行动态查询，这时候继承自JpaRepository的方法和自己写的sql已经无法满足需求，就可以使用Specification接口，我们只需要重写toPredicate方法即可，例如：

Specification接口源代码如下：

@SuppressWarnings("deprecation")
public interface Specification<T> extends Serializable {

	@Nullable
	Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder);
}

自定义动态查询：

持久层：findAll传入两个参数，spec设立查询条件，pageable设置分页信息。

Page<Article> findAll(Specification<Article> spec, Pageable pageable);

服务层：

	@Override
	public Page<Article> findAllPageByTitleAndDate((String title, Timestamp startDate, Timestamp endDate, Integer pageNumber, Integer pageSize)) {
		//规格定义
        Specification<Article> specification = new Specification<UserAccount>() {
			private static final long serialVersionUID = 5555932480624669312L;

		/**
			 * 构造断言
             * @param root 实体对象引用
             * @param query 规则查询对象
             * @param cb 规则构建对象
             * @return 断言
             */
            @Override
            public Predicate toPredicate(Root<Article> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                List<Predicate> predicates = new ArrayList<>();
                //Join<Article,Webonesite> join=root.join("webonesite",JoinType.INNER);
                if (StringUtils.isNotBlank(title)) {
                    Predicate title1 = cb.like(root.get("title").as(String.class), "%" +title + "%");
                    predicates.add(title1);
                }
                if (startDate != null || endDate != null) {
                    Predicate publishDate = cb.between((root.get("publishDate")), startDate, endDate);
                    predicates.add(publishDate);
                }
                Predicate[] p = new Predicate[predicates.size()];
                return cb.and(predicates.toArray(p));
            }

        };
        //分页 前端从1开始，jpa从0开始
        Pageable pageable = PageRequest.of(pageNumber - 1, pageSize，new Sort(Sort.Direction.ASC, "id")); 
        //查询
        return repo.findAll(spec, pageable);
	}

也可以简略写成lambda,里面写toPredicate的方法体：

Specification<Article> spec = (Specification<Article>) (root, query, cb) -> {
            
};

5.动态拼接sql,使用Query动态查询。

/**
     * 根据用户名或者机构名或者创建者查询
     * @param param
     * @return
     */
    public Map<String,Object> findSysLogByName(Map<String,String> param, SearchVo searchVo, PageVo pageVo){
        String userName = param.get("userName");
        String orgName = param.get("orgName");
        String createBy = param.get("createBy");

        int pageNumber = pageVo.getPageNumber();
        int pageSize = pageVo.getPageSize();
        String order = pageVo.getOrder();
        String sort = pageVo.getSort();

        String startDate = searchVo.getStartDate();
        String endDate = searchVo.getEndDate();

        StringBuilder strsql = new StringBuilder();

        strsql.append("SELECT a.name,a.description,a.cost_time As costTime,a.ip,a.ip_info As ipInfo," +
                "a.title,a.request_type As requestType,a.request_param As requestParam,a.request_url As requestUrl," +
                "a.user_id As userId,a.org_id As orgId,a.log_type As logType,a.create_by As createBy," +
                "a.create_time As createTime,a.update_by As updateBy,a.update_time As updateTime " +
                "FROM t_sys_sys_log a WHERE 1=1");

        if(userName != null && !"".equals(userName.trim())){
            strsql.append(" and user_id = (SELECT id FROM t_sys_user b WHERE name like concat('%',:userName,'%'))");
        }

        if(orgName != null && !"".equals(orgName.trim())){
            strsql.append(" and org_id = (SELECT id FROM t_sys_org c WHERE name like concat('%',:orgName,'%'))");
        }

        if(createBy != null && !"".equals(createBy.trim())){
            strsql.append(" and create_by = :createBy");
        }

        if(startDate != null && !"".equals(startDate.trim()) && endDate != null && !"".equals(endDate.trim())){
            strsql.append(" and (a.create_time between :startDate and :endDate) ");
        }

        if(order != null && !"".equals(order.trim()) && sort != null && !"".equals(sort.trim())){
            strsql.append(" ORDER BY a.create_time = :order :sort");
        }

        Query query = entityManager.createNativeQuery(strsql.toString());

        if(userName!= null && ! "".equals(userName.trim())){
            query.setParameter("userName",userName);
        }
        if(orgName != null && ! "".equals(orgName.trim())){
            query.setParameter("orgName",orgName);
        }

        if(createBy != null && ! "".equals(createBy.trim())){
            query.setParameter("createBy",createBy);
        }

        if(startDate != null && ! "".equals(startDate.trim()) && endDate != null && ! "".equals(endDate.trim())){
            query.setParameter("startDate",startDate);
            query.setParameter("endDate",endDate);
        }

        if(order != null && !"".equals(order.trim()) && sort != null && !"".equals(sort.trim())){
            query.setParameter("order",order);
            query.setParameter("sort",sort);
        }

        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        Map<String,Object> map = new HashMap<>();

        List<SysLog> resultList = query.getResultList();

        map.put("totalElements",resultList.size());
        query.setFirstResult(Integer.valueOf(pageSize)*(Integer.valueOf(pageNumber)-1));
        query.setMaxResults(Integer.valueOf(pageSize));

        map.put("content", PageUtil.listToPage(pageVo,resultList));

        log.info("当前页从第{}条开始",Integer.valueOf(pageSize)*(Integer.valueOf(pageNumber)-1));
        log.info("当前页显示的最大数量为{}条",Integer.valueOf(pageSize));
        log.info("当前页查询结果{}条",resultList.size());

        return map;
    }