使用原生SQL的原因:
1、由于在DAO中继承Repository,必须使用自定义实体类型;对接其他系统数据库,只做数据查询,并不想在本项目创建实体。
2、使用@Query方法,如果查询条件复杂,并不好用;
创建工具类:
@Component
@Transactional
public class EntityManagerUtil<T> {
static Logger logger = LoggerFactory.getLogger(EntityManagerUtil.class);
@PersistenceContext
private EntityManager entityManager;
//返回map
public List<Map<String, Object>> getListMap(String sql){
logger.info("sql:" + sql);
Query nativeQuery = entityManager.createNativeQuery(sql);
nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List resultList = nativeQuery.getResultList();
return resultList;
}
//返回自定义实体类
public List<T> nativeQueryResult(String sql, Class clazz) {
sql = Normalizer.normalize(sql, Normalizer.Form.NFKC);
sql = sql.replaceAll(".*([';]+|(--)+).*", "");
Query query = entityManager.createNativeQuery(sql);
List<T> queryList = query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(clazz)).list();
return queryList;
}
// 返回数据条数,用作分页
public Integer getCount(String sql){
logger.info("sql:" + sql);
Query nativeQuery = entityManager.createNativeQuery(sql);
nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
Map<String,Object> map = (Map<String,Object>)nativeQuery.getSingleResult();
if(map!= null && map.get("data_num") != null) {
return Integer.parseInt(map.get("data_num").toString());
}
return 0;
}
//分页工具
public static Integer getPageStart(Integer pageIndex,Integer pageSize) {
Integer pageStart = (pageIndex-1) * pageSize;
return pageStart;
}
}
在Service中引用使用获取列表和分页数据:
@RequiredArgsConstructor
@Service
@Transactional
public class MapServiceImpl implements MapService {
@Autowired
private EntityManagerUtil entityManagerUtil;
// 查询列表
@Override
public List<Map<String, Object>> list() {
String sql = "SELECT * FROM test ";
return entityManagerUtil.getListMap(sql);
}
//实现分页查询
@Override
public LimitDataFO listData(VisuMapSamplingSearchFO search, Integer pageIndex, Integer pageSize) {
LimitDataFO<Map<String,Object>> limitDataFO = new LimitDataFO<Map<String,Object>>();
String sqlCount = "SELECT COUNT(1) AS data_num FROM table ";
String sqlSelect = "SELECT * FROM table ";
StringBuffer sql = new StringBuffer("");
sql.append(" WHERE 1=1 ");
limitDataFO.setTotal(entityManagerUtil.getCount(sqlCount + sql.toString()));
if(dataType == 1) {
sql.append(" ORDER BY sampleDate DESC ");
} else if(dataType == 2 ) {
sql.append(" ORDER BY IF(ISNULL(reportPath),0,1) DESC, IF(ISNULL(reportDate),0,1) DESC ");
} else if(dataType == 3) {
sql.append(" ORDER BY IF(ISNULL(reportDate),0,1) DESC ");
}
sql.append(" LIMIT ").append(entityManagerUtil.getPageStart(pageIndex, pageSize)).append(",").append(pageSize);
limitDataFO.setItems(entityManagerUtil.getListMap(sqlSelect + sql.toString()));
return limitDataFO;
}
}
分页实体:
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class LimitDataFO<T> {
private Collection<T> items;
private long total;
private int totalPage;
}