JBDC---DAO模式连接Mysql

JDBC之DAO模式

---

---

DAO

DAO(Data Access Object)是一个数据访问接口，数据访问：顾名思义就是与数据库打交道。夹在业务逻辑与数据库资源中间。

dao层的设计可以分为两种：
第一种设计：一个顶层父接口，下面有对应具体数据表的一系列子接口，它们可以定义自己特有的方法，然后父接口有个子类实现类复写父接口的所有方法，然后再设计具体实现类继承父类的子类，实现父类的子接口。

第二种设计，一个顶层父接口，下面有对应具体数据表的一系列子接口，它们可以定义自己特有的方法，然后设计一个父工具类（该工具类里的方法均为泛型，方便实现子类调用），真正的实现子类继承父工具类，实现父类的子接口。

---

DAO设计

DAO组件包括DAO接口，DAO实现类，DAO测试类

Javabean类：描述对象
DAO基类：BaseDAO(连接数据库)
DAO接口：XxxDao
DAO实现类：XxxDaoImpl(实现DAO接口)
DAO测试类:XxxDaoTest

DAO操作步骤

1.根据表结构创建Javabean类

public class Dog {
    private Integer id;
    private String name;
    private Integer health;
    private Integer love;
    private String strain;
    private Date lytm;

    public Dog() {
    }

    public Dog(String name, Integer health, Integer love, String strain) {
        this.name = name;
        this.health = health;
        this.love = love;
        this.strain = strain;
    }

    public Dog(Integer id, String name, Integer health, Integer love, String strain, Date lytm) {
        this.id = id;
        this.name = name;
        this.health = health;
        this.love = love;
        this.strain = strain;
        this.lytm = lytm;
    }

    /**
     * 获取
     * @return id
     */
    public Integer getId() {
        return id;
    }

    /**
     * 设置
     * @param id
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * 获取
     * @return name
     */
    public String getName() {
        return name;
    }

    /**
     * 设置
     * @param name
     */
    public void setName(String name) {
        this.name = name;
    }

    /**
     * 获取
     * @return health
     */
    public Integer getHealth() {
        return health;
    }

    /**
     * 设置
     * @param health
     */
    public void setHealth(Integer health) {
        this.health = health;
    }

    /**
     * 获取
     * @return love
     */
    public Integer getLove() {
        return love;
    }

    /**
     * 设置
     * @param love
     */
    public void setLove(Integer love) {
        this.love = love;
    }

    /**
     * 获取
     * @return strain
     */
    public String getStrain() {
        return strain;
    }

    /**
     * 设置
     * @param strain
     */
    public void setStrain(String strain) {
        this.strain = strain;
    }

    /**
     * 获取
     * @return lytm
     */
    public Date getLytm() {
        return lytm;
    }

    /**
     * 设置
     * @param lytm
     */
    public void setLytm(Date lytm) {
        this.lytm = lytm;
    }

    public String toString() {
        return "Dog{id = " + id + ", name = " + name + ", health = " + health + ", love = " + love + ", strain = " + strain + ", lytm = " + lytm + "}";
    }
}

2.创建DAO接口,提供DAO方法

public interface DogDao {
    Integer saveDog(Dog dog);

    Integer updateDog(Dog dog);

    Integer deleteDog(Dog dog);
}

3.实现DAO接口中的方法

public class DogDaoImpl extends BaseDao implements DogDao{
    //新增
     @Override
    public Integer saveDog(Dog dog) {
        String sql = "insert into dog(name,health,love,strain,lytm) values(?,?,?,?,now())";
        int num = super.executeUpdate(sql, dog.getName(), dog.getHealth(), dog.getLove(), dog.getStrain());
        return num;
        if (num>0){
            System.out.println("新增成功");
        }
        return num;
    }

    //修改
    @Override
    public Integer updateDog(Dog dog) {
        String sql = "update dog set name=?,health=?,love=? where id=?";
        int num = super.executeUpdate(sql, dog.getName(), dog.getHealth(), dog.getLove(), dog.getId());
        if (num > 0) {
            System.out.println("修改成功");
        }
        return num;

    //删除
   @Override
    public Integer deleteDog(Dog dog) {
        String sql = "delete from dog where id=?";
        int num = super.executeUpdate(sql, dog.getId());
        if (num > 0) {
            System.out.println("删除成功");
        }
        return num;
    }
}

5.编写DAO基类

*
* 加载properties文件，实例化Connection,释放资源，执行公共方法*/
public class BaseDao {
    private static String driver;
    private static String url;
    private static String user;
    private static String pwd;

    static {
        //创建Properties对象
        Properties properties =new Properties();
        //加载配置文件，转换为一个集合
        //获取文件路径，可以实用类加载器
        InputStream inputStream = BaseDao.class.getClassLoader().getResourceAsStream("database.properties");
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //获取配置文件中定义的数据
        driver= properties.getProperty("mysqldriver");
        url = properties.getProperty("mysqlurl");
        user = properties.getProperty("mysqluser");
        pwd = properties.getProperty("mysqlpwd");
    }
    //获取Connection连接对象
    public Connection getConnection(){
        Connection connection=null;
        try {
            Class.forName(driver);
            connection= DriverManager.getConnection(url,user,pwd);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return connection;
    }
    //释放资源
    public void close(PreparedStatement preparedStatement,Connection connection){
        try {
             if (null!=preparedStatement)
             preparedStatement.close();
             if (null!=connection)
                 connection.close();
             } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    public void close(Connection connection){
        close(null,connection);
    }
    /*将新增，修改，删除统一抽到一个方法当中
    * ... 表示n个参数[0,+无穷]*/
    public int executeUpdate(String sqlStr,Object... params ){
        Connection connection =this.getConnection();
        PreparedStatement preparedStatement =null;
        String sql =sqlStr;
        int num =-1;
        try {
            preparedStatement =connection.prepareStatement(sql);
            if (null!=params){
                for (int i = 0;i< params.length;i++){
                    preparedStatement.setObject(i+1,params[i]);
                }
            }
             num = preparedStatement.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            this.close(connection);
        }
        return num;
    }
    public static void main(String[] args) {
        BaseDao baseDao = new BaseDao();
        Connection connection = baseDao.getConnection();
        System.out.println(connection);
    }
}

4.测试

    //新增
@Test
    public void testDogDaoInsert(){
        DogDaoImpl dogDao = new DogDaoImpl();
        Dog dog = new Dog( "旺财", 100, 88, "中华田园狗");
        dogDao.saveDog(dog);

    }
    //修改
    @Test
    public void testDogDaoUpdate(){
        DogDaoImpl dogDao = new DogDaoImpl();
        Dog dog = new Dog();
        dog.setNname("可乐");
        dog.sethealth(10);
        dog.setlove(10);
        dog.setId(2);
        dogDao.updateDog(dog);

    }
    //删除
    @Test
    public void testDogDaoDelete(){
        DogDaoImpl dogDao = new DogDaoImpl();
        Dog dog = new Dog();
        dog.setId(4);
        dogDao.deleteDog(dog);

    }