hibernate执行sql的方法

1. 1.在使用hibernate的时候，有的时候需要用sql语句来执行，而HibernateDaoSupport方便了操作后，执行sql时候遇到不能执行的问题，  
   2.下列方法是一个内部类来执行sql,可以写一个通用方法来执行sql,通常如果没有必要尽量不使用sql,但是有的时候就必须使用了，就想下列的查询分组查询，不想在hibernate中使用一对多关系，而又必须使用group by 哪么只能使用下列方式，  
   3.import org.hibernate.HibernateException;  
   4.import org.hibernate.Session;  
   5.import org.springframework.orm.hibernate3.HibernateCallback;  
   6.import java.sql.SQLException;  
   7.import java.sql.Connection;  
   8.import java.sql.PreparedStatement;  
   9.import java.sql.ResultSet;  
   10.  
   11.public List<KnowledgeQueryBean> getKnowledgeQueryInfo() {  
   12.        List<KnowledgeQueryBean> ListAll = new ArrayList<KnowledgeQueryBean>();  
   13.        final String sql = "SELECT  ky.KNOWLEDGEQUERYID,ky.TITILE,ky.CREATEDATE,eu.USERNAME ,ky.USERID,COUNT(kyr.KNOWLEDGEQUERYID) AS COUNT"+  
   14.                " FROM  KNOWLEDGEQUERY ky LEFT JOIN KNOWLEDGEQUERYANSWER kyr ON  ky.KNOWLEDGEQUERYID =  kyr.KNOWLEDGEQUERYID" +  
   15.                " LEFT JOIN EOM_USER eu  ON ky.USERID = eu.USER_ID"+  
   16.                " GROUP BY ky.KNOWLEDGEQUERYID,ky.TITILE,ky.CREATEDATE,eu.USERNAME,ky.USERID ORDER BY ky.CREATEDATE DESC";  
   17.        ListAll = (List<KnowledgeQueryBean>)this.getHibernateTemplate().execute(  
   18.                new HibernateCallback(){  
   19.                    public Object doInHibernate(Session session)  
   20.                    throws HibernateException, SQLException {  
   21.                    Connection con = session.connection();  
   22.                    PreparedStatement ps = con.prepareStatement(sql);  
   23.                    ResultSet rs = ps.executeQuery();  
   24.                    List<KnowledgeQueryBean> all = new ArrayList<KnowledgeQueryBean>();  
   25.                    while(rs.next()){  
   26.                        KnowledgeQueryBean kqb = new KnowledgeQueryBean();  
   27.                        kqb.setKnowledgeQueryId( rs.getLong("KNOWLEDGEQUERYID"));  
   28.                        kqb.setTitle(rs.getString("TITILE"));  
   29.                        kqb.setCreateDate(rs.getDate("CREATEDATE"));  
   30.                        kqb.setContent(rs.getString("USERNAME"));  
   31.                        kqb.setUserId(rs.getLong("USERID"));  
   32.                        kqb.setCount(rs.getInt("COUNT"));  
   33.                        all.add(kqb);  
   34.                    }  
   35.                    rs.close();  
   36.                    ps.close();  
   37.                    session.flush();  
   38.                    session.close();  
   39.                    return all;  
   40.                    }  
   41.                }  
   42.        );  
   43.        return ListAll;  
   44.    }