jdbc框架3-动态编写sql语句

我们在jdbc框架-应用程序分层中介绍了JDBC框架的分层，我们掌握了在XxxDaoImpl实现类中添加Dog类的方法，那么通过同样的方式，我们也可以添加List集合，代码如下：

XxxDao接口-添加方法

public interface DogDao {
    Integer saveDog(Dog dog);
    Integer saveDog(List<Dog> dogs);
    Integer updateDog(Dog dog);
    Integer updateDog(List<Dog> dogs);
    Integer delById(Integer id);
    Integer delById(List<Integer> id);
}

XxxDaoImpl实现类-添加单条数据

public class DogDaoImpl extends BaseDao implements DogDao{
    // 新增宠物信息
    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;
    }

    // 修改宠物信息
    public Integer updateDog(Dog dog) {
        String sql = "update dog set name=?, health=?, love=?,strain=? where id=?";
        int num = super.executeUpdate(sql, dog.getName(), dog.getHealth(), dog.getLove(), dog.getStrain(), dog.getId());
        if(num>0){
            System.out.println("修改成功");
        }
        return num;
    }
    
    // 根据id删除宠物信息
    public Integer delById(Integer id) {
        String sql = "delete from dog where id=?";
        int num = super.executeUpdate(sql,id);
        if(num>0){
            System.out.println("id为"+id+"的狗子删除成功");
        }
        return num;
    }
}

XxxDaoImpl实现类-添加List集合

public class DogDaoImpi2 extends BaseDao2<T> implements DogDao{
    // 新增宠物集合信息
    public Integer saveDog(List<Dog> dogs) {
        String sql = "insert into dog(name,health,love,strain,lytm) values";
        for(Dog dog : dogs){
            sql+="('"+dog.getName()+"',"+dog.getHealth()+","+dog.getLove()+",'"+dog.getStrain()+"',now()),";
        }
        sql = sql.substring(0,sql.length()-1);
        System.out.println(sql);

        return super.executeUpdate(sql);
    }

    // 修改宠物集合信息
    public Integer updateDog(List<Dog> dogs) {
    String sql="";
        int num = 0;
        for(Dog dog:dogs){
            sql = "update dog set ";
            if(dog.getName()!=null && dog.getName()!=""){
                sql+="name='"+dog.getName()+"',";
            }
            if(dog.getHealth()!=null){
                sql+="health="+dog.getHealth()+",";
            }
            if(dog.getLove()!=null){
                sql+="love="+dog.getLove()+",";
            }
            if(dog.getStrain()!=null && dog.getStrain()!=""){
                sql+="strain='"+dog.getStrain()+"',";
            }
            sql=sql.substring(0,sql.length()-1) + " where id="+dog.getId()+";";
            System.out.println(sql);
            super.executeUpdate(sql);
            num++;

        }
        return num;
    }

    // 删除宠物集合信息
    public Integer delById(List<Integer> ids) {
        String sql = "delete from dog where id in(";
        for (Integer id :
                ids) {
            sql+=id.toString()+",";
        }
        sql = sql.substring(0,sql.length()-1);
        sql +=")";
        System.out.println(sql);
        return super.executeUpdate(sql);
    }
}

Test测试类

// 批量新增dog
    public void testsaveListDog2(){
        DogDao dogDao = new DogDaoImpl2();
        List<Dog> dogs = new ArrayList<>();
        Dog dog1 = new Dog("a1",20,67,"哈士奇");
        Dog dog2 = new Dog("a2",24,21,"腊肠");
        Dog dog3 = new Dog("a3",32,80,"巴哥");

        dogs.add(dog1);
        dogs.add(dog2);
        dogs.add(dog3);
        System.out.println(dogDao.saveDog(dogs));
    }
// 批量修改dog信息
    public void testupdateListDog2(){
        DogDao dogDao = new DogDaoImpl2();
        List<Dog> dogs = new ArrayList<>();
        Dog dog1 = new Dog(16,"一一",20,67,"哈士奇");
        Dog dog2 = new Dog(17,"二二",24,21,"腊肠");
        Dog dog3 = new Dog(18,"三三",32,80,"巴哥");
        Dog dog4 = new Dog(19,"四四",58,17,"雪纳瑞");
        dogs.add(dog1);
        dogs.add(dog2);
        dogs.add(dog3);
        dogs.add(dog4);
        System.out.println(dogDao.updateDog(dogs));
    }
// 批量删除dog信息
    public void testDeleteListDog2(){
        DogDao dogDao = new DogDaoImpl2();
        List<Integer> dogsID=new ArrayList<>();
        dogsID.add(25);
        dogsID.add(26);
        dogDao.delById(dogsID);
    }