Hibernate中执行SQL语句的方法

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

评论

2 楼 julysohu 2011-03-24  

谢谢啦  牛人 呵呵

1 楼 XingShiYiShi 2010-11-06  

好，哥们，你太牛了。。解决2我一大问题！！！！哈谢谢了啊！！！！！