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

2022-02-24 07:50 2482阅读 0赞

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