动态sql

2019-02-27  本文已影响0人  咸鱼有梦想呀

一、动态sql

mybatis核心对sql语句进行灵活操作,通过表达式进行判断,对sql进行灵活拼接、组装。

if判断

    <select id="findUserByinfo" parameterType="entity.UserQuerVo" resultType="entity.UserCustom">
        SELECT * FROM user
        <!--<where>可以自动去掉条件中的第一个and -->
        <where>
            <if test="userCustom.username != null">
                <if test="userCustom.username != null and userCustom.username != ''">
                    and username = #{userCustom.username}
                </if>
                <if test="userCustom.sex != null and userCustom.sex != ''">
                    and sex like "%${userCustom.sex}%"
                </if>
            </if>
        </where>
    </select>
运行结果

sql片段

<!--动态sql片段
    id:sql片段唯一标识

    基于单表来定义sql片段,这样的话这个sql片段可重用性才高,在sql片段可重用性才高
    在sql片段不要包括where
    -->
    <sql id="qusery_user_where">
        <if test="userCustom.username != null">
            <if test="userCustom.username != null and userCustom.username != ''">
                and username = #{userCustom.username}
            </if>
            <if test="userCustom.sex != null and userCustom.sex != ''">
                and sex like "%${userCustom.sex}%"
            </if>
        </if>
    </sql>


    <select id="findUserByinfo" parameterType="entity.UserQuerVo" resultType="entity.UserCustom">
    SELECT * FROM user
    <where>
        <!--sql片段引用-->
      <include refid="qusery_user_where"></include>
    </where>

foreach
向sql传递数组或List,mybatis使用foreach解析

userMapper.xml

 <!--输入list集合 foreach遍历-->
    <select id="findUserList" parameterType="entity.UserQuerVo" resultType="entity.UserCustom">
        SELECT * FROM user WHERE
        <!--collection:指定输入对象中集合属性
item:每次遍历生成对象
open:开始遍历时拼接串
close:结束遍历时拼接串
separator:遍历两个对象中需要拼接的串
-->
            <foreach collection="ids" item="id" open="(" close=")" separator="or">
                <!--每次遍历需要拼接的串-->
                id = #{id}
            </foreach>
    </select>

UserDaoMapper.java

public UserCustom findUserList(UserQuerVo userQuerVo) throws Exception;

测试

@Test
    public void findUserList() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserDaoMapper userDaoMapper = sqlSession.getMapper(UserDaoMapper.class);
        UserQuerVo userQuerVo = new UserQuerVo();
        List ids = new ArrayList();
        ids.add(1);
        userQuerVo.setIds(ids);
       UserCustom userList =  userDaoMapper.findUserList(userQuerVo);
        System.out.println(userList);
    }
上一篇 下一篇

猜你喜欢

热点阅读