springboot+mybatis+mysql批量插入传递Li
1、dao层
@Mapper
public interface SchoolInfoMapper extends MyMapper {
/**
* 批量写入驾校信息
* @param list
*/
public Integer schoolBatchWrite(List<SchoolInfo>list);
}
2、servervice层
/**
* @Author particle
* @Description
* @Date: Created in 11:00 2020/11/10
* @Modified by:
*/
public interface SchoolInfoService {
/**
* 批量写入驾校信息
* @param list
*/
public void schoolBatchWrite(List<SchoolInfo>list);
}
3、实现层
/**
* @Author particle
* @Description
* @Date: Created in 11:05 2020/11/10
* @Modified by:
*/
@Service(version = "${ghzn.server.service.version}") //我用的dubbo,不用可忽略
public class SchoolInfoServiceImpl implements SchoolInfoService{
@Autowired
private SchoolInfoMapper schoolInfoMapper;
/**
* 批量写入驾校信息
* @param list
*/
@Override
public void schoolBatchWrite(List<SchoolInfo> list) {
schoolInfoMapper.schoolBatchWrite(list);
}
4、mapper.xml：特别注意这里一定不要有原生的 if（“test= xxx!=null and xxx!=''”）；错误的原因就是来源于这里，如果确实需要加判断，请使用第5点的方式
<insert id="schoolBatchWrite" parameterType="java.util.List">
INSERT INTO drv_school_info(
xh,
jxmc,
jxjc,
jxdm,
jxdz,
lxdz,
lxr,
frdb,
zczj,
jxjb,
kpxcx,
fzjg,
jxzt,
checktor,
cjsj,
gxsj
)VALUES
<foreach collection="list" item="item" separator=",">
(
#{item.xh},
#{item.jxmc},
#{item.jxjc},
#{item.jxdm},
#{item.jxdz},
#{item.lxdz},
#{item.lxr},
#{item.frdb},
#{item.zczj},
#{item.jxjb},
#{item.kpxcx},
#{item.fzjg},
#{item.jxzt},
#{item.checktor},
now(),
now()
)
ON DUPLICATE KEY UPDATE
jxmc =VALUES(jxmc),
jxdm =VALUES(jxdm)
</insert>
5、使用if判断方式：
<insert id="schoolBatchWrite" parameterType="java.util.List">
<foreach collection="list" item="item" separator=",">
INSERT INTO drv_school_info
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="item.xh!=null and item.xh!=''">
xh,
<if test="item.jxmc!=null and item.jxmc!=''">
jxmc,
<if test="item.jxjc!=null and item.jxjc!=''">
jxjc,
<if test="item.jxdm!=null and item.jxdm!=''">
jxdm,
<if test="item.jxdz!=null and item.jxdz!=''">
jxdz,
<if test="item.lxdz!=null and item.lxdz!=''">
lxdz,
<if test="item.lxr!=null and item.lxr!=''">
lxr,
<if test="item.frdb!=null and item.frdb!=''">
frdb,
<if test="item.zczj!=null and item.zczj!=''">
zczj,
<if test="item.jxjb!=null and item.jxjb!=''">
jxjb,
<if test="item.kpxcx!=null and item.kpxcx!=''">
kpxcx,
<if test="item.fzjg!=null and item.fzjg!=''">
fzjg,
<if test="item.jxzt!=null and item.jxzt!=''">
jxzt,
<if test="item.checktor!=null and item.checktor!=''">
checktor,
cjsj,
gxsj
VALUES
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="item.xh!=null and item.xh!=''">
#{item.xh},
<if test="item.jxmc!=null and item.jxmc!=''">
#{item.jxmc},
<if test="item.jxjc!=null and item.jxjc!=''">
#{item.jxjc},
<if test="item.jxdm!=null and item.jxdm!=''">
#{item.jxdm},
<if test="item.jxdz!=null and item.jxdz!=''">
#{item.jxdz},
<if test="item.lxdz!=null and item.lxdz!=''">
#{item.lxdz},
<if test="item.lxr!=null and item.lxr!=''">
#{item.lxr},
<if test="item.frdb!=null and item.frdb!=''">
#{item.frdb},
<if test="item.zczj!=null and item.zczj!=''">
#{item.zczj},
<if test="item.jxjb!=null and item.jxjb!=''">
#{item.jxjb},
<if test="item.kpxcx!=null and item.kpxcx!=''">
#{item.kpxcx},
<if test="item.fzjg!=null and item.fzjg!=''">
#{item.fzjg},
<if test="item.jxzt!=null and item.jxzt!=''">
#{item.jxzt},
<if test="item.checktor!=null and item.checktor!=''">
#{item.checktor},
now(),
now()
</insert>
