基本流程:JSP文件显示页面,在前端页面输入赋值,使用form或href超链接传值到Servlet中方法,在Servlet方法中调用Dao层的类对象,将数据传到数据库中,并实现对数据库里的数据的增删改查,完成后将值重新返回到JSP输出操作结果数据。
共分为五个包和对应的JSP文件:
1.Util包:建立连接数据库的方法,以及关闭操作数据库的方法。
package Util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * 数据库连接工具 * @author Hu * */ public class DBUtil { public static String db_url = "jdbc:mysql://localhost:3306/course?useUnicode=true&characterEncoding=UTF-8"; public static String db_user = "root"; public static String db_pass = "root"; public static Connection getConn () { Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver");//加载驱动 conn = DriverManager.getConnection(db_url, db_user, db_pass); } catch (Exception e) { e.printStackTrace(); } return conn; } /** * 关闭连接 * @param state * @param conn */ public static void close (Statement state, Connection conn) { if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close (ResultSet rs, Statement state, Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) throws SQLException { Connection conn = getConn(); PreparedStatement pstmt = null; ResultSet rs = null; String sql ="select * from course"; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); if(rs.next()){ System.out.println("空"); }else{ System.out.println("不空"); } } }
2.Servlet包:①接受来自JSP页面的参数,将这些参数收集起来。②将Dao层传回来的操作结果返回到JSP页面显示。
package Servlet; 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 Javabean.Course; import Service.CourseService; @WebServlet("/CourseServlet") public class CourseServlet extends HttpServlet { private static final long serialVersionUID = 1L; CourseService service = new CourseService(); /** * 方法选择 */ 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 ("del".equals(method)) { del(req, resp); } else if ("update".equals(method)) { update(req, resp); } else if ("search".equals(method)) { search(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); } } /** * 添加 * @param req * @param resp * @throws IOException * @throws ServletException */ private void add(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException { req.setCharacterEncoding("utf-8"); //获取数据 String name = req.getParameter("name"); String teacher = req.getParameter("teacher"); String classroom = req.getParameter("classroom"); Course course = new Course(name, teacher, classroom); System.out.println(name+" "+teacher); //添加后消息显示 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); } } /** * 全部 * @param req * @param resp * @throws ServletException */ 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("detail2.jsp").forward(req,resp); } /** * 通过名字查找 * 跳转至删除 * @param req * @param resp * @throws IOException * @throws ServletException */ 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("del.jsp").forward(req,resp); } else { req.setAttribute("course", course); req.getRequestDispatcher("detail.jsp").forward(req,resp); } } /** * 删除 * @param req * @param resp * @throws IOException * @throws ServletException */ private void del(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); int id = Integer.parseInt(req.getParameter("id")); service.del(id); req.setAttribute("message", "删除成功!"); req.getRequestDispatcher("del.jsp").forward(req,resp); } /** * 修改 * @param req * @param resp * @throws IOException * @throws ServletException */ 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 teacher = req.getParameter("teacher"); String classroom = req.getParameter("classroom"); Course course = new Course(id, name, teacher, classroom); service.update(course); req.setAttribute("message", "修改成功"); req.getRequestDispatcher("CourseServlet?method=list").forward(req,resp); } /** * 查找 * @param req * @param resp * @throws ServletException */ private void search(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{ req.setCharacterEncoding("utf-8"); String name = req.getParameter("name"); String teacher = req.getParameter("teacher"); String classroom = req.getParameter("classroom"); List<Course> courses = service.search(name, teacher, classroom); req.setAttribute("courses", courses); req.getRequestDispatcher("searchlist.jsp").forward(req,resp); } }
3.Javabean包:定义一个Course类,将要操作的对象归纳出他的属性和get,set方法。
package Javabean; public class Course { private int id; private String name; private String teacher; private String classroom; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getTeacher() { return teacher; } public void setTeacher(String teacher) { this.teacher = teacher; } public String getClassroom() { return classroom; } public void setClassroom(String classroom) { this.classroom = classroom; } public Course() {} public Course(int id, String name, String teacher, String classroom) { this.id = id; this.name = name; this.teacher = teacher; this.classroom = classroom; } public Course(String name, String teacher, String classroom) { this.name = name; this.teacher = teacher; this.classroom = classroom; } }
4.Dao层包:最主要的包,在这里通过Util包链接数据库,进行对数据库中的数据的增删改查,主要运用了数据库语言。
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 Javabean.Course; import Util.DBUtil; /** * 课程Dao * Dao层操作数据 * @author Hu * */ public class CourseDao { /** * 添加 * @param course * @return */ public boolean add(Course course) { System.out.println(course.getTeacher()); String sql = "insert into course(name, teacher, classroom) values('" + course.getName() + "','" + course.getTeacher() + "','" + course.getClassroom() + "')"; //创建数据库链接 Connection conn = DBUtil.getConn(); Statement state = null; boolean f = false; int a = 0; try { state = conn.createStatement(); state.executeUpdate(sql); } catch (Exception e) { e.printStackTrace(); } finally { //关闭连接 DBUtil.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 = DBUtil.getConn(); Statement state = null; int a = 0; try { state = conn.createStatement(); a = state.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.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() + "', teacher='" + course.getTeacher() + "', classroom='" + course.getClassroom() + "' where id='" + course.getId() + "'"; Connection conn = DBUtil.getConn(); Statement state = null; boolean f = false; int a = 0; try { state = conn.createStatement(); a = state.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.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 = DBUtil.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 { DBUtil.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 = DBUtil.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 teacher = rs.getString("teacher"); String classroom = rs.getString("classroom"); course = new Course(id, name, teacher, classroom); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.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 = DBUtil.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 teacher = rs.getString("teacher"); String classroom = rs.getString("classroom"); course = new Course(id, name, teacher, classroom); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return course; } /** * 查找 * @param name * @param teacher * @param classroom * @return */ public List<Course> search(String name, String teacher, String classroom) { String sql = "select * from course where "; if (name != "") { sql += "name like '%" + name + "%'"; } if (teacher != "") { sql += "teacher like '%" + teacher + "%'"; } if (classroom != "") { sql += "classroom like '%" + classroom + "%'"; } List<Course> list = new ArrayList<>(); Connection conn = DBUtil.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 teacher2 = rs.getString("teacher"); String classroom2 = rs.getString("classroom"); bean = new Course(id, name2, teacher2, classroom2); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return list; } /** * 全部数据 * @param name * @param teacher * @param classroom * @return */ public List<Course> list() { String sql = "select * from course"; List<Course> list = new ArrayList<>(); Connection conn = DBUtil.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 teacher2 = rs.getString("teacher"); String classroom2 = rs.getString("classroom"); bean = new Course(id, name2, teacher2, classroom2); list.add(bean); } } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(rs, state, conn); } return list; } }
5.Service包:通过Dao包实现数据库的增删改查。
package Service; import java.util.List; import Dao.CourseDao; import Javabean.Course; /** * CourseService * 服务层 * @author Hu * */ public class CourseService { CourseDao cDao = new CourseDao(); /** * 添加 * @param course * @return */ public boolean add(Course course) { boolean f = false; if(!cDao.name(course.getName())) { cDao.add(course); f = true; } return f; } /** * 删除 */ public void del(int id) { cDao.delete(id); } /** * 修改 * @return */ public void update(Course course) { cDao.update(course); } /** * 通过ID得到一个Course * @return */ public Course getCourseById(int id) { return cDao.getCourseById(id); } /** * 通过Name得到一个Course * @return */ public Course getCourseByName(String name) { return cDao.getCourseByName(name); } /** * 查找 * @return */ public List<Course> search(String name, String teacher, String classroom) { return cDao.search(name, teacher, classroom); } /** * 全部数据 * @return */ public List<Course> list() { return cDao.list(); } }
功能一:增加
在首页中选择功能添加信息,通过href超链接跳转到添加add.JSP页面,在add.JSP页面中的Form表格中输入要添加的相应的信息,通过Form表格的提交按钮将输入框Input中的内容和值(该值确定调用Servlet中的add方法)传给Servlet,在Servlet中调用add方法,在该方法中调用的Dao层中的add方法,Dao层中的add方法,该方法通过DB层获取与数据库的连接,对数据库进行数据添加的功能,之后返回操作结果给Servlet的add方法,由Servlet中的add方法判断输出添加成功的JSP信息页面。
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</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="index.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="teacher" name="teacher" /> </div> <div class="a"> 上课地点<input type="text" id="classroom" name="classroom" /> </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 teacher = document.getElementById("teacher"); var classroom = document.getElementById("classroom"); //非空 if(name.value == '') { alert('课程名称为空'); name.focus(); return false; } if(teacher.value == '') { alert('教师为空'); teacher.focus(); return false; } if(classroom.value == '') { alert('上课地点为空'); classroom.focus(); return false; } //教师 if(teacher.value != '王建民' && teacher.value != '王辉' && teacher.value != '刘丹' && teacher.value != '刘立嘉' && teacher.value != '杨子光'){ alert('教师名称错误'); return false; } //教室 if(!/^基教/.test(classroom.value) && !/^一教/.test(classroom.value) && !/^二教/.test(classroom.value) && !/^三教/.test(classroom.value)) { alert('上课地点错误'); return false; } } </script> </body> </html>
功能二:删除
在首页中选择功能删除信息,通过href超链接跳转到删除del.JSP页面中,在该页面中输入要删除的课程名称,然后传值到Servlet中,在Servlet中通过调用Dao层的查询方法,在数据库中查找是否存在此课程,若存在,将课程全部信息存入List中传给Servlet,Servlet在用JSP文件显示该课程全部信息,用户在确定是否要删除,若要删除则继续传值给Servlet,调用Servlet中的删除del方法,该方法调用Dao层的delete函数,实现数据库中的数据的删除,将操作结果返回至Servlet中,在Servlet中判断输出一个JSP结果页面。
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</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="index.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>Insert title here</title> <style> .a{ margin-top: 20px; } .b{ font-size: 20px; width: 160px; color: white; background-color: greenyellow; } .tb, td { border: 1px solid black; font-size: 22px; } </style> </head> <body> <div align="center"> <h1 style="color: black;">课程信息删除</h1> <a href="index.jsp">返回主页</a> <table class="tb"> <tr> <td>课程名称</td> <td>${course.name}</td> </tr> <tr> <td>任课教师</td> <td>${course.teacher}</td> </tr> <tr> <td>上课地点</td> <td>${course.classroom}</td> </tr> </table> <div class="a"> <a onclick="return check()" href="CourseServlet?method=del&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"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</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="index.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="teacher" name="teacher" value="${course.teacher}"/> </div> <div class="a"> 上课地点<input type="text" id="classroom" name="classroom" value="${course.classroom}"/> </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 teacher = document.getElementById("teacher"); var classroom = document.getElementById("classroom"); //非空 if(name.value == '') { alert('课程名称为空'); name.focus(); return false; } if(teacher.value == '') { alert('教师为空'); teacher.focus(); return false; } if(classroom.value == '') { alert('上课地点为空'); classroom.focus(); return false; } //教师 if(teacher.value != '王建民' && teacher.value != '王辉' && teacher.value != '刘丹' && teacher.value != '刘立嘉' && teacher.value != '杨子光'){ alert('教师名称错误'); return false; } //教室 if(!/^基教/.test(classroom.value) && !/^一教/.test(classroom.value) && !/^二教/.test(classroom.value) && !/^三教/.test(classroom.value)) { alert('上课地点错误'); return false; } } </script> </body> </html>
功能三:修改
在首页中选择功能修改信息,通过href超链接跳转到修改list.JSP页面中,显示全部的数据,点击要修改的一组数据,传值(此值为要修改的编号等 )给Servlet,在Servlet中调用Dao层的update修改方法,update方法修改数据库中数据,将操作结果返回至Servlet中,由Servlet最终调用JSP弹出操作结果。
<%@ 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>Insert title here</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="index.jsp">返回主页</a> <table class="tb"> <tr> <td>id</td> <td>课程名称</td> <td>任课教师</td> <td>上课地点</td> <td align="center" colspan="2">操作</td> </tr> <c:forEach items="${courses}" var="item"> <tr> <td>${item.id}</td> <td>${item.name}</td> <td>${item.teacher}</td> <td>${item.classroom}</td> <td><a href="CourseServlet?method=getcoursebyid&id=${item.id}">修改</a></td> </tr> </c:forEach> </table> </div> </body> </html>
功能四:查找
在首页中选择功能查询信息,通过href超链接跳转到查询search.JSP页面中,在该页面中输入你要查询的信息,可进行粗略和精确查询,将输入的内容通过Form表格传值给Servlet,由Servlet中查询search方法调用Dao层的查询search方法,由此方法在数据库中进行模糊或者精确查询,将查询到的信息装在List中传给Servlet,最终由Servlet返回给JSP一个最终查询的信息。
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<div align="center">
<h1 style="color: black;">课程信息查询</h1>
<a href="index.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="teacher" name="teacher" />
</div>
<div class="a">
上课地点<input type="text" id="classroom" name="classroom" />
</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 teacher = document.getElementById("teacher");
var classroom = document.getElementById("classroom");
//非空
if(name.value == '' && teacher.value == '' && classroom.value == '') {
alert('请填写一个条件');
return false;
}
}
</script>
</body>
</html>