JDBC --- 批量增删改数据

墨蓝 2024-03-30 15:28 62阅读 0赞

## 数据库Dog表的结构 ##

create table if not exists dog
    (
        id     int(10) primary key auto_increment,
        name   varchar(32) comment '宠物名字',
        health int(10) comment '健康值',
        love   int(10) comment '亲密度',
        strain varchar(32) comment '品种',
        lytm   datetime comment '领养时间'
    );

## 实体类代码 ##

import java.util.Date;
    import java.util.List;
    
    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 int executeUpdate(String sqlStr, Object... params) {
            Connection connection = getConnection();
            PreparedStatement preparedStatement = null;
            int count = -1;
            String sql = sqlStr;
            try {
                preparedStatement = connection.prepareStatement(sql);
                if (null != params) {
                    for (int i = 0; i < params.length; i++) {
                        preparedStatement.setObject(i + 1, params[i]);
                    }
                }
                count = preparedStatement.executeUpdate();
                return count;
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
    //释放资源
                    this.close(preparedStatement, connection);
            }
            return count;
        }

## 接口里面代码 ##

//新增
        Integer batchSaveDog(List<Dog> dogs);
    
        //根据id删除
        Integer batchDelDog(List<Integer> ids);
    
        //批量修改
        Integer batchUpdateDog(List<Dog> dogs);

## 批量增加数据 ##

@Override
        public Integer batchSaveDog(List<Dog> dogs) {
    //定义SQL语句
            String sql = "insert into dog (name,health,love,strain,lytm) values";
    //利用for循环实现多次添加数据
            for (Dog dog : dogs) {
    //拼接sql语句
                sql += "('" + dog.getName() + "'," + dog.getHealth() + ","
                        + dog.getLove() + ",'" + dog.getStrain() + "'," + "now()),";
            }
    //去掉最后一个标点符号
            sql = sql.substring(0, sql.length() - 1);
            //System.out.println(sql);
            //return null;
            return super.executeUpdate(sql);
        }

### 测试代码 ###

@Test
        public void testBatchSaveDog() {
            DogDao dogDao = new DogDaoImpl2();
            Dog dog = new Dog("可可可乐", 96, 100, "哈士奇");
            Dog dog1 = new Dog("百事可乐", 63, 88, "中华土狗");
            Dog dog2 = new Dog("雪碧", 55, 100, "中华田园犬");
            Dog dog3 = new Dog("芬达", 93, 88, "藏獒");
            List<Dog> list = new ArrayList<>();
            list.add(dog);
            list.add(dog1);
            list.add(dog2);
            list.add(dog3);
            Integer integer = dogDao.batchSaveDog(list);
            System.out.println(“影响的行数：”+integer);
        }

sql语句

insert into dog (name,health,love,strain,lytm) 
    values('可可可乐',96,100,'哈士奇',now()),
          ('百事可乐',63,88,'中华土狗',now()),
          ('雪碧',55,100,'中华田园犬',now()),
          ('芬达',93,88,'藏獒',now());

## 批量删除数据 ##

**利用sql语句中的 IN 操作符判断要删除的id是否包含在集合中如果在则删除**

@Override
        public Integer batchDelDog(List<Integer> ids) {
            String sql = "delete from dog where id in(";
            for (Integer id : ids) {
                sql += id + ",";
            }
            sql = sql.substring(0, sql.length() - 1);
            sql += ")";
    //        System.out.println(sql)；
    //        return null;
            return super.executeUpdate(sql);
        }

### 测试代码 ###

@Test
        public void testBatchDelDog() {
            DogDao dogDao = new DogDaoImpl2();
            List<Integer> list = new ArrayList<>();
            list.add(35);
            list.add(36);
            list.add(38);
            Integer integer = dogDao.batchDelDog(list);
            System.out.println("影响行数：" + integer);
        }

### sql语句 ###

delete from dog where id in(35,36,38);

## 批量修改数据 ##

说明：如果批量修改的每一行数据是不一样的，比如第一行修改name和health，第二行修改health和strain……，这时候就要判断每一个字段是否为空

@Override
        public Integer batchUpdateDog(List<Dog> dogs) {
            String sql = "";
            int count = 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);
                count += super.executeUpdate(sql);
            }
            System.out.println("影响行数：" + count);
            return count;
        }

### 测试代码 ###

@Test
        public void testBatchUpdateDog() {
            DogDao dogDao = new DogDaoImpl2();
            Dog dog1 = new Dog();
            dog1.setName("旺仔");
            dog1.setLove(100);
            dog1.setId(37);
            Dog dog2 = new Dog();
            dog2.setName("牛奶糖");
            dog2.setLove(100);
            dog2.setId(55);
            List<Dog> list = new ArrayList<>();
            list.add(dog1);
            list.add(dog2);
            dogDao.batchUpdateDog(list);
        }

### sql语句 ###

update dog set  name='旺仔', love=100 where id=37;
    update dog set  name='牛奶糖', love=100 where id=55;