MVC模式的学生信息增删改查

准备：建一个名为 userdb的数据库。建一个student表,有stuid,stuname,gender三个字段。其中stuid为主键。j加入相应的驱动包，相应的JSTL标签

先看目录结构

代码：

DDUtil.java

package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author sawyer 2014下午1:20:16
 * 
 */

public class DBUtil {
	private Connection conn = null;
	private PreparedStatement stmt = null;
	private ResultSet rs = null;
	private static String driver = "com.mysql.jdbc.Driver";
	private String url = "jdbc:mysql://localhost:3306/userdb";
	private String user = "root";
	private String password = "orcl";

	/**
	 * Get the driver
	 */
	static {

	}

	/**
	 * Connect the database
	 */
	public Connection getCon() {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			conn = (Connection) DriverManager
					.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * @param sql
	 * @param obj
	 *            Update
	 *//*
	public int update(String sql, Object... obj) {
		int count = 0;
		conn = getCon();
		try {
			stmt = conn.prepareStatement(sql);
			if (obj != null) {
				for (int i = 0; i < obj.length; i++) {
					stmt.setObject(i + 1, obj[i]);
				}
			}
			count = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return count;
	}

	*//**
	 * @param sql
	 * @param obj
	 *            Query
	 *//*
	public ResultSet Query(String sql, Object... obj) {
		conn = getCon();
		try {
			stmt = conn.prepareStatement(sql);
			while (obj != null) {
				for (int i = 0; i < obj.length; i++) {
					stmt.setObject(i + 1, obj[i]);
				}
			}
			rs = stmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return rs;
	}*/

	/**
	 * CLose the resource
	 */
	public void close() {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				if (conn != null) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
	}
}

　　

 

Student.java

package entity;

public class Student {
	private int stuid;
	private String stuname;
	private String gender;

	public int getStuid() {
		return stuid;
	}

	public void setStuid(int stuid) {
		this.stuid = stuid;
	}

	public String getStuname() {
		return stuname;
	}

	public void setStuname(String stuname) {
		this.stuname = stuname;
	}

	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

	public Student()
	{
		
	}
	public Student(int stuid, String stuname, String gender) {
		super();
		stuid = this.stuid;
		stuname = this.stuname;
		gender = this.gender;
	}

}

 

　　

Model.java

package model;

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 entity.Student;

import util.DBUtil;

public class Model {
	private Statement sta;
	private ResultSet rs;
	PreparedStatement ps;
	DBUtil u=new DBUtil();
	
	
	public  int Insert(int stuid,String stuname,String gender) throws SQLException{
		Connection conn=u.getCon();
		String sql="insert student values(?,?,?)";
		ps=conn.prepareStatement(sql);
		ps.setInt(1,stuid);
		ps.setString(2,stuname);
		ps.setString(3,gender);
		int a=ps.executeUpdate();
		return a;
	}
	
	public  int delete(int stuid) throws SQLException{
		Connection conn=u.getCon();
		String sql="delete from student where stuid=?";
		ps=conn.prepareStatement(sql);
		ps.setInt(1,stuid);
		int a=ps.executeUpdate();
		return a;
	}
	
	public  int update(int stuid,String stuname,String gender) throws SQLException{
		Connection conn=u.getCon();
		String sql="update student set stuname=?,gender=? where stuid=?";
		ps=conn.prepareStatement(sql);
		ps.setInt(3,stuid);
		ps.setString(1,stuname);
		ps.setString(2,gender);
		int a=ps.executeUpdate();
		return a;
	}
	public List<Student> queryAll() throws SQLException{
		List<Student> students=new ArrayList<Student>();
		Connection conn=u.getCon();
		String  sql="select * from student";
		sta=conn.createStatement();
		rs=sta.executeQuery(sql);
		while(rs.next()){
			Student student=new Student();
			student.setStuid(rs.getInt("stuid"));
			student.setStuname(rs.getString("stuname"));
			student.setGender(rs.getString("gender"));
			students.add(student);
		}
		return students;
	}
	
	public Student queryById(int stuid) throws SQLException{
		Student student=new Student();
		Connection conn=u.getCon();
		String sql="select * from student where stuid=?";
		ps=conn.prepareStatement(sql);
		ps.setInt(1,stuid);
		rs=ps.executeQuery();
		if(rs.next()){
			student.setStuid(rs.getInt("stuid"));
			student.setStuname(rs.getString("stuname"));
			student.setGender(rs.getString("gender"));
		}
		return student;
		
	}
	
}

　

　

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" 
    xmlns="http://java.sun.com/xml/ns/javaee" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
    http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
  <display-name></display-name>    
  <filter>
  <filter-name>EncodingFilter</filter-name>
  <filter-class>servlet.EncodingFilter</filter-class>
  <init-param>
  <param-name>encoding</param-name>
  <param-value>UTF-8</param-value>
  </init-param>
  </filter>
  <filter-mapping>
  <filter-name>EncodingFilter</filter-name>
  <url-pattern>/*</url-pattern>
  </filter-mapping>
  
  <servlet>
  <servlet-name>QueryAll</servlet-name>
  <servlet-class>servlet.QueryAll</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>QueryAll</servlet-name>
  <url-pattern>/QueryAll.do</url-pattern>
  </servlet-mapping>
  
   <servlet>
  <servlet-name>Insert</servlet-name>
  <servlet-class>servlet.Insert</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>Insert</servlet-name>
  <url-pattern>/Insert.do</url-pattern>
  </servlet-mapping>
  
  <servlet>
  <servlet-name>SelectUpdate</servlet-name>
  <servlet-class>servlet.SelectUpdate</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>SelectUpdate</servlet-name>
  <url-pattern>/SelectUpdate.do</url-pattern>
  </servlet-mapping>
  
   <servlet>
  <servlet-name>Update</servlet-name>
  <servlet-class>servlet.Update</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>Update</servlet-name>
  <url-pattern>/Update.do</url-pattern>
  </servlet-mapping>
  
   <servlet>
  <servlet-name>Delete</servlet-name>
  <servlet-class>servlet.Delete</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>Delete</servlet-name>
  <url-pattern>/Delete.do</url-pattern>
  </servlet-mapping>
  
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
</web-app>

 

 

QueryAll.java

package servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import entity.Student;

import model.Model;


@SuppressWarnings("serial")
public class QueryAll extends HttpServlet{
	public QueryAll(){
		super();
	}
	public void doGet(HttpServletRequest request,HttpServletResponse response) throws IOException{
		Model model=new Model();
		try{
	    List<Student> list=model.queryAll();
		
	    request.getSession().setAttribute("list", list);
		}catch(Exception e){
			e.printStackTrace();
		}
	    response.sendRedirect("index.jsp");
	}
	public void doPost(HttpServletRequest request,HttpServletResponse response) throws IOException{
		this.doGet(request, response);
	}
}

　　

Insert.java

package servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import model.Model;

@SuppressWarnings("serial")
public class Insert extends HttpServlet{
	public Insert(){
		super();
	}
	public void doGet(HttpServletRequest request,HttpServletResponse response) throws IOException{
		Model model = new Model();
		int stuid = Integer.parseInt(request.getParameter("stuid"));
		String stuname = request.getParameter("stuname");
		String gender = request.getParameter("gender");
		try {
			model.Insert(stuid, stuname, gender);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		response.sendRedirect("QueryAll.do");
	}
	public void doPost(HttpServletRequest request,HttpServletResponse response) throws IOException{
		this.doGet(request, response);
	}
}

　　

 

Delete.java

package servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import model.Model;

@SuppressWarnings("serial")
public class Delete extends HttpServlet{
	public Delete(){
		super();
	}
	public void doGet(HttpServletRequest request,HttpServletResponse response) throws IOException{
		Model model=new Model();
		int stuid=Integer.parseInt(request.getParameter("stuid"));
		/*String stuname=request.getParameter("stuname");
		String gender=request.getParameter("gender");*/
		try {
			model.delete(stuid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		response.sendRedirect("QueryAll.do");
	}
	public void doPost(HttpServletRequest request,HttpServletResponse response) throws IOException{
		this.doGet(request, response);
	}
}

　　

SelectUpdate.java

package servlet;

import java.io.IOException;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import entity.Student;

import model.Model;

@SuppressWarnings("serial")
public class SelectUpdate extends HttpServlet{
	public SelectUpdate(){
		super();
	}
	public void doGet(HttpServletRequest request,HttpServletResponse response) throws IOException{
		int stuid=Integer.parseInt(request.getParameter("stuid"));
		Model model=new Model();
		try {
			Student student=model.queryById(stuid);
			request.setAttribute("student", student);
			int id=Integer.parseInt(request.getParameter("id"));
			if(id==1){
			request.getRequestDispatcher("update.jsp").forward(request, response);
			}else if(id==2){
				request.getRequestDispatcher("delete.jsp").forward(request, response);	
			}
			} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ServletException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
	public void doPost(HttpServletRequest request,HttpServletResponse response) throws IOException{
		this.doGet(request, response);
	}
}

　　

Update.java

package servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import model.Model;

@SuppressWarnings("serial")
public class Update extends HttpServlet{
	public Update(){
		super();
	}
	public void doGet(HttpServletRequest request,HttpServletResponse response) throws IOException{
		Model model=new Model();
		int stuid=Integer.parseInt(request.getParameter("stuid"));
		String stuname=request.getParameter("stuname");
		String gender=request.getParameter("gender");
		try {
			model.update(stuid, stuname, gender);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		response.sendRedirect("QueryAll.do");
	}
	public void doPost(HttpServletRequest request,HttpServletResponse response) throws IOException{
		this.doGet(request, response);
	}
}

　　

 

EncodingFilter.java

package servlet;

import java.io.IOException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

public class EncodingFilter implements Filter {
	
	private String encoding = null;
	public void init(FilterConfig config) throws ServletException {
		this.encoding = config.getInitParameter("encoding");
	}
	
	public void doFilter(ServletRequest request, ServletResponse response,
			FilterChain chain) throws IOException, ServletException {
		request.setCharacterEncoding(this.encoding);
		response.setCharacterEncoding(this.encoding);
		chain.doFilter(request, response);
	}
	
	public void destroy() {
		this.encoding = null;
	}

}

　　

index.jsp 

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page language="java" import="model.*,entity.*,servlet.*"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"  %>
<%@ taglib uri="http://jsptags.com/tags/navigation/pager" prefix="pg" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'index.jsp' starting page</title>
    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">    
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">
    <!--
    <link rel="stylesheet" type="text/css" href="styles.css">
    -->
  </head>
  
  <body>
  <h1>学生信息列表</h1>
  <a href="insert.jsp">新增</a><br><hr>
  <pg:pager maxPageItems="10" url="index.jsp">
    <table border="1">
    <tr><td>学号</td><td>姓名</td><td>性别</td><td>修改</td><td>删除</td></tr>
    <c:forEach items="${sessionScope.list}" var="student">
    <pg:item>
    <tr>
    <td>${student.stuid}</td>
    <td>${student.stuname}</td>
    <td>${student.gender}</td>
    <td><a href="SelectUpdate.do?id=1&stuid=${student.stuid}">修改</a></td>
    <td><a href="SelectUpdate.do?id=2&stuid=${student.stuid}">删除</a></td>
    </tr>
    </pg:item>
    </c:forEach>
    <tr>
    <td> 
    <pg:index>
    <pg:first><a href="${pageUrl}">第一页</a></pg:first>
    <pg:prev><a href="${pageUrl}">上一页</a></pg:prev>
    <pg:pages><a href="${pageUrl}">${pageNumber}</a></pg:pages>
    <pg:next><a href="${pageUrl}">下一页</a></pg:next>
    <pg:last><a href="${pageUrl}">最后一页</a></pg:last>
    </pg:index>
    </td>
    </tr>
   </table>
   </pg:pager>
  </body>
</html>

 

delete.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'delete.jsp' starting page</title>
    
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

  </head>
  
  <body>
    <h1>删除数据</h1>
	<form action="Delete.do" method="post">
		<table>
			<tr>
				<td>请输入数据：</td>
				<td>学号：<input type="text" name="stuid" id="stuid" value="${student.stuid}">
				</td>
				<td>姓名：<input type="text" name="stuname" id="stuname" value="${student.stuname}">
				</td>
				<td>性别：<input type="text" name="gender" id="gender" value="${student.gender}">
				</td>
			</tr>
			<tr><td><input type="submit" value="删除"></td>
		</table>
	</form>
  </body>
</html>

　　

 

insert.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'insert.jsp' starting page</title>
    
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

  </head>
  
  <body>
    <h1>增加数据</h1>
	<form action="Insert.do" method="post">
		<table>
			<tr>
				<td>请插入数据：</td>
				<td>学号：<input type="text" name="stuid" id="stuid">
				</td>
				<td>姓名：<input type="text" name="stuname" id="stuname">
				</td>
				<td>性别：<input type="text" name="gender" id="gender">
				</td>
				<td><input type="submit" value="插入" id="submit">
				</td>
			</tr>
		</table>
	</form>
  </body>
</html>

　　

 

update.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'update.jsp' starting page</title>
    
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

  </head>
  
  <body>
    <h1>更新数据</h1>
	<form action="Update.do" method="post">
		<table>
			<tr>
				<td>请输入数据：</td>
				<td>学号：<input type="text" name="stuid" id="stuid" value="${student.stuid}">
				</td>
				<td>姓名：<input type="text" name="stuname" id="stuname" value="${student.stuname}">
				</td>
				<td>性别：<input type="text" name="gender" id="gender" value="${student.gender}">
				</td>
			</tr>
			<tr><td><input type="submit" value="修改"></td>
			<td><input type="reset" value="重置"></td></tr>
		</table>
	</form>
  </body>
</html>

　　

代码完美运行！！！