1,HQL查询语句的时候,要求传递的参数和实体类中的参数类型一致,比如下面的代码:
public TerminalStatusHistory findRecentlyByTime2(String gid, String mid, String sn, Date startTime) {
StringBuilder sb = new StringBuilder();
sb.append(
"SELECT new TerminalStatusHistory(tsh.id,tsh.gid,tsh.mid,tsh.sn,tsh.status,max(tsh.createTime),tsh.detail) FROM TerminalStatusHistory as tsh ");
sb.append("WHERE (tsh.createTime<:createTime or tsh.createTime=:createTime1) AND tsh.sn=:sn");
if (StringUtils.isNotEmpty(gid) && StringUtils.isNotEmpty(mid)) {
sb.append(" and tsh.gid=:gid AND tsh.mid=:mid");
}
Query query = this.getSession().createQuery(sb.toString());
query.setTimestamp("createTime", startTime);
query.setTimestamp("createTime1", startTime);
query.setString("sn", sn);
if (StringUtils.isNotEmpty(gid) && StringUtils.isNotEmpty(mid)) {
query.setString("gid", gid);
query.setString("mid", mid);
}
List<TerminalStatusHistory> list = query.list();
return list.size() > 0 ? list.get(0) : null;
}
query.setString("sn", sn);
是什么类型就必须对应起来
2,原生SQL查询中的参数,都是基本类型的,没有复杂类型,所以像date类型的要转换为字符串类型
public TerminalStatusHistory findRecentlyByTime(String gid, String mid, String sn, Date startTime) {
StringBuilder sb = new StringBuilder();
sb.append("SELECT *,MAX(create_time) FROM sys_terminal_status_history " + "WHERE create_time <? AND sn=?");
if (StringUtils.isNotEmpty(gid) && StringUtils.isNotEmpty(mid)) {
sb.append(" AND gid=? AND mid=?");
}
SQLQuery sqlQuery = this.getSession().createSQLQuery(sb.toString());
String P2 = "yyyy-MM-dd HH:mm:ss";
String time=Tools.getFormatDate(startTime, P2);
sqlQuery.setString(0, time);
sqlQuery.setString(1, sn);
if (StringUtils.isNotEmpty(gid) && StringUtils.isNotEmpty(mid)) {
sqlQuery.setString(2, gid);
sqlQuery.setString(3, mid);
}
TerminalStatusHistory terminalStatusHistory = null;
List<Object[]> list = sqlQuery.list();
// iterator遍历
Iterator its = list.iterator();
while (its.hasNext()) {
Object[] a = (Object[]) its.next();
if (a[0] != null) {
terminalStatusHistory = new TerminalStatusHistory();
BigInteger bigInteger = (BigInteger) a[0];
terminalStatusHistory.setId(bigInteger.longValue());
terminalStatusHistory.setGid((String) a[1]);
terminalStatusHistory.setMid((String) a[2]);
terminalStatusHistory.setSn((String) a[3]);
terminalStatusHistory.setStatus((int) a[4]);
terminalStatusHistory.setCreateTime((Timestamp) a[5]);
terminalStatusHistory.setDetail((String) a[6]);
}
}
return terminalStatusHistory;
// return null;
}
String P2 = "yyyy-MM-dd HH:mm:ss";
String time=Tools.getFormatDate(startTime, P2);
sqlQuery.setString(0, time);
这个要做转换才可以正确的查询
-------------------------------------------------------------------
血的教训,搞了一个下午才搞出来什么原因,经验啊。。。