反射+数据库+JSP+Servlet 读取、查询、删除

1、通过反射获取数据库的用户字段信息（元数据）
2、把数据通过请求转发、展现在前台JSP页面中
3、进行模糊查询信息
4、删除指定的一条信息
工具类 DbUtils

//获取我们的数据库连接
//关闭连接
public class DbUtils {
    //连接池接口
    private static DruidDataSource druidDataSource;

    static {
        //通过输入流获取Properties文件里的配置
        InputStream is =  DbUtils.class.getClassLoader().getResourceAsStream("druid.properties");
        //声明一个properties 的对象
        Properties properties = new Properties();
        try {
            //加载流中的数据
            properties.load(is);
            is.close();//关闭输入流
            //连接池根据这个配置文件来连接我们的数据库;
            druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 连接池获取数据库的连接;返回一个数据库的连接
    public static Connection getConnection() {
        //声明一个连接
        Connection conn = null;
        try{
            //从连接池获取一个连接
            conn = druidDataSource.getConnection();
        }catch (Exception ex) {
            ex.printStackTrace();
        }
        return conn;
    }

    //关闭所有连接  结果集  预处理对象  数据库连接
    public static void colse(ResultSet rs, Statement statement, Connection conn) {
        try{
            if(null != rs) {
                rs.close();
            }
            if(null != statement) {
                statement.close();
            }
            if(null != conn) {
                conn.close();
            }
        }catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

DataUtils

public class DataUtils {

    /**
     * 通过反射获取数据
     * 返回所有数据
     */
    public static <T> List<T> getAll(Class<T> clazz, ResultSet rs) throws Exception {
        List<T> list = new ArrayList<>();

        // while循环是遍历每一行的数据
        while(rs.next()) {
            // 生成一个实例， 数据的一行对应着一个对象
             T t = clazz.getConstructor().newInstance();
            // 返回数据库的元数据信息, 元数据信息中包含列明，列的类型， 列的数量
            ResultSetMetaData resultSetMetaData = rs.getMetaData();
            //获取列的数量
            int colums = resultSetMetaData.getColumnCount();

            // 遍历每一列，取出对应列的数据，然后赋值给对应的属性
            for(int i = 1; i <= colums; i++) {
                //获取字段名，或者别名
                String lable= resultSetMetaData.getColumnLabel(i);
                // 取每列对应的数据
                Object obj = rs.getObject(i);

                //获取属性的类型，因为通过反射获取 set方法的时候, 需要使用类型，set方法的参数类型和属性的类型是一致的
                Class<?> filedType = clazz.getDeclaredField(lable).getType();
                //方法明的首字母大写
                String setMethodName = "set" + lable.substring(0,1).toUpperCase() + lable.substring(1);

                // 获取set方法
                Method setMethod = clazz.getMethod(setMethodName, filedType);  // 获取 set方法
                // 给对应的属性设置值
                setMethod.invoke(t, obj);
            }
            list.add(t);
        }
        return list;
    }
}

People

public class People {
    private Integer id;
    private String name;
    private String gender;
    private String email;
    private Date birthday;
    private Timestamp createTime;
    private Timestamp updateTime;
    private String password;

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public Timestamp getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Timestamp createTime) {
        this.createTime = createTime;
    }

    public Timestamp getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Timestamp updateTime) {
        this.updateTime = updateTime;
    }
}

PeopleServlet

@WebServlet(value = "/people", name = "PeopleServlet")
public class PeopleServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 设置数据，当请求转发的时候，在jsp页面就可以获取数据
        req.setAttribute("peoples", getPeoples());

        //实现请求转发
        req.getRequestDispatcher("list.jsp").forward(req, resp);
    }

    private List<People> getPeoples() {
        List<People> peoples = null;
        // 获取连接
        Connection conn = DbUtils.getConnection();
        //查询的sql
        String sql = "select id, name, gender,email,birthday, createTime, updateTime from people";

        try {
            //预执行sql
            PreparedStatement ps = conn.prepareStatement(sql);
            System.out.println(ps);
            // 执行查询
            ResultSet rs = ps.executeQuery();
            System.out.println(rs);
            // 通过反射给对象设置值
            peoples = DataUtils.getAll(People.class, rs);
            //关闭连接
            DbUtils.colse(rs, ps, conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return peoples;
    }
}

DelectServlet

@WebServlet(value = "/deletePeople",name = "DeletePeople")
public class DeletePeople extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        resp.setCharacterEncoding("utf-8");
        resp.setContentType("text/html;charset=UTF-8");
        //接收JSP传过来的 id
        String id = req.getParameter("id");
        //根据用户 id 删除用户
        deleteuser(id);
        //响应 输出结果
        PrintWriter writer = resp.getWriter();
        String str = "Delete successful!!!";
        writer.write(str);
        //清空 关闭
        writer.flush();
        writer.close();
    }

    /**
     * 根据用户id指定删除 一个人的信息
     * @param id
     */
    private void deleteuser(String id){

        Connection connection = null;//声明一个数据库连接
        PreparedStatement preparedStatement = null;//声明一个sql预处理对象
        String sql = "delete from  people where id = ?";//使用占位符的sql语句
        try {
            connection = DbUtils.getConnection();//连接数据库
            preparedStatement = connection.prepareStatement(sql);//预处理sql
            preparedStatement.setObject(1,id);//使用preparedStatement的setXxx方法设置每一个位置上的值
            int i = preparedStatement.executeUpdate();//执行更新操作

            if (i > 0) {//判断是否执行成功！
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //释放资源
            DbUtils.colse(null,preparedStatement,connection);
        }

    }
}

QueryServlet

@WebServlet(value = "/query", name = "QueryServlet")
public class QueryServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        String name = req.getParameter("username");//获取查询用户的姓名
        //进行模糊处理
        name= "%"+name+"%";
        // 设置数据，当请求转发的时候，在jsp页面就可以获取数据
        req.setAttribute("peoples", getPeoples(name));
        //实现请求转发
        req.getRequestDispatcher("list.jsp").forward(req, resp);

    }

    private List<People> getPeoples(String name ) {
        List<People> peoples = null;
        // 获取连接
        Connection conn = DbUtils.getConnection();
        //查询的sql
//        String sql = "select id, name, gender,email,birthday, createTime, updateTime from people where name like '_%' ";
        String sql = "select id, name, gender,email,birthday, createTime, updateTime from people where name like ? ";

        try {
            //预执行sql
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1,name);//设置值

            // 执行查询
            ResultSet rs = ps.executeQuery();

            // 通过反射给对象设置值
            peoples = DataUtils.getAll(People.class, rs);
            //关闭连接
            DbUtils.colse(rs, ps, conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return peoples;
    }
}

list.jsp

<body>
<h1 class="cls">XXX信息管理系统</h1>
<table border="1" cellpadding="0" cellspacing="0" width="60%" align="center">
    <thead>
    <tr>
        <td>编号</td>
        <td>姓名</td>
        <td>性别</td>
        <td>生日</td>
        <td>邮箱</td>
        <td>创建时间</td>
        <td>更新时间</td>
        <td>编辑</td>
    </tr>
    </thead>
    <tbody>
    <%
        List<People> peopleList = (List<People>) request.getAttribute("peoples");
        for (People p : peopleList) {
//         out.print(123);
    %>

    <tr>
        <td><%=p.getId() %>
        </td>
        <td><%=p.getName() %>
        </td>
        <td><%= (p.getGender()).equals("F")?"女":"男"%>
        </td>
        <td><%=p.getBirthday() %>
        </td>
        <td><%=p.getEmail() %>
        </td>
        <td>
<%--            <%=p.getCreateTime() %>--%>
            <%= new  SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(p.getCreateTime()) %>
        </td>
        <td><%= new  SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(p.getUpdateTime()) %>
<%--        <td><%=p.getUpdateTime() %>--%>
        </td>

        <td>
            <a href="deletePeople?id=<%=p.getId()%>" >删除</a><%--通过id删除--%>
        </td>
    </tr>
    <%} %>

    </tbody>

</table>
</body>

query.jsp

<body>
<h1 class="cls">XXX查询系统</h1>
<form action="query" method="get" class="cls">
    请输入姓名：<input type="text" name="username" >
    <input type="submit" value="查询">
</form>

</body>