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