欢迎使用优快云-markdown编辑器

在给学生排名次时，用到
CASE
WHEN @rowtotal = v1.sum
THEN @rownum
WHEN @rowtotal := v1.sum
THEN @rownum := (@rownum + 1)
WHEN @rowtotal = 0
THEN @rownum := (@rownum + 1)
END

然而hibernate不能执行：=它
所以想到了绕过hibernate,并且将sql放在存储过程里面。
具体做法如下：
1，获取连接的方法
/** 从连接池中取得一个JDBC连接
* @throws SQLException */
@SuppressWarnings(“deprecation”)
public Connection getConnection() {
try {
return SessionFactoryUtils.getDataSource(getSessionFactory()).getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
2，在数据库里面建立一个存储过程
给出具体步骤
在存储过程一览点击新建存储过程，一个存储过程的格式就有了，
将要执行的SQL放在存储过程里面begin 和end中间
DELIMITER $$

CREATE
/[DEFINER = { user | CURRENT_USER }]/
PROCEDURE teaching.getStudentScoreInfo()
/*LANGUAGE SQL
getStudentScoreInfo | [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’*/
BEGIN

END$$

DELIMITER
2，调用存储过程
public List getStudentScoreInfo1(Integer team_id, Integer cid,
Integer examId) {
// TODO Auto-generated method stub
List list = null;
list = new ArrayList();
try{
//获取到连接
Connection con = baseDao.getConnection();
//申明存储过程
String procedure = “{call getStudentScoreInfo()}”;
CallableStatement cstmt = con.prepareCall(procedure);
//执行存储过程
ResultSet rsResultSet= cstmt.executeQuery();
while (rsResultSet.next()) {
StudentScoreInfoVO studentScoreInfoVO = new StudentScoreInfoVO();
System.out.println(rsResultSet.getString(“id”)+”ckeng2”+rsResultSet.getDouble(“courseplan_id5”)+”ckeng1”+rsResultSet.getDouble(“courseplan_id4”)+”ckeng1”+rsResultSet.getDouble(“courseplan_id3”));
studentScoreInfoVO.setId(rsResultSet.getString(“id”));
//studentScoreInfoVO.setGraderAVG(rsResultSet.getInt(“rownum”)+”“);
studentScoreInfoVO.setGradeRanking(rsResultSet.getInt(“rownum”)+”“);
list.add(studentScoreInfoVO);
}
return list;
}
catch(Exception e){
e.printStackTrace();
}
return null;
}