Servlet+JSP实现员工增删改查 |
您所在的位置:网站首页 › jsp增删改查怎么实现的 › 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=1000EmpDao类(负责访问数据库) public class EmpDao { //分页查找 public List findByPageNum(Page page){ int begin=(page.getPageNum()-1)*page.getPageSize(); List empList=new ArrayList(); 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 findAll(){ List empList=new ArrayList(); 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 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(员工分页效果展示) ListEmp .row1{background-color: #E4E4F1} .row2{background-color: #FBD10A} 员工信息(新增员工) 编号 姓名 薪水 年龄 操作 ${emp.id } ${emp.name } ${emp.salary } ${emp.age } 删除 更新 前一页 前一页 ${i } 后一页 后一页 一共${page.pageTotal }页addEmp.jsp(增加员工页面) addEmp 增加员工 姓名: 薪水: 年龄:empInfo.jsp(更新员工信息页面) Insert title here 更新员工 姓名: 薪水: 年龄:
|
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |