hibernate4使用本地sql查询和删除

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;  
        }             

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();  
   }