中南林业科技大学Java实验报告十二：数据库系统设计 - 从0到1搭建java可视化学生管理系统源代码（二）

package javabean;
/**
 * 学生类
 */
public class Student {
    /**
     * 学号
     */
    private Integer stuId;
    /**
     * 姓名
     */
    private String name;
    /**
     * 专业
     */
    private String major;
    /**
     * 班级
     */
    private Integer classNum;
    /**
     * 平均成绩
     */
    private Double avgScore;
    public Student() {
    }
    public Student(Integer stuId, String name, String major, Integer classNum, Double avgScore) {
        this.stuId = stuId;
        this.name = name;
        this.major = major;
        this.classNum = classNum;
        this.avgScore = avgScore;
    }
    public Integer getStuId() {
        return stuId;
    }
    public void setStuId(Integer stuId) {
        this.stuId = stuId;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getMajor() {
        return major;
    }
    public void setMajor(String major) {
        this.major = major;
    }
    public Integer getClassNum() {
        return classNum;
    }
    public void setClassNum(Integer classNum) {
        this.classNum = classNum;
    }
    public Double getAvgScore() {
        return avgScore;
    }
    public void setAvgScore(Double avgScore) {
        this.avgScore = avgScore;
    }
    @Override
    public String toString() {
        return "Student{" +
                "stuId=" + stuId +
                ", name='" + name + '\'' +
                ", major='" + major + '\'' +
                ", className='" + classNum + '\'' +
                ", avgScore=" + avgScore +
                '}';
    }
}

package dao;
import utils.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class BasicDao<T> {
    private QueryRunner queryRunner = new QueryRunner();
    //dml操作
    public int update(String sql,Object...parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return queryRunner.update(connection,sql,parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
    //操作多行数据，即返回多个对象
    public List<T> queryMulti(String sql,Class<T> clazz,Object...parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return queryRunner.query(connection,sql,new BeanListHandler<T>(clazz),parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
    //返回一行数据
    public T querySingle(String sql,Class<T> clazz,Object...parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return queryRunner.query(connection,sql,new BeanHandler<T>(clazz),parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
    //返回单行单列的结果
    public Object queryScalar(String sql,Object...parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return queryRunner.query(connection,sql,new ScalarHandler<>(),parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
}

package dao;
import javabean.Student;
import java.util.List;
public interface StudentDao{
    /**
     * 查询信息，按照分数降序排列
     *
     * @return 全部学生信息
     */
    List<Student> queryByScoreDesc();
    /**
     * 添加学生信息
     *
     * @param name 姓名
     * @param stuId 学号
     * @param major 专业
     * @param classNum 班级编号
     * @param avgScore 平均成绩
     */
    void add(String name,Integer stuId,String major,Integer classNum,Double avgScore);
    /**
     * 通过学号查询学生信息
     *
     * @param id 学号
     * @return 学生信息
     */
    Student queryByStuId(Integer id);
    /**
     * 通过学号删除学生信息
     *
     * @param stuId 学号
     */
    void delByStuId(Integer stuId);
    /**
     * 修改学生信息
     *
     * @param name 姓名
     * @param stuId 学号
     * @param major 专业
     * @param classNum 班级编号
     * @param avgScore 平均成绩
     */
    void updateByStuId(String name,Integer stuId,String major,Integer classNum,Double avgScore);
}

package dao.impl;
import dao.BasicDao;
import dao.StudentDao;
import javabean.Student;
import java.util.List;
/**
 * Dao层实现类
 */
public class StudentDaoImpl extends BasicDao<Student> implements StudentDao{
    @Override
    public List<Student> queryByScoreDesc() {
        String sql = "SELECT stu_id AS `stuId`,`name`,major,class_num AS `classNum`,avg_score AS `avgScore`\n" +
                "FROM score ORDER BY avg_score DESC";
        return queryMulti(sql, Student.class);
    }
    @Override
    public void add(String name, Integer stuId, String major, Integer classNum, Double avgScore) {
        String sql = "INSERT INTO score(stu_id,`name`,major,class_num,avg_score) VALUES\n" +
                "(?,?,?,?,?)";
        update(sql,stuId,name,major,classNum,avgScore);
    }
    @Override
    public Student queryByStuId(Integer id) {
        String sql = "SELECT stu_id AS `stuId`,`name`,major,class_num AS `classNum`,avg_score AS `avgScore`\n" +
                "FROM score WHERE stu_id = ?";
        return querySingle(sql,Student.class,id);
    }
    @Override
    public void updateByStuId(String name, Integer stuId, String major, Integer classNum, Double avgScore) {
        String sql = "update score\n" +
                "set `name`= ?\n" +
                ",major = ?\n" +
                ",class_num = ?\n" +
                ",avg_score = ?\n" +
                "WHERE stu_id = ?";
        update(sql,name,major,classNum,avgScore,stuId);
    }
    @Override
    public void delByStuId(Integer stuId) {
        String sql = "DELETE FROM score\n" +
                "WHERE stu_id = ?";
        update(sql, stuId);
    }
}

package service;
import javabean.Student;
import java.util.List;
public interface StudentService {
    /**
     * 查询学生信息，按照平均成绩降序排列
     *
     * @return 学生信息
     */
    List<Student> queryByScoreDesc();
    /**
     * 添加学生信息
     *
     * @param name 姓名
     * @param stuId 学号
     * @param major 专业
     * @param classNum 班级编号
     * @param avgScore 平均成绩
     */
    void add(String name, Integer stuId, String major, Integer classNum, Double avgScore);
    /**
     * 通过学号查询学生信息
     *
     * @param id 学号
     * @return 学生信息
     */
    Student queryByStuId(Integer id);
    /**
     * 修改学生信息
     *
     * @param name 姓名
     * @param stuId 学号
     * @param major 专业
     * @param classNum 班级编号
     * @param avgScore 平均成绩
     */
    void updateByStuId(String name,Integer stuId,String major,Integer classNum,Double avgScore);
    /**
     * 通过学号删除学生信息
     *
     * @param id 学号
     */
    void delByStuId(Integer id);
}

package service.impl;
import dao.impl.StudentDaoImpl;
import dao.StudentDao;
import javabean.Student;
import service.StudentService;
import java.util.List;
/**
 * Student服务层实现
 */
public class StudentServiceImpl implements StudentService {
    private StudentDao studentDao = new StudentDaoImpl();
    @Override
    public List<Student> queryByScoreDesc() {
        return studentDao.queryByScoreDesc();
    }
    @Override
    public void add(String name, Integer stuId, String major, Integer classNum, Double avgScore) {
        studentDao.add(name, stuId, major, classNum, avgScore);
    }
    @Override
    public Student queryByStuId(Integer id) {
        return studentDao.queryByStuId(id);
    }
    @Override
    public void updateByStuId(String name, Integer stuId, String major, Integer classNum, Double avgScore) {
        studentDao.updateByStuId(name, stuId, major, classNum, avgScore);
    }
    @Override
    public void delByStuId(Integer stuId) {
        studentDao.delByStuId(stuId);
    }
}