7 Servlet进行CRUD操作

1 文件结构

1. dao包下是实体类与数据库的接口
   
2. model包下都是bean，实体模型
3. servlet包下都是Servlet子类

2 bean与dao

2.1 bean与dao的关系

1. bean，对每个属性实现了get和set的类
2. dao，(Data Access Object) 数据访问对象是一个面向对象的数据库接口
3. 一个bean对应一个dao

   2.2 bean

package model;
public class Hero {
    // 实体类，又称模型类,bean
    public int id;
    public String name;
    public float hp;
    public int damage;
    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 float getHp() {
        return hp;
    }
    public void setHp(float hp) {
        this.hp = hp;
    }
    public int getDamage() {
        return damage;
    }
    public void setDamage(int damage) {
        this.damage = damage;
    }
}

2.3 dao

public class HeroDao {
    // DAO
    // (Data Access Object) 数据访问对象是一个面向对象的数据库接口
    // 构造函数加载数据库驱动
    public HeroDao() {
        // 通过反射调用com.mysql.jdbc.Driver的静态域代码
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库加载失败");
            e.printStackTrace();
        }
    }
    // 连接数据库
    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root",
                "HDD740409");
    }
    // 获得记录总个数
    public int getTotal() {
        int total = 0;
        try (Connection c = getConnection(); Statement s = c.createStatement();) {
            String sql = "select count(*) from hero";
            ResultSet rs = s.executeQuery(sql);
            while (rs.next()) {
                total = rs.getInt(1);
            }
            System.out.println("total:" + total);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }
    // 通过预处理语句对数据表单条记录增改删查
    public void add(Hero hero) {
        String sql = "insert into hero values(null,?,?,?)";
        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
            ps.setString(1, hero.name);
            ps.setFloat(2, hero.hp);
            ps.setInt(3, hero.damage);
            ps.execute();
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                int id = rs.getInt(1);
                hero.id = id;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void update(Hero hero) {
        String sql = "update hero set name= ?, hp = ? , damage = ? where id = ?";
        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
            ps.setString(1, hero.name);
            ps.setFloat(2, hero.hp);
            ps.setInt(3, hero.damage);
            ps.setInt(4, hero.id);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void delete(int id) {
        try (Connection c = getConnection(); Statement s = c.createStatement();) {
            String sql = "delete from hero where id = " + id;
            s.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public Hero get(int id) {
        Hero hero = null;
        try (Connection c = getConnection(); Statement s = c.createStatement();) {
            String sql = "select * from hero where id = " + id;
            ResultSet rs = s.executeQuery(sql);
            if (rs.next()) {
                hero = new Hero();
                String name = rs.getString(2);
                float hp = rs.getFloat("hp");
                int damage = rs.getInt(4);
                hero.name = name;
                hero.hp = hp;
                hero.damage = damage;
                hero.id = id;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return hero;
    }
    // 显示所有记录
    public List<Hero> list() {
        return list(0, this.getTotal());
    }
    // 显示指定开头，数量的记录，用limit关键字
    public List<Hero> list(int start, int count) {
        List<Hero> heros = new ArrayList<Hero>();
        String sql = "select * from hero order by id ASC limit ?,? ";
        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
            ps.setInt(1, start);
            ps.setInt(2, count);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Hero hero = new Hero();
                int id = rs.getInt(1);
                String name = rs.getString(2);
                float hp = rs.getFloat("hp");
                int damage = rs.getInt(4);
                hero.id = id;
                hero.name = name;
                hero.hp = hp;
                hero.damage = damage;
                heros.add(hero);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return heros;
    }
}

2.4 SQL脚本

use how2java;
DROP TABLE IF EXISTS `hero`;
CREATE TABLE `hero` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `hp` float DEFAULT NULL,
  `damage` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
select * from hero;

3 Servlet类

3.1 查

留下来一堆超链接指向其他Servlet或静态页面，分别指向

1. 增加
2. 删除
3. 编辑

页面，代码

public class HeroList extends HttpServlet {
    protected void service(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html; charset=UTF-8");
        // 获得所有Hero
        List<Hero> heros = new HeroDao().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>hp</td><td>damage</td><td>delete</td><td>edit</td></tr>\r\n");
        // 指定行元素格式
        String trFormat = "<tr><td>%d</td><td>%s</td><td>%f</td><td>%d</td><td><a href='deletehero?id=%d'>delete</a></td><td><a href='edithero?id=%d'>edit</a></td></tr>\r\n";
        for (Hero hero : heros) {
            // 按照指定行元素格式添加数据
            String tr = String.format(trFormat, hero.getId(), hero.getName(), hero.getHp(), hero.getDamage(),
                    hero.getId(),hero.getId());
            sb.append(tr);
        }
        sb.append("<tr><td colspan=\"6\" style=\"text-align:center\"><a href=\"addhero.html\">新增</a></td></tr>\r\n");
        sb.append("</table>");
        response.getWriter().write(sb.toString());
    }
}

3.2 增

1. 一个静态页面
2. 实现增加功能的Servlet

public class AddHero extends HttpServlet {
    //根据提交过来的添加条目
    protected void service(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        //创建一个新的实体
        Hero hero = new Hero();
        hero.setName(request.getParameter("name"));
        hero.setHp(Float.parseFloat(request.getParameter("hp")));
        hero.setDamage(Integer.parseInt(request.getParameter("damage")));
        new HeroDao().add(hero);
        //这个url不显示页面，而是用客户端跳转的方式跳转到展示页面
        response.sendRedirect("herolist");
    }
}

3.3 删

public class DeleteHero extends HttpServlet{
    // 根据url中的参数部分删除指定id的条目
    protected void service(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        //获取查询字符串中的参数id
        int id = Integer.parseInt(request.getParameter("id"));
        new HeroDao().delete(id);
        //客户端跳转到展示页面
        response.sendRedirect("herolist");
    }
}

3.4 改

1. 修改内容的Servlet，根据传过来的id显示待修改的条目信息。等待修改。

public class EditHero extends HttpServlet {
    protected void service(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 根据查询字符串中的id查询该条目的信息，并且可以修改
        int id = Integer.parseInt(request.getParameter("id"));
        Hero hero = new HeroDao().get(id);
        StringBuffer format = new StringBuffer();
        response.setContentType("text/html; charset=UTF-8");
        format.append("<!DOCTYPE html>");
        format.append("<form action='updatehero' method='post'>");
        format.append("名字 ： <input type='text' name='name' value='%s' > <br>");
        format.append("血量 ： <input type='text' name='hp'  value='%f' > <br>");
        format.append("伤害： <input type='text' name='damage'  value='%d' > <br>");
        format.append("<input type='hidden' name='id' value='%d'>");
        format.append("<input type='submit' value='更新'>");
        format.append("</form>");
        String html = String.format(format.toString(), hero.getName(), hero.getHp(), hero.getDamage(), hero.getId());
        response.getWriter().write(html);
    }
}

1. 把修改提交到具体的功能页面更新。

public class UpdateHero extends HttpServlet {
    protected void service(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 根据post表单，把修改同步到数据库上
        request.setCharacterEncoding("UTF-8");
        Hero hero = new Hero();
        hero.setId(Integer.parseInt(request.getParameter("id")));
        hero.setName(request.getParameter("name"));
        hero.setHp(Float.parseFloat(request.getParameter("hp")));
        hero.setDamage(Integer.parseInt(request.getParameter("damage")));
        new HeroDao().update(hero);
        response.sendRedirect("herolist");
    }
}

4 总结

4.1 与数据库相关的文件组成

1. bean包
2. model包
3. servlet包

   4.2 对url新理解

   url不一定要显示页面，url的作用是实现某个功能的映射，可以实现完这个功能之后跳转到其他url。例如上面的AddHero、DeltetHero、UpdateHero都是实现功能之后跳转到主页，