欢迎使用CSDN-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

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; }