hibernate4使用本地sql语句查询、删除
查询方法：由于带有条件查询参数太多没有封装hibernate参数paramlist
public List<MMSTable> listAll(Map<Object, String> map, int pn, int pageSize) throws Exception { // 定义StringBuffer对象，拼装sql语句 StringBuffer sb = new StringBuffer(); sb.append("select m.* from MMSTable as m "); if (!Global.isEmpty(map.get("mmsReceNo"))) { sb.append(" inner join ReceiptNoInfo as r on m.msgId = r.msgId"); } sb.append(" where 1=1 "); if (!Global.isEmpty(map.get("mmsSubject"))) { sb.append("and m.mmsSubject = '" + map.get("mmsSubject") + "'"); } if (!Global.isEmpty(map.get("startMmsDate"))) { sb.append("and m.mmsDate >= '" + map.get("startMmsDate") + "'"); } if (!Global.isEmpty(map.get("endMmsDate"))) { sb.append("and m.mmsDate <= '" + map.get("endMmsDate") + "'"); } if (!Global.isEmpty(map.get("mmsOrigNo"))) { sb.append("and m.mmsOrigNo = '" + map.get("mmsOrigNo") + "'"); } if (!Global.isEmpty(map.get("mmsReceNo"))) { sb.append(" and r.mmsReceNo like '" + map.get("mmsReceNo") + '%' + "'"); } if (!Global.isEmpty(map.get("mmsStatus"))) { sb.append("and m.mmsStatus = '" + map.get("mmsStatus") + "'"); } sb.append("order by m.mmsDate DESC"); // 获取本地sql语句对象 SQLQuery query = getSession().createSQLQuery(sb.toString()); // 设置将SQL表的别名和实体类联系起来 query.addEntity(MMSTable.class); // 分页代码 if (pn > -1 && pageSize > -1) { query.setMaxResults(pageSize); int start = pn; if (start != 0) { query.setFirstResult(start); } } // 执行查询方法，返回结果 List<MMSTable> mmsList = query.list(); // 返回执行结果 return mmsList; } /** * {@inheritDoc} */ @Override public int countAll(Map<Object, String> map) throws Exception { // 定义StringBuffer对象，拼装sql语句 StringBuffer sb = new StringBuffer(); sb.append("select count(*) from MMSTable as m "); // 如果接受电话号码存在则做内连接 if (!Global.isEmpty(map.get("mmsReceNo"))) { sb.append(" inner join ReceiptNoInfo as r on m.msgId = r.msgId"); } sb.append(" where 1=1 "); if (!Global.isEmpty(map.get("mmsSubject"))) { sb.append("and m.mmsSubject = '" + map.get("mmsSubject") + "'"); } if (!Global.isEmpty(map.get("startMmsDate"))) { sb.append("and m.mmsDate >= '" + map.get("startMmsDate") + "'"); } if (!Global.isEmpty(map.get("endMmsDate"))) { sb.append("and m.mmsDate <= '" + map.get("endMmsDate") + "'"); } if (!Global.isEmpty(map.get("mmsOrigNo"))) { sb.append("and m.mmsOrigNo = '" + map.get("mmsOrigNo") + "'"); } if (!Global.isEmpty(map.get("mmsReceNo"))) { sb.append(" and r.mmsReceNo like '" + map.get("mmsReceNo") + '%' + "'"); } if (!Global.isEmpty(map.get("mmsStatus"))) { sb.append("and m.mmsStatus = '" + map.get("mmsStatus") + "'"); } // 获取本地sql语句对象 SQLQuery query = getSession().createSQLQuery(sb.toString()); // 执行查询方法，返回结果 Integer count = Integer.valueOf(query.uniqueResult().toString()); return count; }?
删除方法：
public void deleteMMSTable(String msgId) throws Exception { // 封装参数 Object[] paramlist = new Object[] {msgId}; // 拼装sql语句 String sql = "delete from MMSTable where msgId = ?"; // 执行sql语句 SQLQuery query = getSession().createSQLQuery(sql); setParameters(query, paramlist); query.executeUpdate(); }?