Servlet增删改查

数据库sql语句

CREATE TABLE `employees` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '',
  `age` int(3) unsigned NOT NULL DEFAULT '0',
  `education` varchar(32) DEFAULT '' COMMENT '学历',
  `address` varchar(254) DEFAULT NULL,
  `salary` float(8,2) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

创建主页面index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>添加新员工</title>
</head>
<body>
<h1>添加新员工</h1>
<form action="SaveServlet" method="post">
<table>
    <tr>
        <td>姓名:</td>
        <td><input type="text" name="name"/></td>
    </tr>
    <tr>
        <td>年龄:</td>
        <td><input type="text" name="age"/></td>
    </tr>
    <tr>
        <td>学历:</td>
        <td>
            <select name="education" style="width: 150px">
                <option value="专科">专科</option>
                <option value="本科">本科</option>
                <option value="研究生">研究生</option>
                <option value="博士">博士</option>
                <option value="其他">其他</option>
            </select>
        </td>
    </tr>
    <tr>
        <td>地址:</td>
        <td><input type="text" name="address"/></td>
    </tr>
    <tr>
        <td>薪水:</td>
        <td><input type="text" name="salary"/></td>
    </tr>
    <tr>
        <td colspan="2"><input type="submit" value="保存员工信息"></td>
    </tr>
</table>
</form>
<br /><br />
<a href="ViewServlet">查看员工信息列表</a>

</body>
</html>

Emp.java

package com.tanlei.pojo;

/**
 * @author:Mr.Tan
 * @Create:2018-11-26-14-42
 **/
public class Emp {
    private int id;
    private String name;
    private int age;
    private String address;
    private String education;
    private float salary;

    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 int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getEducation() {
        return education;
    }

    public void setEducation(String education) {
        this.education = education;
    }

    public float getSalary() {
        return salary;
    }

    public void setSalary(float salary) {
        this.salary = salary;
    }
}

EmpDao.java

package com.tanlei.pojo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author:Mr.Tan
 * @Create:2018-11-26-14-42
 **/
public class EmpDao {
    //表名称
    private static String tbName="employees";

    //连接数据库
    public static Connection getConnection(){
        Connection con=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost/test?useSSL=false&characterEncoding=utf8", "root", "password");
            } catch (Exception e) {
                e.printStackTrace();
            System.out.println(e);
            }
            return con;
    }

    //保存
    public static int save(Emp e){
      int status=0;
      try {
      Connection con=EmpDao.getConnection();
      String sql="INSERT INTO "+tbName +"(name,age,education,address,salary) values (?,?,?,?,?);";
          //SQL语句已预编译并存储在PreparedStatement对象中
      PreparedStatement ps=con.prepareStatement(sql);
      ps.setString(1, e.getName());
      ps.setInt(2, e.getAge());
      ps.setString(3, e.getEducation());
      ps.setString(4, e.getAddress());
      ps.setFloat(5, e.getSalary());

      status=ps.executeUpdate();
      con.close();
      } catch (SQLException e1) {
            e1.printStackTrace();
      }
        return status;
    }

    //修改
    public static int update(Emp e){
        int status=0;
        try {
           String sql="UPDATE "+tbName+" SET name =?,age=?,education=?,address=?,salary=? where id=?";
            System.out.println(sql);
            Connection con = EmpDao.getConnection();
            //SQL语句已预编译并存储在PreparedStatement对象中
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, e.getName());
            ps.setInt(2, e.getAge());
            ps.setString(3, e.getEducation());
            ps.setString(4, e.getAddress());
            ps.setFloat(5, e.getSalary());
            ps.setInt(6, e.getId());
            status=ps.executeUpdate();
            con.close();
        } catch (Exception e1) {
            e1.printStackTrace();
        }

        return status;
    }

    //删除
    public static int delete(int id ){
        int status = 0;
        try {
            String sql = "DELETE FROM " + tbName + " WHERE id=?";
            Connection con = EmpDao.getConnection();
            //SQL语句已预编译并存储在PreparedStatement对象中
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            status = ps.executeUpdate();

            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return status;

    }

    //通过指定id获得
    public static Emp getEmployeeById(int id ){
      Emp e=new Emp();
        try {
        String sql = "SELECT * FROM "+ tbName + " WHERE id=?";
        Connection con = EmpDao.getConnection();
        //SQL语句已预编译并存储在PreparedStatement对象中
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1, id);
        // 执行此 PreparedStatement对象中的SQL查询，并返回查询 PreparedStatement的 ResultSet对象
            ResultSet rs = ps.executeQuery();
            if(rs.next()){
              e.setId(rs.getInt("id"));
              e.setName(rs.getString("name"));
              e.setAge(rs.getInt("age"));
              e.setAddress(rs.getString("address"));
              e.setEducation(rs.getString("education"));
              e.setSalary(rs.getFloat("salary"));

            }
            con.close();
        } catch (Exception e1) {
            e1.printStackTrace();
        }

         return e;
    }

    public static List<Emp> getAllEmployees(){
        List <Emp> list=new ArrayList<Emp>();
        try{
        String sql="SELECT * FROM " + tbName+" ORDER BY id DESC";
        Connection con = EmpDao.getConnection();
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Emp e = new Emp();
            e.setId(rs.getInt("id"));
            e.setName(rs.getString("name"));
            e.setAddress(rs.getString("address"));
            e.setAge(rs.getInt("age"));
            e.setEducation(rs.getString("education"));
            e.setSalary(rs.getFloat("salary"));
            list.add(e);
        }
        con.close();
        } catch (Exception e) {
        e.printStackTrace();
        }

        return list;

}
}

ViewServlet.java

package com.tanlei.Servlet;

import com.tanlei.pojo.Emp;
import com.tanlei.pojo.EmpDao;

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 java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

@WebServlet("/ViewServlet")
public class ViewServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setCharacterEncoding("UTF-8");
        resp.setContentType("text/html;charset=UTF-8");
        req.setCharacterEncoding("UTF-8");
        PrintWriter out=resp.getWriter();
        out.println("<a href='index.html'>添加新员工</a>");
        out.println("<h1>员工列表</h1>");

        List<Emp> list= EmpDao.getAllEmployees();
        out.print("<table border='1' width='100%'");
        out.print("<tr><th>编号</th><th>姓名</th><th>年龄</th><th>学历</th><th>薪水</th><th>地址</th><th>操作</th></tr>");
        for(Emp e:list){
            out.print("<tr><td>"+e.getId()+"</td><td>"+e.getName()+"</td><td>"+e.getAge()+"</td><td>"+e.getEducation()+"</td><td>"+e.getSalary()+"</td><td>"+e.getAddress()+"</td><td><a href='EditServlet?id="+e.getId()+"'>编辑</a> | <a href='DeleteServlet?id="+e.getId()+"' onClick=\"return confirm('确定要删除吗？')\">删除</a></td></tr>");
        }
        out.print("</table>");
        out.close();



    }
}

SaveServlet.java

package com.tanlei.Servlet;

import com.tanlei.pojo.Emp;
import com.tanlei.pojo.EmpDao;

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 java.io.IOException;
import java.io.PrintWriter;

@WebServlet("/SaveServlet")
public class SaveServlet  extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setCharacterEncoding("UTF-8");
        resp.setContentType("text/html;charset=UTF-8");
        req.setCharacterEncoding("UTF-8");
        PrintWriter out =resp.getWriter();
        String name=req.getParameter("name");
        String age=req.getParameter("age");
        String education=req.getParameter("education");
        String address=req.getParameter("address");
        String salary=req.getParameter("salary");

        Emp e=new Emp();
        e.setName(name);
        e.setAge(Integer.parseInt(age));
        e.setAddress(address);
        e.setEducation(education);
        e.setSalary(Float.parseFloat(salary));

        int status= EmpDao.save(e);
        if (status>0){
            out.print("<p>保存员工信息记录成功！</p>");
            req.getRequestDispatcher("index.html").include(req,resp);
        }else{
            out.println("对不起,保存失败");
        }
        out.close();
    }
}

EditServlet.java

package com.tanlei.Servlet;

import com.tanlei.pojo.Emp;
import com.tanlei.pojo.EmpDao;

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 java.io.IOException;
import java.io.PrintWriter;

@WebServlet("/EditServlet")
public class EditServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
       resp.setCharacterEncoding("UTF-8");
       resp.setContentType("text/html;charset=UTF-8");
       req.setCharacterEncoding("UTF-8");
        PrintWriter out=resp.getWriter();
        out.println("<h1>更新员工信息</h1>");
        String sid=req.getParameter("id");
        int id=Integer.parseInt(sid);
        Emp e= EmpDao.getEmployeeById(id);
        String education=e.getEducation();
        out.print("<form action='EditServlet' method='post'>");
        out.print("<table>");
        out.print("<tr><td></td><td><input type='hidden' name='id' value='" + e.getId() + "'/></td></tr>");
        out.print("<tr><td>名字:</td><td><input type='text' name='name' value='" + e.getName() + "'/></td></tr>");
        out.print("<tr><td>年龄:</td><td><input type='text' name='age' value='" + e.getAge()+ "'/></td></tr>");
        out.print("<tr><td>地址:</td><td><input type='text' name='address' value='" + e.getAddress() + "'/></td></tr>");
        out.print("<tr><td>学历:</td><td>");
        out.print("<select name='education' style='width:150px'>");
        if(education == "专科") {
            out.print("<option value='专科' selected='selected'>专科</option>");
        }else {
            out.print("<option value='专科'>专科</option>");
        }

        if(education == "本科") {
            out.print("<option value='本科' selected='selected'>本科</option>");
        }else {
            out.print("<option value='本科'>本科</option>");
        }

        if(education == "研究生") {
            out.print("<option value='研究生' selected='selected'>研究生</option>");
        }else {
            out.print("<option value='研究生'>研究生</option>");
        }
        if(education == "博士") {
            out.print("<option value='博士' selected='selected'>博士</option>");
        }else {
            out.print("<option value='博士'>博士</option>");
        }

        if(education == "其它") {
            out.print("<option value='其它' selected='selected'>其它</option>");
        }else {
            out.print("<option value='其它'>其它</option>");
        }
        out.print("</select>");
        out.print("</td></tr>");
        out.print("<tr><td>薪水:</td><td><input type='text' name='salary' value='" + e.getSalary() + "'/></td></tr>");
        out.print("<tr><td colspan='2'><input type='submit' value='编辑&保存'/></td></tr>");
        out.print("</table>");
        out.print("</form>");

        out.close();

    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setCharacterEncoding("UTF-8");
        resp.setContentType("text/html;charset=UTF-8");
        req.setCharacterEncoding("UTF-8");
        PrintWriter out = resp.getWriter();

        String sid = req.getParameter("id");
        int id = Integer.parseInt(sid);
        String name = req.getParameter("name");
        String age = req.getParameter("age");
        String address = req.getParameter("address");
        String education = req.getParameter("education");
        String salary = req.getParameter("salary");

        Emp e = new Emp();
        e.setId(id);
        e.setName(name);
        e.setAddress(address);
        e.setAge(Integer.parseInt(age));
        e.setSalary(Float.parseFloat(salary));
        e.setEducation(education);

        int status = EmpDao.update(e);
        if (status > 0) {
            resp.sendRedirect("ViewServlet");
        } else {
            out.println("对不起更新信息失败！");
        }
        out.close();
    }

}

DeleteServlet.java

package com.tanlei.Servlet;

import com.tanlei.pojo.EmpDao;

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 java.io.IOException;

@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        resp.setCharacterEncoding("UTF-8");
        resp.setContentType("text/html;charset=UTF-8");
        req.setCharacterEncoding("UTF-8");
        String sid = req.getParameter("id");
        int id = Integer.parseInt(sid);
        EmpDao.delete(id);
        resp.sendRedirect("ViewServlet");
    }

}

注意：这里不需要配置web.xml，所有Servlet的模式映射都是使用@WebServlet("/mapurl") 注解来声明了。

http://localhost:8080/index.html

posted @ 2018-11-27 15:41 言西早石头侠阅读(544) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

言西早石头侠

宁肯像种子一样等待也不愿像疲惫的陀螺旋转得那样勉强

Servlet增删改查

公告

言西早石头侠

宁肯像种子一样等待 也不愿像疲惫的陀螺 旋转得那样勉强

Servlet增删改查

公告

宁肯像种子一样等待也不愿像疲惫的陀螺旋转得那样勉强