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