批量添加数据库操作代码（代码+sql+postman测试示例）

小笼包306

已于 2023-02-13 14:01:33 修改

阅读量254

点赞数

文章标签： java intellij idea sql mysql 数据库 Powered by 金山文档

于 2023-02-10 10:46:54 首次发布

本文链接：https://blog.csdn.net/ccxlct/article/details/128965070

版权

该文章展示了一个使用JavaSpringBoot框架，通过控制器、服务、Mapper接口来批量添加People数据到MySQL数据库的过程。数据表结构、实体类定义、RESTAPI接口、MapperSQL语句以及Postman的测试用例都进行了详细说明。

摘要由CSDN通过智能技术生成

（1）创建数据表 People

CREATETABLE `people` (
  `id` int(10) NOTNULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  `height` double(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=gb2312

（2）创建实体类People

@Data//有这个注解就不用写get、set、toString方法了，前提是要引入lombok依赖
public class People {
    private String name;
    private Integer id;
    private Integer age;
    private Double height;
}

（3）controller层代码

@RestController
@RequestMapping("/people")
public class PeopleController{ 
    @Autowired
    private PeopleService peopleService;
/**
     * 批量添加数据
     * @param people
     * @return int返回形式可以自己定
     */
    @PostMapping("/addList")
    public int addList(@RequestBody List<People> people){
        return peopleService.addList(people);
    }
}

（4）service层代码

/**
 * @author xxx
 * @version $Id: PeopleService.java, v 0.1 2022-07-11 14:51
 */
public interface PeopleService {
    int addList(List<People> people);
}

/**
* @author xxx
* @version $Id: PeopleServiceImpl.java, v 0.1 2022-07-11 14:52
*/
@Service
public class PeopleServiceImpl implements PeopleService {
    @Resource
    private PeopleMapper peopleMapper;

    @Override
    public int addList(List<People> people) {
        return peopleMapper.addList(people);
    }
}

（5）Mapper层代码

/**
 * @author xxx
 * @version $Id: PeopleMapper.xml.java, v 0.1 2022-07-11 14:54
 */
@Mapper
public interface PeopleMapper {
    
    int addList(@Param("list") List<People> people);
}

（6）SQL语句

 <insert id="addList">
        insert into people(id,name,age,height)
        values
        <foreach collection="list" item="item" open="(" separator="),(" close=")">
            #{item.id},#{item.name},#{item.age},#{item.height}
        </foreach>
    </insert>

（7）postman自测(注意入参json格式)

[{
        "id": 10,
        "name": "张三",
        "age": "22",
        "height": "185"

    },{
        "id": 9,
        "name": "李四",
        "age": "24",
        "height": "188"

    }]