hibernate里使用JDBC查询示例代码

来源：互联网发布：2015淘宝新店扶持多久编辑：程序博客网时间：2024/06/05 01:51

iLife's 博客http://blog.csdn.net/fei1502816

在使用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;         }

iLiFe's 博客http://blog.csdn.net/fei1502816