课程管理系统详解
一. 新建数据表
在mysql中新建一个名为course的数据库,并在其中新建一个course数据表,包含4个字段,id,name,teacher,classname
ps:用一个叫做SQLyog的数据库操作软件实现的
注意:将id设为自动递增,否则后面新增会出错。id设为自增,新增时就只需关注course的其它属性。
二. 新建web项目
1. eclipse新建一个名为MyServlet的Dynamic Web Project
生成的项目在java EE透视图中的结构如下:
2. 将连接mysql的驱动jar包(mysql-connector-java-5.1.7-bin.jar)copy到WEB-INF下的lib目录下
3. 新建五个包,entity存放实体类(User),dao存放数据库操作类(UserDao),servlet存放控制类(addServlet,updateServlet,deleteServlet,listServlet),util存放开发帮助类
service是算法实现(这里是数据库操作帮助类,封装了数据库连接部分代码,避免大量重复代码)
4. 新建类,java类放在对应包中,jsp页面放在WebContent目录下,替换web.xml中内容,结构如图
5.代码:
//Course.Java package com.hjf.entity; 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; } }
//CoursseDao.java package com.hjf.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 com.hjf.entity.Course; import com.hjf.util.DBUtil; /** * 课程Dao * Dao层操作数据 * @author Hu * */ public class CourseDao { /** * 添加 * @param course * @return */ public boolean add(Course course) { 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; } }
//CourseService.java package com.hjf.service; import java.util.List; import com.hjf.dao.CourseDao; import com.hjf.entity.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(); } }
//CourseService.java
package com.hjf.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 com.hjf.entity.Course;
import com.hjf.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);
//添加后消息显示
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);
}
}
//DBUtil.java
package com.hjf.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";
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("不空");
}
}
}
<!-- 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" οnsubmit="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>
<!-- del.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" οnsubmit="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>
<!-- detail.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>
<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 οnclick="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>
<!-- detail2.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=update" method="post" οnsubmit="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>
<!-- index.jsp -->
<%@ 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">
<div class="a">
<a href="add.jsp">课程信息添加</a>
</div>
<div class="a">
<a href="CourseServlet?method=list">课程信息修改</a>
</div>
<div class="a">
<a href="del.jsp">课程信息删除</a>
</div>
<div class="a">
<a href="search.jsp">课程信息查询</a>
</div>
</div>
</body>
</html>
<!-- list.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>
<!-- search.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" οnsubmit="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>
<!-- searchlist.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>
<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>
</tr>
<!-- forEach遍历出adminBeans -->
<c:forEach items="${courses}" var="item" varStatus="status">
<tr>
<td>${item.id}</td>
<td><a>${item.name}</a></td>
<td>${item.teacher}</td>
<td>${item.classroom}</td>
</tr>
</c:forEach>
</table>
</div>
</body>
</html>
别忘了修改DBUtil中的用户名和密码,根据个人而定
6.运行结果如下:
主页面:
添加:
修改:
删除:
查询:
2、使用web页面连接数据库、增加信息到数据库操作;
3、再学会如何使用serverlet控制页面之间的交互
在每一步未完成之前,不要进行下一步
单纯模仿永远不会成功)
3、在不参考成熟代码基础上,自己按照流程图重写代码,编译成功
4、设定一个应用场景(如本文课程信息管理系统),运用自己的代码完成上述操作
这样代码才是你自己的
中间步骤需要相互重复,不断迭代,最后代码才是你的