后端学习-学生成绩管理系统(html+ajax+servlet实现)
一、前言
本学生成绩管理系统运用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 : {
原文地址:https://blog.csdn.net/LeeGe666/article/details/103417231