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

最新推荐文章于 2022-08-23 10:04:34 发布

原创最新推荐文章于 2022-08-23 10:04:34 发布 · 1w 阅读

17 ·

CC 4.0 BY-SA版权

文章标签：

#SpringData

SpringData 专栏收录该内容

1 篇文章

订阅专栏

 
 @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;

    }