EntityManager 数据查询

//添加注解
@PersistenceContext
private EntityManager em;

//一般形式, 与数据库表关联的bean
@Entity
@Table(name = "c_s_appagent")
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
@Data
@NoArgsConstructor
@AllArgsConstructor
public class AppAgent {}

//查询方法
@SuppressWarnings("unchecked")
public List<AppAgent> queryBySql(String execsql, Map<String, String> params) {
Query query = null;
query = em.createNativeQuery(execsql, AppAgent.class);
if(params!=null) {
Iterator<Entry<String, String>> it = params.entrySet().iterator();
while(it.hasNext()) {
Entry<String, String> entry = it.next();
query.setParameter(entry.getKey(), entry.getValue());
}
}

List<AppAgent> ret = query.getResultList();
return ret;
}
//

//映射为普通的pojo对象(无table关联)
@Data
@NoArgsConstructor
@AllArgsConstructor
public class SiteRouteDto {}

//查询方法
@SuppressWarnings("unchecked")
public List<SiteRouteDto> queryBySql(String sql, Map<String, String> params) {
Query query = null;
query = em.createNativeQuery(sql);
if(params!=null) {
Iterator<Entry<String, String>> it = params.entrySet().iterator();
while(it.hasNext()) {
Entry<String, String> entry = it.next();
query.setParameter(entry.getKey(), entry.getValue());
}
}
List<SiteRouteDto> ret = query.unwrap(org.hibernate.Query.class).setResultTransformer(Transformers.aliasToBean(SiteRouteDto.class)).list();
return ret;
}

//

//返回List<String>
public List<String> GetReceiveSiteRetrySend() {
Query query = null;
String sql = "SELECT DISTINCT Guid from run_s_sendfailure where RetryCount<=60 and McBackup='N' ORDER BY id LIMIT 0,20";

query = em.createNativeQuery(sql);
List<?> objList = query.getResultList();
List<String> ret = new ArrayList<String>(objList.size());
for(Object obj : objList) {
ret.add(obj.toString());
}
return ret;
}

//返回统计数据
public long getFileCnt(List<Integer> agentList, String earlyStartTime, String lastEndTime) {

String sql = "select count(1) from run_s_fileapproveinfo where 1=1";

String sourceIdQuery = " and sourceId in (";
if (agentList != null && agentList.size() > 0) {
for (int i = 0; i < agentList.size(); i++) {
sourceIdQuery += "'" + agentList.get(i) + "'";
if (i < agentList.size() - 1) {
sourceIdQuery += ", ";
}
}
sourceIdQuery += ") ";
sql += sourceIdQuery;
}

if (!Strings.isNullOrEmpty(earlyStartTime) && !Strings.isNullOrEmpty(earlyStartTime.trim())) {
sql += " and submitTime >='" + earlyStartTime +"'";
}

if (!Strings.isNullOrEmpty(lastEndTime) && !Strings.isNullOrEmpty(lastEndTime.trim())) {
sql += " and submitTime <'" + lastEndTime +"'";
}

Query query = null;
query = em.createNativeQuery(sql);

List<?> ret = query.getResultList();
Object result = ret.get(0);
int cnt = Integer.parseInt(result.toString());
return cnt;

}