hibernate里使用JDBC查询示例代码

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/fei1502816/article/details/7198213

 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 

展开阅读全文

没有更多推荐了,返回首页