hibernate-jpa复杂查询，in

为什么80%的码农都做不了架构师？>>>   

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要传集合，我一开始传递的

StringUtils.join(recordIds,",");

被坑的不浅。。。。。。

转载于:https://my.oschina.net/kezhen/blog/1583539