Hibernate里使用JDBC查询示例代码

jopen 11年前

在使用hibernate的时候,有的时候需要用sql语句来执行,而HibernateDaoSupport方便了操作后,执行sql时候遇到不能执行的问题,   

下列方法是一个内部类来执行sql,可以写一个通用方法来执行sql,通常如果没有必要尽量不使用sql,但是有的时候就必须使用了,就想下列的查询分组查询,不想在hibernate中使用一对多关系,而又必须使用group by 哪么只能使用下列方式

import org.hibernate.HibernateException;        import org.hibernate.Session;        import org.springframework.orm.hibernate3.HibernateCallback;        import java.sql.SQLException;        import java.sql.Connection;        import java.sql.PreparedStatement;        import java.sql.ResultSet;               public List<KnowledgeQueryBean> getKnowledgeQueryInfo() {                 List<KnowledgeQueryBean> ListAll = new ArrayList<KnowledgeQueryBean>();                final String sql = "SELECT   ky.KNOWLEDGEQUERYID,ky.TITILE,ky.CREATEDATE,eu.USERNAME ,ky.USERID,COUNT(kyr.KNOWLEDGEQUERYID) AS COUNT"+                        " FROM   KNOWLEDGEQUERY ky LEFT JOIN KNOWLEDGEQUERYANSWER kyr ON   ky.KNOWLEDGEQUERYID =   kyr.KNOWLEDGEQUERYID" +                        " LEFT JOIN EOM_USER eu   ON ky.USERID = eu.USER_ID"+                        " GROUP BY ky.KNOWLEDGEQUERYID,ky.TITILE,ky.CREATEDATE,eu.USERNAME,ky.USERID ORDER BY ky.CREATEDATE DESC";                 ListAll = (List<KnowledgeQueryBean>)this.getHibernateTemplate().execute(                        new HibernateCallback(){                            public Object doInHibernate(Session session)                            throws HibernateException, SQLException {                             Connection con = session.connection();                             PreparedStatement ps = con.prepareStatement(sql);                             ResultSet rs = ps.executeQuery();                             List<KnowledgeQueryBean> all = new ArrayList<KnowledgeQueryBean>();                            while(rs.next()){                                 KnowledgeQueryBean kqb = new KnowledgeQueryBean();                                 kqb.setKnowledgeQueryId( rs.getLong("KNOWLEDGEQUERYID"));                                 kqb.setTitle(rs.getString("TITILE"));                                 kqb.setCreateDate(rs.getDate("CREATEDATE"));                                 kqb.setContent(rs.getString("USERNAME"));                                 kqb.setUserId(rs.getLong("USERID"));                                 kqb.setCount(rs.getInt("COUNT"));                                 all.add(kqb);                             }                             rs.close();                             ps.close();                             session.flush();                             session.close();                            return all;                             }                         }                 );                return ListAll;             }
地址:http://blog.csdn.net/fei1502816/article/details/7198213