hibernate-jpa复杂查询，in

最新推荐文章于 2022-03-14 15:04:18 发布

原创最新推荐文章于 2022-03-14 15:04:18 发布 · 135 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #mysql #mybatis #数据库 #hibernate

本文介绍了一个复杂的SQL查询构建过程，该查询用于从数据库中检索特定条件下的记录列表。通过使用Java和JPQL，文章详细展示了如何动态地添加过滤条件，并提供了参数设置和结果转换的具体实现。

public List<MiniVisionFaceRecorderVO> getRecorderListByCondition(Integer applyId, List<Integer> recordIds, FaceSignUpSearchVO vo) {
    String sql = "select t.recordId,t.name,IFNULL(ext.is_vip,0) vip,ext.face_url imgFile,t.job position,t.mobile phoneNumber,t.position companyName,t.codedata qrCode,t1.face_id faceId " +
            "from t_template_record t LEFT JOIN t_template_record_ext ext on t.recordid=ext.template_record_id LEFT JOIN t_template_record_face t1 on t.recordid=t1.recordid WHERE t.APPLYID=:applyId and t.status=1 and t.vilidstatus!=2";

    if (CollectionUtils.isNotEmpty(recordIds)) {
        sql += " and t.recordid in :recordIds";
    }

    if (StringUtils.isNotBlank(vo.getName())) {
        sql += " and t.name like '% :name %' ";
    }

    if (StringUtils.isNotBlank(vo.getMobile())) {
        sql += " and t.name like '% :mobile %' ";
    }

    if (vo.getStatus() != null) {
        sql += " and t.status =:status";
    }

    if (vo.getVilidStatus() != null) {
        sql += " and t.vilidStatus =:vilidStatus";
    }

    if (vo.getIsPush() != null && vo.getIsPush() == IsPushEnum.YES.getType()) {
        sql += " and t1.is_push =:isPush";
    }

    Query query = entityManager.createNativeQuery(sql.toString());
    query.setParameter("applyId", applyId);

    if (CollectionUtils.isNotEmpty(recordIds)) {
        query.setParameter("recordIds", recordIds);
    }

    if (StringUtils.isNotBlank(vo.getName())) {
        query.setParameter("name", vo.getName());
    }

    if (StringUtils.isNotBlank(vo.getMobile())) {
        query.setParameter("name", vo.getMobile());
    }

    if (vo.getStatus() != null) {
        query.setParameter("status", vo.getStatus());
    }

    if (vo.getVilidStatus() != null) {
        query.setParameter("vilidStatus", vo.getVilidStatus());
    }

    if (vo.getIsPush() != null) {
        query.setParameter("isPush", vo.getIsPush());
    }

    query.unwrap(SQLQuery.class)
            .addScalar("recordId", IntegerType.INSTANCE)
            .addScalar("name", StringType.INSTANCE)
            .addScalar("vip", BooleanType.INSTANCE)
            .addScalar("imgFile", StringType.INSTANCE)
            .addScalar("position", StringType.INSTANCE)
            .addScalar("phoneNumber", StringType.INSTANCE)
            .addScalar("companyName", StringType.INSTANCE)
            .addScalar("qrCode", StringType.INSTANCE)
            .addScalar("faceId", StringType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(MiniVisionFaceRecorderVO.class));
    return query.getResultList();
}

recordIds要传集合，我一开始传递的