一、批量语句
1.1 批量更新
1)mysql
<update id="updateStoreTask" parameterType="Object">
<foreach collection="storeTaskList" item="item" index="index" separator=";">
update t_store_task set
store_sale_task=#{item.storeSaleTask}
where task_id=#{taskId} AND store_no=#{item.storeNo}
</foreach>
</update>
注意:
1.在配置时必须加上allowMultiQueries=true如:
url=“jdbc:mysql://localhost:3306/testDatabase?allowMultiQueries=true”
allowMultiQueries=true时,可以允许一次执行多条sql(通过分号分割)
2.最后编译的结果为
Preparing: update t_store_task set store_sale_task=? where task_id=? AND store_no=? ; update t_store_task set store_sale_task=? where task_id=? AND store_no=?
- oracle
<!--oracle-->
<update id="updateStoreTask" parameterType="Object">
<foreach collection="storeTaskList" item="item" index="index" open="begin" close=";end;" separator=";">
update t_store_task set
store_sale_task=#{item.storeSaleTask}
where task_id=#{taskId} AND store_no=#{item.storeNo}
</foreach>
</update>
@see https://www.cnblogs.com/feixian/p/5960111.html
1.2 批量插入
1.2.1 mysql
<insert id="batchAdd" parameterType="Object">
insert into
t_store_task(task_id,store_no,store_name,store_sale_task)
values
<foreach collection="storeTaskList" item="item" index="index" separator=",">
(#{taskId},#{item.storeNo},#{item.storeName},#{item.storeSaleTask})
</foreach>
</insert>
最后编译的结果为
Preparing: insert into t_store_task(task_id,store_no,store_name,store_sale_task) values (?,?,?,?) , (?,?,?,?)
1.2.2 批量插入并返回主键
<insert id="batchAddReturnPK" parameterType="java.util.List" keyProperty="id" useGeneratedKeys="true">
insert into study_stage
(study_plan_id, stage_name, study_proposal,create_user_id, update_user_id, version)
values
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.studyPlanId}, #{item.stageName}, #{item.studyProposal},#{item.createUserId}, #{item.updateUserId},#{item.version}
)
</foreach>
</insert>
对应的mapper为:
/**
* 批量插入并返回主键
* @param list
*/
void batchAddReturnPK(List<StudyStage> list);
注:
使用时 mapper中的 collection 参数必须为 list,即外部的mapper接口中只接受两种情况:
1、不Param注解,默认值list
2、使用param注解,但注解的value也必须为list
3.mybatis的版本必须在3.3.1以上。官方在这个版本中加入了批量新增返回主键id的功能
4.更新语句或者SaveOrUpdate语句都不支持返回主键,如果有插入或者更新的需求,建议分开进行,对性能也不会有多大影响。
参考文档:https://www.cnblogs.com/abel-he/p/mybatis.html
思考
1.大数据量批量插入mybatis如何优化操作?
- SqlServer 对语句的条数和参数的数量都有限制,分别是 1000 和 2100。
- Mysql 对语句的长度有限制,默认是 4M。
- Mybatis 对动态语句没有数量上的限制。
1.使用labmbda表达式
思路:分批插入,并使用labmbda表达式skip和limit特性。
源码如下:
@Test
public void insert() {
List<TestVO> list = new ArrayList<>();
for (int i = 0; i < 200000; i++) {
list.add(new TestVO(i + "," + i, i + "," + i, i + "," + i, i + "," + i, i + "," + i));
}
int index = list.size() / 10000;
for (int i=0;i<= index;i++){
//stream流表达式,skip表示跳过前i*10000条记录,limit表示读取当前流的前10000条记录
testMapper.testBatchInsert(list.stream().skip(i*10000).limit(10000).collect(Collectors.toList()));
}
}
2.需要自己把集合拆分成较小的集合,可以用guava的List.partition()。
List<List<AuthAppOrg>> partition = Lists.partition(list, 1000);
partition.parallelStream().forEach(item -> {
List<String> ids = item.stream().map(IdEntity::getId).collect(Collectors.toList());
authAppOrgMapper.deleteBatchIds(ids);
//super.removeByIds(ids);
});
1.3 批量筛选
in条可以实现通过id批量查询,批量删除,批量更新
动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历,通常是在构建 IN 条件语句的时候。
<if test="categoryList != null and categoryList.size()!=0">
and produt_pl IN
<foreach collection="categoryList" item="item" index="index" open="(" close=")" separator=",">
#{item.categoryNo}
</foreach>
</if>
编译结果:
and produt_pl IN ( ? , ? )
注意:如果不加categoryList.size()!=0,当list不为null且size为0会报错,需要在在业务中提前判断categoryList.size()是否为0
如果是数组则用length,如:
<if test="driverNos != null and driverNos.length>0">
and tt.driver_no in
<foreach collection="driverNos" item="item" index="index" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
二。插入不成功执行更新
1.oracle
<!--插入不成功,则执行更新-->
<insert id="batchSaveOrUpdate" parameterType="Object">
MERGE INTO
t_store_task T1
USING (
<foreach collection="storeTaskList" item="item" index="index" separator="union">
SELECT #{taskId} task_id,
#{item.storeNo} store_no,
#{item.storeName} store_name,
<choose>
<when test="item.storeSaleTask!=null">
#{item.storeSaleTask} store_sale_task
</when>
<otherwise>
0 store_sale_task
</otherwise>
</choose>
FROM DUAL
</foreach>
) T
ON (T1.task_id = T.task_id AND T1.store_no = T.store_no)
WHEN MATCHED THEN
UPDATE SET T1.store_sale_task=T.store_sale_task
WHEN NOT MATCHED THEN
INSERT
(task_id,store_no,store_name,store_sale_task)
VALUES
(T.task_id,T.store_no,T.store_name,T.store_sale_task)
</insert>
@see http://blog.csdn.net/gjldwz/article/details/17414155
2.mysql
<insert id="bachSaveOrUpdate" parameterType="Object">
insert into
t_goods_info(id,goods_no,category_no,category_name,brand,goods_name,scutcheon_price,transaction_price,promotion_cost,brokerage,promotion_level,promotion_time,selling_points,remark,is_img,is_video,status,create_time)
values
<foreach collection="goodsInfoList" item="item" index="index" separator=",">
(#{item.id},#{item.goodsNo},#{item.categoryNo},#{item.categoryName},#{item.brand},#{item.goodsName},#{item.scutcheonPrice},#{item.transactionPrice},#{item.promotionCost},#{item.brokerage},#{item.promotionLevel},#{item.promotionTime},#{item.sellingPoints},#{item.remark},#{item.isImg},#{item.isVideo},#{item.status},now())
</foreach>
ON DUPLICATE KEY UPDATE
category_name = VALUES(category_name),
brand = VALUES(brand),
goods_name = VALUES(goods_name),
scutcheon_price = VALUES(scutcheon_price),
transaction_price = VALUES(transaction_price),
promotion_cost = VALUES(promotion_cost),
brokerage = VALUES(brokerage),
promotion_level = VALUES(promotion_level),
promotion_time = VALUES(promotion_time),
selling_points = VALUES(selling_points),
remark = VALUES(remark)
</insert>
@see 批量插入记录,遇到重复记录则为自动更新https://blog.csdn.net/rj042/article/details/50560220
非批量
<insert id="saveOrUpdate" parameterType="object">
insert into student_sigup
(`student_id`,`category_id`,`year`,`sigup_type`,`province_id`,`operator_id`)
values(#{studentId}, #{categoryId}, #{year},#{sigupType}, #{provinceId},#{operatorId})
ON DUPLICATE KEY UPDATE
<if test="sigupType != null">
sigup_type=VALUES(sigup_type),
</if>
<if test="provinceId != null">
province_id=VALUES(province_id),
</if>
update_date=now()
</insert>
mybatis 的《<choose》《/when》标签和《if test=“”》标签的区别
1.choose标签是按顺序判断其内部when标签中的test条件出否成立,如果有一个成立,则 choose 结束。
当 choose 中所有 when 的条件都不满则时,则执行 otherwise 中的sql。类似于Java 的 switch 语句,choose 为 switch,when 为 case,otherwise 则为 default。
判断相等条件demo:
<if test="surveyStatus != null">
<choose>
<when test="surveyStatus==1">
<![CDATA[
AND now() < start_date
]]>
</when>
<when test="surveyStatus==3">
<![CDATA[
AND now() > stop_date
]]>
</when>
<when test="surveyStatus==2">
<![CDATA[
AND now() >= start_date and now() <= stop_date
]]>
</when>
</choose>
</if>
2.if-test 不会跳出判断语句
2.1 if-test标签判断语法:
@see http://blog.csdn.net/z69183787/article/details/51589171
用==判断时应写成
而不是
<if test="type=='y'">
and status = 0
</if>
《if test=“”》常见用法
<if test="searchConditon != null and searchConditon != '' ">
AND `position` LIKE CONCAT(CONCAT('%', #{searchConditon}), '%')
</if>
mybatis是使用的OGNL表达式来进行解析的,在OGNL的表达式中,’y’会被解析成字符,因为java是强类型的,char 和 一个string 会导致不等。所以if标签中的sql不会被解析。具体的请参照 OGNL 表达式的语法
mybatis实现一对多关系映射
实体:
public class Task {
private static final long serialVersionUID = 1L;
private Integer id;// 主键
private String branchNo;// 分部代码
private String branchName;// 分部名称
private String taskNo;// 任务代码
private String taskName;// 任务名称
private Double branchTotal;// 任务总计
private String taskType;// 任务类型;阶段性,月度
private String startDate;// 开始日期
private String endDate;// 结束日期
private List<StoreTask> storeTaskList;
public List<StoreTask> getStoreTaskList() {
return storeTaskList;
}
public void setStoreTaskList(List<StoreTask> storeTaskList) {
this.storeTaskList = storeTaskList;
}
public Integer getId() {
return this.id;
}
public void setId(Integer id) {
this.id = id;
}
public String getBranchNo() {
return this.branchNo;
}
public void setBranchNo(String branchNo) {
this.branchNo = branchNo;
}
public String getBranchName() {
return this.branchName;
}
public void setBranchName(String branchName) {
this.branchName = branchName;
}
public String getTaskNo() {
return this.taskNo;
}
public void setTaskNo(String taskNo) {
this.taskNo = taskNo;
}
public String getTaskName() {
return this.taskName;
}
public void setTaskName(String taskName) {
this.taskName = taskName;
}
public Double getBranchTotal() {
return this.branchTotal;
}
public void setBranchTotal(Double branchTotal) {
this.branchTotal = branchTotal;
}
public String getTaskType() {
return this.taskType;
}
public void setTaskType(String taskType) {
this.taskType = taskType;
}
public String getStartDate() {
return this.startDate;
}
public void setStartDate(String startDate) {
this.startDate = startDate;
}
public String getEndDate() {
return this.endDate;
}
public void setEndDate(String endDate) {
this.endDate = endDate;
}
maper.xml
<resultMap id="taskStoreResultMap" type="entity.Task">
<result column="branch_no" property="branchNo" />
<result column="branch_name" property="branchName" />
<result column="task_no" property="taskNo" />
<result column="task_name" property="taskName" />
<result column="branch_total" property="branchTotal" />
<collection property="storeTaskList" ofType="entity.StoreTask">
<result property="storeNo" column="store_no"/>
<result property="storeName" column="store_name"/>
<result property="storeSaleTask" column="store_sale_task"/>
</collection>
</resultMap>
<select id="queryByBranchNoAndTaskNo" resultMap="taskStoreResultMap"
parameterType="Object">
SELECT
t.branch_no,
t.branch_name,
t.task_no,
t.task_name,
t.branch_total,
st.store_no,
st.store_name,
st.store_sale_task
FROM
`t_task` t
LEFT JOIN t_store_task st ON t.id = st.task_id
WHERE
branch_no = 'HN01'
AND task_no = '2';
</select>
Mybatis 处于大于,小于等特殊符号
<![CDATA[
AND t2.shift_start_time <= #{shiftStartTime}
AND t2.shift_end_time >= #{shiftEndTime})
]]>