海阔天空

手机扫码查看

2020java框架教程之mybatis动态sql

1.if

需求：

1.查询已激活的，并且博客的名字是包含某个查询字符串的记录
2.如果用户没有输入任何查询字符串，那么就显示所有已激活的博客

mapper:

<select id="selectActiveBlogByTitle" parameterType="string" resultMap="blogResultMap">
    select*from blog where state = 'active'
    <if test="value !=null and value !='' ">
        and title like concat('%','${value}','%')
    </if>
</select>

dao:

List<Blog> selectActiveBlogByTitle(String title);

测试：

 

@Test
public void test13(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UsersDao mapper = sqlSession.getMapper(UsersDao.class);
    List<Blog> blogList = mapper.selectActiveBlogByTitle("a");
    List<Blog> blogList = mapper.selectActiveBlogByTitle(null);
    sqlSession.close();
    System.out.println(blogList );
}

2.choose、when、otherwise

需求：
1.查询已激活的
2.如果用户输入了标题的查询关键字，则根据关键字查询
3.否则根据blog的风格样式查询
4.如果什么都没输入，则显示推荐的博客

mapper：

<select id="selectActiveBlogByTitleOrStyle" parameterType="Blog" resultMap="blogResultMap">
    select*from blog where state='active'
    <choose>
        <when test="title !=null and titlt !=''">
            and lower(title) like lower(#{title})
        </when>
        <when test="style != null and style !=''">
            and style = #{style}
        </when>
        <otherwise>
            and featured = true
        </otherwise>
    </choose>
</select>

dao：

List<Blog> selectActiveBlogByTitleOrStyle(Blog blog);

测试：

@Test
public void test14(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UsersDao mapper = sqlSession.getMapper(UsersDao.class);
    Blog blog=new Blog();
    blog.setTitle("%o%");
    blog.setStyle("black");
    List<Blog> blogList = mapper.selectActiveBlogByTitleOrStyle(blog);
    sqlSession.close();
    System.out.println(blogList);
}

3.where

需求：多条件查询，根据状态、标题、是否被推荐

mapper：

<select id="selectBlogByCondition" parameterType="Blog" resultMap="blogResultMap">
    select*from blog
    <where>
        <if test="state != null and state !='' ">
            state=#{state}
        </if>
        <if test="title != null and title !='' ">
            and lower(title) like lower(#{title})
        </if>
        <if test="featured != null">
            and featured = #{featured}
        </if>
    </where>
</select>

dao：

List<Blog> selectBlogByCondition(Blog blog);

测试：

@Test
public void test15(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UsersDao mapper = sqlSession.getMapper(UsersDao.class);
    Blog blog=new Blog();
    blog.setTitle("%o%");
    blog.setStyle("black");
    List<Blog> blogList = mapper.selectBlogByCondition(blog);
    sqlSession.close();
    System.out.println(blogList);
}

4.set

需求：

按需修改，修改指定的列，未指定不修改

mapper：

<update id="updateBlogByCondition" parameterType="Blog">
    update blog
    <set>
        <if test="title !=null">title=#{title},</if>
        <if test="style !=null">style=#{style},</if>
        <if test="authorId !=null">author_Id=#{authorId}</if>
    </set>
    where id=#{id}
</update>

dao：

int updateBlogByCondition(Blog blog);

测试：

@Test
public void test16(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UsersDao mapper = sqlSession.getMapper(UsersDao.class);
    Blog blog=new Blog();
    blog.setId(1);
    blog.setTitle("my blog");
    int i=mapper.updateBlogByCondition(blog);
    sqlSession.commit();
    sqlSession.close();
    System.out.println(blog);
}

5.trim

mapper：

<select id="selectBlogByConditionTrim" parameterType="Blog" resultMap="blogResultMap">
    select*from blog
    <trim prefix="where" prefixOverrides="and / or">
        <if test="state != null and state !='' ">
            state=#{state}
        </if>
        <if test="title != null and title !='' ">
            and lower(title) like lower(#{title})
        </if>
        <if test="featured != null">
            and featured = #{featured}
        </if>
    </trim>
</select>
<update id="updateBlogByConditionTrim" parameterType="Blog">
    update blog
    <trim prefix="sex" suffixOverrides=",">
        <if test="title !=null">title=#{title},</if>
        <if test="style !=null">style=#{style},</if>
        <if test="authorId !=null">author_Id=#{authorId}</if>
    </trim>
    where id=#{id}
</update>

dao：

List<Blog> selectBlogByConditionTrim(Blog blog);
int updateBlogByConditionTrim(Blog blog);

测试：

@Test
public void test17(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UsersDao mapper = sqlSession.getMapper(UsersDao.class);
    Blog blog=new Blog();
    blog.setTitle("%o%");
    blog.setStyle("black");
    List<Blog> blogList = mapper.selectBlogByConditionTrim(blog);
    sqlSession.close();
    System.out.println(blogList);
}
@Test
public void test18(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UsersDao mapper = sqlSession.getMapper(UsersDao.class);
    Blog blog=new Blog();
    blog.setId(1);
    blog.setTitle("my blog");
    int i=mapper.updateBlogByConditionTrim(blog);
    sqlSession.commit();
    sqlSession.close();
    System.out.println(blog);
}

6.foreach批量删除

需求：批量删除

mapper：

<delete id="deleteBlogList" parameterType="list">
    delete from blog where id in
    <foreach collection="list" item="i" open="(" close=")" separator=",">
        #{i}
    </foreach>
</delete>

dao：

int deleteBlogList(List<Integer> ids);

测试：

@Test
public void test19(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UsersDao mapper = sqlSession.getMapper(UsersDao.class);
    List<Integer> ids= Arrays.asList(32,33,34);
    int item = mapper.deleteBlogList(ids);
    sqlSession.commit();
    sqlSession.close();
}

7.SQL片段

mapper：

<!-- sql片段 -->
<sql id="columnBase">
    id,username,password,rolename,identity,mobile
</sql>
<!-- 通过 id 查询用户 2 -->
<select id="selectUserBase" resultType="Users" parameterType="int">
    select
    <include refid="columnBase"></include>
    from users where id = #{id}
</select>

dao：

List<Users> selectUserBase(Integer id);

测试：

@Test
public void test20(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UsersDao mapper = sqlSession.getMapper(UsersDao.class);
    List<Users> users = mapper.selectUserBase(2);
    System.out.println(users);
}