mybatis动态sql和resultMap关联查询

以你之姓@ 2023-06-14 10:55 3阅读 0赞

## mybatis动态sql ##

###### if和where ######

*  动态sql parameterType的类型必须是 实体类 test 中判断的属性必须是 实体类的属性

<select id="findAllOrBy" parameterType="com.baidu.pojo.TbUser" resultType="com.baidu.pojo.TbUser">
            select * from t_user
    
            <where>
                <if test="null != id">
                    and id=#{id}
                </if>
            </where>
        </select>

mapper 接口方法 及返回值

public interface TbUserMapper {
    
        List<TbUser> findAllOrBy(TbUser id);
    
    }

测试的代码

@Test
        public void findAllOrBy(){
            SqlSession sqlSession = sqlSessionFactory.openSession();
            TbUserMapper mapper = sqlSession.getMapper(TbUserMapper.class);
            TbUser tbUser = new TbUser();
    //        tbUser.setId(1);    //注释 时id 为空
    
            List<TbUser> list = mapper.findAllOrBy(tbUser);
            list.forEach(c-> System.out.println(c));
        }

当 id不等于空 也就是 **null != id** 时 查询出的数据 和 所执行拼接的**sql语句**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw_size_16_color_FFFFFF_t_70]

当 id等于空 判断 **id =null** sql语句不拼接 所执行的**sql 和 查询的数据**如下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw_size_16_color_FFFFFF_t_70 1]

#### foreach sql 拼接 ####

一般用于批量操作

#### 例 1 ####

所需要拼接SQL

> select \* from t\_user WHERE id in(1,2,3)

foreach 中的**参数** 讲解  
– collection ：是传递过来的参数类型（首字母小写 ），它可以是array, list,Map集合的键，POJO包装类中数组或集合的属性名等  
– open ：以什么符号开始  
– close ：以什么符号结束 open和close 作用就是用符号将这些元素包装起来  
– separator ：各个元素的间隔符  
– item ：循环中当前的元素  
– index ： 当前元素在结合位置的下标 ------当前例子没引用此属性

<select id="findByIds" parameterType="com.baidu.pojo.TbUser" resultType="com.baidu.pojo.TbUser">
            select * from t_user
            <where>
                <if test="ids!=null">
    
                    <foreach collection="ids" open="and id in(" close=")" separator="," item="id">
                        #{id}
                    </foreach>
                </if>
            </where>
        </select>

测试结果 sql拼接成功  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw_size_16_color_FFFFFF_t_70 2]

测试代码

@Test
        public void findByIds(){
            SqlSession sqlSession = sqlSessionFactory.openSession();
            TbUserMapper mapper = sqlSession.getMapper(TbUserMapper.class);
            List<Integer> list = new ArrayList<>();
            list.add(1);
            list.add(2);
            list.add(3);
            TbUser tbUser = new TbUser();
            tbUser.setIds(list);
            List<TbUser> tbUsers = mapper.findByIds(tbUser);
            tbUsers.forEach(c-> System.out.println(c));
        }

#### 例2 ####

所要拼接 SQL

> select \* from t\_user WHERE id=? or id=? or id=?

开始拼接

<select id="findByIds2" parameterType="com.baidu.pojo.TbUser" resultType="com.baidu.pojo.TbUser">
            select * from t_user
            <where>
                <if test="ids!=null">
    
                    <foreach collection="ids"  separator="or" item="id">
                        id=#{id}
                    </foreach>
                </if>
            </where>
        </select>

运行结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw_size_16_color_FFFFFF_t_70 3]  
测试代码

@Test
        public void findByIds2(){
            SqlSession sqlSession = sqlSessionFactory.openSession();
            TbUserMapper mapper = sqlSession.getMapper(TbUserMapper.class);
            List<Integer> list = new ArrayList<>();
            list.add(1);
            list.add(2);
            list.add(3);
            TbUser tbUser = new TbUser();
            tbUser.setIds(list);
            List<TbUser> tbUsers = mapper.findByIds2(tbUser);
            tbUsers.forEach(c-> System.out.println(c));
        }

两个例子 执行效果一样，只是拼接的sql方法不同

## sql片段 ##

把 所用sql提取出来 < sql > 标签 id:定义 唯一标识

<sql id="ById">
            <where>
                <if test="null != id">
                    and id=#{id}
                </if>
            </where>
        </sql>

sql 片段的引用 < include refid=“ById”>< /include>

<select id="findAllOrBy" parameterType="com.baidu.pojo.TbUser" resultType="com.baidu.pojo.TbUser">
            select * from t_user
            
            <include refid="ById"></include>
    
        </select>

#### resultMap关联查询 ####

<resultMap id="findAllAndeptRMap" type="com.baidu.pojo.TbUser">
            <id column="id" property="id"></id>
            <result column="name" property="name"></result>
    
            <association property="tbDept" javaType="com.baidu.pojo.TbDept">
                <id column="did" property="did"></id>
                <result column="dname" property="dname"></result>
            </association>
        </resultMap>
        <select id="findAllAndept" resultMap="findAllAndeptRMap">
            SELECT * FROM t_user a, t_dept b where a.did=b.did;
        </select>

TbUser 类中包含了 TbDept类的属性，需要把TbDept封装到TbUser 中

**测试代码**

@Test
        public void findAllAndept(){
            SqlSession sqlSession = sqlSessionFactory.openSession();
            TbUserMapper mapper = sqlSession.getMapper(TbUserMapper.class);
            List<TbUser> allAndept = mapper.findAllAndept();
            allAndept.forEach(c-> System.out.println(c));
        }

**结果**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw_size_16_color_FFFFFF_t_70 4]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191119161709720.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191119162128909.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191119171510203.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191119172304528.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191124200158630.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0MDg2MDYw,size_16,color_FFFFFF,t_70