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;
}
AI 代码解读
hibernate高级查询技巧：
Hibernate对本地查询提供了内置支持，为了把SQL查询返回的关系数据映射为对象，需要在SQL查询语句中为字段制定别名。如下面代码所示：
String sql=”select cs.id as {c.id},cs.name as {c.name},cs.age as {c.age} from customer cs where cs.id=’1’ ”;
Query query=session.createSQLQuery(sql,”c”,Customer.class);
以上的程序代码将Customer实体对象的别名设置为c,将customer表的别名设置为cs，字段的别名必须位于大括号之内。
本地SQL查询还可以支持连接查询，如下面的程序代码：
String sql=”select {c.*},{o.*} from customer c inner join order o where c.id=o.customer_ID”;
Query query=session.createSQLQuery(sql,
new String[]{“c”,”o”},
new Class[]{Customer.class,Order.class});
List list=query.list();
for(int i=0;i<list.size();i++){
Object objs=(Object[])list.get(i);
Customer customer=(Customer)objs[0];
Order order=(Order)objs[1];
}
值得注意的是以上程序代码中Query的list()方法返回的结果集中存放的是对象数组，在对象数组中成对存放着一对Customer对象和Order对象。
删除操作
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();
}
AI 代码解读
