DAO

DAO=Data Access Object

数据访问对象

实际上就是运用了练习-ORM中的思路，把数据库相关的操作都封装在这个类里面，其他地方看不到JDBC的代码

package test;

import bean.Hero;

import java.util.List;

public interface DAO {
    //add
    public void add(Hero hero);
    //update
    public void update(Hero hero);
    //delete
    public void delete(int id);
    //retrieve
    public Hero get(int id);
    //查询
    public List<Hero> list();
    //分页查询
    public List<Hero> list(int start,int count);

}


package test;

import bean.Hero;

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

public class DAOImpl implements DAO {
    public DAOImpl() {
        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",
                "123456");
    }
    @Override
    public void add(Hero h) {
        String sql="insert into hero values(?,?,?,?)";
        try(Connection c=getConnection();
            PreparedStatement ps=c.prepareStatement(sql);) {
            ps.setInt(1,h.id);
            ps.setString(2,h.name);
            ps.setFloat(3,h.hp);
            ps.setInt(4,h.damage);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void update(Hero h) {
        String sql="UPDATE hero SET name=?,hp=?,damage=? WHERE id=?";
        try(Connection c=getConnection();
            PreparedStatement ps=c.prepareStatement(sql);) {
            ps.setInt(1,h.id);
            ps.setString(2,h.name);
            ps.setFloat(3,h.hp);
            ps.setInt(4,h.damage);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void delete(int id) {
        String sql="DELETE FROM hero where id=?";
        try(Connection c=getConnection();
            PreparedStatement ps=c.prepareStatement(sql);) {
            ps.setInt(1,id);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Hero get(int id) {
        Hero h=new Hero();
        String sql="SELECT * FROM hero WHERE id=?";
        //使用try-with-resource的方式自动关闭连接
        try (
                //建立数据库链接
                Connection c= getConnection();
                //创建语句
                PreparedStatement ps=c.prepareStatement(sql);
        ){
            ps.setInt(1,id);
            ResultSet rs=ps.executeQuery();
            if (rs.next()){
                h.id=rs.getInt(1);
                h.name=rs.getString(2);
                h.hp=rs.getFloat(3);
                h.damage=rs.getInt(4);
                System.out.println(h.name);
            }

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

    @Override
    public List<Hero> list() {
     return list(0,Short.MAX_VALUE);
}
    @Override
    public List<Hero> list(int start, int count) {
        List<Hero> heroes=new ArrayList<>();
        String sql="SELECT * FROM hero limit ?,?";
        //使用try-with-resource的方式自动关闭连接
        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 h=new Hero();
                h.id=rs.getInt(1);
                h.name=rs.getString(2);
                h.hp=rs.getFloat(3);
                h.damage=rs.getInt(4);
                System.out.println(h.name);
                heroes.add(h);
            }

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

package test;

import bean.Hero;

import java.util.List;

public class test {
    public static void main(String[] args) {
        DAOImpl dao=new DAOImpl();
        List<Hero> list=dao.list(0,5);
        Hero hero=list.get(0);
        System.out.println("数据个数：："+ list.size());
        System.out.println("id："+hero.id+" name："+hero.name+" hp："+hero.hp+" damage："+hero.damage);
    }
    }