mybatis常用操作

mybatis常用操作

背景

最近开发了一个管理平台，使用mybatis作为ORM框架，整理了常用的几种较复杂的操作

准备

准备一张表格
应用表：tb_business

   create table 'tb_business'{
    	'id' varchar(50) NOT NULL COMMENT '业务id',
    	'status' tinyint(2) NOT NULL COMMENT '业务状态，0：禁用，1：正常，2：删除 ，默认为1',
    	'desc' varchar(50) NOT NULL COMMENT '业务id',
    	PRIMARY KEY ('id')
    }

映射实体 Business

	@Data
   public class Business{
		private String id;
		private Integer status;
		private String desc;
	}

批量新增

mapper:
 int insertBatch(List<Business> businessList);

xml：
    <insert id = "insertBatch" parameterType = "java.util.List">
    	insert into tb_business (id , status , desc)
    	values
    	<foreach collections = "list" item = "item" index = "index" separator = ",">
    	(#{item.id} , #{item.status } , #{item.desc})
    	</foreach>
    </insert>

注意分隔符separator = “,”

批量更新

略

批量删除

根据多条件字段批量删除

 mapper:
 int deleteBatch(List<Business> businessList);
 
 xml:
<delete id = “deleteBatch” parameterType = "java.util.List">
	delete from tb_business  where
	<foreach collections = "list" item = "item" index = "index" separator = "or">
    	( #{item.status } 
			and
		#{item.desc})
    </foreach>
</delete >

注意分隔符separator = “or”

分页查找

封装一个分页结果对象

@Data
public class  PageResult<T> {
	private int pageNumber;	//当前页码
	private int pageSize;	//每页数据量
	private int totalPage;	//总页数
	private int totalRow;	//总数据条数
	private List<T> content;		//数据内容
}

业务代码

 //map封装查询条件
 Map<String,Object> criteriaMap = new HashMap<>();
 criteriaMap .put("startRow" , startRow - 1);		//注意mysql的分页查询需要减一
 criteriaMap .put("pageSize" , pageSize);
 criteriaMap .put("status" , status);
//查询总条数
int  totalRow = businessMapper.selectTotalRow(criteriaMap);
//查询总数据量
List<Business> businessList = businessMapper.selectPage(criteriaMap);
//设置分页结果
totalPage = (int)Math.ceil((float)totaalRow / size);
PageResult<Business>  businessPageResult = new PageResult<>();
businessPageResult .setPageNumber(startRow);
businessPageResult .setPageSize(pageSize)
businessPageResult .setTotalPage(totalPage )
businessPageResult .setTotalRow(totalRow )
businessPageResult .setContent(businessList )

xml

<select id = "selectTotalRow" parameter = "java.util.Map" resultType = "java.lang.Integer">
select count(1)
   from tb_business
  <where>
	<if test = "status !=null">
		status = #{status}
	</if>
  </where>
</select>

<select id = "selectPage" parameter = "java.util.Map"  resultType = "cn.xxx.Business">
select *
   from tb_business
  <where>
	<if test = "status !=null">
		status = #{status}
	</if>
  </where>
  limit #{startRow} , #{pageSize}
</select>

过滤查找

同分页查询使用map封装查询条件