Servlet CRUD-Servlet提供查询功能

CRUD是常见的页面功能,即我们常说的增删改查 
C - Creation 增加 
R - Retrieve 查询 
U - Update 修改 
D - DELETE 删除 

介绍如何与JDBC结合,通过servlet查询数据库,根据查询结果,得到一个html页面,显示数据库中的内容。 

  • 准备实体类Hero

    Hero类有id,name,hp,damage等属性。

    并且为每一个属性提供public的getter和setter。
    package bean;
    public class Hero {
        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;
        }
         
    }
  • 准备DAO 类 HeroDAO

    准备一个HeroDAO,提供增加,删除,修改,查询等常规数据库操作方法
    package dao;
      
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    import java.util.List;
     
    import bean.Hero;
      
    public class HeroDAO {
      
        public HeroDAO() {
            try {
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
      
        public Connection getConnection() throws SQLException {
            return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root",
                    "admin");
        }
      
        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, Short.MAX_VALUE);
        }
      
        public List<Hero> list(int start, int count) {
            List<Hero> heros = new ArrayList<Hero>();
      
            String sql = "select * from hero order by id desc 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;
        }
      
    }
  • 创建表Hero的SQL

    用于创建表Hero的SQL语句
    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;
  • 为web应用导入mysql-jdbc的jar包

    为web应用导入mysql-jdbc的jar包与为项目导入mysql-jdbc的jar包不同,其作用的web应用在tomcat中运行起来后,能够找到jar包中的类。
    所以需要把 mysq的jar包放在WEB-INF/lib 目录下。
    注: 放在WEB-INF/lib 下指的是能够web应用中找到对应的class,如果要在eclipse中做调试,还是需要为项目添加该jar才可以。
  • 编写 HeroListServlet

    做一个Hero的维护页面需要一些通用的操作,比如增加,删除,编辑,修改,查询等。
    每个不同的操作,都需要一个对应的Servlet,除了做Hero之外,还会做到其他的一些表的相关操作,所以好的规范会对将来的维护更有好处。
    一般会这样命名,以查询为例 HeroListServlet : [表][行为]Servlet 这样一种命名规则。

    所以对于Hero而言就会如此命名:
    增加 HeroAddServlet
    删除 HeroDeleteServlet
    编辑 HeroEditServlet
    修改 HeroUpdateServlet
    查询 HeroListServlet

    在HeroListServlet中,会使用HeroDAO把数据查询出来,然后拼接成一个table用于显示其内容
    package servlet;
     
    import java.io.IOException;
    import java.util.List;
     
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
     
    import bean.Hero;
    import dao.HeroDAO;
     
    public class HeroListServlet extends HttpServlet {
     
        protected void service(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException {
            response.setContentType("text/html; charset=UTF-8");
             
            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></tr>\r\n");
     
            String trFormat = "<tr><td>%d</td><td>%s</td><td>%f</td><td>%d</td></tr>\r\n";
     
            for (Hero hero : heros) {
                String tr = String.format(trFormat, hero.getId(), hero.getName(), hero.getHp(), hero.getDamage());
                sb.append(tr);
            }
     
            sb.append("</table>");
     
            response.getWriter().write(sb.toString());
     
        }
    }
  • 配置web.xml

    在web.xml中把路径 listHero映射到HeroListServlet上。
    <?xml version="1.0" encoding="UTF-8"?>
    <web-app>
     
        <servlet>
            <servlet-name>HelloServlet</servlet-name>
            <servlet-class>HelloServlet</servlet-class>
            <load-on-startup>10</load-on-startup>
        </servlet>
     
        <servlet-mapping>
            <servlet-name>HelloServlet</servlet-name>
            <url-pattern>/hello</url-pattern>
        </servlet-mapping>
         
        <servlet>
            <servlet-name>LoginServlet</servlet-name>
            <servlet-class>LoginServlet</servlet-class>
        </servlet>
     
        <servlet-mapping>
            <servlet-name>LoginServlet</servlet-name>
            <url-pattern>/login</url-pattern>
        </servlet-mapping>   
        <servlet>
            <servlet-name>RegisterServlet</servlet-name>
            <servlet-class>RegisterServlet</servlet-class>
        </servlet>
     
        <servlet-mapping>
            <servlet-name>RegisterServlet</servlet-name>
            <url-pattern>/register</url-pattern>
        </servlet-mapping>
         
            <servlet>
            <servlet-name>HeroListServlet</servlet-name>
            <servlet-class>servlet.HeroListServlet</servlet-class>
        </servlet>
     
        <servlet-mapping>
            <servlet-name>HeroListServlet</servlet-name>
            <url-pattern>/listHero</url-pattern>
        </servlet-mapping>   
             
    </web-app>
  • 重启tomcat,访问http://127.0.0.1/listHero

    重启tomcat,访问
    http://127.0.0.1/listHero



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值