mybatis plus中的${ew.sqlSegment}，${ew.sqlSelect}，${ew.customSqlSegment},${ew.sqlSet}使用

mybatis plus中的 SQL片段

• https://blog.csdn.net/huang6chen6/article/details/121664393

• ${ew.sqlSegment}， SQL片段

  • ${ew.customSqlSegment}, 传统SQL片段。常用

• ${ew.sqlSelect}， SQL选择

• ${ew.sqlSet}使用 SQL设置

Segment
英
/ˈseɡmənt

n.
部分，片段；（市场）细分的部分；（水果或花自然形成的）瓣，（昆虫的）节； 球缺，弓形；
v.
分割，划分；（细胞）分裂

custom
英
/ˈkʌstəm/
n.
风俗，习俗；习惯；光顾，惠顾；<法律>惯例，习惯法；（经常性的）顾客
adj.
定做的，量身设计的

${customSqlSegment}

接口和xml

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

    IPage<SiteCalibrationVo> searchCalibrationSite(
        Page<SiteCalibrationVo> page, 
        @Param("ew") LambdaQueryWrapper queryWrapper);

    List<String> selectDeviceCodeByIds(@Param("ew") QueryWrapper<Device> queryWrapper);

	//BaseMapper中的
    List<T> selectList(@Param("ew") Wrapper<T> queryWrapper);

<select id="searchCalibrationSite" resultMap="siteCalibrationVo">
        SELECT
            *
        FROM
            site
                ${ew.customSqlSegment}
</select>

    <select id="selectDeviceCodeByIds" resultType="java.lang.String">
            select deviceCode from device
           ${ew.customSqlSegment}
    </select>

原理

ew是mapper方法里的@Param(Constants.WRAPPER) Wrapper queryWrapper对象

public interface Constants extends StringPool, Serializable {
    String WRAPPER = "ew";
}

• 首先判断ew.emptyOfWhere是否存在where条件，有的话再拼接上去，

• ew.customSqlSegment是WHERE + sql语句
  没有where的时候加上 == false

• 使用${ew.sqlSegment} 如果是 连表查询且查询条件 是连表的字段 则需在service层拼接查询条件时字段前指定别名

最佳例子1

• mapper.xml

 <select id="tableList" resultType="java.util.LinkedHashMap">
        SELECT
            ${ew.sqlSelect} // 这里拼接select后面的语句
        FROM
            ${table_name} //如果是单表的话，这里可以写死
        ${ew.customSqlSegment}
    </select>

• mapper

IPage<LinkedHashMap<String,Object>> tableList(
    @Param("table_name") String table_name,
	Page page,
    @Param(Constants.WRAPPER) QueryWrapper queryWrapper);

• test

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT, classes = JeecgSystemApplication.class)
public class HuaUserTest {

    @Autowired
    private RouteMapper routeMapper;

    @Test
    public void testQuery() {
    }
}

        QueryWrapper<Route> qr = new QueryWrapper();
        qr.select("*");
        IPage<LinkedHashMap<String, Object>> route = routeMapper.tableList("route", new Page(1, 10), qr);
		//List 长度为10
        List<LinkedHashMap<String, Object>> records = route.getRecords();

        System.out.println(records);

String responseField = "name";
queryWrapper.select(responseField);
// 即 select name ...

最佳例子2

controller

 public String saveAddress(HttpSession session) {
     
        UserVO user1 = (UserVO)session.getAttribute("user");

     
        LambdaQueryWrapper<User> lambdaQueryWrapper = Wrappers
            .<User>lambdaQuery()
            // 需要查询的列，即 ${ew.sqlSelect}
            .select(User::getNickName, User::getUserId) 
            .eq(User::getUserId, user1.getUserId());// 条件
     
     
        User user = this.userMapper
            .selectNickNameAndUserId(lambdaQueryWrapper);
        System.out.println(user);
     
        return null;
    }

mapper

User selectNickNameAndUserId(
    @Param(Constants.WRAPPER) Wrapper<User> queryWrapper);

mapper.xml

<select id="selectNickNameAndUserId" resultType="com.example.demo.entity.User">
    
        select
            <if test="
                ew != null 
                and ew.sqlSelect != null 
                and ew.sqlSelect != ''">
                
                //不为null，不为空的情况下，才拼接
                ${ew.sqlSelect}
            </if>
        from
            user
        	where is_deleted != 1
                
        <if test="ew != null">
            <if test="ew.nonEmptyOfWhere"> //条件为空，拼接个 and。sqlSegment不带wher
                AND
            </if>
            ${ew.sqlSegment}
        </if>
    </select>

 <select id="selectNickNameAndUserId" resultType="com.example.demo.entity.User">
        select
            <if test="
            ew != null 
            and ew.sqlSelect != null 
            and ew.sqlSelect != ''">
                ${ew.sqlSelect }
            </if>
        from
            user
        	${ew.customSqlSegment}
    </select>

${sqlSegment}

SELECT
	* 
FROM
	route 
WHERE
	isEnable = 1 
	AND ( NAME = ?)

        QueryWrapper<Route> qr = new QueryWrapper();
        qr.select("*");
        qr.eq("name", "1504路");
        
      IPage<LinkedHashMap<String, Object>> route = routeMapper.tableList("route", new Page(1, 10), qr);  

    IPage<LinkedHashMap<String, Object>> tableList(
            @Param("table_name") String table_name,
            Page page,
            @Param(Constants.WRAPPER) QueryWrapper queryWrapper);

        <select id="tableList" resultType="java.util.LinkedHashMap">
            SELECT
                ${ew.sqlSelect}
            FROM
                ${table_name}
            where isEnable = 1

            <if test="ew != null">
                <if test="ew.nonEmptyOfWhere">
                    AND
                </if>
                ${ew.sqlSegment}
            </if>
        </select>

使用${ew.sqlSegment} 如果是 联表查询 且查询条件是连表的字段 则需在service层拼接查询条件时 字段前指定别名，而且不能用lambda的查询了

 <select id="selectByRoleId" resultType="com.captain.crewer.mybatis.plus.dto.RolePermsDTO">
        SELECT tp.id,
               tp.perm_name,
               tp.url,
               tr.role_id   as roleId,
               tr.role_name as roleName
        FROM tb_role tr
             LEFT JOIN tb_perm_role tpr ON tr.role_id = tpr.role_id
             LEFT JOIN tb_perm tp ON tpr.perm_id = tp.id ${ew.customSqlSegment}
    </select>

mapper

 List<RolePermsDTO> selectByRoleId(@Param(Constants.WRAPPER) Wrapper<RolePermsDTO> wrapper);

 @Test
    public void test2(){
        //设置表明
        QueryWrapper<RolePermsDTO> wrapper = new QueryWrapper<>();
        wrapper.eq("tr.role_id", 1);
        
        tbPermService.selectByRoleId(wrapper);
    }

${ew.sqlSet}

LambdaUpdateWrapper<User> wrapper = Wrappers.
    <User>lambdaUpdate()
    .set(User::getNickName, "1")
    .eq(User::getUserId, 1);

        this.userMapper.updateUser(wrapper);

int updateUser(@Param(Constants.WRAPPER) 
               Wrapper<User> updateWrapper);

• 要更新的字段

<update id="updateUser">
		update user
		set ${ew.sqlSet}
		where ${ew.sqlSegment}
    </update>