考试用的,好多错误
SQL文件如下:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for tb_stu
-- ----------------------------
DROP TABLE IF EXISTS `tb_stu`;
CREATE TABLE `tb_stu` (
`stuId` int(11) NOT NULL,
`stuName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`stuSex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`stuPhone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`stuId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of tb_stu
-- ----------------------------
INSERT INTO `tb_stu` VALUES (1, 'zhangsan', 'male', '123456789');
INSERT INTO `tb_stu` VALUES (2, 'lisi', 'male', '123456789');
INSERT INTO `tb_stu` VALUES (3, 'wangwu', 'male', '123456789');
INSERT INTO `tb_stu` VALUES (4, 'liliu', 'male', '123456781');
INSERT INTO `tb_stu` VALUES (5, 'qiqi', 'female', '77777777777');
SET FOREIGN_KEY_CHECKS = 1;
配置:
导入jar包
或者pom.xml文件中添加
<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>taglibs</groupId>
<artifactId>standard</artifactId>
<version>1.1.2</version>
</dependency>
</dependencies>
工具bean:DBUtil
package com.hbc.bean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
static {
//MySQL 5.7及以下使用的代码
String driverClass="com.mysql.jdbc.Driver";
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} // 加载数据库驱动
}
public DBUtil() {
// TODO Auto-generated constructor stub
}
//获取数据库连接
public static Connection getConnection() {
String url="jdbc:mysql://localhost:3306/db_student";
String username = "root";
String password = "123456";
Connection conn = null;
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//获取静态语句操作对象
public static Statement getStatement(Connection conn) {
Statement stat = null;
try {
stat = conn.createStatement();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return stat;
}
//释放资源
public static void closeAll(Connection connection, Statement statement,ResultSet resultSet){
try {
if (resultSet!=null){
resultSet.close();
}
if (connection!=null){
connection.close();
}
if (statement!=null){
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//释放资源 (方法重载)
public static void close(Connection connection, Statement statement){
try {
if (connection!=null){
connection.close();
}
if (statement!=null){
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
实体bean:StuBean
package com.hbc.bean;
public class StuBean {
private int stuId;
private String stuName;
private String stuSex;
private String stuPhone;
public StuBean() {
}
public int getStuId() {
return this.stuId;
}
public void setStuId(int stuId) {
this.stuId = stuId;
}
public String getStuName() {
return this.stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public String getStuSex() {
return this.stuSex;
}
public void setStuSex(String stuSex) {
this.stuSex = stuSex;
}
public String getStuPhone() {
return this.stuPhone;
}
public StuBean(int stuId, String stuName, String stuSex, String stuPhone) {
this.stuId = stuId;
this.stuName = stuName;
this.stuSex = stuSex;
this.stuPhone = stuPhone;
}
public void setStuPhone(String stuPhone) {
this.stuPhone = stuPhone;
}
}
StuDAO
package com.hbc.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.hbc.bean.DBUtil;
import com.hbc.bean.StuBean;
public class StuDAO {
public StuDAO() {
// TODO Auto-generated constructor stub
}
public List queryAll() {
List list = new ArrayList();
Connection conn = DBUtil.getConnection();
Statement stat = DBUtil.getStatement(conn);
ResultSet rs = null;
try {
rs = stat.executeQuery("select * from tb_stu");
StuBean stu = null;
while(rs.next()) {
stu = new StuBean();
stu.setStuId(rs.getInt(1));
stu.setStuName(rs.getString(2));
stu.setStuSex(rs.getString(3));
stu.setStuPhone(rs.getString(4));
System.out.println("name is " + stu.getStuName());
list.add(stu);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.closeAll(conn, stat, rs);
}
System.out.println("before return list;");
return list;
}
public void deleteById(int stuId) {
List list = new ArrayList();
Connection conn = DBUtil.getConnection();
Statement stat = DBUtil.getStatement(conn);
try {
int rn = stat.executeUpdate("delete from tb_stu where stuId="+stuId);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.close(conn, stat);
}
}
public void addStu(StuBean stu) {
Connection conn = DBUtil.getConnection();
PreparedStatement stat = null;
String sql = "insert into tb_stu(stuId,stuName,stuSex,stuPhone) values(?,?,?,?)";
try {
stat = conn.prepareStatement(sql);
stat.setInt(1, stu.getStuId());
stat.setString(2, stu.getStuName());
stat.setString(3, stu.getStuSex());
stat.setString(4, stu.getStuPhone());
int rtn = stat.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.close(conn, stat);
}
}
public StuBean selectById(int stuId) {
StuBean stu = new StuBean();
Connection conn = DBUtil.getConnection();
Statement stat = DBUtil.getStatement(conn);
ResultSet rs = null;
try {
rs = stat.executeQuery("select * from tb_stu where stuId="+stuId);
while(rs.next()) {
stu.setStuId(rs.getInt(1));
stu.setStuName(rs.getString(2));
stu.setStuSex(rs.getString(3));
stu.setStuPhone(rs.getString(4));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.closeAll(conn, stat,rs);
}
return stu;
}
public void updateStu(StuBean stu) {
Connection conn = DBUtil.getConnection();
PreparedStatement stat = null;
String sql = "update tb_stu set stuName=?,stuSex=?,stuPhone=? where stuId=?";
try {
stat = conn.prepareStatement(sql);
stat.setString(1, stu.getStuName());
stat.setString(2, stu.getStuSex());
stat.setString(3, stu.getStuPhone());
stat.setInt(4, stu.getStuId());
int rtn = stat.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.close(conn, stat);
}
}
}
CRUD
AddServlet
package com.hbc.servlet;
import com.hbc.bean.StuBean;
import com.hbc.dao.StuDAO;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
@WebServlet("/AddServlet")
public class AddServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//解决Post中文乱码问题
request.setCharacterEncoding("utf-8");
//1.接受表单提交的数据并将其封装为一个StuBean对象
String stuId=request.getParameter("stuId");
String stuName=request.getParameter("stuName");
String stuSex=request.getParameter("stuSex");
String stuPhone=request.getParameter("stuPhone");
//封装成stuBean对象
StuBean stuBean=new StuBean(Integer.parseInt(stuId),stuName,stuSex,stuPhone);
//完成添加操作
StuDAO dao = new StuDAO();
dao.addStu(stuBean);
//转发到查询所有Servlet
request.getRequestDispatcher("/QuerryAllServlet").forward(request,response);
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
}
DeleteServlet
package com.hbc.servlet;
import com.hbc.dao.StuDAO;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//解决Post中文乱码问题
request.setCharacterEncoding("utf-8");
//1.接受表单提交的数据获取id
String id = request.getParameter("stuId");
//完成删除操作
StuDAO dao=new StuDAO();
dao.deleteById(Integer.parseInt(id));
//3.转发到查询所有Servlet
request.getRequestDispatcher("/QueryAllServlet").forward(request,response);
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
}
QueryAllServlet
package com.hbc.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.hbc.dao.StuDAO;
/**
* Servlet implementation class QueryAllServlet
*/
@WebServlet("/QueryAllServlet")
public class QueryAllServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public QueryAllServlet() {
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
doPost(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);
StuDAO dao = new StuDAO();
List list = dao.queryAll();
request.setAttribute("stuList", list);
request.getRequestDispatcher("show_jstl.jsp").forward(request, response);
// request.getRequestDispatcher("show.jsp").forward(request, response);
}
}
ToUpdateServlet
package com.hbc.servlet;
import com.hbc.bean.StuBean;
import com.hbc.dao.StuDAO;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
@WebServlet("/ToUpdateServlet")
public class ToUpdateServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//解决Post中文乱码问题
request.setCharacterEncoding("utf-8");
StuDAO dao=new StuDAO();
// 获取stuId参数
int stuId = Integer.parseInt(request.getParameter("stuId"));
// 根据stuId从数据库中获取学生信息(略,使用你的DAO层方法)
StuBean stu=dao.selectById(stuId);
// 将学生信息存储到request对象中
request.setAttribute("stu", stu);
request.getRequestDispatcher("update.jsp").forward(request, response);
//转发到查询所有Servlet
request.getRequestDispatcher("/QuerryAllServlet").forward(request,response);
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
}
}
show_jstl.jsp
<%@ page contentType="text/html;charset=utf-8"%>
<%@ page import="java.util.ArrayList" %>
<%@ page import="com.hbc.bean.StuBean" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<% ArrayList stuList=(ArrayList)request.getAttribute("stuList"); %>
<form action="/AddServlet" method="post">
编号: <input type="text" name="StuId">
姓名: <input type="text" name="StuName">
性别: <input type="radio" name="stuSex" value="male"> 男
<input type="radio" name="stuSex" value="female"> 女
电话: <input type="text" name="stuPhone">
<input type="submit" value="添加学生">
</form>
<br>
<table border="1" width="450" rules="none" cellspacing="0" cellpadding="0">
<tr height="50"><td colspan="6" align="center">学生信息如下</td></tr>
<tr align="center" height="30" bgcolor="lightgrey">
<td>编号</td>
<td>姓名</td>
<td>性别</td>
<td>电话</td>
<td>操作1</td>
<td>操作2</td>
</tr>
<c:forEach items="${stuList}" var="stu">
<tr height="50" align="center">
<td>${stu.stuId}</td>
<td>${stu.stuName}</td>
<td>${stu.stuSex}</td>
<td>${stu.stuPhone}</td>
<td><a href="DeleteServlet?stuId=${stu.stuId}">删除</a></td>
<td><a href="ToUpdateServlet?stuId=${stu.stuId}">修改</a></td>
</tr>
</c:forEach>
</table>
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>
<body>
<h2>修改学生信息</h2>
<form action="ToUpdateServlet" method="post">
<label for="stuId">学生ID:</label>
<input type="text" id="stuId" name="stuId" readonly>
<br>
<label for="stuName">姓名:</label>
<input type="text" id="stuName" name="stuName" required>
<br>
<label for="stuSex">性别:</label>
<input type="text" id="stuSex" name="stuSex" required>
<br>
<label for="stuPhone">电话:</label>
<input type="text" id="stuPhone" name="stuPhone" required>
<br>
<input type="submit" value="更新">
</form>
<script>
// 使用JavaScript填充表单字段的值
document.getElementById("stuId").value = "${stu.stuId}"; // 使用EL表达式获取stu对象的stuId属性
document.getElementById("stuName").value = "${stu.stuName}";
document.getElementById("stuSex").value = "${stu.stuSex}";
document.getElementById("stuPhone").value = "${stu.stuPhone}";
</script>
</body>
</html>