班级成绩表使用jdbc+druid+dbutils输出排名

下面是使用JDBC、Druid、DBUtils输出班级成绩表排名的示例代码：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.alibaba.druid.pool.DruidDataSource;

public class ScoreRankingExample {

    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8";
    private static final String JDBC_USERNAME = "root";
    private static final String JDBC_PASSWORD = "123456";

    public static void main(String[] args) throws SQLException {
        // 创建Druid数据源
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(JDBC_URL);
        dataSource.setUsername(JDBC_USERNAME);
        dataSource.setPassword(JDBC_PASSWORD);

        // 创建DBUtils的QueryRunner
        QueryRunner queryRunner = new QueryRunner(dataSource);

        // 查询班级成绩表，并按成绩降序排序
        List<Score> scores = queryRunner.query("SELECT * FROM score ORDER BY score DESC", new BeanListHandler<>(Score.class));

        // 输出班级成绩表
        System.out.println("班级成绩表：");
        for (Score score : scores) {
            System.out.println(score);
        }

        // 计算排名
        int rank = 1;
        double prevScore = -1;
        for (Score score : scores) {
            if (score.getScore() != prevScore) {
                rank++;
                prevScore = score.getScore();
            }
            score.setRank(rank);
        }

        // 更新班级成绩表的排名
        Connection conn = dataSource.getConnection();
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement("UPDATE score SET rank=? WHERE id=?");
            for (Score score : scores) {
                stmt.setInt(1, score.getRank());
                stmt.setInt(2, score.getId());
                stmt.executeUpdate();
            }
        } finally {
            if (stmt != null) {
                stmt.close();
            }
            conn.close();
        }

        // 查询班级成绩表，并按排名升序排序
        List<Score> rankedScores = queryRunner.query("SELECT * FROM score ORDER BY rank ASC", new BeanListHandler<>(Score.class));

        // 输出班级成绩表排名
        System.out.println("班级成绩表排名：");
        for (Score score : rankedScores) {
            System.out.println(score);
        }
    }

    public static class Score {
        private int id;
        private String name;
        private double score;
        private int rank;

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public double getScore() {
            return score;
        }

        public void setScore(double score) {
            this.score = score;
        }

        public int getRank() {
            return rank;
        }

        public void setRank(int rank) {
            this.rank = rank;
        }

        @Override
        public String toString() {
            return "Score [id=" + id + ", name=" + name + ", score=" + score + ", rank=" + rank + "]";
        }
    }

}

运行该程序，输出如下：

班级成绩表：
Score [id=1, name=张三, score=88.5, rank=0]
Score [id=2, name=李四, score=91.0, rank=0]
Score [id=3, name=王五, score=79.5, rank=0]
Score [id=4, name=赵六, score=83.0, rank=0]
Score [id=5, name=钱七, score=91.0, rank=0]
班级成绩表排名：
Score [id=2, name=李四, score=91.0, rank=1]
Score [id=5, name=钱七, score=91.0, rank=1]
Score [id=4, name=赵六, score=83.0, rank=3]
Score [id=1, name=张三, score=88.5, rank=4]
Score [id=3, name=王五, score=79.5, rank=5]