Mybatis基础学习5——SQL标签

最新推荐文章于 2023-02-06 14:37:27 发布

huhanghao

最新推荐文章于 2023-02-06 14:37:27 发布

阅读量233

点赞数

分类专栏：后端开发文章标签： mybatis

本文链接：https://blog.csdn.net/u011486491/article/details/100049190

版权

后端开发专栏收录该内容

32 篇文章 2 订阅

订阅专栏

Mybatis基础学习5——SQL标签

`<if> <else>`标签

    <select id="getUserByUserName" parameterType="com.hhh.spring.demo.bean.User" resultType="com.hhh.spring.demo.bean.User">
        <!-- SELECT * FROM USER WHERE username LIKE #{name} -->
        SELECT * FROM USER
        where 1=1
        <if test="username != null and username != ''">
           and username LIKE '%${username}%'
        </if>
        <if test="sex !=null and sex != ''">
            and sex = '2'
        </if>
    </select>

`<where>` 标签

where 1=1用于动态SQL，这里可用where标签来代替

<where>
  <if test="username != null and username != ''">
  	and username LIKE '%${username}%'
  </if>
  <if test="sex !=null and sex != ''">
  	and sex = '1'
  </if>
</where>

sql片段

在一些公用的sql语句，我们可以将其抽离，在使用的时候通过inclide 进行引入

<sql id="user_sql">
    `username`,
    `birthday`,
    `sex`,
    `address`,
    `uuid2`
</sql>

    <select id="getUserById" parameterType="int" resultType="com.hhh.spring.demo.bean.User">
        SELECT
        <include refid="user_sql"/>
        FROM
        USER
        WHERE id = #{id1}
    </select>

foreach标签

<select id="getUserByIds" parameterType="com.hhh.spring.demo.bean.QueryVo" resultType="com.hhh.spring.demo.bean.User">
    SELECT
    <include refid="user_sql"/>
    FROM
    USER
    <where>
        <!-- foreach 循环标签
            collection：要遍历额集合
            open：循环开始之前输出的内容
            item：设置循环变量
            separator：分隔符
            close：循环结束之后输出内容
        -->
        <foreach collection="ids" open="id IN (" item ="uid" separator="," close=")">
            #{uid}
        </foreach>
    </where>
</select>

// 使用时的代码
 @Override
    public List<User>  getUserByIds(QueryVo vo) {
        SqlSessionFactory sqlSessionFactory = 					  SqlSessionnFactoryUtils.getSqlSessionFactory();
        // 创建SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 获取映射对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        // 执行命令
        List<User>  mUsers = mapper.getUserByIds(vo);
        // 释放对象
        sqlSession.close();

        return mUsers;
    }


 @Test
public void demo1() throws Exception {
   UserDao userDao = new UserDaoImpl();
   QueryVo queryVo = new QueryVo();
   queryVo.setIds(Arrays.asList(1,25,29,30,35));

   List<User> users = userDao.getUserByIds(queryVo);
   System.out.println(users);
}