实现技术JSP+Servlet+MySQL
思想：首先前台用jsp获取数据，然后用Form表单传值给Servlet，Servlet操作MySQL，获取数据再通过jsp显示
1.jsp功能选择界面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import ="MySQL.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body style="text-align: center;"> <!--设置body的样式 -->
<div id="menu" style="background-color:#D5D5D5;height:30px;width:;">
<!-- 添加标题 -->
<div id="content" style="float:left;">
课程基本信息管理系统
</div>
<!-- 添加div样式 -->
</div>
<div id="menu-m" style="background-color:#ccffff;height:40px;width:200px;text-align:center;">
<br>
<!-- 添加跳转的A标签 -->
<b><a href="add.jsp">添加新课程</a></b>
</div>
<div id="menu-m" style="background-color:#ccffff;height:40px;width:200px;text-align:center;">
<br><b><a href="delete.jsp">删除课程信息</a></b>
</div>
<div id="menu-m" style="background-color:#ccffff;height:40px;width:200px;text-align:center;">
<br>
<b><a href="search.jsp">查询课程信息</a></b>
</div>
<div id="menu-m" style="background-color:#ccffff;height:40px;width:200px;text-align:center;">
<br>
<b><a href="modification.jsp">修改课程信息</a></b>
</div> </body>
</html>
2.添加新课程（jsp+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 style="text-align: center;"> <div id="login">
<div id="form">
<fieldset> <legend>添加新课程</legend>
<form action="Add" name="add" method="get">
课程名称：<input type="text" name="addclass"><br>
上课老师：<input type="text" name="addteacher"><br>
上课地点：  <input type="text" name="addplace"><br>
<input type="submit" value="添加">
<input type="reset" value="清空">
<br>
</form> </fieldset>
</div>
</div> </body>
</html>
对应的servlet
Add.java
package MySQL; import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement; import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; /**
* Servlet implementation class Add
*/
@WebServlet("/Add")
public class Add extends HttpServlet {
private static final long serialVersionUID = 1L; // JDBC 驱动名及数据库 URL
static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/experiment?useSSL=false&serverTimezone=UTC"; // 数据库的用户名与密码，需要根据自己的设置
static final String USER = "root";
static final String PASS = "HEIYANG";
static boolean flag=true; /**
* @see HttpServlet#HttpServlet()
*/
public Add() {
super();
// TODO Auto-generated constructor stub
} /**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
@SuppressWarnings("resource")
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub response.setContentType("text/html;charset=UTF-8");
response.getWriter().append("Served at: ").append(request.getContextPath()); String addclass=request.getParameter("addclass");//获取输入
String addteacher=request.getParameter("addteacher"); //获取输入
String addplace =request.getParameter("addplace");//获取输入
if(addplace.startsWith("一教")||addplace.startsWith("二教")||addplace.startsWith("三教")||addplace.startsWith("基教")) {
//对教室的判断若不为这几个教室则无法录入
if(addteacher.equals("王建民")||addteacher.equals("刘立嘉")||addteacher.equals("刘丹")||addteacher.equals("王辉")||addteacher.equals("杨子光")) {
//对老师额判断
//连接数据库
Connection conn = null;
Statement stmt = null; // 设置响应内容类型
response.setContentType("text/html;charset=UTF-8"); try{ // 注册 JDBC 驱动器
Class.forName("com.mysql.jdbc.Driver"); // 打开一个连接
conn = DriverManager.getConnection(DB_URL,USER,PASS); // 执行 SQL 查询
stmt = conn.createStatement();
String sql;
sql = "SELECT name FROM classroom";
ResultSet rs = stmt.executeQuery(sql);
// 展开结果集数据库
while(rs.next()){
// 通过字段检索 String username = rs.getString("name"); if(username.equals(addclass))
flag=false;
} // 完成后关闭
rs.close();
stmt.close();
conn.close();
if(flag==true)
{
PreparedStatement stmt1 = null;
// 注册 JDBC 驱动器
Class.forName("com.mysql.jdbc.Driver"); // 打开一个连接
conn = DriverManager.getConnection(DB_URL,USER,PASS); String add= "INSERT into classroom values(?,?,?)";
stmt1 = conn.prepareStatement(add);
((PreparedStatement) stmt1).setString(1, addclass);
((PreparedStatement) stmt1).setString(2, addteacher );
((PreparedStatement) stmt1).setString(3, addplace); stmt1.executeUpdate(); // 完成后关闭
stmt.close();
conn.close(); } } catch(SQLException se) {
// 处理 JDBC 错误
se.printStackTrace();
} catch(Exception e) {
// 处理 Class.forName 错误
e.printStackTrace();
}finally{
// 最后是用于关闭资源的块
try{
if(stmt!=null)
stmt.close();
}catch(SQLException se2){
}
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
if(flag==true)
{
response.getWriter().print("<script language='javascript'>alert('操作失败！')</script>");
response.setHeader("refresh", "1;function.jsp");
}
else
response.sendRedirect("add.jsp"); }else
response.sendRedirect("add.jsp");
}
else
response.sendRedirect("add.jsp"); } /**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
} }
3.修改内容（jsp+servlet）
modification.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 style="text-align: center;"> <div id="login">
<div id="form">
<fieldset> <legend>修改课程信息</legend>
<form action="Modification" name="add" method="get">
选择修改的课程名称：<input type="text" name="addclass0">  修改后的课程名称：<input type="text" name="addclass1"><br>
选择修改的上课老师：<input type="text" name="addteacher0">  修改后的上课老师：<input type="text" name="addteacher1"><br>
选择修改的上课地点：<input type="text" name="addplace0">  修改后的上课地点：<input type="text" name="addplace1"><br>
<input type="submit" value="修改">
<input type="reset" value="清空">
<br>
</form> </fieldset>
</div>
</div>
</body>
</html>
对应的servlet：
Modification.java
package MySQL; import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException; import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; /**
* Servlet implementation class Modification
*/
@WebServlet("/Modification")
public class Modification extends HttpServlet {
private static final long serialVersionUID = 1L; // JDBC 驱动名及数据库 URL
static String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static String DB_URL = "jdbc:mysql://localhost:3306/experiment?useSSL=false&serverTimezone=UTC"; // 数据库的用户名与密码，需要根据自己的设置
static String USER = "root";
static String PASS = "HEIYANG";
/**
* @see HttpServlet#HttpServlet()
*/
public Modification() {
super();
// TODO Auto-generated constructor stub
} /**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
@SuppressWarnings("resource")
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.getWriter().append("Served at: ").append(request.getContextPath()); Connection conn =null;
PreparedStatement stmt = null;
ResultSet rs = null;
boolean Flag=false; String class0=request.getParameter("addclass0");//获取输入
String class1=request.getParameter("addclass1");//获取输入
String teacher0=request.getParameter("addteacher0");//获取输入
String teacher1=request.getParameter("addteacher1");//获取输入
String place0=request.getParameter("addplace0");//获取输入
String place1=request.getParameter("addplace1");//获取输入 try{ try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} String sql;
System.out.println("数据库连接成功");
if(class0!=null)
{
sql="update classroom set name='"+class1+"' where name='"+class0+"'";
stmt = conn.prepareStatement(sql);
stmt.executeUpdate();
}
if(teacher0!=null)
{
sql="update classroom set teacher='"+teacher1+"' where teacher='"+teacher0+"'";
stmt = conn.prepareStatement(sql);
stmt.executeUpdate();
}
if(place0!=null)
{
sql="update classroom set place='"+place1+"' where place='"+place0+"'";
stmt = conn.prepareStatement(sql);
stmt.executeUpdate(); } System.out.println("success");
Flag=true;
// out.flush();
// out.close();
}catch (SQLException e) {
e.printStackTrace();
}finally {
try {
//注意关闭原则：从里到外
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
if(Flag==true)
response.sendRedirect("function.jsp");
else
response.sendRedirect("modification.jsp");
} /**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
} }
4.删除（jsp+servlet）
delete.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 style="text-align: center;"> <div id="login">
<div id="form">
<fieldset>
<legend>删除课程信息</legend>
<form action="delete" name="add" method="get">
要删除的课程名称：<input type="text" name="addclass"><br>
要删除的上课老师：<input type="text" name="addteacher"><br>
要删除的上课地点：  <input type="text" name="addplace"><br>
<input type="submit" value="删除">
<input type="reset" value="清空">
<br>
</form> </fieldset>
</div>
</div> </body>
</html>
delete.java
package MySQL; import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement; 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.mysql.cj.protocol.Resultset; /**
* Servlet implementation class delete
*/
@WebServlet("/delete")
public class delete extends HttpServlet {
private static final long serialVersionUID = 1L; // JDBC 驱动名及数据库 URL
static String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static String DB_URL = "jdbc:mysql://localhost:3306/experiment?useSSL=false&serverTimezone=UTC"; // 数据库的用户名与密码，需要根据自己的设置
static String USER = "root";
static String PASS = "HEIYANG"; //连接数据库
static Connection conn = null;
static PreparedStatement stmt = null; /**
* @see HttpServlet#HttpServlet()
*/
public delete() {
super();
// TODO Auto-generated constructor stub
} /**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
@SuppressWarnings("unused")
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
boolean Flag=false;
response.setContentType("text/html;charset=UTF-8");
response.getWriter().append("Served at: ").append(request.getContextPath()); String delclass=request.getParameter("addclass");//获取输入
String delteacher=request.getParameter("addteacher"); //获取输入
String adelplace =request.getParameter("addplace");//获取输入 String del1= "DELETE from classroom where name=?";
String del2= "DELETE from classroom where teacher=?";
String del3= "DELETE from classroom where place=?"; try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(DB_URL,USER,PASS);
if(delclass!=null)
{
stmt = conn.prepareStatement(del1);
stmt.setString(1,delclass);
int rs = stmt.executeUpdate();
Flag=true;
}
else if(delteacher!=null)
{
stmt = conn.prepareStatement(del2);
stmt.setString(2,delteacher);
int rs = stmt.executeUpdate();
Flag=true;
}
else if(adelplace!=null)
{
stmt = conn.prepareStatement(del3);
stmt.setString(3,adelplace);
int rs = stmt.executeUpdate();
Flag=true;
} stmt.close();
conn.close();
if(Flag==true) {
response.getWriter().print("<script language='javascript'>alert('操作失败！')</script>");
response.setHeader("refresh", "1;function.jsp");
// response.sendRedirect("function.jsp");
}
else
response.sendRedirect("delete.jsp");
} catch (SQLException | ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} } /**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
} }
5.查找（jsp+servlet）
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 style="text-align: center;"> <div id="login">
<div id="form">
<fieldset>
<legend>查询课程信息</legend>
<form action="display" name="add" method="get">
课程名称：<input type="text" name="class"><br>
上课老师：<input type="text" name="teacher"><br>
上课地点：  <input type="text" name="place"><br>
<input type="submit" value="查询">
<input type="reset" value="清空">
<br>
</form>
</fieldset>
</div>
</div> </body>
</html>
display.java
package MySQL; import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
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; /**
* Servlet implementation class display
*/
@WebServlet("/display")
public class display extends HttpServlet {
private static final long serialVersionUID = 1L; /**
* @see HttpServlet#HttpServlet()
*/
public display() {
super();
// TODO Auto-generated constructor stub
} /**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.setContentType("text/html;charset=utf-8"); response.setContentType("text/html"); response.getWriter().append("Served at: ").append(request.getContextPath()); String addclass=request.getParameter("class");//获取输入
String addteacher=request.getParameter("teacher"); //获取输入
String addplace =request.getParameter("place");//获取输入 // 设置响应内容类型
response.setContentType("text/html;charset=utf-8");
response.setContentType("text/html;charset=GBK");
response.setContentType("text/html"); Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
PrintWriter printWriter = response.getWriter();
List<String> list = new LinkedList<String>();
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/experiment?useSSL=false&serverTimezone=UTC";
connection = DriverManager.getConnection(url, "root", "HEIYANG");
statement = connection.createStatement(); resultSet = statement.executeQuery("SELECT name,teacher,place FROM classroom");
printWriter.print("<html><head><title>Servlet连接数据库</title></head>");
printWriter.print("<body><table border=1 align=\"center\">"); while (resultSet.next()) {
if(addclass!=null) {
if((resultSet.getString("name")).indexOf(addclass)!=-1) {
printWriter.print("<tr>");
printWriter.print("<td>" + resultSet.getString("name") + "</td>"+"<td>" + resultSet.getString("teacher") + "</td>"+"<td>" + resultSet.getString("place") + "</td>");
printWriter.print("</tr>");
}
}
else if(addteacher!=null) {
if((resultSet.getString("teacher")).indexOf(addteacher)!=-1)
{
printWriter.print("<tr>");
printWriter.print("<td>" + resultSet.getString("name") + "</td>"+"<td>" + resultSet.getString("teacher") + "</td>"+"<td>" + resultSet.getString("place") + "</td>");
printWriter.print("</tr>");
String name=resultSet.getString("name");
list.add(name);
}
}
else if(addplace!=null) { if((resultSet.getString("place")).indexOf(addplace)!=-1)
{
printWriter.print("<tr>");
printWriter.print("<td>" + resultSet.getString("name") + "</td>"+"<td>" + resultSet.getString("teacher") + "</td>"+"<td>" + resultSet.getString("place") + "</td>");
printWriter.print("</tr>");
}
}
}
}catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
// resultSet.close();
statement.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} request.setAttribute( "list ",list);
request.getRequestDispatcher("show.jsp").forward(request,response); } /**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
} }