一、前言

本学生成绩管理系统运用html+ajax+servlet,未使用任何框架,且未用jsp实现,前后端数据统一用json传输(Gson包)。
本系统基于上次的纯前端学生成绩系统实现。
由于SQL server占用内存过大,所以不能安装在云服务器中,所以无在线网址演示。
环境:
windows 10
SQL server 2008
tomcat 9.0.7
开发工具:IDEA 2018
整体结构:
项目结构

二、主要功能实现

1.成绩列表

为了方便ajax和servlet之间的json传输,于是创建实体类StudentInfo,方便后续直接利用Gson将Student类转化为对应的json,下述代码未给出setter,getter方法。

/**
 * 学生类(成员变量为表格中的属性)
 * @author nh4l
 * @version 12-02
 */
public class StudentInfo {
    private String specialty;
    private String grade;
    private String studentNo;
    private String studentSex;
    private String studentName;
    private String subjectName;
    private float studentScore;
}

一加载首页index.html就利用ajax向后端servlet请求数据。
ajax代码:

$.ajax({
    type: "POST",
    dataType: "json",
    url: "/sgms/LoadStudentServlet" ,
    contentType: "application/json;charset=utf-8",
    // data: 'req="LoadStudent"',
    success: function(data){
        console.log(data);
        for (var i = 0; i < data.length; i++) {
            var obj = {
                "major": data[i]['specialty'],
                "grade": data[i]['grade'],
                "id": data[i]['studentNo'],
                "name": data[i]['studentName'],
                "sex": data[i]['studentSex'],
                "subject":data[i]['subjectName'],
                "score":data[i]['studentScore']
            };
            reponse.addtr(obj, "table");
        }
    },
    error: function () {
        alert("访问繁忙,请重试")
    }
});

LoadStudentServlet中接收前端发送的请求
doGet方法中:

conn = DbUtils.getConnection();
stmt = conn.createStatement();

String querySql = "SELECT specialty, grade, studentNo, studentSex, " +
"studentName, subjectName, studentScore\n" +
"FROM StudentInfo, ScoreInfo, SubjectInfo\n" +
"WHERE ScoreInfo.studentId=StudentInfo.studentId \n" +
"\t  AND ScoreInfo.subjectId=SubjectInfo.subjectId";
rs = stmt.executeQuery(querySql);
while (rs.next()) {
    StudentInfo student = new StudentInfo();
    
    student.setSpecialty(rs.getString("specialty"));
    student.setGrade(rs.getString("grade"));
    student.setStudentNo(rs.getString("studentNo"));
    student.setStudentSex(rs.getString("studentSex"));
    student.setStudentName(rs.getString("studentName"));
    student.setSubjectName(rs.getString("subjectName"));
    student.setStudentScore(rs.getFloat("studentScore"));
    
    students.add(student);
}
String studentJson = JsonUtils.objectToJson(students);
System.out.println(studentJson);
out.write(studentJson);

DbUtils为数据库工具类,方便连接数据库及关闭

package cn.util;

import java.sql.*;

public class DbUtils {
	private static final String DRIVER = "net.sourceforge.jtds.jdbc.Driver";
	private static final String URL = "jdbc:jtds:sqlserver://localhost:1433/StudentScore";
	public final static int PAGE_SIZE=2;
	

	private static final String USERID = "sa";
	private static final String UERPASSWORD = "1234";

	// 禁止实例对象
	private DbUtils() {
	}

	static {
		try {
			Class.forName(DRIVER);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 获得打开的数据连接
	public static Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(URL, USERID, UERPASSWORD);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	// 关闭数据集/语句/连接对象
	public static void close(ResultSet rs, Statement st, Connection conn) {
		try {
			if (rs != null)
				rs.close();
			if (st != null)
				st.close();
			if (conn != null)
				conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

2.新增成绩

向servlet发送请求,数据为学生信息json,接收到后端返回的success响应后添加数据到表格中。
请求ajax代码:

var obj2 = {
            "specialty":major,
            "grade": grade,
            "studentNo": id,
            "studentName": name,
            "studentSex": sex,
            "subjectName": subject,
            "studentScore": parseFloat(score)
        };
$(function() {
    $.ajax({
        type: "POST",
        dataType: "text",
        url: "/sgms/AddStudentServlet",
        contentType: "application/json;charset=utf-8",
        data: JSON.stringify(obj2),
        success: function (data) {
            alert("添加学生成绩信息成功");
            if (data == "success") {
                reponse.addtr(obj, "table");
                $('#input_id').attr('value', "");
                $('#input_name').attr('value', "");
                $('#input_score').attr('value', "");
            } else {
                alert("请勿重复添加成绩");
                return false;
            }
        },
        error: function () {
            alert("访问繁忙,请重试")
        }
    });
});

在servlet中先用流读取到前端传回的json数据,利用Gson直接将json对象转化为StudentInfo对象,而后首先判断学生成绩信息是否已存在数据库中(学号+科目),若返回查询结果为空即可插入学生信息,插入成功返回success,否则返回fail。
AddStudentServlet中doGet代码:

// 读取请求内容
        BufferedReader br = new BufferedReader(new InputStreamReader(request.getInputStream(),"utf-8"));
        String line = null;
        StringBuilder sb = new StringBuilder();
        while ((line = br.readLine()) != null) {
            sb.append(line);
        }
        //将json字符串转换为json对象
        System.out.println(sb.toString());

        //将前端传入的数据加载入student对象
        StudentInfo student = (StudentInfo) JsonUtils.jsonToObject(sb.toString(), StudentInfo.class);
        System.out.println(student);

        PrintWriter out = response.getWriter();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            stmt = conn.createStatement();
            String checkSql = "SELECT *\n" +
                    "FROM ScoreInfo\n" +
                    "WHERE subjectId = (SELECT subjectId FROM SubjectInfo WHERE subjectName='" + student.getSubjectName() + "') \n" +
                    "\t  AND studentId = (SELECT studentId FROM StudentInfo WHERE studentNo='" + student.getStudentNo() + "')";
            rs = stmt.executeQuery(checkSql);

            if (!rs.next()) {
                String insertSql = "INSERT INTO ScoreInfo(subjectId, studentId, studentScore, modifyTime)\n" +
                        "VALUES ((SELECT subjectId FROM SubjectInfo WHERE subjectName='" + student.getSubjectName() + "'),\n" +
                        "\t\t(SELECT studentId FROM StudentInfo WHERE studentNo='" + student.getStudentNo() + "'), " + student.getStudentScore() + ", GETDATE())";
                int isSuccess = stmt.executeUpdate(insertSql);
                System.out.println(insertSql + '\n' + isSuccess);

                if (isSuccess > 0) {
                    System.out.println("插入成绩成功");
                    out.write("success");
                } else {
                    out.write("fail");
                }
            } else {
                System.out.println("插入成绩重复,插入失败");
                out.write("fail");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.close(rs, stmt, conn);
        }

        out.flush();
        out.close();

3.删除成绩

点击删除后弹出是否删除,选择确认删除,取消则不做改变。
利用ajax向后端发送删除成绩请求,数据为学号+科目名,后端返回success即删除成功,将表格中数据删除,返回fail则说明删除失败。
代码:

$(function() {
    $.ajax({
        type: "POST",
        dataType: "text",
        url: "/sgms/DeleteScoreServlet",
        contentType: "application/x-www-form-urlencoded;charset=utf-8",
        data: "studentNo=" + id + "&subjectName=" + subject,
        success: function (data) {
            if (data == "success") {
                console.log("删除学生信息成功");
                reponse.deletetr(tr, e);
                return true;
            } else {
                alert("删除失败,请稍后重试");
                return false;
            }
        },
        error: function () {
            alert("访问繁忙,请重试");
            return false;
        }
    });
});

后端接收到请求后先获取学号和科目名,然后将其载入SQL中进行删除操作。
DeleteScoreServlet的doGet方法:

	conn = DbUtils.getConnection();
    stmt = conn.createStatement();

    String querySql = "DELETE\n" +
            "FROM ScoreInfo\n" +
            "WHERE subjectId = (SELECT subjectId FROM SubjectInfo WHERE subjectName='" + subjectName + "')\n" +
            "\t  AND studentId = (SELECT studentId FROM StudentInfo WHERE studentNo='" + studentNo + "')";
            System.out.println(querySql);
    int isSuccess = stmt.executeUpdate(querySql);
            if (isSuccess > 0) {
        System.out.println("删除成绩成功");
        out.write("success");
    } else {
        out.write("fail");
    }

4.修改

点击修改按钮后进行修改操作,因为是成绩管理系统,只能修改成功,不能修改其他元素,所以禁用所有非成绩输入框的控件,这些元素会变灰,鼠标放在上面时。
禁用非成绩的控件
js实现:

$("#select_grade").attr("disabled", true);
$("input[name='input_id']").attr("disabled", true);
$("input[name='input_name']").attr("disabled", true);
$("#radio_man").attr("disabled", true);
$("#radio_women").attr("disabled", true);
$("#radio_ds").attr("disabled", true);
$("#radio_java").attr("disabled", true);
$("#radio_c").attr("disabled", true);

修改成绩后即利用ajax向后台请求修改请求
data为修改的学生信息,若删除成功则删除表格中的数据,且将禁用的控件恢复,不成功则提示重新修改。

$(function() {
    $.ajax({
        type: "POST",
        dataType: "text",
        url: "/sgms/ExecScoreServlet",
        contentType: "application/json;charset=utf-8",
        data: JSON.stringify(obj2),
        success: function (data) {
            alert("修改学生信息成功");
            if (data == "success") {
                reponse.editsavetr(obj, "table");
                $("input[name='input_id']").attr("disabled", false);
                $("input[name='input_name']").attr("disabled", false);
                $("#select_grade").attr("disabled", false);
                $("#radio_computer").attr("disabled", false);
                $("#radio_iot").attr("disabled", false);
                $("#radio_tongxin").attr("disabled", false);
                $("#radio_man").attr("disabled", false);
                $("#radio_women").attr("disabled", false);
                $("#radio_ds").attr("disabled", false);
                $("#radio_java").attr("disabled", false);
                $("#radio_c").attr("disabled", false);
                $('#input_id').attr('value', "");
                $('#input_name').attr('value', "");
                $('#input_score').attr('value', "");
            } else {
                return false;
            }
        },
        error: function () {
            alert("访问繁忙,请重试")
        }
    });
});

为了不增加程序复杂性,未实现校验成绩与数据库中车估计是否相同。
ExecScoreServlet的doGet方法:

	conn = DbUtils.getConnection();
    stmt = conn.createStatement();

    String querySql = "UPDATE ScoreInfo\n" +
            "SET studentScore=" + student.getStudentScore() + "\n" +
            "WHERE subjectId = (SELECT subjectId FROM SubjectInfo WHERE subjectName='" + student.getSubjectName() + "') \n" +
            "\t  AND studentId = (SELECT studentId FROM StudentInfo WHERE studentNo='" + student.getStudentNo() + "')";
            System.out.println(querySql);
    int isSuccess = stmt.executeUpdate(querySql);
            if (isSuccess > 0) {
        System.out.println("修改成绩成功");
        out.write("success");
    } else {
        out.write("fail");
    }

5.统计

统计学生信息柱状图引用图表模板echarts实现,均是一加载页面就ajax向请求统计数据。
第一个柱状图为学生总成绩,横坐标为学生信息,纵轴为学生总成绩;
第二个柱状图为科目平均成绩,横坐标为科目信息,纵轴为平均成绩。
统计数据
ajax请求代码:

var studentSumStudentArray = [];
var studentSumScoreArray = [];
var subjectNameArray = [];
var avgScoreArray = [];
$.ajax({
    type: "POST",
    dataType: "json",
    url: "/sgms/StudentStatisticsServlet",
    contentType: "application/json;charset=utf-8",
    success: function (data) {
        console.log(data);
        var studentSum = data['studentSum'];
        var subjectAvg = data['subjectAvg'];

        for (var i=0; i<studentSum.length; i++) {
            studentSumStudentArray.push(studentSum[i]["studentName"] + "\n" + studentSum[i]["studentNo"]);
            studentSumScoreArray.push(studentSum[i]["sumScore"]);
        }
        for (var j=0; j<subjectAvg.length; j++) {
            subjectNameArray.push(subjectAvg[j]["subjectName"]);
            avgScoreArray.push(subjectAvg[j]["avgScore"]);
        }

        // console.log(studentSumStudentArray, studentSumScoreArray);
        // console.log(subjectNameArray, avgScoreArray);
        console.log(subjectNameArray);

        var sum_option = {
            color: ['#3398DB'],
            tooltip : {
                trigger: 'axis',
                axisPointer : {            // 坐标轴指示器,坐标轴触发有效
                    type : 'shadow'        // 默认为直线,可选为:'line' | 'shadow'
                }
            },
            grid: {
                left: '3%',
                right: '4%',
                bottom: '3%',
                containLabel: true
            },
            legend: {
                data:['总成绩']
            },
            xAxis : [
                {
                    type : 'category',
                    data : studentSumStudentArray,
                    axisTick: {
                        alignWithLabel: true
                    }
                }
            ],
            yAxis : [
                {
                    type : 'value'
                }
            ],
            series : [
                {
                    name:'总成绩',
                    type:'bar',
                    barWidth: '60%',
                    data: studentSumScoreArray
                }
            ]
        };

        var avg_option = {
            color: ['#3398DB'],
            tooltip : {
                trigger: 'axis',
                axisPointer : {            // 坐标轴指示器,坐标轴触发有效
                    type : 'shadow'        // 默认为直线,可选为:'line' | 'shadow'
                }
            },
            grid: {
                left: '3%',
                right: '4%',
                bottom: '4%',
                containLabel: true
            },
            xAxis : [
                {
                    type : 'category',
                    data : subjectNameArray,
                    axisTick: {
                        alignWithLabel: true
                    }
                }
            ],
            legend: {
                data:['平均成绩']
            },
            yAxis : [
                {
                    type : 'value'
                }
            ],
            series : [
                {
                    name:'平均成绩',
                    type:'bar',
                    barWidth: '60%',
                    data: avgScoreArray
                }
            ]
        };
        console.log(avg_option);

        app_sum.setOption(sum_option);
        app_avg.setOption(avg_option);

    },
    error: function () {
        alert("图表请求失败");
    }
});

用到的SQL语句:

--每个学生所有科目总成绩
SELECT studentNo, studentName, SUM(studentScore) AS sumScore
FROM StudentInfo, ScoreInfo, SubjectInfo
WHERE ScoreInfo.studentId=StudentInfo.studentId 
	  AND ScoreInfo.subjectId=SubjectInfo.subjectId
GROUP BY studentNo, studentName

--所有学生每门课程平均成绩
SELECT subjectName, AVG(studentScore) AS avgScore
FROM StudentInfo, ScoreInfo, SubjectInfo
WHERE ScoreInfo.studentId=StudentInfo.studentId 
	  AND ScoreInfo.subjectId=SubjectInfo.subjectId
GROUP BY subjectName

StudentStatisticsServlet的doGet代码:

	conn = DbUtils.getConnection();
    stmt = conn.createStatement();

    String sumSql = "SELECT studentNo, studentName, SUM(studentScore) AS sumScore\n" +
            "FROM StudentInfo, ScoreInfo, SubjectInfo\n" +
            "WHERE ScoreInfo.studentId=StudentInfo.studentId \n" +
            "\t  AND ScoreInfo.subjectId=SubjectInfo.subjectId\n" +
            "GROUP BY studentNo, studentName";
    rs = stmt.executeQuery(sumSql);
            while (rs.next()) {
        JsonObject obj = new JsonObject();
        obj.addProperty("studentNo", rs.getString("studentNo"));
        obj.addProperty("studentName", rs.getString("studentName"));
        obj.addProperty("sumScore", rs.getFloat("sumScore"));
        studentSum.add(obj);
    }

    String avgSql = "SELECT subjectName, AVG(studentScore) AS avgScore\n" +
            "FROM StudentInfo, ScoreInfo, SubjectInfo\n" +
            "WHERE ScoreInfo.studentId=StudentInfo.studentId \n" +
            "\t  AND ScoreInfo.subjectId=SubjectInfo.subjectId\n" +
            "GROUP BY subjectName";
    rs = stmt.executeQuery(avgSql);
            while (rs.next()) {
        JsonObject obj = new JsonObject();
        obj.addProperty("subjectName", rs.getString("subjectName"));
        obj.addProperty("avgScore", rs.getFloat("avgScore"));
        subjectAvg.add(obj);
    }
            statistics.setStudentSum(studentSum);
            statistics.setSubjectAvg(subjectAvg);

    String studentStatistics = JsonUtils.objectToJson(statistics);
            System.out.println(studentStatistics);
            out.write(studentStatistics);

6.分页

由于数据量过大原因,不可能将数据一次性加载到页面显示,所以一般网站都会分页显示,点击第几页,查询到第几页的数据。
由于本系统的数据量较小,只有两个学生,三门课程,所以每页显示两条成绩信息。
分页显示战术
由于数据量较小,超过20条后将会是这种:
分页按钮显示
首先要动态获得一共有多少条数据,才能获取一共显示多少页(总条数/2)。

$.ajax({
    type: "POST",
    dataType: "json",
    url: "/sgms/GetScoreNumServlet",
    contentType: "application/json;charset=utf-8",
    success: function (data) {
        // console.log(data);
        var scoreCount = data["scoreCount"];
        // console.log(scoreCount);
        $("#pager").zPager({
            totalData: scoreCount,//从后台加载出来一共有多少条学生成绩信息
            pageData: 2,//设置每页有两条数据
            current: 1,
            htmlBox: $('#wraper'),
            btnShow: true,
            ajaxSetData: false
        });
    },
    error: function () {
        alert("访问繁忙,请重试")
    }
});

GetScoreNumServlet中doGet方法:

	conn = DbUtils.getConnection();
	stmt = conn.createStatement();

	JsonObject obj = new JsonObject();

	String querySql = "SELECT COUNT(*) AS scoreCount\n" +
                "FROM ScoreInfo";
	System.out.println(querySql);
	rs = stmt.executeQuery(querySql);
	while (rs.next()) {
	    obj.addProperty("scoreCount", rs.getInt("scoreCount"));
	}
	out.write(obj.toString());

获得一共多少条记录后便可构建分页按钮。

接下来时点击分页按钮获得该页成绩信息
ajax请求(data为第几个分页):

//通过分页按钮加载对应页数的学生信息
$.ajax({
    type: "POST",
    dataType: "json",
    url: "/sgms/PageTableServlet",
    contentType: "application/x-www-form-urlencoded;charset=utf-8",
    data: "page=" + 1,
    success: function (data) {
        console.log(data);
        clearTable();
        for (var i = 0; i < data.length; i++) {
            var obj = {
                "major": data[i]['specialty'],
                "grade": data[i]['grade'],
                "id": data[i]['studentNo'],
                "name": data[i]['studentName'],
                "sex": data[i]['studentSex'],
                "subject":data[i]['subjectName'],
                "score":data[i]['studentScore']
            };
            reponse.addtr(obj, "table");
        }
    },
    error: function () {
        alert("访问繁忙,请重试")
    }
});

SQL代码示例(第2页)

--分页查询学生成绩
(SELECT top 4 specialty, grade, studentNo, studentSex, studentName, subjectName, studentScore
FROM StudentInfo, ScoreInfo, SubjectInfo
WHERE ScoreInfo.studentId=StudentInfo.studentId AND ScoreInfo.subjectId=SubjectInfo.subjectId)
EXCEPT
(SELECT top 2 specialty, grade, studentNo, studentSex, studentName, subjectName, studentScore
FROM StudentInfo, ScoreInfo, SubjectInfo
WHERE ScoreInfo.studentId=StudentInfo.studentId AND ScoreInfo.subjectId=SubjectInfo.subjectId)

PageTableServlet的都Get中代码:

conn = DbUtils.getConnection();
    stmt = conn.createStatement();

    String querySql = "(SELECT top " + rpage + " specialty, grade, studentNo, studentSex, studentName, subjectName, studentScore\n" +
            "FROM StudentInfo, ScoreInfo, SubjectInfo\n" +
            "WHERE ScoreInfo.studentId=StudentInfo.studentId AND ScoreInfo.subjectId=SubjectInfo.subjectId)\n" +
            "EXCEPT\n" +
            "(SELECT top " + lpage + " specialty, grade, studentNo, studentSex, studentName, subjectName, studentScore\n" +
            "FROM StudentInfo, ScoreInfo, SubjectInfo\n" +
            "WHERE ScoreInfo.studentId=StudentInfo.studentId AND ScoreInfo.subjectId=SubjectInfo.subjectId)";
    rs = stmt.executeQuery(querySql);
    while (rs.next()) {
        StudentInfo student = new StudentInfo();
        student.setSpecialty(rs.getString("specialty"));
        student.setGrade(rs.getString("grade"));
        student.setStudentNo(rs.getString("studentNo"));
        student.setStudentSex(rs.getString("studentSex"));
        student.setStudentName(rs.getString("studentName"));
        student.setSubjectName(rs.getString("subjectName"));
        student.setStudentScore(rs.getFloat("studentScore"));
        students.add(student);
    }
    String studentJson = JsonUtils.objectToJson(students);
    System.out.println(studentJson);
    out.write(studentJson);

实现分页!

7.数据库样例

学生信息表(查),不用于增删改
StudentInfo
科目信息表(查),不用于增删改
SubjectInfo
分数信息表,用于增删改查(studentId和subjectId为外键)
ScoreInfo

三、总结

由于以前开发过后端,但是数据操作不怎么复杂,没有涉及到这么多繁杂的增删改查的操作,这次实现本系统,加深了对ajax, servlet和json的理解,对数据的操作更为理解。
所有控件样式均来自于JQuery插件库 http://www.jq22.com/

github源码链接

https://github.com/NH4L/SGMS

Logo

快速构建 Web 应用程序

更多推荐