JDBC 封装 增删改

在编写JDBC时存在的问题

 

• 可读性差
• 不利于后期维护和修改
• 不利于代码复用

JDBC封装的步骤：

在src里面创建一个文件，命名为：database.properties

 database.properties的内容如下：

mysqldriver=com.mysql.cj.jdbc.Driver
mysqlurl=jdbc:mysql://IP地址:端口/数据库名
mysqluser=账号
mysqlpwd=密码

将通用的操作封装到工具类数据库工具类 

public class BaseDao {

    private static String driver;
    private static String url;
    private static String user;
    private static String pwd;

  
    static {
        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");

    public Connection getConnection() {

        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, pwd);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public void close(PreparedStatement preparedStatement, Connection connection) {
        try {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (null != connection) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void close(Connection connection) {

        close(null, connection);

    }
    
    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(preparedStatement,connection);
        }
        return num;
    }

定义实体类

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 + "}";
    }
}

将用户对象的所有操作抽取成接口

public interface DogDao {
    Integer saveDog(Dog dog);

    Integer updateDog(Dog dog);

    Integer delById(Integer id);
    Integer delByHealth(Integer health);
}

由不同数据库的实现类分别实现接口；实现类实现接口并继承数据库工具类

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());
        if (num > 0) {
            System.out.println("插入成功");
        }
        return num;
    }

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

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

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

测试类进行测试

public class AppTest {
    
    @Test
    public void testDogDaoInsert() {
        DogDao dogDao = new DogDaoImpl();
        Dog dog = new Dog("雪碧", 100, 88, "牧羊犬");
        dogDao.saveDog(dog);
    }

    @Test
    public void testDogDaoUpdate() {
        DogDao dogDao = new DogDaoImpl();
        Dog dog = new Dog("非常可乐", 100, 88, "中华田园狗");
        dog.setId(1);
        dogDao.updateDog(dog);
    }

    @Test
    public void testDogDaoDelete() {
        DogDao dogDao = new DogDaoImpl();
        dogDao.delById(2);
    }

    @Test
    public void testDogDaoDelete2() {
        DogDao dogDao = new DogDaoImpl();
        dogDao.delByHealth(98);
    }