DAO.java
package com.ohh.suc;
import java.sql.*;
public class DAO {
private static Connection connection;
public static Connection getConn(){
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbtes","root","123456");
} catch (Exception e) {
}
return connection;
}
public static void CloseAll(ResultSet res, PreparedStatement stm,
Connection conn) {
try {
if (res != null) {
res.close();
}
if (stm != null) {
stm.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
}
}
}
Student.java
package com.ohh.suc;
public class Student {
private Integer StuId;
private String StuName;
private String StuAge;
private String StuSex;
public Integer getStuId() {
return StuId;
}
public void setStuId(Integer stuId) {
StuId = stuId;
}
public String getStuName() {
return StuName;
}
public void setStuName(String stuName) {
StuName = stuName;
}
public String getStuAge() {
return StuAge;
}
public void setStuAge(String stuAge) {
StuAge = stuAge;
}
public String getStuSex() {
return StuSex;
}
public void setStuSex(String stuSex) {
StuSex = stuSex;
}
public Student(Integer stuId, String stuName, String stuAge, String stuSex) {
super();
StuId = stuId;
StuName = stuName;
StuAge = stuAge;
StuSex = stuSex;
}
}
StudentDAO.java
package com.ohh.suc;
import java.sql.*;
import java.util.*;
public class StudentDAO {
private Connection connection;
private List<Student> stus;
private PreparedStatement stm;
private ResultSet rs;
public List<Student> getAll(){
stus = new ArrayList<Student>();
try {
connection = DAO.getConn();
stm = connection.prepareStatement("SELECT * FROM student");
rs = stm.executeQuery();
while(rs.next()){
Student student = new Student(rs.getInt("stuid"),rs.getString("stuname"),rs.getString("stuage"),rs.getString("stusex"));
//SELECT结果放入ArrayList中
stus.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DAO.CloseAll(rs, stm, connection);
}
return stus;
}
public Student getById(Integer stuid){
connection = DAO.getConn();
Student stu = null;
try {
stm = connection.prepareStatement("SELECT * FROM student WHERE stuid = ?");
stm.setInt(1, stuid);
rs = stm.executeQuery();
while(rs.next()){
stu = new Student(rs.getInt("stuid"),rs.getString("stuname"),rs.getString("stuage"),rs.getString("stusex"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DAO.CloseAll(rs, stm, connection);
}
return stu;
}
public boolean add(Student student){
int result = 0;
try {
connection = DAO.getConn();
stm = connection.prepareStatement("INSERT INTO student(stuname,stuage,stusex)VALUES(?,?,?)");
stm.setString(1, student.getStuName());
stm.setString(2, student.getStuAge());
stm.setString(3, student.getStuSex());
result = stm.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally{
DAO.CloseAll(rs, stm, connection);
}
if(result > 0){
return true;
}else{
return false;
}
}
public void delete(Integer stuid){
connection = DAO.getConn();
try {
stm = connection.prepareStatement("DELETE FROM student WHERE stuid = ?");
stm.setInt(1, stuid);
stm.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DAO.CloseAll(rs, stm, connection);
}
}
public void update(Student student){
connection = DAO.getConn();
try {
stm = connection.prepareStatement("UPDATE student SET stuname = ?, stuage = ?, stusex = ? WHERE stuid = ?");
stm.setString(1, student.getStuName());
stm.setString(2, student.getStuAge());
stm.setString(3, student.getStuSex());
stm.setInt(4, student.getStuId());
stm.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DAO.CloseAll(rs, stm, connection);
}
}
}
StudentServlet.java
package com.ohh.suc;
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;
@WebServlet("/studentservlet")
public class StudentServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public StudentServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String method = request.getParameter("method");
if(method.equals("all")){
this.all(request,response);
}else if(method.equals("add")){
this.add(request,response);
}else if(method.equals("delete")){
this.delete(request,response);
}else if(method.equals("upbyid")){
this.upbyid(request,response);
}else if(method.equals("update")){
this.update(request,response);
}
}
private void all(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
List list = new StudentDAO().getAll();
request.setAttribute("students", list);
request.getRequestDispatcher("students.jsp").forward(request, response);
}
private void add(HttpServletRequest request, HttpServletResponse response) throws IOException {
String stuname = request.getParameter("stuname");
String stuage = request.getParameter("stuage");
String stusex = request.getParameter("stusex");
boolean result = new StudentDAO().add(new Student(null,stuname,stuage,stusex));
if(result){
response.sendRedirect("index.jsp");
}else{
response.sendRedirect("error.jsp");
}
}
private void delete(HttpServletRequest request, HttpServletResponse response) throws IOException {
Integer stuid = Integer.parseInt(request.getParameter("stuid"));
new StudentDAO().delete(stuid);
response.sendRedirect("index.jsp");
}
private void upbyid(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer stuid = Integer.parseInt(request.getParameter("stuid"));
Student student = new StudentDAO().getById(stuid);
request.setAttribute("stu", student);
request.getRequestDispatcher("update.jsp").forward(request, response);
}
private void update(HttpServletRequest request, HttpServletResponse response) throws IOException {
Integer stuid = Integer.parseInt(request.getParameter("stuid"));
String stuname = request.getParameter("stuname");
String stuage = request.getParameter("stuage");
String stusex = request.getParameter("stusex");
new StudentDAO().update(new Student(stuid,stuname,stuage,stusex));
response.sendRedirect("index.jsp");
}
}
index.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">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<a href="studentservlet?method=all">List All Students</a>
</body>
</html>
<%@page import="com.ohh.suc.Student" %>
<%@page import="java.util.List" %>
<%@ 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>Insert title here</title>
</head>
<body>
<%
List<Student> stus = (List<Student>)request.getAttribute("students");
%>
<a href="add.jsp">添加学生</a>
<table border="1" cellpadding="5" cellspacing="0">
<tr>
<th>StuId</th>
<th>StuName</th>
<th>StuAge</th>
<th>StuSex</th>
<th>操作</th>
</tr>
<%
for(Student student: stus){
%>
<tr>
<td><%= student.getStuId() %></td>
<td><%= student.getStuName() %></td>
<td><%= student.getStuAge() %></td>
<td><%= student.getStuSex() %></td>
<td>
<a href="studentservlet?method=upbyid&stuid=<%= student.getStuId()%>">修改</a>
<a href="studentservlet?method=delete&stuid=<%= student.getStuId()%>">删除</a>
</td>
</tr>
<%
}
%>
</table>
</body>
</html>
<%@page import="com.ohh.suc.Student"%>
<%@ 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>
</head>
<body>
<%
Student stu = (Student)request.getAttribute("stu");
%>
<h4>修改信息</h4><br><br>
<form action="studentservlet?method=update" method="post">
<input type="hidden" name="stuid" id="stuid" value=<%= stu.getStuId() %>>
姓名:<input type="text" name="stuname" id="stuname" value=<%= stu.getStuName() %>><br> <br>
年龄:<input type="text" name="stuage" id="stuage" value=<%= stu.getStuAge() %>><br> <br>
性别:<input type="text" name="stusex" id="stusex" value=<%= stu.getStuSex() %>><br> <br>
<input type="submit" value="修改">
</form>
<br><a href="index.jsp">返回</a>
</body>
</html>
add.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">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>添加</title>
</head>
<body>
<h4>添加信息</h4><br><br>
<form action="studentservlet?method=add" method="post">
姓名:<input type="text" name="stuname" id="stuname"><br>
年龄:<input type="text" name="stuage" id="stuage"><br>
性别:<input type="text" name="stusex" id="stusex"><br>
<input type="submit" value="添加">
</form>
<br><a href="index.jsp">返回</a>
</body>
</html>