Mybatis——动态SQL环境搭建、if、Choose、set、foreach语句的使用

一.动态SQL环境搭建

1.

@SuppressWarnings("all") //抑制警告，全部都不报错

2.随机生成ID

package com.Utils;

import java.util.UUID;

@SuppressWarnings("all") //抑制警告，全部都不报错
public class IDUtils {
    public static String getId()
    {
        return UUID.randomUUID().toString().replaceAll("-","");

    }
}

3.mybatis-config.xml

    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/> 日志工厂
        <setting name="mapUnderscoreToCamelCase" value="true"/>  使Bean类中的属性与数据库名对应
    </settings>

二、if语句的使用

1、接口类

 //if语句查找
    List<Blog> getblogif(Map map);

2、sql语句 if where

<select id="getblogif" parameterType="map" resultType="Blog">
        select * from blog
        <where>
        <if test="title != null">
            and title=#{title}
        </if>
        <if test="author != null">
            and author=#{author}
        </if>
        </where>
    </select>

注意 select * from blog where 1=1后面不用加“；”号

where只有在有一个子查询的情况下才会加上and或or，否则<where>标签会去掉and或or

3、测试类

    @Test
    public void getblogif(){
        SqlSession sqlSession=MybatisUtils.getSqlSeesion();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map=new HashMap();
        map.put("title","Java");
        List<Blog> blogs=mapper.getblogif(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

主要是哈希函数的使用

三、choose语句

    <select id="getblogChoose" parameterType="map" resultType="Blog">
        select * from Blog
        <where>
            <choose>
                <when test="title !=null">
                    and title=#{title}
                </when>
                <when test="authoe!=null">
                    and author=#{author}
                </when>
                <otherwise>
                    and views=#{views}
                </otherwise>

            </choose>
        </where>
    </select>

 @Test
    public void getblogChoose(){
        SqlSession sqlSession=MybatisUtils.getSqlSeesion();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map=new HashMap();
        map.put("title","Java");
        map.put("author","My");
        map.put("views",9999);
        List<Blog> blogs=mapper.getblogChoose(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

choose仅会执行第一条成功的指令。

四、update table set 语句

    <update id="getblogset" parameterType="map">
        update blog
        <set>
            <if test="title!=null">
                title=#{title},      //后面一定要有逗号
            </if>
            <if test="author!=null">
                author=#{author},
            </if>

        </set>
        where id=#{id}  //放在set标签后面
    </update>

   @Test
    public void getblogSet(){
        SqlSession sqlSession=MybatisUtils.getSqlSeesion();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map=new HashMap();
        map.put("title","Java2");
        map.put("author","My2");
        map.put("id","4fca22d7-70db-4ef3-b04d-ee0d9dfc951d");
        mapper.getblogset(map);
        sqlSession.commit();
        sqlSession.close();
    }

如果有逗号，set会自动去除。

五、foreach语句

select * from blog where 1=1 and (id = #{id} or id=#{id})

<!--foreach-->
    <select id="getForeach" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

 @Test
    public void getblogForeach(){
        SqlSession sqlSession=MybatisUtils.getSqlSeesion();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map=new HashMap();
        ArrayList<Integer> ids=new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);
        map.put("ids",ids);
        List<Blog> blogs = mapper.getForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }