一个DAO的实现

接口 package jdbctest; import java.util.List; public interface DAO { //增加 public void add(Hero hero); //更新 public void update(Hero hero); //删除 public void delete(int id); //获取 public Hero get(int id); //查询 public List<Hero> list(int start, int count); } 接口实现 package jdbctest; 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 java.sql.Connection; public class HeroDAO implements DAO{ public HeroDAO(){ try { Class.forName("com.mysql.cj.jdbc.Driver");//mysql8中注册驱动与mysql5不一样 } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //getConnections()函数返回conn对象 public Connection getConnections() throws SQLException{ //注册驱动，获得连接Connection return DriverManager.getConnection("jdbc:mysql://192.168.88.120:3306：3306/db_hero?characterEncoding=UTF-8","root","123456"); } public int getTotal(){ int total = 0; //创建连接对象和连接语句对象 try(Connection c = getConnections();Statement s = c.createStatement();){ String sql = "select count(*) from hero"; //executeQuery()方法仅仅用于select语句，返回对象。execute()用于执行所有语句，返回boolean类型。executeUpdate()用于执行delete,update,insert语句，返回int ResultSet rs = s.executeQuery(sql); while(rs.next()){ //next()方法使ResultSet的游标向下移动，获取int列的数据，参数1是代表第一列 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 = getConnections(); PreparedStatement ps = c.prepareStatement(sql);) { //让指定位置（0,1,2,3）的参数设定String，float，int类型 ps.setString(1, hero.name); ps.setFloat(2, hero.hp); ps.setInt(3, hero.damage); ps.execute(); //需要获取插入记录的自增主键，这时候通常用getGeneratedKeys()方法获取主键 ResultSet rs = ps.getGeneratedKeys(); if (rs.next()) { //getInt(column index)获取当前行指定列上的值，参数就是列数，列数从1开始，而不是0 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 = getConnections(); 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){ String sql = "delete from hero where id = ?"; try (Connection c = getConnections(); PreparedStatement ps = c.prepareStatement(sql);) { ps.setInt(1,id); ps.execute(); }catch(SQLException e){ e.printStackTrace(); } } @Override public List<Hero> list(int start, int count) { // TODO Auto-generated method stub ArrayList<Hero> heros = new ArrayList(); String sql = "select *from hero limit ?,?"; try (Connection c = getConnections(); 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.id = rs.getInt(1); hero.name = rs.getString(2); hero.hp = rs.getFloat(3); hero.damage = rs.getInt(4); heros.add(hero); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return heros; } @Override public Hero get(int id) { // TODO Auto-generated method stub Hero hero = null; String sql = "select *from hero where id = ?"; try (Connection c = getConnections(); PreparedStatement ps = c.prepareStatement(sql);) { //先设置值 ps.setInt(1,id); //再进行结果集的操作 ResultSet rs = ps.executeQuery(); if(rs.next()){ hero = new Hero(); hero.id = rs.getInt(1); hero.name = rs.getString(2); hero.hp = rs.getFloat(3); hero.damage = rs.getInt(4); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return hero; } }