Eclipse三层架构项目(增删改查)

搭建好项目记得导包：

连接池(根据自己的配置改)：

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf-8&useSSL=false
user=root
password=994994

连接数据库工具类：

package com.accp.dao;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

public class BaseDao {
	private static String driver;
	private static String url;
	private static String user;
	private static String password;
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	static {
		Properties params = new Properties();
		String configFile = "database.properties";
		InputStream is = BaseDao.class.getClassLoader().getResourceAsStream(configFile);
		try {
			params.load(is);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		driver=params.getProperty("driver");
		url=params.getProperty("url");
		user=params.getProperty("user");
		password=params.getProperty("password");
	}
	
    //连接
	public Connection getConnection() {
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

    //关闭流
	public void closeAll() {
		if (null != rs) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (null != pstmt) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (null != conn) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

    //增删改
	public int executeUpdate(String sql, Object... objects) {
		getConnection();
		int rows = 0;
		try {
			pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < objects.length; i++) {
				pstmt.setObject(i + 1, objects[i]);
			}
			rows = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeAll();
		}
		return rows;
	}

    //查询
	public ResultSet executeQuery(String sql, List<Object> list) {
		getConnection();
		try {
			pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				pstmt.setObject(i + 1, list.get(i));
			}
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}
}

实体类：

package com.accp.entity;

public class StudentInfo {
	private Integer sId;
	private String sName;
	private String sGender;
	private Integer sAge;
	private String sAddress;
	private String sEmail;
	public Integer getsId() {
		return sId;
	}
	public void setsId(Integer sId) {
		this.sId = sId;
	}
	public String getsName() {
		return sName;
	}
	public void setsName(String sName) {
		this.sName = sName;
	}
	public String getsGender() {
		return sGender;
	}
	public void setsGender(String sGender) {
		this.sGender = sGender;
	}
	public Integer getsAge() {
		return sAge;
	}
	public void setsAge(Integer sAge) {
		this.sAge = sAge;
	}
	public String getsAddress() {
		return sAddress;
	}
	public void setsAddress(String sAddress) {
		this.sAddress = sAddress;
	}
	public String getsEmail() {
		return sEmail;
	}
	public void setsEmail(String sEmail) {
		this.sEmail = sEmail;
	}
}

Dao层：

package com.accp.dao;

import java.util.List;

import com.accp.entity.StudentInfo;

public interface StudentInfoDao {
	public List<StudentInfo> select(StudentInfo si);
	
	public int update(StudentInfo si);
	
	public int delete(StudentInfo si);
}

Dao层Impl实现类：

package com.accp.dao.impl;

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

import com.accp.dao.BaseDao;
import com.accp.dao.StudentInfoDao;
import com.accp.entity.StudentInfo;

public class StudentInfoDaoImpl extends BaseDao implements StudentInfoDao{

	@Override
	public List<StudentInfo> select(StudentInfo si) {
		// TODO Auto-generated method stub
		String sql = "select * from studentInfo where 1=1";
		List<StudentInfo> list = new ArrayList<StudentInfo>();
		List<Object> olist = new ArrayList<Object>();
		if(si.getsId()!=null) {
			sql += " and sid=?";
			olist.add(si.getsId());
		}
		try {
			ResultSet rs = this.executeQuery(sql, olist);
			while(rs.next()) {
				StudentInfo sis = new StudentInfo();
				sis.setsId(rs.getInt("sid"));
				sis.setsName(rs.getString("sname"));
				sis.setsGender(rs.getString("sgender"));
				sis.setsAge(rs.getInt("sage"));
				sis.setsAddress(rs.getString("saddress"));
				sis.setsEmail(rs.getString("semail"));
				list.add(sis);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			this.closeAll();
		}
		return list;
	}

	@Override
	public int update(StudentInfo si) {
		// TODO Auto-generated method stub
		String sql = "update studentInfo set sname=?,sgender=?,sage=?,saddress=?,semail=? where sid=?";
		return this.executeUpdate(sql, si.getsName(),si.getsGender(),si.getsAge(),si.getsAddress(),si.getsEmail(),si.getsId());
	}

	@Override
	public int delete(StudentInfo si) {
		// TODO Auto-generated method stub
		String sql = "delete from studentInfo where sid=?";
		return this.executeUpdate(sql, si.getsId());
	}

}

Service层：

package com.accp.service;

import java.util.List;

import com.accp.entity.StudentInfo;

public interface StudentInfoService {
	public List<StudentInfo> select(StudentInfo si);
	
	public int update(StudentInfo si);
	
	public int delete(StudentInfo si);
}

Service层Impl实现类：

package com.accp.service.impl;

import java.util.List;

import com.accp.dao.StudentInfoDao;
import com.accp.dao.impl.StudentInfoDaoImpl;
import com.accp.entity.StudentInfo;
import com.accp.service.StudentInfoService;

public class StudentInfoServiceImpl implements StudentInfoService{
	
	StudentInfoDao sid = new StudentInfoDaoImpl();
	@Override
	public List<StudentInfo> select(StudentInfo si) {
		// TODO Auto-generated method stub
		return sid.select(si);
	}

	@Override
	public int update(StudentInfo si) {
		// TODO Auto-generated method stub
		return sid.update(si);
	}

	@Override
	public int delete(StudentInfo si) {
		// TODO Auto-generated method stub
		return sid.delete(si);
	}

}

Servlet层删除：

package com.accp.servlet;

import java.io.IOException;
import java.io.PrintWriter;

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.accp.dao.impl.StudentInfoDaoImpl;
import com.accp.entity.StudentInfo;

/**
 * Servlet implementation class DeleteStudentInfoServlet
 */
@WebServlet("/dsis")
public class DeleteStudentInfoServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public DeleteStudentInfoServlet() {
        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
		request.setCharacterEncoding("UTF-8");
		response.setCharacterEncoding("UTF-8");
		response.setContentType("text/html; charset=UTF-8");
		String id = request.getParameter("id"); //获得要删除的用户ID
		StudentInfo si = new StudentInfo();
		si.setsId(Integer.valueOf(id));
		StudentInfoDaoImpl sis = new StudentInfoDaoImpl();
		int count = sis.delete(si);	//调用删除方法
		
		//写入流
		PrintWriter out = response.getWriter();
		out.print(count);
		out.flush();
		out.close();
	}

	/**
	 * @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);
	}

}

Servlet层修改：

package com.accp.servlet;

import java.io.IOException;
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.accp.dao.impl.StudentInfoDaoImpl;
import com.accp.entity.StudentInfo;

/**
 * Servlet implementation class UpdateStudentInfoServlet
 */
@WebServlet("/usis")
public class UpdateStudentInfoServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public UpdateStudentInfoServlet() {
		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
		request.setCharacterEncoding("UTF-8");
		response.setCharacterEncoding("UTF-8");
		response.setContentType("text/html; charset=UTF-8");

		/*
		 * 获得修改后的学员信息
		 */
		String id = request.getParameter("id");
		String name = request.getParameter("name");
		name = new String(name.getBytes("ISO-8859-1"), "UTF-8");
		String gender = request.getParameter("gender");
		gender = new String(gender.getBytes("ISO-8859-1"), "UTF-8");
		String age = request.getParameter("age");
		String address = request.getParameter("address");
		address = new String(address.getBytes("ISO-8859-1"), "UTF-8");
		String email = request.getParameter("email");

		// 申明学员对象存入值
		StudentInfo si = new StudentInfo();
		si.setsId(Integer.valueOf(id));
		si.setsName(name);
		si.setsGender(gender);
		si.setsAge(Integer.valueOf(age));
		si.setsAddress(address);
		si.setsEmail(email);
		StudentInfoDaoImpl sis = new StudentInfoDaoImpl();

		// 调用修改方法
		int count = sis.update(si);

		// 修改后提示
		if (count > 0) {
			response.getWriter().write("<script>alert('修改成功！');window.location.href='ssis'</script>");
		} else {
			response.getWriter().write("<script>alert('修改失败！');history.go(-1)</script>");
		}
	}

	/**
	 * @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);
	}

}

Servlet层查询：

package com.accp.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.accp.dao.impl.StudentInfoDaoImpl;
import com.accp.entity.StudentInfo;

/**
 * Servlet implementation class SelectStudentInfoServlet
 */
@WebServlet("/ssis")
public class SelectStudentInfoServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public SelectStudentInfoServlet() {
        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
		request.setCharacterEncoding("UTF-8");
		response.setCharacterEncoding("UTF-8");
		response.setContentType("text/html; charset=UTF-8");
		StudentInfo si = new StudentInfo();
		StudentInfoDaoImpl sis = new StudentInfoDaoImpl();
		List<StudentInfo> list = sis.select(si);	//调用查询方法
		request.setAttribute("list",list);	//存入作用域
		
		//转发
		request.getRequestDispatcher("index.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);
	}

}

Servlet层传值到修改页面查看：

package com.accp.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.accp.dao.impl.StudentInfoDaoImpl;
import com.accp.entity.StudentInfo;

/**
 * Servlet implementation class SelectStudentInfoByIdServlet
 */
@WebServlet("/ssibis")
public class SelectStudentInfoByIdServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public SelectStudentInfoByIdServlet() {
        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
		request.setCharacterEncoding("UTF-8");
		response.setCharacterEncoding("UTF-8");
		response.setContentType("text/html; charset=UTF-8");
		String id = request.getParameter("id");	//获得要修改的学员ID
		StudentInfo si = new StudentInfo();
		si.setsId(Integer.valueOf(id));
		StudentInfoDaoImpl sis = new StudentInfoDaoImpl();
		List<StudentInfo> list = sis.select(si);	//通过ID去查询该学员信息
		request.setAttribute("info", list.get(0));	//将信息存入作用域
		
		//转发
		request.getRequestDispatcher("upd.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);
	}

}

显示页面代码：

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!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>
<script type="text/javascript" src="js/jquery-1.12.4.js"></script>
<script type="text/javascript">
	//删除方法
	function del(id) {
		if (confirm("确认要删除该学员信息？")) { //确认是否删除

			//调用ajax方法
			$.ajax({
				url : "dsis",
				type : "get",
				data : "id=" + id,
				success : function(data) {
					if (data > 0) {
						alert("删除成功！");
						window.location.href = "ssis";
					} else {
						alert("删除失败！");
					}
				}
			})
		}
	}
</script>
<style type="text/css">
table tr:nth-of-type(even) {
	background: #ccc;
}
</style>
</head>
<body>
	<table align="center" border="1">
		<tr align="center">
			<td colspan="7" style="font-weight: bold;">学员信息列表</td>
		</tr>
		<tr align="center">
			<td>学员编号</td>
			<td>学员姓名</td>
			<td>学员性别</td>
			<td>学员年龄</td>
			<td>家庭住址</td>
			<td>Email</td>
			<td>操作</td>
		</tr>
		<c:forEach items="${requestScope.list}" var="item">
			<tr align="center">
				<td>${item.sId}</td>
				<td><a href="ssibis?id=${item.sId }">${item.sName}</a></td>
				<td>${item.sGender}</td>
				<td>${item.sAge}</td>
				<td>${item.sAddress}</td>
				<td>${item.sEmail}</td>
				<td><a href="javascript:del(${item.sId})">删除</a></td>
			</tr>
		</c:forEach>
	</table>
</body>
</html>

<%@ 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>
<script type="text/javascript" src="js/jquery-1.12.4.js"></script>
<script type="text/javascript">

	//验证非空方法
	function check(){
		var name = $("#name").val();
		var sex = $("#gender").val();
		var age = $("#age").val();
		var address = $("#address").val();
		var email = $("#email").val();
		if(name.trim().length==0){
			alert("姓名不能为空");
			return false;
		}
		if(sex.trim().length==0){
			alert("性别不能为空");
			return false;
		}
		if(age.trim().length==0){
			alert("年龄不能为空");
			return false;
		}		
		return true;
	}
</script>
</head>
<body>
	<form action="usis"  method="get">
		<table border="1" align="center">
			<tr align="center">
				<td colspan="2"><h2>学员信息</h2></td>
			</tr>
			<tr align="center">
				<td>学员姓名</td>
				<td><input id="name" name="name" type="text" value="${requestScope.info.sName }"/></td>
			</tr>
			<tr align="center">
				<td>学员性别</td>
				<td><input id="gender" name="gender" type="text" value="${requestScope.info.sGender }"/></td>
			</tr>
			<tr>
				<td>学员年龄</td>
				<td><input id="age" name="age" type="text" value="${requestScope.info.sAge }"/></td>
			</tr>
			<tr align="center">
				<td>家庭住址</td>
				<td><input id="address" name="address" type="text" value="${requestScope.info.sAddress }"/></td>
			</tr>
			<tr align="center">
				<td>Email</td>
				<td><input id="email" name="email" type="text" value="${requestScope.info.sEmail }"/></td>
			</tr>
			<tr align="center">
 				<td colspan="2">
 				
 				
				<!--将学员ID放入隐藏域 用于修改 -->
				<input name="id" type="hidden" value="${requestScope.info.sId }"/>
				<input type="submit" value="修改" onclick="return check()"/></td>
			</tr>
		</table>
	</form>
</body>
</html>