Mybatis动态SQL

摘要

语法介绍：

 1.调用sql片段：

2.foreach 循环 ：

 3.choose-when/otherwise

4.if  

 MyTest：

 BlogMapper.xml:

 mybatis-config.xml:

---

 

语法介绍：

 1.调用sql片段：

<include refid="if-title-author">
</include>
<sql id="if-title-author">

……

</sql>

2.foreach 循环 ：

例如：查询id为1，2，3，4，5同学的信息

<foreach collection="ids" item="id" open="and (" close=")" separator="or">
    id = #{id}
</foreach>

 3.choose-when/otherwise

这个类似与or‘或’只要满足其中一个就会跳出，都不满足执行otherwise

<choose>

        <when test = "title !=null">

                sql

        </when>

        <when test = "author">

                sql

        </when>

        <otherwise>

                sql

        </otherwise>

</choose>

4.if  

相当与and只要满足if语句就会拼接sql

<if test="title != null">

        SQL

</if>

<if test="author != null">

        SQL

</if>

 MyTest：

import com.gao.dao.BlogMapper;
import com.gao.pojo.Blog;
import com.gao.utils.IDutils;
import com.gao.utils.MybatisUtils;
import lombok.Data;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

public class MyTest {
    @Test
    public void addBlogTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        //创建独一无二的UUID
        blog.setId(IDutils.getId());
        blog.setTitle("Mybatis");
        blog.setAuthor("高地方avac");
        blog.setCreateTime(new Date());
        blog.setViews(9999);
        mapper.addBlog(blog);


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

    @Test
    public void queryBlogSet(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map  = new HashMap();
        map.put("title","高");
        map.put("author","高");
        map.put("id","7cf560d1ebd8450eb92966dbc8be307a");

        mapper.updateBlog(map);
        mapper.updateBlog(map);
        sqlSession.close();
    }

    @Test
    public  void  queryBlogForEach(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map = new HashMap();
        ArrayList<Integer> ids = new ArrayList<Integer>();

        ids.add(1);
        ids.add(2);
        ids.add(3);

        map.put("ids",ids);
        List<Blog> blogs = mapper.queryBlogForeach(map);

        for (Blog blog:blogs){
            System.out.println(blog);
        }

        sqlSession.close();
    }
}

 BlogMapper.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace=绑定要给对应的Dao/mapper接口-->
<mapper namespace="com.gao.dao.BlogMapper">
    <!--动态SQL，本质还是SQL语句，只是我们可以在SQL层面，去执行一个逻辑代码-->

    <!--
        有时候，我们可能会将一些功能的部分抽取出来，方便复用
        使用SQL标签抽取公共的部分
        注意事项：
            最好基于单表定义SQL片段
            不要存在where标签
    -->

    <sql id="if-title-author">
        <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </sql>
    <select id="queryBlogIF" parameterType="map" resultType="com.gao.pojo.Blog">
        select * from blog
        --      这样写保证第一个不满足后面满足，拼接出来的sql不会多个and也可以保证，都不满足时多出来where
        <where>
            -- 调用上面的sql
            <include refid="if-title-author">

            </include>
        </where>
    </select>

    <insert id="addBlog" parameterType="com.gao.pojo.Blog">
        insert  into blog(id,title,author,create_time,views)
        values (#{id},#{title},#{author},#{createTime},#{views});
    </insert>

    <select id="queryBlogChoose" parameterType="map" resultType="com.gao.pojo.Blog">
        select * from blog
        <where>
            <choose>
                <when test="title != null">
                    title = #{title}
                </when>
                <when test="author">
                    and author = #{author}
                </when>
                <otherwise>
                    and views = #{views}
                </otherwise>
            </choose>
        </where>
    </select>

    <update id="updateBlog" parameterType="map">
        update blog
--      set语句会
        <set>
            <if test="title != null">
                title = #{title},
            </if>
            <if test="author != null">
                author = #{author},
            </if>
        </set>
        where id = #{id}
    </update>

    <select id="queryBlogForeach" parameterType="map" resultType="com.gao.pojo.Blog">
--     查询编号为1~3的用户
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>
    </select>
    <!--
        Mysql引擎
        InnoDB底层原理
        索引
        索引优化
    -->
</mapper>

 mybatis-config.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <!--引入外部文件-->
    <properties resource="db.properties"/>

    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--是否开启自动驼峰命名规则（camel case）映射-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <environments default="development"><!--mybatis默认环境-->
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <!--绑定接口-->
    <mappers>
        <mapper class="com.gao.dao.BlogMapper"></mapper>

    </mappers>
</configuration>