前言
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