SpringData JPA动态拼接sql语句实现动态的多表条件查询-蒲公英云

SpringData JPA动态拼接sql语句实现动态的多表条件查询

@Autowired
 private EntityManager entityManager;
public List<Object[]> findSignFileAndPosition(int pageNum, TbSignFile search) {
        PageRequest pageRequest = new PageRequest(pageNum, 10);
        StringBuffer sql = new StringBuffer("SELECT sf.file_code, sup.type " +
                "FROM tb_sign_file sf LEFT JOIN tb_sign_user_position sup " +
                "ON sf.file_code = sup.file_id " +
                "where 1=1 ");
        Map<String, Object> map = new HashMap<String, Object>();
        int i = 1;
        if (StringUtils.isNotBlank(search.getFileType())) {
            sql.append(" and sf.file_type=");
            sql.append("?" + i);
            map.put(i + "", search.getFileType());
            i++;
        }
        if (StringUtils.isNotBlank(search.getSignerType())) {
            sql.append(" and sf.signer_type=");
            sql.append("?" + i);
            map.put(i + "", search.getSignerType());
            i++;
        }
        if (StringUtils.isNotBlank(search.getSignMethod())) {
            sql.append(" and sf.sign_method=");
            sql.append("?" + i);
            map.put(i + "", search.getSignMethod());
            i++;
        }
        if (StringUtils.isNotBlank(search.getStatus())) {
            sql.append(" and sf.status=");
            sql.append("?" + i);
            map.put(i + "", search.getStatus());
            i++;
        }
        sql.append(" order by sf.update_time desc");
        String sqlStr = sql.toString();
        String count = "SELECT count(1) ";
        String substring = sqlStr.substring(0, sql.indexOf("FROM"));
        String countSql = sqlStr.replace(substring, count);
        Query query = entityManager.createNativeQuery(sqlStr);
        Query countQuery = entityManager.createNativeQuery(countSql);
        for (String key : map.keySet()) {
            query.setParameter(key, map.get(key));
            countQuery.setParameter(key, map.get(key));
        }
        query.setFirstResult((pageNum -1) * 10);
        query.setMaxResults(10);
        long total = ((BigInteger)countQuery.getSingleResult()).longValue();
        List list = query.getResultList();
        return list;
    }