数据库程序设计第六天--管理员权限

一、说在前面

　　今天目标完成管理员对隔离人员的增删改查操作

二、任务进度

package Servlet;

import java.io.IOException;
import java.util.ArrayList;
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 Bean.LoginBean;
import Bean.PersonBean;
import Dao.IPDao;
import Dao.LoginDao;

/**
 * Servlet implementation class PeopleShowServlet
 */
@WebServlet("/peopleShowServlet")
public class PeopleShowServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public PeopleShowServlet() {
        super();
        // TODO Auto-generated constructor stub
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        List<PersonBean> list=new ArrayList();
        IPDao dao=new IPDao();
        list=dao.list();
        request.setAttribute("list", list);
        request.getRequestDispatcher("personShow.jsp").forward(request, response);
        
        
    }
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

package Bean;
/**
*
*@author 作者:高宇豪
*@version 创建时间:2020年7月16日上午8:18:26
*类说明:
*/
public class PersonBean {
    
    
    //person表
    private int pid;
    private String pname;
    private String sex;
    private String birth;
    
    
    //information表
    private int wid;
    private String wname;
    private String startday;    //隔离日期
    private String endday;        //结束日期
    private String from;        //来源地
    private String eg;            //备注
    
    
    public PersonBean(String pname, String sex, String birth) {
        super();
        this.pname = pname;
        this.sex = sex;
        this.birth = birth;
    }
    public PersonBean(int pid, int wid, String startday, String endday, String from, String eg) {
        super();
        this.pid = pid;
        this.wid = wid;
        this.startday = startday;
        this.endday = endday;
        this.from = from;
        this.eg = eg;
    }
    public PersonBean(int wid, String wname, String startday, String endday, String from, String eg) {
        super();
        this.wid = wid;
        this.wname = wname;
        this.startday = startday;
        this.endday = endday;
        this.from = from;
        this.eg = eg;
    }
    public PersonBean(int pid, String pname, String sex, String birth, int wid, String wname, String startday,
            String endday, String from, String eg) {
        super();
        this.pid = pid;
        this.pname = pname;
        this.sex = sex;
        this.birth = birth;
        this.wid = wid;
        this.wname = wname;
        this.startday = startday;
        this.endday = endday;
        this.from = from;
        this.eg = eg;
    }
    public PersonBean(int pid, String pname, String sex, String birth) {
        super();
        this.pid = pid;
        this.pname = pname;
        this.sex = sex;
        this.birth = birth;
    }
    public int getPid() {
        return pid;
    }
    public void setPid(int pid) {
        this.pid = pid;
    }
    public String getPname() {
        return pname;
    }
    public void setPname(String pname) {
        this.pname = pname;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getBirth() {
        return birth;
    }
    public void setBirth(String birth) {
        this.birth = birth;
    }
    public int getWid() {
        return wid;
    }
    public void setWid(int wid) {
        this.wid = wid;
    }
    public String getWname() {
        return wname;
    }
    public void setWname(String wname) {
        this.wname = wname;
    }
    public String getStartday() {
        return startday;
    }
    public void setStartday(String startday) {
        this.startday = startday;
    }
    public String getEndday() {
        return endday;
    }
    public void setEndday(String endday) {
        this.endday = endday;
    }
    public String getFrom() {
        return from;
    }
    public void setFrom(String from) {
        this.from = from;
    }
    public String getEg() {
        return eg;
    }
    public void setEg(String eg) {
        this.eg = eg;
    }
}

package Dao;

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

import Bean.LoginBean;
import Bean.PersonBean;
import DBUtil.DBUtil;

/**
*
*@author 作者:高宇豪
*@version 创建时间:2020年7月16日上午8:32:44
*类说明:
*/
public class PersonDao {

    //添加
        public boolean insert(PersonBean person){
            String sql="insert into person (pname,sex,birth) values ('"+person.getPname()+"','"+person.getSex()+"','"+person.getBirth()+"')";
            System.out.println("执行了添加"+sql);
            Connection conn=DBUtil.getConn();
            Statement state=null;
            try {
                state=conn.createStatement();
                state.executeUpdate(sql);
            }catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }finally {
                DBUtil.close(state,conn);
            }
            return true ;
        }
        //删除
        public boolean delete(int pid){
            String sql="delete from person where pid='"+pid+"'";
            Connection conn=DBUtil.getConn();
            Statement state=null;
            try {
                if(!select(pid)) {
                    return false;
                }
                state=conn.createStatement();
                state.executeUpdate(sql);
            }catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }finally {
                DBUtil.close(state,conn);
            }
            return true ;
        }
        //判断是否存在
        public boolean select(PersonBean person){
            boolean flag=false;
            String sql="select * from person where pid='"+person.getPid()+"'  ";
            //判断语句正确
            //System.out.println(sql);
            Connection conn=DBUtil.getConn();
            Statement state=null;
            ResultSet rs = null;
            try {
                state=conn.createStatement();
                rs=state.executeQuery(sql);
                while (rs.next()) {
                    flag = true;
                }
            }catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }finally {
                DBUtil.close(state,conn);
            }
            return flag;
        }
        public boolean select(int pid){
            boolean flag=false;
            String sql="select * from person where pid='"+pid+"' ";
            //判断语句正确
            //System.out.println(sql);
            Connection conn=DBUtil.getConn();
            Statement state=null;
            ResultSet rs = null;
            try {
                state=conn.createStatement();
                rs=state.executeQuery(sql);
                if (rs.next()) {
                    flag = true;
                }
            }catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }finally {
                DBUtil.close(state,conn);
            }
            return flag;
        }
        public int select(String pname){
            int pid=0;
            String sql="select * from person where pname='"+pname+"' ";
            //判断语句正确
            //System.out.println(sql);
            Connection conn=DBUtil.getConn();
            Statement state=null;
            ResultSet rs = null;
            try {
                state=conn.createStatement();
                rs=state.executeQuery(sql);
                if (rs.next()) {
                    pid=rs.getInt("pid");
                }
            }catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }finally {
                DBUtil.close(state,conn);
            }
            return pid;
        }
        //搜索
        public PersonBean search(int pid){
            boolean flag=false;
            String sql="select * from person where pid='"+pid+"' ";
            //判断语句正确
            //System.out.println(sql);
            Connection conn=DBUtil.getConn();
            Statement state=null;
            ResultSet rs = null;
            PersonBean bean=null;
            try {
                state=conn.createStatement();
                rs=state.executeQuery(sql);
                
                while (rs.next()) {
                    String pname=rs.getString("pname");
                    String sex=rs.getString("sex");
                    String birth=rs.getString("birth");
                    bean=new PersonBean(pid,pname,sex,birth);
                }
            }catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }finally {
                DBUtil.close(state,conn);
            }
            return bean;
        }
        public boolean search(String pname){
            boolean flag=false;
            String sql="select * from person where pname='"+pname+"' ";
            System.out.println("执行了搜索");
            //判断语句正确
            //System.out.println(sql);
            Connection conn=DBUtil.getConn();
            Statement state=null;
            ResultSet rs = null;
            PersonBean bean=null;
            try {
                state=conn.createStatement();
                rs=state.executeQuery(sql);
                if (rs.next()) {
                    flag=true;
                }
            }catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }finally {
                DBUtil.close(state,conn);
            }
            return flag;
        }
        //遍历
        public List<PersonBean> list(){
            String sql="select * from person order by pid asc";
            
            List<PersonBean>list=new ArrayList<>();
            //给集合list创造（new）一个存储空间用于存放数据
            
            Connection conn=DBUtil.getConn();
            Statement state=null;
            ResultSet rs=null;
            try {
                state=conn.createStatement();
                rs=state.executeQuery(sql);
                PersonBean bean=null;
                while(rs.next()) {
                    int pid=rs.getInt("pid");
                    String pname=rs.getString("pname");
                    String sex=rs.getString("sex");
                    String birth=rs.getString("birth");
                    bean=new PersonBean(pid,pname,sex,birth);
                    list.add(bean);
                }
            }catch (SQLException e)
            {
                e.printStackTrace();
            }
            finally
            {
                DBUtil.close(state, conn);
            }
            return list;
        }
        public List<PersonBean> list(String pname){
            String sql="select * from person where pname='"+pname+"' order by pid asc";
            
            List<PersonBean>list=new ArrayList<>();
            //给集合list创造（new）一个存储空间用于存放数据
            
            Connection conn=DBUtil.getConn();
            Statement state=null;
            ResultSet rs=null;
            try {
                state=conn.createStatement();
                rs=state.executeQuery(sql);
                PersonBean bean=null;
                while(rs.next()) {
                    int pid=rs.getInt("pid");
                    String sex=rs.getString("sex");
                    String birth=rs.getString("birth");
                    bean=new PersonBean(pid,pname,sex,birth);
                    list.add(bean);
                }
            }catch (SQLException e)
            {
                e.printStackTrace();
            }
            finally
            {
                DBUtil.close(state, conn);
            }
            return list;
        }
        //更改
        public boolean update(PersonBean person){
            String sql="update person set pname='"+person.getPname()+"',sex='"+person.getSex()+"',birth='"+person.getBirth()+"' where pid='"+person.getPid()+"'";
            //System.out.println(sql);
            Connection conn=DBUtil.getConn();
            Statement state=null;
            try {
                state=conn.createStatement();
                state.executeUpdate(sql);
            }catch (Exception e) {
                // TODO: handle exception
                e.printStackTrace();
            }finally {
                DBUtil.close(state,conn);
            }
            return true ;
        }
}

package 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 Bean.LoginBean;
import Dao.LoginDao;

/**
 * Servlet implementation class UpdatePeopleServlet
 */
@WebServlet("/updatePeopleServlet")
public class UpdatePeopleServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public UpdatePeopleServlet() {
        super();
        // TODO Auto-generated constructor stub
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        
        String account=request.getParameter("account");
        String password=request.getParameter("password");
        int level=Integer.parseInt(request.getParameter("level"));
        LoginDao loginjudger=new LoginDao();
        LoginBean login=new LoginBean(account, password, level);
        if(loginjudger.update(login)){
            request.setAttribute("message", "修改成功");
            request.getRequestDispatcher("adminShowServlet").forward(request, response);
        }else {
//            System.out.println("用户不存在");
            request.setAttribute("message", "修改失败");
            request.getRequestDispatcher("adminShowServlet").forward(request, response);
            
        }
        
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

package 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 Bean.LoginBean;
import Dao.LoginDao;

/**
 * Servlet implementation class DeletePeopleServlet
 */
@WebServlet("/deletePeopleServlet")
public class DeletePeopleServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public DeletePeopleServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        String account=request.getParameter("account");
        System.out.println(account);
        LoginDao loginjudger=new LoginDao();
        if(loginjudger.delete(account)){
            request.setAttribute("message", "删除成功");
            request.getRequestDispatcher("adminShowServlet").forward(request, response);
        }else {
            request.setAttribute("message", "删除失败");
            request.getRequestDispatcher("adminShowServlet").forward(request, response);
        }
        
        
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}