专业管理菜单的增删改、查重

1，点击专业管理菜单------查询所有专业信息列表

①点击菜单，切换专业组件

②切换到列表组件后，向后端发送请求到Servlet

③调用DAO层，查询数据库（sql），封装查询到的内容

④从后端向前端做出响应

⑤在前端进行渲染

网页点击触发查询事件，向后端传入"select"，后端接收，再响应数据回到前端。

前端：

<template><div><el-button type="primary">添加专业信息</el-button><el-table :data="tableData" height="400" border style="width: 100%"><el-table-column prop="id" label="序号" width="100"></el-table-column><el-table-column prop="name" label="专业名" width="100"></el-table-column><el-table-column prop="account" label="修改人"></el-table-column><el-table-column  value-format="yyyy-MM-dd HH:mm:ss" prop="oper_time" label="修改时间" align="center"></el-table-column><el-table-column width="200"><template slot-scope="scope"><el-button type="primary" icon="el-icon-edit"></el-button><el-button type="primary" icon="el-icon-delete"></el-button></template></el-table-column></el-table></div>
</template>
<script>export default {data() {return {tableData: []}},methods: {},mounted() {this.$http.get("admin/MajorServlet?mark=select").then(resp => {this.tableData = resp.data.data;})}}
</script><style>
</style>

 后端：后端接收数据，对数据库查询，获得数据，传回前端

    private void selectMajor(HttpServletRequest req, HttpServletResponse resp) throws IOException {PrintWriter printWriter = resp.getWriter();CommonData commonData = null;try {MajorDao majorDao = new MajorDao();List<Major> majorlist = majorDao.majors();commonData = new CommonData(200, majorlist, "查询成功！");} catch (SQLException throwables) {throwables.printStackTrace();commonData = new CommonData(200, "服务器忙...请稍后再试！");}ObjectMapper objectMapper = new ObjectMapper();String json = objectMapper.writeValueAsString(commonData);printWriter.print(json);}

    public List<Major>majors() throws SQLException {List<Major>list=new ArrayList<>();Connection connection= DButils.getConnection();String sql="SELECT m.id,m.name,a.account,m.oper_time\n" +"FROM major m LEFT JOIN USER a \n" +"ON a.id=m.adminid;";PreparedStatement preparedStatement= connection.prepareStatement(sql);ResultSet resultSet=preparedStatement.executeQuery();while(resultSet.next()){Major major=new Major();major.setId(resultSet.getInt("id"));major.setAccount(resultSet.getString("account"));major.setName(resultSet.getString("name"));major.setOper_time(resultSet.getDate("oper_time"));list.add(major);}DButils.close(resultSet,preparedStatement,connection);return list;}

页面显示结果：

---

2，新增专业（要求不能新增已有专业--查重） 

①点击新增，打开新增专业表单

②在表单中动态显示专业信息

③输入专业信息（查重，使用axios响应拦截器）

④点击保存，提交数据到后端，保存到数据库，前端更新页面

前端：

<template><el-dialog title="新增专业" :visible.sync="dialogFormVisible"><el-form label-width="80px"><el-form-item label="专业名"><el-input v-model="form.name"></el-input></el-form-item><el-form-item label="授课老师"><el-input v-model="form.teacher"></el-input></el-form-item></el-form><div slot="footer" class="dialog-footer"><el-button @click="dialogFormVisible = false">取 消</el-button><el-button type="primary" @click="save()">确 定</el-button></div></el-dialog>
</template><script>export default {data() {return {dialogFormVisible: false,majorlist: [],form: {name:"",teacher:"",}}},methods: {save() {this.$http.post("admin/MajorServlet?mark=save", jsonToString(this.form)).then(resp => {if (resp.data.code == 200) {this.$message({message: resp.data.message,type: 'success'});this.dialogFormVisible = false;this.$router.go(); //更新当前页面}})}},mounted() {this.$http.get("admin/StudentServlet?mark=majorselect").then(resp => {this.majorlist = resp.data.data;})}}//将json对象序列化为键=值&键=值function jsonToString(jsonobj) {console.log(jsonobj)var str = "";for (var s in jsonobj) {str += s + "=" + jsonobj[s] + "&";}return str.substring(0, str.length - 1);}
</script><style>
</style>

后端： POST请求，传入save字符串，获取传来的数据，新增到数据库。

public class MajorServlet extends HttpServlet {@Overrideprotected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {String mark=req.getParameter("mark");if("save".equals(mark)){saveMajor(req,resp);}}private void saveMajor(HttpServletRequest req, HttpServletResponse resp) throws IOException {PrintWriter printWriter = resp.getWriter();MajorDao majorDao=new MajorDao();CommonData commonData = null;try {String id=req.getParameter("id");String name = req.getParameter("name");String teacher=req.getParameter("teacher");String adminToken = req.getHeader("userToken");System.out.println(adminToken);DecodedJWT decodedJWT = JWTUtil.getTokenInfo(adminToken);int adminid = decodedJWT.getClaim("id").asInt();Date date = null;if(id==null){majorDao.save(name,teacher,adminid);commonData = new CommonData(200, "新增成功！");}
//            else
//            {
//                studentDao.update(id,num,name,gender,birthday,phone, address,adminid, majorid, date);
//                commonData = new CommonData(200, "修改成功！");
//            }} catch (Exception e) {e.printStackTrace();commonData = new CommonData(500, "服务器忙，请稍后重试！");}ObjectMapper objectMapper = new ObjectMapper();String json = objectMapper.writeValueAsString(commonData);printWriter.print(json);}
}

调用DAO层，将数据打包发送给前端

import javax.imageio.ImageReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;public class MajorDao {public void save(String name, String teacher, int adminid) throws SQLException {String sql="INSERT INTO major(NAME,adminid," +"oper_time,teacher) " +"VALUES (?,?,?,?);";Connection connection= DButils.getConnection();PreparedStatement preparedStatement=connection.prepareStatement(sql);preparedStatement.setObject(1,name);preparedStatement.setObject(2,adminid);preparedStatement.setObject(3,new Date());preparedStatement.setObject(4,teacher);preparedStatement.executeUpdate();}
}

结果显示：

在新增专业基础上进行查重，如果已经存在当前新增的专业和任课老师，则新增失败。

 我使用了两个方法，第一个是在DAO层数据库查询判断是否为空，为空则返回状态值“200”，表示可以新增，否则不可以。

后端：

if(id==null){if(!majorDao.check(name,teacher)){majorDao.save(name,teacher,adminid);commonData = new CommonData(200, "新增成功！");}else{System.out.println("课程已存在！");commonData = new CommonData(202, "课程已存在！");}}

    public boolean check(String name, String teacher) throws SQLException {String sql="SELECT teacher,name FROM major WHERE teacher=? AND name=?";Connection connection= DButils.getConnection();PreparedStatement preparedStatement=connection.prepareStatement(sql);preparedStatement.setObject(1,teacher);preparedStatement.setObject(2,name);ResultSet resultSet=preparedStatement.executeQuery();return resultSet.next();}

前端：

		methods: {save() {this.$http.post("admin/MajorServlet?mark=save", jsonToString(this.form)).then(resp => {if (resp.data.code == 200) {this.$message({message: resp.data.message,type: 'success'});this.dialogFormVisible = false;} else if (resp.data.code == 202) {this.$message({message: resp.data.message,type: 'warning'});}this.$router.go(); //更新当前页面})}},

第二种方法：

axios响应拦截器，类似例子见登录或注册，直接查询是否存在当前账号。

以百度账号注册为例进行模仿：

---

3，修改专业

①点击修改按钮，弹出对话框，拿到专业id，传到后端，后端数据库查询

②在表单中显示专业信息

③提交学生信息到后端，更新数据库

 首先对各属性值进行前后端绑定，如下样式

然后在已有基础上进行修改。

前端代码：首先向前端传入当前修改的专业id，从后端获取该id下的专业信息。

			updateinfo(id){this.form.id=id;this.$http.get("admin/StudentServlet?mark=findstudent&id="+id).then(resp=>{this.form=resp.data.data;})}

 后端代码：

   @Overrideprotected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {String mark = req.getParameter("mark");if ("delete".equals(mark)) {deleteMajor(req, resp);} else if ("select".equals(mark)) {selectMajor(req, resp);}else if("update".equals(mark)) {update(req,resp);}}private void update(HttpServletRequest req, HttpServletResponse resp) throws IOException {PrintWriter printWriter = resp.getWriter();CommonData commonData = null;try {MajorDao majorDao=new MajorDao();String id = req.getParameter("id");Major major = majorDao.findmajor(id);commonData = new CommonData(200, major, "查询成功！");} catch (Exception e) {e.printStackTrace();commonData = new CommonData(500, "服务器忙，请稍后重试！");}ObjectMapper objectMapper = new ObjectMapper();String json = objectMapper.writeValueAsString(commonData);printWriter.print(json);}

dao层：查询

    public Major findmajor(String id) throws SQLException {String sql="SELECT NAME,teacher FROM major WHERE id=?;";Connection connection= DButils.getConnection();PreparedStatement preparedStatement=connection.prepareStatement(sql);preparedStatement.setObject(1,id);ResultSet resultSet=preparedStatement.executeQuery();Major major=new Major();while(resultSet.next()){major.setTeacher(resultSet.getString("teacher"));major.setName(resultSet.getString("name"));}return major;}

修改之后，进行保存。

 保存大体上和新增类似，依旧使用新增的save()函数，向后端传去save字符串，

			save() {this.$http.post("admin/MajorServlet?mark=save", jsonToString(this.form)).then(resp => {if (resp.data.code == 200) {this.$message({message: resp.data.message,type: 'success'});}this.dialogFormVisible = false;this.$router.go(); //更新当前页面})}

后端收到前端传来的请求，判断id是否为空。如果id为空说明此时是新增专业，如果id不为空，说明是修改专业信息，从后端传来的id。

所以我们就依据id是否为空来使用分支编写代码。

 private void saveMajor(HttpServletRequest req, HttpServletResponse resp) throws IOException {PrintWriter printWriter = resp.getWriter();MajorDao majorDao=new MajorDao();CommonData commonData = null;try {String id=req.getParameter("id");String name = req.getParameter("name");String teacher=req.getParameter("teacher");String adminToken = req.getHeader("userToken");System.out.println(adminToken);DecodedJWT decodedJWT = JWTUtil.getTokenInfo(adminToken);int adminid = decodedJWT.getClaim("id").asInt();Date date = null;if(id==null){if(!majorDao.check(name,teacher)){majorDao.save(name,teacher,adminid);commonData = new CommonData(200, "新增成功！");}else{System.out.println("课程已存在！");commonData = new CommonData(202, "课程已存在！");}}else//id不为空，存在当前专业，进行修改操作{if(!majorDao.check(name,teacher)){majorDao.update(id,name,teacher,adminid);commonData = new CommonData(200, "修改成功！");}else{System.out.println("课程存在！无需再增加");commonData = new CommonData(202, "课程存在！无需再增加");}}} catch (Exception e) {e.printStackTrace();commonData = new CommonData(500, "服务器忙，请稍后重试！");}ObjectMapper objectMapper = new ObjectMapper();String json = objectMapper.writeValueAsString(commonData);printWriter.print(json);}

在修改专业信息情况下调用dao层。

public void update(String id, String num, String name, String gender, String birthday, String phone, String address, int adminid, String majorid, Date date) throws SQLException {String sql="UPDATE student s SET\n" +" s.num=?,\n" +" s.name=?,\n" +" s.gender=?,\n" +" s.address=?,\n" +" s.phone=?,\n" +" s.birthday=?,\n" +" s.majorid=?,\n" +" s.adminid=?,\n" +" s.oper_time=?\n" +" WHERE id=?;";Connection connection= DButils.getConnection();PreparedStatement preparedStatement=connection.prepareStatement(sql);preparedStatement.setObject(1,num);preparedStatement.setObject(2,name);preparedStatement.setObject(3,gender);preparedStatement.setObject(4,address);preparedStatement.setObject(5,phone);preparedStatement.setObject(6,birthday);preparedStatement.setObject(7,majorid);preparedStatement.setObject(8,adminid);preparedStatement.setObject(9,new Date());preparedStatement.setObject(10,id);preparedStatement.executeUpdate();}

 最后要判断一下，如果该专业已经存在那么修改失败。

结果展示：

---

 4，删除专业

 ①点击删除按钮，弹出对话框，拿到专业id，传到后端，后端数据库删除

②点击确认删除，保存到数据库，前端更新页面

前端代码：向后端传id，和字符串

handleDelete(id) {this.$confirm('此操作将删除当前专业, 是否继续?', '操作提示', {confirmButtonText: '确定',cancelButtonText: '取消',type: 'warning'}).then(() => {this.$http.get("admin/MajorServlet?mark=delete&id=" + id).then(resp => {this.$message({message: resp.data.message,type: 'success'});this.$router.go(); //更新当前页面});});}

后端获取数据，调用dao删除当前专业

private void deleteMajor(HttpServletRequest req, HttpServletResponse resp) throws IOException {PrintWriter printWriter = resp.getWriter();MajorDao majorDao = new MajorDao();CommonData commonData = null;try {majorDao.deleteinfo(req.getParameter("id"));commonData = new CommonData(200, "删除成功！");} catch (Exception e) {e.printStackTrace();commonData = new CommonData(500, "服务器忙，请稍后重试！");}ObjectMapper objectMapper = new ObjectMapper();String json = objectMapper.writeValueAsString(commonData);printWriter.print(json);}

dao：

public void deleteinfo(String id) throws SQLException {String sql="DELETE FROM major WHERE id=?";Connection connection= DButils.getConnection();PreparedStatement preparedStatement=connection.prepareStatement(sql);preparedStatement.setObject(1,id);preparedStatement.executeUpdate();}