eclipse+tomcat+MySQL+SQLyog
1、在SQLyog中新建sqltestdb数据库,其中新建all数据表。包含四个字段:id、course、teacher、area,将id设为自动递增,否则后面递增会出错。
2、
新建web项目,点击两次next,勾选自动生成web.xml。
3、
将连接mysql的驱动jar包(mysql-connector-java-8.0.13-bin.jar)复制到WEB-INF下的lib目录下,直接拖拽即可。
4.新建3个文档包,在每个包下建对应的Java类。
Stu.java
package example.bean.stu;
public class Stu {
private int id;
private String name;
private String teacher;
private String workplace;
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 getWorkplace() {
return workplace;
}
public void setWorkplace(String workplace) {
this.workplace = workplace;
}
}
BookJdbcDao.java
package example.dao.stu;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import example.bean.stu.Stu;
public class StuJdbcDao {
private PreparedStatement ptmt = null;
private ResultSet rs = null;
public StuJdbcDao() {
}
public void findAll(Connection conn) throws SQLException
{
//to do
}
public void delete(Connection conn, int id) throws SQLException
{
String sql = "delete from tb_books where id=?";
try{
ptmt = conn.prepareStatement(sql);
// 对SQL语句中的第一个占位符赋值
ptmt.setInt(1, id);
// 执行更新操作
ptmt.executeUpdate();
}finally{
if (null!=ptmt) {
ptmt.close();
}
if (null!=conn) {
conn.close();
}
}
}
public void update(Connection conn, int id ) throws SQLException
{
//to do
}
}
ConnectionFactory.java
package example.dao.stu;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class ConnectionFactory {
private String driverClassName = "com.mysql.cj.jdbc.Driver";
private String url = "jdbc:mysql://localhost:3306/db_book?&useSSL=false&serverTimezone=UTC";
private String userName = "root";
private String password = "password";
private static ConnectionFactory connectionFactory=null;
private ConnectionFactory() {
try {
Class.forName(driverClassName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public Connection getConnection() throws SQLException
{
return DriverManager.getConnection(url, userName, password);
}
public static ConnectionFactory getInstance()
{
if (null==connectionFactory) {
connectionFactory=new ConnectionFactory();
}
return connectionFactory;
}
}
DeleteServlet.java
package example.servlet.stu;
import java.io.IOException;
import java.sql.Connection;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import example.dao.stu.StuJdbcDao;
import example.dao.stu.ConnectionFactory;
/**
* Servlet implementation class DeleteServlet
*/
public class DeleteServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
int id = Integer.valueOf(request.getParameter("id"));
try {
//
StuJdbcDao stuDao=new StuJdbcDao();
Connection conn=ConnectionFactory.getInstance().getConnection();
stuDao.delete(conn,id);
} catch (Exception e) {
e.printStackTrace();
}
// 重定向到FindServlet
response.sendRedirect("FindServlet");
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
FindServlet.java
package example.servlet.stu;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import example.bean.stu.Stu;//导入包下的Stu类
/**
* Servlet implementation class FindServlet
*/
public class FindServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
try {
// 加载数据库驱动,注册到驱动管理器
Class.forName("com.mysql.cj.jdbc.Driver");
// 数据库连接字符串
String url = "jdbc:mysql://localhost:3306/db_book?&useSSL=false&serverTimezone=UTC";
// 数据库用户名
String username = "root";
// 数据库密码
String password = "root";
// 创建Connection连接
Connection conn = DriverManager.getConnection(url, username,
password);
// 添加图书信息的SQL语句
String sql = "select * from tb_books";
// 获取Statement
Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
List<Stu> list = new ArrayList<Stu>();
while (resultSet.next()) {
Stu stu = new Stu();
stu.setId(resultSet.getInt("id"));
stu.setName(resultSet.getString("name"));
stu.setTeacher(resultSet.getString("teacher"));
stu.setWorkplace(resultSet.getString("workplace"));
list.add(stu);
}
request.setAttribute("list", list);
resultSet.close();
statement.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
request.getRequestDispatcher("stu_list.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);
}
}
UpdateServlet.java
package example.servlet.stu;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class UpdateServlet
*/
public class UpdateServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
int id = Integer.valueOf(request.getParameter("id"));
String name = request.getParameter("name");
String teacher = request.getParameter("teacher");
String workplace = request.getParameter("workplace");
try {
// 加载数据库驱动,注册到驱动管理器
Class.forName("com.mysql.cj.jdbc.Driver");
// 数据库连接字符串
String url = "jdbc:mysql://localhost:3306/db_book?&useSSL=false&serverTimezone=UTC";
// 数据库用户名
String username = "root";
// 数据库密码
String password = "root";
// 创建Connection连接
Connection conn = DriverManager.getConnection(url, username,
password);
// 更新SQL语句
String sql = "UPDATE tb_books SET name=?,teacher=?,workplace=? WHERE id=?";
// 获取PreparedStatement
PreparedStatement ps = conn.prepareStatement(sql);
// 对SQL语句中的第一个参数赋值
ps.setString(1, name);
ps.setString(2, teacher);
ps.setString(3, workplace);
ps.setInt(4, id);
// 对SQL语句中的第二个参数赋值
// 执行更新操作
ps.executeUpdate();
// 关闭PreparedStatement
ps.close();
// 关闭Connection
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
// 重定向到FindServlet
response.sendRedirect("FindServlet");
}
/**
* @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.右击Webcontent新建5个jsp页面。
addbook.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.PreparedStatement"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>添加结果</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
%>
<jsp:useBean id="stu" class="example.bean.stu.Stu"></jsp:useBean>
<jsp:setProperty property="*" name="stu" />
<%
try {
// 加载数据库驱动,注册到驱动管理器
Class.forName("com.mysql.cj.jdbc.Driver");
// 数据库连接字符串
String url = "jdbc:mysql://localhost:3306/db_book?&useSSL=false&serverTimezone=UTC";
// 数据库用户名
String username = "root";
// 数据库密码
String password = "root";
// 创建Connection连接
Connection conn = DriverManager.getConnection(url, username,
password);
// 添加图书信息的SQL语句
String sql = "insert into tb_books(name,teacher,workplace) values(?,?,?)";
// 获取PreparedStatement
PreparedStatement ps = conn.prepareStatement(sql);
// 对SQL语句中的第1个参数赋值
ps.setString(1, stu.getName());
// 对SQL语句中的第2个参数赋值
ps.setString(2, stu.getTeacher());
ps.setString(3, stu.getWorkplace());
// 对SQL语句中的第3个参数赋值
// 对SQL语句中的第4个参数赋值
// 执行更新操作,返回所影响的行数
int row = ps.executeUpdate();
// 判断是否更新成功
if (row > 0) {
// 更新成输出信息
out.print("成功添加了 " + row + "条数据!");
}
// 关闭PreparedStatement,释放资源
ps.close();
// 关闭Connection,释放资源
conn.close();
} catch (Exception e) {
out.print("课程信息添加失败!");
e.printStackTrace();
}
%>
<br>
<a href="main.jsp">返回</a>
</body>
</html>
book_list.jsp
<!--<%@page import="sun.awt.SunHints.Value"%>-->
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@page import="java.util.List"%>
<%@page import="example.bean.stu.Stu"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>所有课程信息</title>
<style type="text/css">
td {
font-size: 12px;
}
h2 {
margin: 0px
}
</style>
</head>
<body>
<table align="center" width="450" border="1" height="180"
bordercolor="white" bgcolor="black" cellpadding="1" cellspacing="1">
<tr bgcolor="white">
<td align="center" colspan="7">
<h2>所有课程信息</h2>
</td>
</tr>
<tr align="center" bgcolor="#e1ffc1">
<td><b>ID</b></td>
<td><b>课程名称</b></td>
<td><b>老师</b></td>
<td><b>上课地点</b></td>
<td><b>删除</b></td>
</tr>
<%
// 获取图书信息集合
List<Book> list = (List<Book>) request.getAttribute("list");
// 判断集合是否有效
if (list == null || list.size() < 1) {
out.print("没有数据!");
} else {
// 遍历图书集合中的数据
for (Book book : list) {
%>
<tr align="center" bgcolor="white">
<td><%=book.getId()%></td>
<td><%=book.getName()%></td>
<td><%=book.getTeacher()%></td>
<td><%=book.getWorkplace()%></td>
<td><a href="DeleteServlet?id=<%=book.getId()%>">删除</a></td>
</tr>
<%
}
}
%>
</table>
<h2 align="center">
<a href="main.jsp">返回主菜单</a>
</h2>
</body>
</html>
Update.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>课程信息修改</title>
</head>
<script type="text/javascript">
function check(form) {
with (form) {
if (name.value == "") {
alert("课程名称不能为空");
return false;
}
if (teacher.value == "") {
alert("老师名字不能为空");
return false;
}
if (workplace.value == "") {
alert("上课地点不能为空");
return false;
}
}
}
</script>
<body>
<td>
<form style="align: center; line-height: 1.5 !important;">
action="UpdateServlet" method="post" onsubmit=" return check(this);">
<input type="text" name="id" size=3>
<input type="text" name="name" size="3">
<input type="text" name="teacher" size="3">
<input type="text" name="workplace" size="3">
<input type="submit" value="修改">
</form>
</td>
<h2 align="center">
<a href="main.jsp">返回主菜单</a>
</h2>
</body>
</html>
index.jsp
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>添加课程信息</title>
<script type="text/javascript">
function check(form) {
with (form) {
if (name.value == "") {
alert("课程名称不能为空");
return false;
}
if (teacher.value == "") {
alert("老师名字不能为空");
return false;
}
if (workplace.value == "") {
alert("上课地点不能为空");
return false;
}
}
}
</script>
</head>
<body>
<form action="addbook.jsp" method="post" onsubmit="check(this)">
<table align="center" width="450">
<tr>
<td align="center" colspan="2">
<h2>添加课程信息</h2>
<hr>
</td>
</tr>
<tr>
<td align="right">课程名称:</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td align="right">老师:</td>
<td><input type="text" name="teacher"></td>
</tr>
<tr>
<td align="right">上课地点:</td>
<td><input type="text" name="workplace" /></td>
</tr>
<tr>
<td align="center" colspan="2"><input type="submit" value="添 加">
</td>
</tr>
</table>
</form>
<h2 align="center">
<a href="main.jsp">返回主菜单</a>
</h2>
</body>
</html>
main.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>
<center><h1>主菜单</h1><center>
<table><center>
<td><A href="index.jsp"><font size=2>课程信息录入</font></A></td>
<td><A href="Update.jsp"><font size=2>课程信息修改</font></A></td>
<h2 align="center">
<a href="FindServlet">查询课程信息</a>
</h2>
<h2 align="center">
<a href="FindServlet">删除课程信息</a>
</h2>
</table></center>
</body>
</html>
接下来配置web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>JdbcConnection</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
<servlet>
<description></description>
<display-name>FindServlet</display-name>
<servlet-name>FindServlet</servlet-name>
<servlet-class>example.servlet.stu.FindServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>FindServlet</servlet-name>
<url-pattern>/FindServlet</url-pattern>
</servlet-mapping>
<servlet>
<description></description>
<display-name>UpdateServlet</display-name>
<servlet-name>UpdateServlet</servlet-name>
<servlet-class>example.servlet.stu.UpdateServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>UpdateServlet</servlet-name>
<url-pattern>/UpdateServlet</url-pattern>
</servlet-mapping>
<servlet>
<description></description>
<display-name>DeleteServlet</display-name>
<servlet-name>DeleteServlet</servlet-name>
<servlet-class>example.servlet.stu.DeleteServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>DeleteServlet</servlet-name>
<url-pattern>/DeleteServlet</url-pattern>
</servlet-mapping>
</web-app>