mybatis常用技巧

qq_26264237

已于 2024-01-16 18:03:42 修改

阅读量1.9k

点赞数 2

文章标签： java

于 2021-06-29 14:55:15 首次发布

本文链接：https://blog.youkuaiyun.com/qq_26264237/article/details/118334388

版权

list的for循环

mybatis-plus

	Page page = new Page<Map<String,Object>>(pageIndex, pageSize);
	IPage<Map<String, Object>> page = this.baseMapper.selectMyPage(page, paramMap);

	// ------

	@Select({"<script>", 
		"select a.* from t_customer a where a.TABLE_ID in",
		"<foreach item='item' collection='tableIdList' separator=',' open='(' close=')' >",
		" #{item, jdbcType=VARCHAR}",
		"</foreach>",
		"</script>"})
	List<Customer > findByTableIdList(@Param("tableIdList")List<String> tableIdList);

分页

	paramMap.put("cust_name", "%公司");
	paramMap.put("clue_level_code ", "6");
	Page page = new Page<Map<String,Object>>(pageIndex, pageSize);
	IPage<Map<String, Object>> page = this.baseMapper.selectMyPage(page, paramMap);

	// ------
	
	@Select({"<script>",  
		" select * from t_customer ",
		" where 1=1 ",
		" <if test=\"param.cust_name != null and param.cust_name != '' \"> and cust_name like #{param.cust_name} </if> ",
		" <if test=\"param.cust_code != null and param.cust_code != '' \"> and cust__code = #{param.cust_code} </if> ",
		"</script>"})
    IPage<Map<String, Object>> selectMyPage(IPage<Map<String, Object>> page, @Param("param")Map<String, Object> param);

insert

void insertMapObject(@Param("param")Map<String, Object> param);

	<insert id="insertMapObject" parameterType="Map">
		insert into t_customer (
			<if test="param.ID != null and param.ID != '' ">ID </if>
			<if test="param.CUST_ID != null and param.CUST_ID != '' ">, CUST_ID </if>
			<if test="param.CUST_NAME != null and param.CUST_NAME != '' ">, CUST_NAME </if>
			) values (
			<if test="param.ID != null and param.ID != '' ">#{param[${'ID'}]} </if>
			<if test="param.CUST_ID != null and param.CUST_ID != '' ">, #{param[${'CUST_ID'}]} </if>
			<if test="param.CUST_NAME != null and param.CUST_NAME != '' ">, #{param[${'CUST_NAME'}]} </if>
			)
	</insert>

写法二
动态变量map参数，
缺点：必须确保key就是字段名

	@Select({"<script>",
		" insert into t_customer ",
		" <foreach collection='param.keys' item='key' open='(' close=')' separator=',' >",
		" 	${key}",
		" </foreach>",
		" values",
		" <foreach collection='param.keys' item='key' open='(' close=')' separator=',' >",
		" 	#{param[${key}]}",
		" </foreach>",
		"</script>"})
	void insertMapObject(@Param("param")Map<String, Object> param);

update

void updateMapObject(@Param("param")Map<String, Object> param, @Param("id")String id);

	<update id="updateMapObject" parameterType="Map">
		update t_customer 
		set id = #{id}
		<if test="param.ID != null and param.ID != '' ">, ID = #{param.ID} </if>
		<if test="param.CUST_ID != null and param.CUST_ID != '' ">, CUST_ID = #{param.CUST_ID} </if>
		<if test="param.CUST_NAME != null and param.CUST_NAME != '' ">, CUST_NAME = #{param.CUST_NAME} </if>
		where id = #{id} 
	</update>

写法二
动态变量map参数，
缺点：必须确保key就是字段名

	@Select({"<script>",
		" update t_customer ",
		" set",
		" <foreach collection='param.keys' item='key' open='' close='' separator=',' >",
		" 	${key} = #{param[${key}]}",
		" </foreach>",
		" where id = #{id}",
	"</script>"})
	void updateMapObject(@Param("param")Map<String, Object> param, @Param("id")String id);

#{} 预处理

#{}可以防止sql注入

${} 字符串替换

批量提交

mybatis plus实用工具类

TableInfoHelper

// 实体类反射表辅助类
com.baomidou.mybatisplus.core.metadata.TableInfoHelper

// 获取实体映射表信息
public static TableInfo getTableInfo(Class<?> clazz)

// 获取所有实体映射表信息
public static List<TableInfo> getTableInfos()

    public boolean saveOrUpdate(T entity) {
        if (null != entity) {
            Class<?> cls = entity.getClass();
            TableInfo tableInfo = TableInfoHelper.getTableInfo(cls);
            Assert.notNull(tableInfo, "error: can not execute. because can not find cache of TableInfo for entity!");
            String keyProperty = tableInfo.getKeyProperty();
            Assert.notEmpty(keyProperty, "error: can not execute. because can not find column for id from entity!");
            Object idVal = ReflectionKit.getMethodValue(cls, entity, tableInfo.getKeyProperty());
            return StringUtils.checkValNull(idVal) || Objects.isNull(getById((Serializable) idVal)) ? save(entity) : updateById(entity);
        }
        return false;
    }

mapper.xml文件常用标签

select标签

<select id="selectByPage" parameterType="" resultType="" resultMap="" 
	flushCache="" useCache="">
	select * from t_table where id = #{param.id}
</select>

parameterType：参数类型，可选属性。
MyBatis 可以通过 TypeHandler推断出具体传入语句的参数，默认值为 unset
resultType：期望返回的数据类型的类的全限定名。
resultMap：结果集映射，不可以和 resultType 同时使用。一般如果返回数据类型是常用的类型
比如 String Map List 的时候，可以使用 resultType
如果返回的是简单 POJO 类的时候，也可以直接使用 resultType
如果是复杂的映射或者连级查询的时候就需要使用 resultMap
flushCache ：将其设置为 true，任何时候只要语句被调用，都会导致本地缓存和二级缓存都会被清空，默认值：false。
useCache：将其设置为 true，将会导致本条语句的结果被二级缓存，默认值：对 select 元素为 true。

List<Map<String, Object>> selectByPage(Page<Map<String, Object>> page, @Param("param")Map<String, Object> param);

<select id="selectByPage" resultType="Map">
select * from t_table t1 where 1=1
<if test="param.id !=null and param.id !=''">and t1.ID=#{param.id}</if>
</select>

insert标签

update标签

delete标签

if标签

choose标签

where标签

where标签会除去多余的 and 和 or

	<select id="selectByPage" resultType="Map">
		select * from t_table
		<where>
			<if test="param.isEnable !=null and param.isEnable !=''"> and IS_ENABLE=#{param.isEnable}</if>
			<if test="param.updateControlId !=null and param.updateControlId !=''"> and UPDATE_CONTROL_ID=#{param.updateControlId}</if>
		</where>
	</select>

set标签

set标签会除去多余的逗号（, ）

	<update id="update">
		upate t_table
		<set>
			<if test="param.isEnable !=null and param.isEnable !=''"> IS_ENABLE=#{param.isEnable},</if>
			<if test="param.updateControlId !=null and param.updateControlId !=''">UPDATE_CONTROL_ID=#{param.updateControlId},</if>
		</set>
		where id = #{param.id}
	</update>

trim标签

去掉前缀and

	<select id="selectByPage" resultType="Map">
		select * from t_table
		<trim prefix="where" prefixOverrides="and">
			<if test="param.isEnable !=null and param.isEnable !=''"> and IS_ENABLE=#{param.isEnable}</if>
			<if test="param.updateControlId !=null and param.updateControlId !=''"> and UPDATE_CONTROL_ID=#{param.updateControlId}</if>
		</trim>
	</select>

去掉后缀逗号,

	<insert id="insert">
		insert into t_table
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="param.isEnable !=null">IS_ENABLE,</if>
			<if test="param.updateControlId !=null">UPDATE_CONTROL_ID,</if>
		</trim>
		<trim prefix="values(" suffix=")" suffixOverrides=",">
			<if test="param.isEnable !=null">#{param.isEnable},</if>
			<if test="param.updateControlId !=null">#{param.updateControlId},</if>
		</trim>
	</insert>

foreach

	<delete id="insert">
		delete t_table
		where id in
		<foreach collection="param.ids" index="i" item="id" open="(" separator="," close=")">
		#{id}
		</foreach>
	</delete>

mybatis QueryWrapper

		QueryWrapper<AccMaterial> queryWrapper = new QueryWrapper<AccMaterial>();
		queryWrapper.lambda().eq(AccMaterial::getMaterialName, mapParam.get("materialName"));
		int count = this.count(queryWrapper);

		QueryWrapper<User > queryWrapper = new QueryWrapper<User >();
		queryWrapper.lambda().eq(User::getId, "1");
		queryWrapper.and(qw->
			qw.isNotNull("SDP_USER_ID").or().eq("SDP_USER_ID", mapParam.get("sdpUserId"))
		);

		User user= this.getOne(new LambdaQueryWrapper<User>()
				.eq(User::getId, "1")
				.eq(User::getName, "2")
				.eq(User::getGender, "3")
		);

LambdaQueryWrapper<User> queryWrapper = Wrappers.lambdaQuery();  
  
queryWrapper.eq(User::getName, "指定名称");  
queryWrapper.ne(User::getAge, "指定年龄");  // 这里要注意，年龄不能直接使用字符串 "指定年龄"，应使用实际年龄数字  
queryWrapper.like(User::getEmail, "指定邮箱");  
queryWrapper.inSql(User::getAddress, "address1", "address2", "address3");  
queryWrapper.between(User::getCreateTime, "2022-01-01", "2022-12-31");  
queryWrapper.notBetween(User::getCreateTime, "2021-01-01", "2021-12-31");  
queryWrapper.likeRight(User::getCreateTime, "2023");  // 这个方法可能不适用，因为 create_time 是日期类型，而不是字符串类型  
queryWrapper.or()  
    .like(User::getAddress, "指定地址1")  
    .like(User::getAddress, "指定地址2");  
queryWrapper.notIn(User::getAddress, "address4", "address5");  
queryWrapper.likeLeft(User::getCreateTime, "2024");

SELECT * FROM user   
WHERE name = '指定名称'   
AND age <> 指定年龄   
AND email LIKE '%指定邮箱%'   
AND address IN ('address1', 'address2', 'address3')   
AND create_time BETWEEN '2022-01-01' AND '2022-12-31'   
AND create_time NOT BETWEEN '2021-01-01' AND '2021-12-31'   
AND create_time LIKE '2023%'   
AND (address LIKE '%指定地址1%'    OR address LIKE '%指定地址2%'   )
AND address NOT IN ('address4', 'address5')   
AND create_time LIKE '2024%';

mybatis UpdateWrapper

    //修改值
    User user = new User();
    user.setName("黎明");
    user.setGender("1");

    //修改条件s
    UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
    userUpdateWrapper.eq("id", "123");
    
    int update = mapper.update(user, userUpdateWrapper);
// 生成的sql: 
//   * ==> Preparing: UPDATE user SET name= ? , gender= ? WHERE id= ?;
//   * ==> Parameters: 黎明(String), 1(Boolean), 123(String)