1、添加 EntityManager
@PersistenceContext
private EntityManager entityManager;
2、hql 查询
//拼接sql
String hql = findStatisHql( serverId, channel,startTime,endTime);
// 查询数据
Query query = entityManager.createQuery(hql);
// 根据返回的结果来定义接收数据类型(对象,集合,基本数据类型等),我这里查询统计数据,所以 list<Object[]> 接收,下方有示例代码
List<Object[]> objArray = query.getResultList();
就是这么简单,不明白继续看下方示例代码
2.1 hql 查询方法示例
/**
* hql 查询统计数据
*
* @author wangsong
* @date 2019年9月10日
*/
@SuppressWarnings("unchecked")
public Map<String, Object> findStatis(Integer serverId, String channel,String startTime,String endTime) {
// 拼接sql
String hql = findStatisHql( serverId, channel,startTime,endTime);
// 查询数据
Query query = entityManager.createQuery(hql);
List<Object[]> objArray = query.getResultList();
// 处理结果数据
Map<String, Object> mapVal = new HashMap<String, Object>();
List<Object> stepsList = new ArrayList<Object>();
List<Object> conutList = new ArrayList<Object>();
for (Object[] obj : objArray) {
stepsList.add(obj[0]);
conutList.add(obj[1]);
}
mapVal.put("stepsList", stepsList);
mapVal.put("conutList", conutList);
return mapVal;
}
2.2 拼接sql 方法示例(拼接出的sql 同原生sql)
/**
* 拼接hql 查询语句 <br/>
* hql = select steps,count(*) from WastageRate where 1=1 and serverId=1 and channel='home' and time <= '2019-09-13' and time >= '2019-09-10 00:00:00' GROUP BY steps
* @author wangsong
* @date 2019年9月10日
*/
public String findStatisHql(Integer serverId, String channel,String startTime,String endTime) {
// 动态拼接sql
String hql = "select steps,count(*) from WastageRate where 1=1";
if (serverId != null && serverId != -1) {
hql += " and serverId=" + serverId;
}
if (StringUtils.isNotEmpty(channel) && !channel.equals("-1")) {
hql += " and channel='" + channel +"'";
}
if (StringUtils.isNotEmpty(startTime)) {
hql += " and time >= '"+startTime +"'";
}
if (StringUtils.isNotEmpty(endTime)) {
hql += " and time <= '" + endTime +"'";
}
hql += " GROUP BY steps";
return hql;
}