mybatis-动态SQL,SQL标签

 

 

 

 

BlogMapper.java

package dao;

import pojo.Blog;

import java.util.List;
import java.util.Map;

public interface BlogMapper {

    //添加博客
    public int addBlog(Blog blog);

    //查询博客
    public List<Blog> queryBlogIF(Map map);

    public List<Blog> queryBlogChoose(Map map);

    public List<Blog> queryBlogForeach(Map map);

    //更新博客
    public int updateBlog(Map map);
}

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="dao.BlogMapper">




    <!--SQL片段，可多次引用SQL片，实现代码的复用-->
    <sql id="if_title_author">
        <if test="title != null ">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </sql>
    <!--paraeterType传入的参数类型-->
    <!--resultType返回的参数类型-->
    <insert id="addBlog" parameterType="Blog">
        insert into blog values(#{id},#{title},#{author},#{createTime},#{views})
    </insert>

    <select id="queryBlogIF" parameterType="map" resultType="Blog">
        select * from  blog
        <where>
            <include refid="if_title_author"></include>
        </where>

    </select>


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

    </select>

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



    </update>
    <!--
         /*
            <trim>很重要的了啊</trim>
        */
    -->



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

</mapper>

MyTest.java

package test;

import dao.BlogMapper;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.Test;
import pojo.Blog;
import utils.IdUtils;
import utils.MybatisUtils;

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

public class MyTest {

    static Logger logger = Logger.getLogger(MyTest.class); //LogDemo为相关的类

    @Test
    public void test()
    {
        SqlSession sqlSession= MybatisUtils.getSqlSession();
        BlogMapper blogMapper=sqlSession.getMapper(BlogMapper.class);
        Blog blog=new Blog();
        blog.setId(IdUtils.getId());
        blog.setCreateTime(new Date());
        blog.setAuthor("mkz3");
        blog.setTitle("mkz3");
        blog.setViews(0);
        int flag=blogMapper.addBlog(blog);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void queryBlogIF()
    {
        SqlSession sqlSession= MybatisUtils.getSqlSession();
        BlogMapper blogMapper=sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title","md");
        //logger.info("测试");
        map.put("author","mkz1");
        List<Blog> blogList=blogMapper.queryBlogIF(map);
        for (Blog blog : blogList) {
            System.out.println(blog);
        }

        sqlSession.close();
    }
    @Test
    public void queryBlogCHOOSE()
    {
        SqlSession sqlSession= MybatisUtils.getSqlSession();
        BlogMapper blogMapper=sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        //map.put("title","md");
        //logger.info("测试");
        //map.put("author","mkz1");
        map.put("views","0");
        List<Blog> blogList=blogMapper.queryBlogChoose(map);
        for (Blog blog : blogList) {
            System.out.println(blog);
        }

        sqlSession.close();
    }
    @Test
    public void updateBlog()
    {
        SqlSession sqlSession= MybatisUtils.getSqlSession();
        BlogMapper blogMapper=sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("id","ad5bc0db43b343cca7971a0eaa981129");
        //logger.info("测试");
        map.put("title","我是苗可卓");
        //map.put("views","0");
        blogMapper.updateBlog(map);
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void queryBlogForeach()
    {
        SqlSession sqlSession=MybatisUtils.getSqlSession();
        BlogMapper blogMapper=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> blogList=blogMapper.queryBlogForeach(map);
        for (Blog blog : blogList) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

}

 

mybatis官网动态SQL官方文档连接：https://mybatis.org/mybatis-3/zh/dynamic-sql.html