show-cloud(十四)写复杂sql的例子，使用in

蔚落 2023-06-25 02:10 4阅读 0赞

前面都是通过sql注解的方式来写sql，但今天碰到了个比较复杂的sql，采用sql注解尝试了几次，最终还是以失败告终，最终还是通过xml的方式来解决。  
1、service的代码内容：

@Override
        public Map<String, String> getUserRelDepName(String ids) {
            Map<String,String> mapInfos=new HashMap<>();
            List<Long> sqlStr=CommonUtils.getLongList(ids,BaseEnum.BASE_REL_CHAR.getValue());
            List<SimpleUserRelDep> list=baseMapper.getUserRelDepName(sqlStr);
            for(SimpleUserRelDep simpleUserRelDep:list){
                mapInfos.put(simpleUserRelDep.getUserId()+"",simpleUserRelDep.getDepName());
            }
            return mapInfos;
        }

2、mapper的定义

/**
         *获取用户关联的部门信息
         * @param ids
         * @return
         */
        @SqlParser(filter = true)
        List<SimpleUserRelDep>  getUserRelDepName(List<Long> ids );

3、ManagerOperatorMapper.xml的定义：

<resultMap id="SimpleResultMap" type="org.go.show.users.dto.SimpleUserRelDep">
    		<id column="ID" property="userId" jdbcType="BIGINT"/>
    		<result column="DEP_ID" property="depId" jdbcType="BIGINT"/>
    		<result column="DEP_NAME" property="depName" jdbcType="VARCHAR"/>
    	</resultMap>
    
    	
    	<select id="getUserRelDepName"  resultMap="SimpleResultMap">
    		select user.ID as ID,user.DEP_ID as DEP_ID,dept.`NAME` as DEP_NAME from GS_MANAGER_OPERATOR as user LEFT JOIN GS_MANAGER_DEPARTMENT as dept on user.DEP_ID=dept.ID WHERE user.ID IN
    		<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
    			#{item}
    		</foreach>
    	</select>

4、查询结果临时实体定义

package org.go.show.users.dto;
    
    public class SimpleUserRelDep {
    
        private Long userId;
        //角色中文名
        private Long depId;
        //是否选中
        private String depName;
    
        public Long getUserId() {
            return userId;
        }
    
        public void setUserId(Long userId) {
            this.userId = userId;
        }
    
        public Long getDepId() {
            return depId;
        }
    
        public void setDepId(Long depId) {
            this.depId = depId;
        }
    
        public String getDepName() {
            return depName;
        }
    
        public void setDepName(String depName) {
            this.depName = depName;
        }
    }