查询方法:由于带有条件查询参数太多没有封装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();
}