Day 09 学生管理（增删改查）

1.Dao.java

//连接数据库

package com.stu.tab;

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/studb","root","12345678");//得到数据库链接对象并且给connection赋值
	} catch (Exception e) {
		e.printStackTrace();
	}
	return connection;
	   
   }
   
   public static void closeAll(ResultSet res,PreparedStatement smt,Connection conn){
	   try{
		   if(res!=null){
			   res.close();
		   }
		   if(smt!=null){
			   smt.close();
		   }
		   if(conn!=null){
			   conn.close();
		   }
	   }catch(Exception e){
		   
	   }
   }
   
}

2.Student.java

//定义Student类型

package com.stu.tab;

public class Student {
	private Integer id;
	private String stuname;
	private String stuage;
	private String stusex;  //javabean
	
	//private不能被外部直接访问，每个属性都有一个get和set方法用来读取或设置私有属性的值
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getStuname() {
		return stuname;
	}
	public void setStuname(String stuname) {
		this.stuname = stuname;
	}
	public String getStuage() {
		return stuage;
	}
	public void setStuage(String stuage) {
		this.stuage = stuage;
	}
	public String getStusex() {
		return stusex;
	}
	public void setStusex(String stusex) {
		this.stusex = stusex;
	}
	public Student(Integer id, String stuname, String stuage, String stusex) {//构造方法：实例化对象时直接赋值
		super();
		this.id = id;
		this.stuname = stuname;
		this.stuage = stuage;
		this.stusex = stusex;
	}
	public Student(){
		
	}
	
	
}

3.StudentDao()

package com.stu.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {

	private Connection conn;
	private List<Student> stus;
	private PreparedStatement smt;
	private ResultSet rs;
	public List<Student> getAll(){
		
		try {
			stus=new ArrayList<Student>();
			conn= Dao.getconn();
			smt=conn.prepareStatement("select * from stu_tab");
			rs=smt.executeQuery();
			while(rs.next()){
				Student student=new Student(rs.getInt("id"),rs.getString("stuname"),rs.getString("stuage"),rs.getString("stusex")); 
				stus.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			new Dao().closeAll(rs, smt, conn);
		}
		return stus;
		
	} 
	
	public void delete(int id){
		try {
			conn= Dao.getconn();
			smt=conn.prepareStatement("delete from stu_tab where id=?");
			smt.setInt(1, id);
			smt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			new Dao().closeAll(rs, smt, conn);
		}
	}
	
	public void insert(Student student){
		try {
			conn= Dao.getconn();
			smt=conn.prepareStatement("insert into stu_tab (stuname,stuage,stusex)values(?,?,?)");
			smt.setString(1, student.getStuname());
			smt.setString(2, student.getStuage());
			smt.setString(3, student.getStusex());
			smt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			new Dao().closeAll(rs, smt, conn);
			
		}
	}
	public Student ById(Integer id){
		Student val = null;
		try {
			conn=Dao.getconn();
			smt=conn.prepareStatement("select * from stu_tab where id=?");
			smt.setInt(1, id);
			rs=smt.executeQuery();
			
			while(rs.next()){
			val=new Student(rs.getInt("id"),rs.getString("stuname"),rs.getString("stuage"),rs.getString("stusex"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			new Dao().closeAll(rs, smt, conn);
			
		}
		return val;
	}
	public void update(Student stu){
		try {
			conn= Dao.getconn();
			smt=conn.prepareStatement("update stu_tab set stuname=?,stuage=?,stusex=? where id=?");
			smt.setString(1, stu.getStuname());
			smt.setString(2, stu.getStuage());
			smt.setString(3, stu.getStusex());
			smt.setInt(4,stu.getId());
			smt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			new Dao().closeAll(rs, smt, conn);
			
		}
	}
}

4.StudentServlet.java

package com.stu.test;

import java.io.IOException;
import java.util.ArrayList;

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;

	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);
		}
		if(method.equals("delete")){
		this.delete(request,response);
		}
		if(method.equals("insert")){
			this.insert(request,response);
		}
		if(method.equals("update1")){
			this.update1(request,response);
		}
		if(method.equals("update2")){
			this.update2(request,response);
		}
	}
	
	private void update2(HttpServletRequest request,
			HttpServletResponse response) throws IOException {
		Integer id=Integer.parseInt(request.getParameter("id"));
		String stuname=request.getParameter("stuname");
		String stuage=request.getParameter("stuage");
		String stusex=request.getParameter("stusex");
		new StudentDao().update(new Student(id,stuname,stuage,stusex));
		response.sendRedirect("studentservlet?method=all");
	}

	private void update1(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		Integer id=Integer.parseInt(request.getParameter("id"));
		Student stu=new StudentDao().ById(id);
		request.setAttribute("stus", stu);
		request.getRequestDispatcher("/update.jsp").forward(request, response);
	}

	private void delete(HttpServletRequest request, HttpServletResponse response) throws IOException {
		Integer id=Integer.parseInt(request.getParameter("id"));//从show页面接收id的值
		new StudentDao().delete(id);
		response.sendRedirect("studentservlet?method=all");
	}

	public void all(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
		ArrayList al=(ArrayList) new StudentDao().getAll();//调用getall()方法
		request.setAttribute("all",al);//将值放入Request
		request.getRequestDispatcher("/show.jsp").forward(request, response);//请求转发到指定页面
	}
	public void insert(HttpServletRequest request, HttpServletResponse response) throws IOException{
		String stuname=request.getParameter("stuname");
		String stuage=request.getParameter("stuage");
		String stusex=request.getParameter("stusex");
		new StudentDao().insert(new Student(0,stuname,stuage,stusex));
		response.sendRedirect("studentservlet?method=all");
	}

}

5.show.jsp

<%@page import="com.stu.test.Student"%>
<%@page import="java.util.ArrayList"%>
<%@ 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>
	<%
      ArrayList<Student> list=(ArrayList<Student>)request.getAttribute("all");//
    %>
    <h1><a href="insert.jsp">insert</a></h1>
	<table border=1>
		<tr>
		<td>id</td>
		<td>username</td>
		<td>userage</td>
		<td>usersex</td>
		<td>操作</td>
		</tr>

		<%for(Student lists:list){%>
		<tr>
		<td><%=lists.getId()%></td>
		<td><%=lists.getStuname()%></td>
		<td><%=lists.getStuage()%></td>
		<td><%=lists.getStusex()%></td>
		<td>
		<a href="studentservlet?method=delete&id=<%=lists.getId()%>">删除</a> 
		<a href="studentservlet?method=update1&id=<%=lists.getId()%>">修改</a>
		</td>
		</tr>
		<%} %>
	</table>
</body>
</html>

6.insert.jsp

<!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>

<form method="post" action="studentservlet?method=insert">
  stuname:<input type="text" name="stuname" ><br>
  stuname:<input type="text" name="stuage" ><br>
  stuname:<input type="text" name="stusex" ><br>
          <input type="submit" name="sub" value="tijiao">
</form>

</body>
</html>






7.updata.jsp
<%@page import="com.stu.test.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>Insert title here</title>
</head>
<body>
<%Student val=(Student)request.getAttribute("stus"); %>
<form method="post" action="studentservlet?method=update2">
		  <input type="hidden" name="id" value=<%=val.getId() %> >
  stuname:<input type="text" name="stuname" value=<%=val.getStuname() %>><br>
  stuname:<input type="text" name="stuage" value=<%=val.getStuage() %>><br>
  stuname:<input type="text" name="stusex" value=<%=val.getStusex() %>><br>
          <input type="submit" name="sub" value="tijiao">
</form>

</body>
</html>




8.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">show</a>
</body>
</html>