修改gradeList.jsp
在GradeServlet.java写doPost方法
在GradeService补全方法
要先到DBUtils添加方法
回到GradeService
到gradeDao补全方法
并在DBUtils 添加方法
又在GradeDao补全方法
在GradeService如下操作
在GradeServlet返回
搞好后用Postman测试一下
代码补全及注释
gradeList.jsp
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta charset="UTF-8">
<title>教师列表</title>
<link href='<c:url context="${pageContext.request.contextPath}" value="/easyui/themes/default/easyui.css"/>'
rel="stylesheet" type="text/css"/>
<link href='<c:url context="${pageContext.request.contextPath}" value="/easyui/themes/icon.css"/>' rel="stylesheet"
type="text/css"/>
<link href='<c:url context="${pageContext.request.contextPath}" value="/easyui/css/demo.css"/>' rel="stylesheet"
type="text/css"/>
<script type="text/javascript"
src='<c:url value="/easyui/jquery.min.js" context="${pageContext.request.contextPath}"/>'></script>
<script type="text/javascript"
src='<c:url value="/easyui/jquery.easyui.min.js" context="${pageContext.request.contextPath}"/>'></script>
<script type="text/javascript"
src='<c:url value="/easyui/js/validateExtends.js" context="${pageContext.request.contextPath}"/>'></script>
<script type="text/javascript">
$(function () {
//datagrid初始化
$('#dataList').datagrid({
title: '年级列表',
iconCls: 'icon-more',//图标
border: true,
collapsible: false,//是否可折叠的
fit: true,//自动大小
method: "get",
url: "/s/grade?action=data",
idField: 'gid',
singleSelect: true,//是否单选
pagination: false,//分页控件
rownumbers: true,//行号
sortName: 'gid',
sortOrder: 'asc',
remoteSort: false,
columns: [[
{field: 'chk', checkbox: true, width: 50},
{field: 'gid', title: '年级编号', width: 50, sortable: true},
{field: 'gradeName', title: '年级名称', width: 200},
{
field: 'courses', title: '年级课程', width: 650,
//value 就是当前的值，即courses所对应的数组
//row 当前行所对应的json
//index 当前渲染到第几行了
formatter: function (value, row, index) {
if (row.courses) {
var cl = " | ";
var list = row.courses;
for (var i = 0; i < list.length; i++) {
cl += list[i].courseName + " | ";
}
return cl;// |数学|英语|
} else {
return value;
}
}
},
]],
toolbar: "#toolbar"
});
//设置工具类按钮
$("#add").click(function () {
$("#addDialog").dialog("open");
});
//删除
$("#delete").click(function () {
var selectRow = $("#dataList").datagrid("getSelected");
if (selectRow == null) {
$.messager.alert("消息提醒", "请选择数据进行删除!", "warning");
} else {
var gradeid = selectRow.id;
$.messager.confirm("消息提醒", "将删除与年级相关的所有数据(包括班级,学生)，确认继续？", function (r) {
if (r) {
$.ajax({
type: "post",
url: "GradeServlet?method=DeleteGrade",
data: {gradeid: gradeid},
success: function (msg) {
if (msg == "success") {
$.messager.alert("消息提醒", "删除成功!", "info");
//刷新表格
$("#dataList").datagrid("reload");
} else {
$.messager.alert("消息提醒", "删除失败!", "warning");
return;
}
}
});
}
});
}
});
//课程下拉框,课程下拉框是一个多选的下拉框
//我们只需要按照下拉框的要求返回数据格式，这个下拉框可以自动渲染出来
//[{cid:xxx,courseName:xxx},{},{}]
$("#add_courseList").combobox({
valueField: "cid",//将来提交的值
textField: "courseName",//下拉框展示给用户看的内容
multiple: true, //可多选
editable: false, //不可编辑
method: "get",
url: "/s/course?action=data",
});
//设置添加学生窗口
$("#addDialog").dialog({
title: "添加年级",
width: 500,
height: 400,
iconCls: "icon-add",
modal: true,
collapsible: false,
minimizable: false,
maximizable: false,
draggable: true,
closed: true,
buttons: [
{
text: '添加',
plain: true,
iconCls: 'icon-world-add',
handler: function () {
var validate = $("#addForm").form("validate");
if (!validate) {
$.messager.alert("消息提醒", "请检查你输入的数据!", "warning");
return;
} else {
$.ajax({
type: "post",
url: "GradeServlet?method=AddGrade",
data: $("#addForm").serialize(),
success: function (msg) {
if (msg == "success") {
$.messager.alert("消息提醒", "添加成功!", "info");
//关闭窗口
$("#addDialog").dialog("close");
//清空原表格数据
$("#add_name").textbox('setValue', "");
$("#add_courseList").combobox("clear");
//重新刷新页面数据
$('#dataList').datagrid("reload");
} else {
$.messager.alert("消息提醒", "添加失败!", "warning");
return;
}
}
});
}
}
},
{
text: '重置',
plain: true,
iconCls: 'icon-world-reset',
handler: function () {
$("#add_name").textbox('setValue', "");
$("#add_courseList").combobox("clear");
}
},
]
});
});
</script>
</head>
<body>
<!-- 数据列表 -->
<table id="dataList" cellspacing="0" cellpadding="0">
</table>
<!-- 工具栏 -->
<div id="toolbar">
<div style="float: left;"><a id="add" href="javascript:;" class="easyui-linkbutton"
data-options="iconCls:'icon-add',plain:true">添加</a></div>
<div style="float: left;" class="datagrid-btn-separator"></div>
<div><a id="delete" href="javascript:;" class="easyui-linkbutton"
data-options="iconCls:'icon-some-delete',plain:true">删除</a></div>
</div>
<!-- 添加数据窗口 -->
<div id="addDialog" style="padding: 10px">
<form id="addForm" method="post">
<table cellpadding="8">
<tr>
<td>年级名称:</td>
<td><input id="add_name" style="width: 200px; height: 30px;" class="easyui-textbox" type="text"
name="name" data-options="required:true, validType:'repeat_grade', missingMessage:'不能为空'"/>
</td>
</tr>
<tr>
<td>选择课程:</td>
<td><select id="add_courseList" style="width: 200px; height: 30px;" name="clazzid"
data-options="required:true, missingMessage:'请选择课程'"></select></td>
</tr>
</table>
</form>
</div>
</body>
</html>GradeServlet.java
package demo.servlet.grade;
import com.fasterxml.jackson.databind.ObjectMapper;
import demo.model.GradeDTO;
import demo.model.RespBean;
import demo.service.GradeService;
import demo.utils.CommonsUtils;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
//http://localhosst:8080/s/grade?action=page
@WebServlet(urlPatterns = "/grade")
public class GradeServlet extends HttpServlet {
GradeService gradeService = new GradeService();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String action = req.getParameter("action");
if ("page".equals(action)) {
req.getRequestDispatcher("/WEB-INF/jsp/grade/gradeList.jsp").forward(req, resp);
} else if ("data".equals(action)) {
//获取json
//查询所有的年级
List<GradeDTO> list = gradeService.getAllGrades();
resp.setContentType("application/json;charset=utf-8");
PrintWriter out = resp.getWriter();
out.write(new ObjectMapper().writeValueAsString(list));
}
}
/**
* 添加年级
*
* @param req
* @param resp
* @throws ServletException
* @throws IOException
*/
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//获取年级名字
String gradeName = req.getParameter("gradeName");
//获取课程id
String[] cids = req.getParameterValues("cid");
List<Integer> collect = Arrays.stream(cids).map(i -> Integer.parseInt(i)).collect(Collectors.toList());
Integer result = gradeService.addGrade(gradeName, collect);
RespBean respBean = null;
if (result == CommonsUtils.REPEATABLE_VALUE) {
respBean = RespBean.error("年级名重复,添加失败");
} else if (result == CommonsUtils.INSERT_SUCCESS) {
respBean = RespBean.ok("添加成功");
} else {
respBean = RespBean.error("添加失败");
}
resp.setContentType("application/json;charset=utf-8");
PrintWriter out = resp.getWriter();
out.write(new ObjectMapper().writeValueAsString(respBean));
}
}
GradeService.java
package demo.service;
import demo.dao.GradeDao;
import demo.model.Grade;
import demo.model.GradeDTO;
import demo.utils.CommonsUtils;
import demo.utils.DBUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
public class GradeService {
GradeDao gradeDao = new GradeDao();
public List<GradeDTO> getAllGrades() {
try {
return gradeDao.getAllGrades();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 1.年级名不能重复
* 2.添加分两步:
* 2.1 添加年级
* 2.2 添加年级和课程的关联关系
*
* @param gradeName
* @param cids
* @return
*/
public Integer addGrade(String gradeName, List<Integer> cids) {
//当前线程第一次获取到con,这个con是从ds中拿到的
Connection con = DBUtils.getCon();
//开启事务
try {
Grade g = gradeDao.getGradeByGradeName(gradeName);
if (g != null) {
//说明年级名称重复了,添加失败
return CommonsUtils.REPEATABLE_VALUE;
}
con.setAutoCommit(false);
Grade grade = new Grade();
grade.setGradeName(gradeName);
//现在gid没有值,等插入完成之后,会自动给gid赋上值
Integer r1 = gradeDao.addGrade(grade);
//添加年级和课程的关联关系
Integer r2 = gradeDao.addGradeCourse(grade.getGid(), cids);
//正常实行完毕,就提交
con.commit();
return (r1 == 1 & r2 == cids.size()) ? CommonsUtils.INSERT_SUCCESS : CommonsUtils.OTHER_EXCEPTION;
} catch (SQLException e) {
// e.printStackTrace();
try {
//出问题了就回滚
con.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
//关闭连接
DBUtils.close(con);
}
return CommonsUtils.OTHER_EXCEPTION;
}
}
GradeDao.java
package demo.dao;
import demo.model.Course;
import demo.model.Grade;
import demo.model.GradeDTO;
import demo.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class GradeDao {
QueryRunner queryRunner = new QueryRunner(DBUtils.getDs());
public List<GradeDTO> getAllGrades() throws SQLException {
return queryRunner.query("select g.*,c.* from grade g left join grade_course gc on g.`gid`=gc.`gid` left join course c on gc.`cid`=c.`cid` order by g.`gid` ", new ResultSetHandler<List<GradeDTO>>() {
@Override
public List<GradeDTO> handle(ResultSet rs) throws SQLException {
List<GradeDTO> list = new ArrayList<>();
//上一次遍历发年纪id
int lastGid = -1;
GradeDTO gradeDTO = null;
while (rs.next()) {
int gid = rs.getInt("gid");
if (gid == lastGid) {
//说明这一行的年级已经读过了，这个地方只要处理课程id和课程名称
Course c = new Course();
c.setCid(rs.getInt("cid"));
c.setCourseName(rs.getString("courseName"));
gradeDTO.getCourses().add(c);
} else {
//这一行的年级还没读过,这里要处理四列
//说明这里是一个新的年级了
gradeDTO = new GradeDTO();
gradeDTO.setGid(gid);
gradeDTO.setGradeName(rs.getString("gradeName"));
List<Course> courses = new ArrayList<>();
Course c = new Course();
c.setCid(rs.getInt("cid"));
c.setCourseName(rs.getString("courseName"));
courses.add(c);
gradeDTO.setCourses(courses);
list.add(gradeDTO);
}
lastGid = gid;
}
return list;
}
});
}
public Grade getGradeByGradeName(String gradeName) throws SQLException {
return queryRunner.query("select * from grade where gradeName=? ", new BeanHandler<>(Grade.class), gradeName);
}
/**
* 注意,为了事务这个地方需要确保这个查询方法使用和service中相同的connection
*
* @param grade
* @return
*/
public Integer addGrade(Grade grade) throws SQLException {
Connection con = DBUtils.getCon();
PreparedStatement ps = con.prepareStatement("insert into grade(gradeName) values (?)", Statement.RETURN_GENERATED_KEYS);
ps.setString(1, grade.getGradeName());
//执行插入操作
int i = ps.executeUpdate();
//获取刚刚插入记录的id
//这里获取的rs只有一行一列
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
int anInt = rs.getInt(1);
grade.setGid(anInt);
}
DBUtils.close(rs);
DBUtils.close(ps);
return i;
}
/**
* insert into grade_course(gid,cid) values(?,?),(?,?)
* <p>
* gid 9
* cids 1 2 3 4
* <p>
* 9 1 9 2 9 3 9 4
*
* @param gid
* @param cids
* @return
*/
public Integer addGradeCourse(Integer gid, List<Integer> cids) throws SQLException {
StringBuffer sql = new StringBuffer("insert into grade_course(gid,cid) values");
//参数的长度是cids长度的2倍
Object[] params = new Object[cids.size() * 2];
for (int i = 0; i < cids.size(); i++) {
if (i == cids.size() - 1) {
//最后一次遍历
sql.append("(?,?)");
} else {
sql.append("(?,?),");
}
params[2 * i] = gid;
params[2 * i + 1] = cids.get(i);
}
return queryRunner.update(DBUtils
.getCon(), sql.toString(), params);
}
}
DBUtils.java
package demo.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class DBUtils {
private static DataSource ds = null;
private static final Properties PROPERTIES = new Properties();
private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();
static {
try {
PROPERTIES.load(DBUtils.class.getResourceAsStream("/jdbc.properties"));
ds = DruidDataSourceFactory.createDataSource(PROPERTIES);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getCon() {
Connection connection = THREAD_LOCAL.get();
if (connection == null) {
try {
//说明当前线程是第一次来获取con的,此时应该从ds中拿一个出来
Connection con = ds.getConnection();
THREAD_LOCAL.set(con);
return con;
} catch (SQLException e) {
e.printStackTrace();
}
}
return connection;
}
public static void close(Connection con) {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(PreparedStatement con) {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet con) {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static DataSource getDs() {
return ds;
}
}
3260
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
