jpa复杂查询总结

一.基本方法

1.  findAll（）；没有参数可以查询该表中的所有数据;

2.  findAll（Sort sort）可以传入一个排序的条件，根据该条件将查询到的数据进行排序;

     Sort sort =new Sort(Sort.Direction.DESC,"time");

3. findAll（Pageable pageable）传入一个分页条件，将查询到的条件按照传入的分页条件        显示出来;

    Pageable pageable =new PageRequest(0,5);

    也可在分页中加排序：Pageable pageable =new PageRequest(0,5,sort);

4.findAll（Specification<object> specification, Pageable pageable）条件分页查询

            Specification<object> specification下面会讲到

二.自定义查询

1.自定义方法（要符合jpa接口规范）

findByxxx（）

findByxxxAndxxxAndxxx...（）

2.自定义sql

nativeQuery = true一定要写，这里写的是sql放到数据库里能直接执行字段和表名对应的都是数据库的字段和表名。

@Query(value = "SELECT cr.project_number as projectNumber,cr.file_number as fileNumber,cr.role_id as projectRoleId," +
        "r.project_role as projectRole,r.responsibility_text as responsibilityText," +
        "GROUP_CONCAT(cr.contacts_id) as contactsId,GROUP_CONCAT(c.user_full_name)as userFullName " +
        "FROM project_role_contacts_relation cr left JOIN contacts_info c on cr.contacts_id=c.id " +
        "LEFT JOIN project_role r on cr.role_id=r.id WHERE cr.project_number=?1 AND cr.file_number=?2 " +
        "GROUP BY cr.role_id",nativeQuery = true)
List<Object[]> roleAndDutyVoList(String projectNumber, String fileNumber);

当不写nativeQuery = true时，是hql，对应的字段是实体类的字段和类名

@Query("select t from ProjectSampleRelationEntity t where t.fileNumber=?1 order by t.insertTime")
List<ProjectSampleRelationEntity> findByFileNumber(String fileNumber);

3.通过继承JpaSpecificationExecutor实现多条件复杂查询

• Root root 这个参数是根对象，也就是要把条件封装到哪个对象中

• CriteriaQuery cq这个参数封装的是查询的关键字例如 orderby 等，下个例子会用到这个参数
• CriteriaBuilder cb这个参数则是用来封装条件对象的，如果返回null就说明不需要任何条件

1.直接带入userId，和fanId作为条件进行查询

List<UsersFans> findByUserIdAndFansId(final String id, final String fansId){
    return usersFansDao.findAll(new Specification<UsersFans>() {
        @Override
        public Predicate toPredicate(Root root, CriteriaQuery cq, CriteriaBuilder cb) {
            List<Predicate> list =new ArrayList<>();
            Predicate p1 = cb.equal(root.get("userId").as(String.class), id);
            Predicate p2 = cb.equal(root.get("fanId").as(String.class), fansId);
            list.add(p1);
            list.add(p2);
            Predicate[] predicate = new Predicate[list.size];
            list.toArray(predicate)
            return cb.and(predicate);
        }
    });
}

2.分页查询

 Page<Comments> commentsPage = commentsDao.findAll(new Specification<Comments>() {
            @Override
            public Predicate toPredicate(Root<Comments> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
                //将实体类中的videoId作为参数传入
                Predicate p1 = cb.equal(root.get("videoId").as(String.class), videoId);
                //通过这个条件进行查询
                cq.where(p1);
                //通过创建实现对查询结果进行降序排列
                cq.orderBy(cb.desc(root.get("createTime").as(Date.class)));

                return cq.getRestriction();
            }
        }, pageable);

三.总结

其实用jpa主要就是想直接调用规范好的接口，简化开发，但是面试复杂查询时，jpa并不是最好的选择，但是又不得不用jpa的话，我比较倾向于自定义sql的写法，接下来重点讲一下对于多表进行，分组或者聚合等复杂查询怎么用（再复杂的查询，只有sql会写就能搞定）

1.Repository层写法

这里是我业务里选对三张表进行连表，分组查询

@Query(value = "SELECT cr.project_number as projectNumber,cr.file_number as fileNumber,cr.role_id as projectRoleId," +
        "r.project_role as projectRole,r.responsibility_text as responsibilityText," +
        "GROUP_CONCAT(cr.contacts_id) as contactsId,GROUP_CONCAT(c.user_full_name)as userFullName " +
        "FROM project_role_contacts_relation cr left JOIN contacts_info c on cr.contacts_id=c.id " +
        "LEFT JOIN project_role r on cr.role_id=r.id WHERE cr.project_number=?1 AND cr.file_number=?2 " +
        "GROUP BY cr.role_id",nativeQuery = true)
List<Object[]> roleAndDutyVoList(String projectNumber, String fileNumber);

2.返回的类型需要用自定义vo接收

@Data
@AllArgsConstructor  //（有参构造）
@NoArgsConstructor   //（无参构造）
public class RoleAndDutyVo {

    private String projectNumber;
    private String fileNumber;
    private String projectRoleId;
    private String projectRole;
    private String responsibilityText;
    private String contactsId;
    private String userFullName;

}

注意：vo中构造方法字段和Repository中sql所查询的字段的顺序、类型必须一致

3.把List<Object[]>转化成List<RoleAndDutyVo>

用一个转化工具类：

import lombok.extern.slf4j.Slf4j;
 
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
/**
 * @author 954L
 * @create 2019/10/30 17:27
 */

@Slf4j
public class EntityUtils {
 
    /**
     * 将数组数据转换为实体类
     * 此处数组元素的顺序必须与实体类构造函数中的属性顺序一致
     *
     * @param list  数组对象集合
     * @param clazz 实体类
     * @param <T>   实体类
     * @param model 实例化的实体类
     * @return 实体类集合
     */
    public static <T> List<T> castEntity(List<Object[]> list, Class<T> clazz, Object model) {
        List<T> returnList = new ArrayList<T>();
        if (list.isEmpty()) return returnList;
        Object[] co = list.get(0);
        List<Map> attributeInfoList = getFiledsInfo(model);
        Class[] c2 = new Class[attributeInfoList.size()];
        if (attributeInfoList.size() != co.length) {
            return returnList;
        }
        for (int i = 0; i < attributeInfoList.size(); i++) {
            c2[i] = (Class) attributeInfoList.get(i).get("type");
        }
        try {
            for (Object[] o : list) {
                Constructor<T> constructor = clazz.getConstructor(c2);
                returnList.add(constructor.newInstance(o));
            }
        } catch (Exception ex) {
            log.error("实体数据转化为实体类发生异常：异常信息：{}", ex.getMessage());
            return returnList;
        }
        return returnList;
    }
 
    private static Object getFieldValueByName(String fieldName, Object modle) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method method = modle.getClass().getMethod(getter, new Class[]{});
            Object value = method.invoke(modle, new Object[]{});
            return value;
        } catch (Exception e) {
            return null;
        }
    }
 
    private static List<Map> getFiledsInfo(Object model) {
        Field[] fields = model.getClass().getDeclaredFields();
        List<Map> list = new ArrayList(fields.length);
        Map infoMap = null;
        for (int i = 0; i < fields.length; i++) {
            infoMap = new HashMap(3);
            infoMap.put("type", fields[i].getType());
            infoMap.put("name", fields[i].getName());
            infoMap.put("value", getFieldValueByName(fields[i].getName(), model));
            list.add(infoMap);
        }
        return list;
    }
}

实际业务实现：

public List<RoleAndDutyVo> roleContactsList(String projectNumber, String fileNumber) {
    List<Object[]> objects = projectRoleContactsRepository.roleAndDutyVoList(projectNumber, fileNumber);
    List<RoleAndDutyVo> vos = EntityUtils.castEntity(objects, RoleAndDutyVo.class, new RoleAndDutyVo());

}