java web简单页面开发

package dao;

import Bean.User;

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

数据库的链接及表的增删改查
public class UserDAO {
    /*public static void main(String[] args) {
        new UserDAO().getTotal();
    }*/

    public UserDAO() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //jdbc:mysql://localhost:3306
    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8",
                "root",
                "123456");
    }

    public int getTotal() {
        int total = 0;
        try (Connection c = getConnection(); Statement s = c.createStatement()) {
            String sql = "select count(*) from user";

            ResultSet resultSet = s.executeQuery(sql);
            while (resultSet.next()) {
                total = resultSet.getInt(1);
            }
            System.out.println("total: " + total);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    public void add(User bean) {
        String sql = "insert into user values(null,?,?,?,?,?)";

        try (Connection c = getConnection();
            PreparedStatement ps = c.prepareStatement(sql,
                    Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, bean.getName());
            ps.setString(2, bean.getPwd());
            ps.setString(3, bean.getSex());
            ps.setString(4, bean.getHome());
            ps.setString(5, bean.getInfo());
            ps.execute();

            ResultSet generatedKeys = ps.getGeneratedKeys();
            if (generatedKeys.next()) {
                int id = generatedKeys.getInt(1);
                bean.setId(id);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void delete(int id) {
        try (Connection c = getConnection();
            Statement s = c.createStatement();){
            String sql = "delete from user where id = " + id;

            s.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void update(User bean) {
        String sql = "update user set name = ?, pwd = ?, sex = ?, home = ?, info = ? where id = ?";

        try (Connection c = getConnection();
            PreparedStatement ps = c.prepareStatement(sql)){

            ps.setString(1, bean.getName());
            ps.setString(2, bean.getPwd());
            ps.setString(3, bean.getSex());
            ps.setString(4, bean.getHome());
            ps.setString(5, bean.getInfo());
            ps.setInt(6, bean.getId());

            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public User get(int id) {
        User user = null;

        try (Connection c = getConnection();
            Statement s = c.createStatement();){
            String sql = "select * from user where id = " + id;

            ResultSet resultSet = s.executeQuery(sql);
            if (resultSet.next()) {
                user = new User();
                String name = resultSet.getString("name");
                String pwd = resultSet.getString("pwd");
                String sex = resultSet.getString("sex");
                String home = resultSet.getString("home");
                String info = resultSet.getString("info");
                user.setId(id);
                user.setName(name);
                user.setPwd(pwd);
                user.setSex(sex);
                user.setHome(home);
                user.setInfo(info);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    public List<User> list() {
        return list(0, Short.MAX_VALUE);
    }

    public List<User> list(int start, int count) {
        List<User> list = new ArrayList<>();

        String sql = "select * from user order by id DESC limit ?, ?";

        try (Connection c = getConnection();
            PreparedStatement ps = c.prepareStatement(sql)){
            ps.setInt(1, start);
            ps.setInt(2, count);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                User user = new User();
                int id = resultSet.getInt(1);
                String name = resultSet.getString("name");
                String pwd = resultSet.getString("pwd");
                String sex = resultSet.getString("sex");
                String home = resultSet.getString("home");
                String info = resultSet.getString("info");
                user.setId(id);
                user.setName(name);
                user.setPwd(pwd);
                user.setSex(sex);
                user.setHome(home);
                user.setInfo(info);
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}

 

重写service方法

将查询的数据进行页面渲染

package service;

import Bean.User;
import dao.UserDAO;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

public class UserServlet extends HttpServlet{
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setContentType("text/html; charset=UTF-8");

        List<User> list = new UserDAO().list();

        StringBuffer sb = new StringBuffer();
        sb.append("<table align='center' border='1' cellspacing='0'>\r\n");
        sb.append("<tr><td>id</td><td>name</td><td>pwd</td><td>sex</td><td>home</td><td>info</td>" +
                "<td>edit</td><td>delete</td></tr>\r\n");

        String sFormat = "<tr><td>%d</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td>" +
                "<td><a href='editUser?id=%d'>edit</a></td><td><a href='deleteUser?id=%d'>delete</a></td></tr>\r\n";

        for (User user : list) {
            String tr = String.format(sFormat, user.getId(), user.getName(), user.getPwd(), user.getSex(),
                    user.getHome(), user.getInfo(), user.getId(), user.getId());
            sb.append(tr);
        }
        sb.append("</table>");
        resp.getWriter().println(sb.toString());

        /*for (User user : list) {
            resp.getWriter().println(user.toString());
        }*/
    }
}

web.xml配置

<servlet>
    <servlet-name>UserServlet</servlet-name>
    <servlet-class>service.UserServlet</servlet-class>
</servlet>

<servlet-mapping>
    <servlet-name>UserServlet</servlet-name>
    <url-pattern>/user</url-pattern>
</servlet-mapping>

配置好tomcat启动访问页面