一、前言
本学生成绩管理系统运用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.数据库样例
学生信息表(查),不用于增删改
科目信息表(查),不用于增删改
分数信息表,用于增删改查(studentId和subjectId为外键)
三、总结
由于以前开发过后端,但是数据操作不怎么复杂,没有涉及到这么多繁杂的增删改查的操作,这次实现本系统,加深了对ajax, servlet和json的理解,对数据的操作更为理解。
所有控件样式均来自于JQuery插件库 http://www.jq22.com/