1、DBUtil包连接数据库
2、Bin包设计成员函数及方法
3、Dao包设计sql语句
4、servlet包增删改查方法
5、service连接servlet
6、设计jsp增删改查页面
7、连接各个页面
package dao; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import util.Util; import bin.Course; /** * 课程Dao * Dao层操作数据 * @author YP */ public class CourseDao { /** * 添加 * @param course * @return */ public boolean add(Course course) { String sql = "insert into course(name, teach, local) values('" + course.getName() + "','" + course.getTeach() + "','" + course.getLocal() + "')"; //创建数据库链接 Connection conn = Util.getConn(); Statement state = null; boolean f = false; int a = 0; try { state = conn.createStatement(); state.executeUpdate(sql); } catch (Exception e) { e.printStackTrace(); } finally { //关闭连接 Util.close(state, conn); } if (a > 0) { f = true; } return f; } /** * 删除 * * @param id * @return */ public boolean delete (int id) { boolean f = false; String sql = "delete from course where id='" + id + "'"; Connection conn = Util.getConn(); Statement state = null; int a = 0; try { state = conn.createStatement(); a = state.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } finally { Util.close(state, conn); } if (a > 0) { f = true; } return f; } /** * 修改 * @param name * @param pass */ public boolean update(Course course) { String sql = "update course set name='" + course.getName() + "', teach='" + course.getTeach() + "', local='" + course.getLocal() + "' where id='" + course.getId() + "'"; Connection conn = Util.getConn(); Statement state = null; boolean f = false; int a = 0; try { state = conn.createStatement(); a = state.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } finally { Util.close(state, conn); } if (a > 0) { f = true; } return f; } /** * 验证课程名称是否唯一 * true --- 不唯一 * @param name * @return */ public boolean name(String name) { boolean flag = false; String sql = "select name from course where name = '" + name + "'"; Connection conn = Util.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { flag = true; } } catch (SQLException e) { e.printStackTrace(); } finally { Util.close(rs, state, conn); } return flag; } /** * 通过ID得到课程信息 * @param id * @return */ public Course getCourseById(int id) { String sql = "select * from course where id ='" + id + "'"; Connection conn = Util.getConn(); Statement state = null; ResultSet rs = null; Course course = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { String name = rs.getString("name"); String teach = rs.getString("teach"); String local = rs.getString("local"); course = new Course(id, name, teach, local); } } catch (Exception e) { e.printStackTrace(); } finally { Util.close(rs, state, conn); } return course; } /** * 通过name得到Course * @param name * @return */ public Course getCourseByName(String name) { String sql = "select * from course where name ='" + name + "'"; Connection conn = Util.getConn(); Statement state = null; ResultSet rs = null; Course course = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); while (rs.next()) { int id = rs.getInt("id"); String teach = rs.getString("teach"); String local = rs.getString("local"); course = new Course(id, name, teach, local); } } catch (Exception e) { e.printStackTrace(); } finally { Util.close(rs, state, conn); } return course; } /** * 查找 * @param name * @param teach * @param local * @return */ public List<Course> find(String name, String teach, String local) { String sql = "select * from course where "; if (name != "") { sql += "name like '%" + name + "%'"; } if (teach != "") { sql += "teach like '%" + teach + "%'"; } if (local != "") { sql += "local like '%" + local + "%'"; } List<Course> list = new ArrayList<>(); Connection conn = Util.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); Course bean = null; while (rs.next()) { int id = rs.getInt("id"); String name2 = rs.getString("name"); String teach2 = rs.getString("teach"); String local2 = rs.getString("local"); bean = new Course(id, name2, teach2, local2); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { Util.close(rs, state, conn); } return list; } /** * 全部数据 * @param name * @param teach * @param local * @return */ public List<Course> list() { String sql = "select * from course"; List<Course> list = new ArrayList<>(); Connection conn = Util.getConn(); Statement state = null; ResultSet rs = null; try { state = conn.createStatement(); rs = state.executeQuery(sql); Course bean = null; while (rs.next()) { int id = rs.getInt("id"); String name2 = rs.getString("name"); String teach2 = rs.getString("teach"); String local2 = rs.getString("local"); bean = new Course(id, name2, teach2, local2); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { Util.close(rs, state, conn); } return list; } }
package serverlet; import java.io.IOException; import java.util.List; 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 bin.Course; import service.Service; @WebServlet("/CourseServlet") public class CourseServlet extends HttpServlet { private static final long serialVersionUID = 1L; Service service = new Service(); /** * 方法选择 */ protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); String method = req.getParameter("method"); if ("add".equals(method)) { add(req, resp); } else if ("delete".equals(method)) { delete(req, resp); } else if ("update".equals(method)) { update(req, resp); } else if ("find".equals(method)) { find(req, resp); } else if ("getcoursebyid".equals(method)) { getCourseById(req, resp); } else if ("getcoursebyname".equals(method)) { getCourseByName(req, resp); } else if ("list".equals(method)) { list(req, resp); } } /** * 添加*/ private void add(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException { req.setCharacterEncoding("utf-8"); //获取数据 String name = req.getParameter("name"); String teach = req.getParameter("teach"); String local = req.getParameter("local"); Course course = new Course(name, teach, local); //添加后消息显示 if(service.add(course)) { req.setAttribute("message", "添加成功"); req.getRequestDispatcher("add.jsp").forward(req,resp); } else { req.setAttribute("message", "课程名称重复，请重新录入"); req.getRequestDispatcher("add.jsp").forward(req,resp); } } /** * 全部*/ private void list(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); List<Course> courses = service.list(); req.setAttribute("courses", courses); req.getRequestDispatcher("list.jsp").forward(req,resp); } /** * 通过ID得到Course * @param req * @param resp * @throws ServletException */ private void getCourseById(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); int id = Integer.parseInt(req.getParameter("id")); Course course = service.getCourseById(id); req.setAttribute("course", course); req.getRequestDispatcher("update.jsp").forward(req,resp); } /** * 通过名字查找 * 跳转至删除*/ private void getCourseByName(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String name = req.getParameter("name"); Course course = service.getCourseByName(name); if(course == null) { req.setAttribute("message", "查无此课程！"); req.getRequestDispatcher("delete.jsp").forward(req,resp); } else { req.setAttribute("course", course); req.getRequestDispatcher("deletelist.jsp").forward(req,resp); } } /** * 删除*/ private void delete(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); int id = Integer.parseInt(req.getParameter("id")); service.delete(id); req.setAttribute("message", "删除成功！"); req.getRequestDispatcher("delete.jsp").forward(req,resp); } /** * 修改*/ private void update(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); int id = Integer.parseInt(req.getParameter("id")); String name = req.getParameter("name"); String teach = req.getParameter("teach"); String local = req.getParameter("local"); Course course = new Course(id, name, teach, local); service.update(course); req.setAttribute("message", "修改成功"); req.getRequestDispatcher("CourseServlet?method=list").forward(req,resp); } /** * 查找 * @param req * @param resp * @throws ServletException */ private void find(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String name = req.getParameter("name"); String teach = req.getParameter("teach"); String local = req.getParameter("local"); List<Course> courses = service.find(name, teach, local); req.setAttribute("courses", courses); req.getRequestDispatcher("findlist.jsp").forward(req,resp); } }
package service; import java.util.List; import dao.CourseDao; import bin.Course; /** * CourseService * 服务层 * @author YP * */ public class Service { CourseDao cDao = new CourseDao(); /** * 添加 */ public boolean add(Course course) { boolean f = false; if(!cDao.name(course.getName())) { cDao.add(course); f = true; } return f; } /** * 删除 */ public void delete(int id) { cDao.delete(id); } /** * 修改 */ public void update(Course course) { cDao.update(course); } /** * 通过ID得到一个Course * @return */ public Course getCourseById(int id) { return cDao.getCourseById(id); } /** * 通过Name得到Course */ public Course getCourseByName(String name) { return cDao.getCourseByName(name); } /** * 查找 */ public List<Course> find(String name, String teacher, String classroom) { return cDao.find(name, teacher, classroom); } /** * 全部数据 */ public List<Course> list() { return cDao.list(); } }
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>新增课程</title> </head> <body> <% Object message = request.getAttribute("message"); if(message!=null && !"".equals(message)){ %> <script type="text/javascript"> alert("<%=request.getAttribute("message")%>"); </script> <%} %> <div align="center"> <h1 style="color: black;">课程信息录入</h1> <a href="main.jsp">返回主页</a> <form action="CourseServlet?method=add" method="post" onsubmit="return check()"> <div class="a"> 课程名称<input type="text" id="name" name="name"/> </div> <div class="a"> 任课教师<input type="text" id="teach" name="teach" /> </div> <div class="a"> 上课地点<input type="text" id="local" name="local" /> </div> <div class="a"> <button type="submit" class="b">保 存</button> </div> </form> </div> <script type="text/javascript"> function check() { var name = document.getElementById("name");; var teach = document.getElementById("teach"); var local = document.getElementById("local"); //非空 if(name.value == '') { alert('课程名称为空'); name.focus(); return false; } if(teach.value == '') { alert('教师为空'); teach.focus(); return false; } if(local.value == '') { alert('上课地点为空'); local.focus(); return false; } //教师 if(teach.value != '王建民' && teach.value != '王辉' && teach.value != '刘丹' && teach.value != '刘立嘉' && teach.value != '杨子光'){ alert('教师名称错误'); return false; } //教室 if(!/^基教/.test(local.value) && !/^一教/.test(local.value) && !/^二教/.test(local.value) && !/^三教/.test(local.value)) { alert('上课地点错误'); return false; } } </script> </body> </html>
<%@page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html > <html> <head> <meta charset="UTF-8"> <title>删除查询</title> </head> <body> <% Object message = request.getAttribute("message"); if(message!=null && !"".equals(message)){ %> <script type="text/javascript"> alert("<%=request.getAttribute("message")%>"); </script> <%} %> <div align="center"> <h1 style="color: black;">课程信息删除</h1> <a href="main.jsp">返回主页</a> <form action="CourseServlet?method=getcoursebyname" method="post" onsubmit="return check()"> <div class="a"> 课程名称<input type="text" id="name" name="name"/> </div> <div class="a"> <button type="submit" class="b">查 找</button> </div> </form> </div> <script type="text/javascript"> function check() { var name = document.getElementById("name");; //非空 if(name.value == '') { alert('课程名称为空'); name.focus(); return false; } } </script> </body> </html>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>删除界面</title> </head> <body> <div align="center"> <h1 style="color: black;">课程信息删除</h1> <a href="main.jsp">返回主页</a> <table class="tb"> <tr> <td>课程名称</td> <td>${course.name}</td> </tr> <tr> <td>任课教师</td> <td>${course.teach}</td> </tr> <tr> <td>上课地点</td> <td>${course.local}</td> </tr> </table> <div class="a"> <a onclick="return check()" href="CourseServlet?method=delete&id=${course.id}">删 除</a> </div> </div> <script type="text/javascript"> function check() { if (confirm("真的要删除吗？")){ return true; }else{ return false; } } </script> </body> </html>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>显示</title> </head> <body> <% Object message = request.getAttribute("message"); if(message!=null && !"".equals(message)){ %> <script type="text/javascript"> alert("<%=request.getAttribute("message")%>"); </script> <%} %> <div align="center"> <h1 style="color: black;">课程信息列表</h1> <a href="main.jsp">返回主页</a> <table class="tb"> <tr> <td>id</td> <td>课程名称</td> <td>任课教师</td> <td>上课地点</td> <td>操作</td> </tr> <c:forEach items="${courses}" var="item" varStatus="status"> <tr> <td>${item.id}</td> <td>${item.name}</td> <td>${item.teach}</td> <td>${item.local}</td> <td><a href="CourseServlet?method=getcoursebyid&id=${item.id}">修改</a></td> </tr> </c:forEach> </table> </div> </body> </html>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>修改课程信息页面</title> </head> <body> <% Object message = request.getAttribute("message"); if(message!=null && !"".equals(message)){ %> <script type="text/javascript"> alert("<%=request.getAttribute("message")%>"); </script> <%} %> <div align="center"> <h1 style="color: black;">课程信息修改</h1> <a href="main.jsp">返回主页</a> <form action="CourseServlet?method=update" method="post" onsubmit="return check()"> <div class="a"> 课程名称<input type="text" id="name" name="name" value="${course.name}"/> </div> <div class="a"> 任课教师<input type="text" id="teach" name="teach" value="${course.teach}"/> </div> <div class="a"> 上课地点<input type="text" id="local" name="local" value="${course.local}"/> </div> <input type="hidden" id="id" name="id" value="${course.id}"/> <div class="a"> <button type="submit" class="b">修 改</button> </div> </form> </div> <script type="text/javascript"> function check() { var name = document.getElementById("name");; var teach = document.getElementById("teach"); var local = document.getElementById("local"); //非空 if(name.value == '') { alert('课程名称为空'); name.focus(); return false; } if(teach.value == '') { alert('教师为空'); teach.focus(); return false; } if(local.value == '') { alert('上课地点为空'); local.focus(); return false; } //教师 if(teach.value != '王建民' && teach.value != '王辉' && teach.value != '刘丹' && teach.value != '刘立嘉' && teach.value != '杨子光'){ alert('教师名称错误'); return false; } //教室 if(!/^基教/.test(local.value) && !/^一教/.test(local.value) && !/^二教/.test(local.value) && !/^三教/.test(local.value)) { alert('上课地点错误'); return false; } } </script> </body> </html>
<%@page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html > <html> <head> <meta charset="UTF-8"> <title>查找界面</title> </head> <body> <div align="center"> <h1 style="color: black;">课程信息查询</h1> <a href="main.jsp">返回主页</a> <form action="CourseServlet?method=search" method="post" onsubmit="return check()"> <div class="a"> 课程名称<input type="text" id="name" name="name"/> </div> <div class="a"> 任课教师<input type="text" id="teach" name="teach" /> </div> <div class="a"> 上课地点<input type="text" id="local" name="local" /> </div> <div class="a"> <button type="submit" class="b">查 询</button> </div> </form> </div> <script type="text/javascript"> function check() { var name = document.getElementById("name"); var teach = document.getElementById("teach"); var local = document.getElementById("local"); //非空 if(name.value == '' && teach.value == '' && local.value == '') { alert('请填写一个条件'); return false; } } </script> </body> </html>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>查询结果</title> </head> <body> <div align="center"> <h1 style="color: black;">课程信息列表</h1> <a href="main.jsp">返回主页</a> <table class="tb"> <tr> <td>id</td> <td>课程名称</td> <td>任课教师</td> <td>上课地点</td> </tr> <!-- forEach遍历出adminBeans --> <c:forEach items="${courses}" var="item" varStatus="status"> <tr> <td>${item.id}</td> <td>${item.name}</td> <td>${item.teach}</td> <td>${item.local}</td> </tr> </c:forEach> </table> </div> </body> </html>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html > <html> <head> <meta charset="UTF-8"> <title>首页</title> </head> <body> <h1 style="font-family:宋体;font-size:1.5em; 550px" align="right">课程信息管理系统</h1> <div align="center"> <div class="a"> <a href="add.jsp">课程信息添加</a> </div> <div class="a"> <a href="CourseServlet?method=list">课程信息修改</a> </div> <div class="a"> <a href="delete.jsp">课程信息删除</a> </div> <div class="a"> <a href="find.jsp">课程信息查询</a> </div> </div> </body> </html>