从零开始搭建JDBC+servlet管理系统（2）——建立java bean和DAO类

首先我们要建立一个类用来存放数据。之后数据库中存放的也就是这个类的成员。像这种只有一组成员变量和它们的setter/getter的类称所bean。你可以简单地理解成C里的结构体。我们以一个Hero类为例：

package bean;

public class Hero {

    private int id;
    private String name;
    private float hp;
    private 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层。DAO就是数据交互的意思，顾名思义，DAO对象控制一个bean对象和数据库的信息传递。我们可以使用上篇文章提到的JDBC控制语句来构建一个DAO类。

package dao;

import bean.Hero;

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

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://localhost:3306/students_info?characterEncoding=UTF8",
                "root", "moshouniqi419");
    }

    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);
            if (rs.next()) {

                total = rs.getInt(1);
            }
        } 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.getName());
            ps.setFloat(2, hero.getHp());
            ps.setInt(3, hero.getDamage());

            ps.execute();

            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {

                hero.setId(rs.getInt(1));
            }
        } 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.getName());
            ps.setFloat(2, hero.getHp());
            ps.setInt(3, hero.getDamage());
            ps.setInt(4, hero.getId());

            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(3);
                int damage = rs.getInt(4);

                hero.setName(name);
                hero.setHp(hp);
                hero.setDamage(damage);
                hero.setId(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 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();
                hero.setId(rs.getInt(1));
                hero.setName(rs.getString(2));
                hero.setHp(rs.getFloat(3));
                hero.setDamage(rs.getInt(4));

                heros.add(hero);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }

        return heros;
    }
}

这个类实现了最基本的增删改查操作。至此，JDBC部分就结束了。之后我们将进入servlet开发的部分。