mybatis的动态sql是十分好用的,而mybatis的强大在于可以省略很多的代码量。其实mybatis的批量添加就是基于普通的添加一条数据的基础上进行的,所以pom.xml不需要再引入别的配置文件,下面直接来上代码。
数据库及表的创建
-- 创建数据库
CREATE DATABASE IF NOT EXISTS shoop DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
-- 创建第一个取数据的表
create table test_a(
id int auto_increment primary key,
createdate date,
username varchar(200)
)
insert into test_a(createdate,username) values('1997-03-14','44'),('1998-03-14','44'),('1991-03-14','44')
-- 创建第二个存数据的表
create table test_b(
id int auto_increment primary key,
createdate date,
username varchar(200)
)
实体类(entity)
public class Test_a {
private Integer id;
//日期时间的格式转换
@DateTimeFormat(pattern = "yyyy-MM-dd")
private Date createdate;
private String username;
//省略getter和setter方法
}
public class Test_b {
private int id;
//日期时间的格式转换
@DateTimeFormat(pattern = "yyyy-MM-dd")
private Date createdate;
private String username;
//省略getter和setter方法
}
接口(mapper)
@Mapper
public interface Test_aMapper {
//查询全部
public List<Test_a> selectAll();
//批量添加
public int insertAll(@Param("test_b") List<Test_b> test_b);
}
myBatis配置文件(xml)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.springboot.mapper.Test_aMapper">
<!-- 查询所有信息 -->
<select id="selectAll" resultType="com.springboot.entity.Test_a">
select id,createdate,username from test_a
</select>
<insert id="insertAll" parameterType="com.springboot.entity.Test_b">
insert into test_b(createdate,username) values
<foreach collection="test_b" item="order" index="index" separator=",">
(#{order.createdate},#{order.username})
</foreach>
</insert>
</mapper>
业务层(service)
public interface ITest_aService {
//查询全部
public List<Test_a> selectAll();
//批量添加
public int insertAll(List<Test_b> test_b);
}
业务实现层(service.impl)
@Service
public class Test_aServiceImpl implements ITest_aService {
@Resource
Test_aMapper mapper;
@Override
public List<Test_a> selectAll() {
return mapper.selectAll();
}
//批量添加
@Override
public int insertAll(List<Test_b> test_b) {
return mapper.insertAll(test_b);
}
}
逻辑控制层(Controller)
@Controller
@RequestMapping("/Test_a")
public class Test_aController {
@Resource
ITest_aService service;
@RequestMapping("/selectAll")
public String selectAll(Model model){
List<Test_a> aList = service.selectAll();
model.addAttribute("aList",aList);
return "/selectAll";
}
//批量添加
@RequestMapping(value = "/insert")
@ResponseBody
public int insert(@RequestBody List<Test_b> test_b){
int test_bList = service.insertAll(test_b);
if(test_bList > 0){
return 1;
}
return 0;
}
}
html
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.com">
<head>
<meta charset="UTF-8">
<title>全部商品</title>
<script src="/js/jquery-1.7.1.min.js"></script>
</head>
<body>
<form id="formId" action="/Test_a/insert" method="post">
<table id="tab">
<tr>
<td>编号</td>
<td>日期</td>
<td>姓名</td>
</tr>
<th:block th:each="test:${aList}">
<tr>
<td class="testId" th:text="${test.id}"></td>
<td><input name="createdate" class="createdate" th:value="${#dates.format(test.createdate,'yyyy-MM-dd')}"></td>
<td><input name="username" class="username" th:value="${test.username}"></td>
</tr>
</th:block>
<tr>
<td>
<input type="button" id="add" value="添加">
</td>
</tr>
</table>
</form>
</body>
<script>
$("#add").click(function () {
var username = new Array();
var i = 0;
$('.username').each(function(){
username[i] = $(this).val();
i = i+1;
});
var createdate = new Array();
var i = 0;
$('.createdate').each(function(){
createdate[i] = $(this).val();
i = i+1;
});
//把测试返回的结果放到外面,这样就不会受for循环的影响
var bol=false;
bol=alert("测试成功")
for (var i=0;i<username.length;i++) {
for (var i=0;i<createdate.length;i++) {
var aa = {username: username[i],createdate:createdate[i]};
var goodsList = new Array();
goodsList.push(aa);
var data = JSON.stringify(goodsList);
$.ajax({
type:'post',
url:'/Test_a/insert',
data:data,
contentType : 'application/json',
success:function (data) {
if(data == 1){
bol=true;
}
}
})
}
}
})
</script>
</html>
这就是mybatis的批量导入,主要注意的有二点,一是mybatis中的xml文件里的动态sql的用法,二是html页面传递值给controller需要的格式。项目源码https://pan.baidu.com/s/1p5_NmrlSc-hGKxHEz_voVg提取码:qqsw