07月
29

发表于 • 字数统计 2152 • 被 366 人看爆

前言

7月份中旬入职了新公司，ORM框架是JPA，之前一直没用过JPA，有些陌生。开始都是使用el-admin生成的代码，没啥复杂业务，后面遇到了个需要动态SQL的业务，原本想使用JpaRepository，在JpaRepository内添加新的接口，加上@Query注解实现自定义SQL，但是又有动态条件要求所以在网上查了一下，发现以下写法，记录一下

具体实现


/**
 * @author terly
 * @description 服务实现
 * @date 2022-07-19
 **/
@Service
@RequiredArgsConstructor
public class TopicContentServiceImpl implements TopicContentService {
    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Map<String, Object> queryAllByApp(TopicContentQueryCriteria criteria, Pageable pageable) {
		//先组装sql
        StringBuilder dataSql = new StringBuilder("SELECT tc.* , tcf.* FROM tb_topic_content tc  LEFT JOIN (SELECT * FROM tb_topic_content_file ORDER BY tcf_id ASC) tcf ON tcf.tc_id=tc.tc_id ");
        StringBuilder countSql = new StringBuilder("SELECT count(1) FROM tb_topic_content tc ");
        StringBuilder whereSql = new StringBuilder(" WHERE 1 = 1 AND tc.tc_publish_status = 1");
        Map<String, Object> params = new HashMap<>();
        if (StrUtil.isNotEmpty(criteria.getFName())) {
            whereSql.append(" AND tc.f_name like :fName");
            params.put("fName", criteria.getFName());
        }
        if (ObjectUtil.isNotEmpty(criteria.getTiId())) {
            whereSql.append(" AND tc.ti_id = :tiId");
            params.put("tiId", criteria.getTiId());
        }
        dataSql.append(whereSql).append(" GROUP BY tc.tc_id");
        countSql.append(whereSql);
        
        //创建本地sql查询实例（sql预编译防止sql注入）
        Query dataQuery = entityManager.createNativeQuery(dataSql.toString());
       //如果想转为具体的bean，将 Transformers.ALIAS_TO_ENTITY_MAP 改为 Transformers.aliasToBean(Bean.class) 即可
       dataQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        Query countQuery = entityManager.createNativeQuery(countSql.toString());

        //传入参数
        for (String key : params.keySet()) {
            countQuery.setParameter(key, params.get(key));
            dataQuery.setParameter(key, params.get(key));
        }
        //设置分页
        dataQuery.setFirstResult((int) pageable.getOffset());
        dataQuery.setMaxResults(pageable.getPageSize());
        BigInteger count = (BigInteger) countQuery.getSingleResult();
        long total = count.longValue();
        List<Map<String,Object>> content2 = total > pageable.getOffset() ? dataQuery.getResultList() : ImmutableList.of();
        return PageUtil.toPage(content2, total);
    }
}

参考：
https://blog.csdn.net/yyj108317/article/details/118731219

分享到：