Servlet+JSP实现员工增删改查

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_40196043/article/details/79737691

环境信息

        JDK 1.7

        Tomcat 7

        Mysql 5.5

        Navicat...emmm....破解版  穷

练习地址

        链接: https://pan.baidu.com/s/1TlfX-ZY2Yaya_TV3ndeYXQ 密码: c5u6

先上运行效果吧

表结构

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `salary` double DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

 

对应实体类

public class Emp {
	private int id;
	private String name;
	private double salary;
	private int age;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public double getSalary() {
		return salary;
	}
	public void setSalary(double salary) {
		this.salary = salary;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	
}

DBUtil类(负责获取和关闭数据库连接)

public class DBUtil {
	//初始化连接池
	private static BasicDataSource basicDataSource=new BasicDataSource();
	//初始化properties对象
	private static Properties properties=new Properties();
	//设置数据库和连接池变量
	private static String driver;
	private static String url;
	private static String user;
	private static String pwd;
	private static String initialSize;
	private static String maxIdle;
	private static String minIdle;
	private static String maxActive;
	private static String maxWait;
	static{
		try {
			//获取数据库连接信息
			properties.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties"));
			driver=properties.getProperty("jdbc.driver");
			url=properties.getProperty("jdbc.url");
			user=properties.getProperty("jdbc.user");
			pwd=properties.getProperty("jdbc.pwd");
			//获取连接池信息
			initialSize=properties.getProperty("dataSource.initialSize");
			maxIdle=properties.getProperty("dataSource.maxIdle");
			minIdle=properties.getProperty("dataSource.maxIdle");
			maxActive=properties.getProperty("dataSource.maxIdle");
			maxWait=properties.getProperty("dataSource.maxWait");
			//设置连接池
			basicDataSource.setDriverClassName(driver);
			basicDataSource.setUrl(url);
			basicDataSource.setUsername(user);
			basicDataSource.setPassword(pwd);
			basicDataSource.setInitialSize(Integer.parseInt(initialSize));
			basicDataSource.setMinIdle(Integer.parseInt(minIdle));
			basicDataSource.setMaxIdle(Integer.parseInt(maxIdle));
			basicDataSource.setMaxActive(Integer.parseInt(maxActive));
			basicDataSource.setMaxWait(Integer.parseInt(maxWait));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//从连接池获取连接
	public static Connection getConnection() throws SQLException{
		Connection con=basicDataSource.getConnection();
		return con;
	}
	//关闭连接
	public static void closeConnection(Connection con,Statement statement,ResultSet rs) throws SQLException{
		if(rs!=null){
			rs.close();
		}
		if(statement!=null){
			statement.close();
		}
		if(con!=null){
			con.close();
		}
	}
}

db.properties(数据库和连接池的配置信息)

#数据库驱动
jdbc.driver=com.mysql.jdbc.Driver
#数据库连接地址
jdbc.url=jdbc:mysql://localhost:3306/你的数据库名称
#数据库用户名
jdbc.user=你的数据库用户名
#数据库密码
jdbc.pwd=你的数据库密码

#初始化连接数
dataSource.initialSize=10
#最大空闲数连接数
dataSource.maxIdle=20
#最小空闲连接数
dataSource.minIdle=5
#最大连接数量
dataSource.maxActive=50
#超时等待时间(单位毫秒)
dataSource.maxWait=1000

EmpDao类(负责访问数据库)

public class EmpDao {
	//分页查找
	public List<Emp> findByPageNum(Page page){
		int begin=(page.getPageNum()-1)*page.getPageSize();
		List<Emp> empList=new ArrayList<Emp>();
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			con=DBUtil.getConnection();
			String sql="select * from emp limit ?,?";
			ps=con.prepareStatement(sql);
			ps.setInt(1, begin);
			ps.setInt(2, page.getPageSize());
			rs=ps.executeQuery();
			while(rs.next()){
				Emp emp=new Emp();
				emp.setId(rs.getInt("id"));
				emp.setName(rs.getString("name"));
				emp.setSalary(rs.getDouble("salary"));
				emp.setAge(rs.getInt("age"));
				empList.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				DBUtil.closeConnection(con, ps, rs);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return empList;
		
	}
	//查找所有员工
	public List<Emp> findAll(){
		List<Emp> empList=new ArrayList<Emp>();
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		try {
			con=DBUtil.getConnection();
			String sql="select * from emp";
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				Emp emp=new Emp();
				emp.setId(rs.getInt("id"));
				emp.setName(rs.getString("name"));
				emp.setSalary(rs.getDouble("salary"));
				emp.setAge(rs.getInt("age"));
				empList.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				DBUtil.closeConnection(con, ps, rs);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return empList;
	}
	//添加员工
	public void addEmp(Emp emp){
		Connection con=null;
		PreparedStatement ps=null;
		String sql="insert into emp(name,salary,age) values (?,?,?)";
		
		try {
			con=DBUtil.getConnection();
			ps=con.prepareStatement(sql);
			ps.setString(1,emp.getName());
			ps.setDouble(2, emp.getSalary());
			ps.setInt(3, emp.getAge());
			int flag=ps.executeUpdate();
			if(flag>0){
				System.out.println("添加员工成功");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				DBUtil.closeConnection(con, ps, null);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	//删除员工
	public void deleteEmp(int id){
		Connection con=null;
		PreparedStatement ps=null;
		String sql="delete from emp where id = ?";
		
		try {
			con=DBUtil.getConnection();
			ps=con.prepareStatement(sql);
			ps.setInt(1, id);
			int flag=ps.executeUpdate();
			if(flag>0){
				System.out.println("删除员工成功");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				DBUtil.closeConnection(con, ps, null);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	//加载员工信息用于更新
	public Emp loadEmp(int id){
		Emp emp = null;
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			con=DBUtil.getConnection();
			String sql="select * from emp where id =?";
			ps=con.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			while(rs.next()){
				emp=new Emp();
				emp.setId(rs.getInt("id"));
				emp.setName(rs.getString("name"));
				emp.setSalary(rs.getDouble("salary"));
				emp.setAge(rs.getInt("age"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				DBUtil.closeConnection(con, ps, null);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return emp;
	}	
	//更新员工
	public void updateEmp(Emp emp){
		Connection con=null;
		PreparedStatement ps=null;
		String sql="update emp set name=?,salary=?,age=? where id=?";
		
		try {
			con=DBUtil.getConnection();
			ps=con.prepareStatement(sql);
			ps.setString(1, emp.getName());
			ps.setDouble(2, emp.getSalary());
			ps.setInt(3, emp.getAge());
			ps.setInt(4, emp.getId());
			int flag=ps.executeUpdate();
			if(flag>0){
				System.out.println("更新员工成功");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				DBUtil.closeConnection(con, ps, null);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	//获取总记录数量
	public int getRecordsNum(){
		Connection con=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		int recordsNum=0;
		try {
			con=DBUtil.getConnection();
			String sql="select count(*) count from emp";
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				recordsNum=rs.getInt("count");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				DBUtil.closeConnection(con, ps, null);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return recordsNum;
	}
}

分页辅助类Page类

public class Page {
	//页码数
	private int pageNum;
	//每页显示数量,默认为3
	private int pageSize=3;
	//总页数
	private int pageTotal;
	//总记录数
	private int recordsNum;
	
	EmpDao empDao=new EmpDao();
		
	public int getPageNum() {
		return pageNum;
	}

	public void setPageNum(int pageNum) {
		this.pageNum = pageNum;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	public int getRecordsNum() {
		return recordsNum;
	}

	public void setRecordsNum(int recordsNum) {
		this.recordsNum = recordsNum;
	}

	public void setPageTotal(int pageTotal) {
		this.pageTotal = pageTotal;
	}

	public int getPageTotal(){
		recordsNum=empDao.getRecordsNum();
		int mod=recordsNum%pageSize;
		if(mod==0){
			pageTotal=recordsNum/pageSize;
		}else{
			pageTotal=recordsNum/pageSize+1;
		}
		return pageTotal;
	}	
}

访问控制层Controller类

@WebServlet("*.do")
public class ControllerServlet extends HttpServlet {

	private EmpDao empDao=new EmpDao();

	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		String url=request.getRequestURI();
		String action=request.getRequestURI().substring(url.lastIndexOf('/')+1, url.lastIndexOf('.'));
		
		if("list".equals(action)){
			Page page=new Page();
			String pageNum=request.getParameter("pageNum");
			if(pageNum==null){
				page.setPageNum(1);
			}else{
				page.setPageNum(Integer.parseInt(pageNum));
			}	
			List<Emp> listEmp=empDao.findByPageNum(page);
			request.setAttribute("listEmp", listEmp);
			request.setAttribute("page", page);
			request.getRequestDispatcher("listEmp.jsp").forward(request, response);
		}
		else if("add".equals(action)){
			Emp emp=new Emp();
			emp.setName(request.getParameter("name"));
			emp.setSalary(Double.parseDouble(request.getParameter("salary")));
			emp.setAge(Integer.parseInt(request.getParameter("age")));
			empDao.addEmp(emp);
			response.sendRedirect("list.do");
		}else if("delete".equals(action)){
			int id=Integer.parseInt(request.getParameter("id"));
			empDao.deleteEmp(id);
			response.sendRedirect("list.do");
		}else if("load".equals(action)){
			Emp emp=empDao.loadEmp(Integer.parseInt(request.getParameter("id")));
			request.setAttribute("emp", emp);
			request.getRequestDispatcher("empInfo.jsp").forward(request, response);
		}else if("update".equals(action)){
			Emp emp=new Emp();
			emp.setId(Integer.parseInt(request.getParameter("id")));
			emp.setName(request.getParameter("name"));
			emp.setSalary(Double.parseDouble(request.getParameter("salary")));
			emp.setAge(Integer.parseInt(request.getParameter("age")));
			empDao.updateEmp(emp);
			response.sendRedirect("list.do");
		}
	}
}

listEmp.jsp(员工分页效果展示)

<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<title>ListEmp</title>
</head>
<style>
	.row1{background-color: #E4E4F1}
	.row2{background-color: #FBD10A}
</style>
<body>
	<table border="1" cellpadding="0" cellspacing="0" width="500px" height="250px" style="margin:0 auto">
		<caption>员工信息(<a href="addEmp.jsp">新增员工</a>)</caption>
		<tr>
			<td>编号</td>
			<td>姓名</td>
			<td>薪水</td>
			<td>年龄</td>
			<td>操作</td>
		</tr>
		<c:forEach items="${listEmp }" var="emp" varStatus="i">
			<tr class="row${i.index%2+1 }">
			<td>${emp.id }</td>
			<td>${emp.name }</td>
			<td>${emp.salary }</td>
			<td>${emp.age }</td>
			<td><a href="delete.do?id=${emp.id }" οnclick="return confirm('是否确认删除${emp.name }信息?')">删除</a> <a href="load.do?id=${emp.id }">更新</a></td>
		</tr>
		</c:forEach>
	</table>
	<div style="width:250px;margin:0 auto">
		<c:choose>
			<c:when test="${page.pageNum<=1 }">
				<a href="list.do?pageNum=1">前一页</a>
			</c:when>
			<c:otherwise>
				<a href="list.do?pageNum=${page.pageNum-1 }">前一页</a>
			</c:otherwise>
		</c:choose>
		<c:forEach var="i" begin="1" end="${page.pageTotal }" step="1">
			<a href="list.do?pageNum=${i }">${i }</a>
		</c:forEach>
		<c:choose>
			<c:when test="${page.pageNum>=page.pageTotal }">
				<a href="list.do?pageNum=${page.pageTotal} ">后一页</a>
			</c:when>
			<c:otherwise>
				<a href="list.do?pageNum=${page.pageNum+1 }">后一页</a>
			</c:otherwise>
		</c:choose>
		<span style="margin-left:10px">一共${page.pageTotal }页</span>
	</div>
</body>
</html>

addEmp.jsp(增加员工页面)

<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<title>addEmp</title>
</head>
<body>
	<h3>增加员工</h3>
	<form method="post" action="add.do">
		姓名:<input type="text" name="name"/>
		薪水:<input type="text" name="salary"/>
		年龄:<input type="text" name="age"/>
			<input type="submit" name="增加" οnclick="alert('添加成功!')"/>
	</form>
</body>
</html>

empInfo.jsp(更新员工信息页面)

<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<title>Insert title here</title>
</head>
<body>
	<h3>更新员工</h3>
	<form method="post" action="update.do">
			<input type="hidden" name="id" value="${emp.id }"/>
		姓名:<input type="text" name="name" value="${emp.name }"/>
		薪水:<input type="text" name="salary" value="${emp.salary }"/>
		年龄:<input type="text" name="age" value="${emp.age }"/>
			<input type="submit" name="更新" οnclick="alert('更新成功!')"/>
	</form>
</body>
</html>

 

展开阅读全文

没有更多推荐了,返回首页