上一篇讲的handsontable获取后台的数据并显示在excel表中,那么接下来就要实现基本的增删改操做
1.新增数据
HTML部分:
<div class="col-md-10 col-sm-10 col-xs-3" style="display: block" id="grade_div">
<input type="button" id="gradeadd" value="新增" style="margin-bottom: 5px;margin-right: 5px" class="btn btn-success btn-sm" data-toggle="modal" data-target="#addGrade">
<div id="grade"></div>
<%--添加成绩的模态框--%>
<div class="modal fade" id="addGrade" tabindex="-1" role="dialog" aria-labelledby="addGradeLabel">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
<h4 class="modal-title" id="myModalLabel">添加成绩信息</h4>
</div>
<div class="modal-body">
<form class="form-horizontal" action="/GradeInfo?method=AddScore" method="post" id="addgradeform">
<div class="form-group has-feedback">
<label class="control-label col-sm-2" for="inputStuID">学号</label>
<div class="col-sm-9">
<input name="stuID" type="number" class="form-control" id="inputStuID" placeholder="学号" aria-describedby="inputStuIDStatus">
</div>
</div>
<div class="form-group has-feedback">
<label class="control-label col-sm-2" for="inputStuName">姓名</label>
<div class="col-sm-9">
<input name="stuName" type="text" class="form-control" id="inputStuName" placeholder="姓名" aria-describedby="inputStuNameStatus">
</div>
</div>
<div class="form-group has-feedback">
<label class="control-label col-sm-2" for="inputcourseID">课程编号</label>
<div class="col-sm-9">
<input name="courseID" type="text" class="form-control" id="inputcourseID" placeholder="课程编号" aria-describedby="inputcourseIDStatus">
</div>
</div>
<div class="form-group has-feedback">
<label class="control-label col-sm-2" for="inputcourseName">课程名称</label>
<div class="col-sm-9">
<input name="courseName" type="text" class="form-control" id="inputcourseName" placeholder="课程名称" aria-describedby="inputcourseNameStatus">
</div>
</div>
<div class="form-group has-feedback">
<label class="control-label col-sm-2" for="inputGrade">成绩</label>
<div class="col-sm-9">
<input name="Grade" type="number" class="form-control" id="inputGrade" placeholder="成绩" aria-describedby="inputGradeStatus">
</div>
</div>
<div class="for-group">
<input type="submit" value="保存" class="btn btn-primary" style="margin-left: 50px">
<input type="button" value="关闭" class="btn btn-default" style="margin-left: 50px" data-dismiss="modal">
</div>
</form>
</div>
</div>
</div>
</div>
</div>
运行效果:
controller:
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//获取请求的方法
String method = request.getParameter("method");
if("ScoreList".equalsIgnoreCase(method)){
scoreList(request,response);
//获取所有成绩的数据
}else if ("AddScore".equalsIgnoreCase(method)){
addScore(request,response);
//登记成绩
}else if ("DeleteScore".equalsIgnoreCase(method)){
deleteScore(request,response);
}else if ("UpdateScore".equalsIgnoreCase(method)){
updateScore(request,response);
}
}
private void addScore(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset:utf-8");
String stuID1 = request.getParameter("stuID");
Integer stuID = Integer.valueOf(stuID1).intValue();
String stuName = new String(request.getParameter("stuName").getBytes("ISO-8859-1"),"utf-8");
String courseID = request.getParameter("courseID");
String courseName = new String(request.getParameter("courseName").getBytes("ISO-8859-1"),"utf-8");
String Grade = request.getParameter("Grade");
Integer courseGrade = Integer.valueOf(Grade).intValue();
Grade grade = new Grade(stuID,stuName,courseID,courseName,courseGrade);
if (stuID!=null&&stuName!=null&&courseID!=null&&courseName!=null&&courseGrade!=null){
Connection conn = JdbcUtils.getConnection();
PreparedStatement pre = null;
ResultSet res = null;
String sql = "INSERT INTO grade(stuID,stuName,courseID,courseName,courseGrade) VALUES(?,?,?,?,?)";
try {
pre = conn.prepareStatement(sql);
pre.setInt(1,stuID);
pre.setString(2,stuName);
pre.setString(3,courseID);
pre.setString(4,courseName);
pre.setInt(5,courseGrade);
pre.executeUpdate();
}
catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(conn,pre,res);
}
request.getRequestDispatcher("WEB-INF/main.jsp").forward(request,response);
}
}
2.删除某一行:
//当要删除一或多个行时触发。
beforeRemoveRow:function(index, amount){
if (confirm("确定删除该行吗")){
var rid = index,
dtArr = hot.getDataAtRow(rid),
dtId = dtArr[0],
st = {"stuID":dtId};
$.ajax({
type:"post",
url:"GradeInfo?method=DeleteScore",
data:st,
dataType:"text",
success:function (data) {
alert("删除成功!");
},
error:function () {
alert("删除失败!")
}
})
}
else {
return;
}
}
controller
private void deleteScore(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String stuID = request.getParameter("stuID");
Connection connection = JdbcUtils.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "DELETE FROM grade WHERE stuID= '"+stuID+"'";
System.out.println(sql);
try {
ps = connection.prepareStatement(sql);
ps.executeUpdate(sql);
JdbcUtils.close(connection,ps,rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
3.修改数据
afterChange:function (changes,source) {
if (changes!=null){
var row = changes[0][0];
var updata = {
"colName":changes[0][1],
"oValue":changes[0][2],
"nValue":changes[0][3],
};
if (changes[0][2] === changes[0][3]){
return;
}else {
// 修改后自动异步提交数据
$.ajax({
type:'post',
url:"GradeInfo?method=UpdateScore",
data:updata,
dataType:'text',
success:function (data) {
alert("更新成功");
},
error:function () {
alert("更新失败");
}
})
}
}
},
controller
private void updateScore(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException{
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
String ovalue = request.getParameter("oValue");
String nvalue = request.getParameter("nValue");
String colname = request.getParameter("colName");
Connection conn = JdbcUtils.getConnection();
PreparedStatement pre = null;
String sql = "UPDATE grade SET "+colname+" = '"+nvalue+"' WHERE "+colname+" = '"+ovalue+"'";
System.out.println(sql);
try {
pre = conn.prepareStatement(sql);
int res = pre.executeUpdate(sql);
out.println(res);
pre.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
request.getRequestDispatcher("WEB-INF/main.jsp").forward(request,response);
}