Servlet+JSP实现员工增删改查
环境信息
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 }” onclick=”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=”增加” onclick=”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=”更新” onclick=”alert(‘更新成功!’)”/>
</form>
</body>
</html>
———————
作者：XiaoCheng5200
来源：CSDN
原文：https://blog.csdn.net/weixin_40196043/article/details/79737691
版权声明：本文为博主原创文章，转载请附上博文链接！