mybatis学习记录～

最新推荐文章于 2025-09-06 09:33:29 发布

转载最新推荐文章于 2025-09-06 09:33:29 发布 · 50 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/tsing0520/p/10198880.html

文章标签：

#java

mybatis版本

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.6</version>
</dependency>

动态SQL语句

IF标签

<select id="findSelective" parameterType="com.tsing.model.Blog"
        resultMap="BaseResultMap">
        SELECT * FROM blog 
            WHERE `status` = 1
            <if test="title != null and title != '' ">
                and title like #{title}
            </if>
            
            <if test="summary != null ">
                and summary like #{summary}
            </if>
</select>

@Test
    public void testFindSelective() {
        SqlSessionFactory factory = DataBaseTools.getSqlSessionFactory();

        SqlSession session = factory.openSession(true);
        BlogMapper mapper = session.getMapper(BlogMapper.class);
        
        Blog blog = new Blog();
        blog.setTitle("");
        blog.setSummary("");
        mapper.findSelective(blog) ;

    }

日志输出

==> Preparing: SELECT * FROM blog WHERE `status` = 1 and summary like ?

==> Parameters: (String)

CHOOSE标签

<select id="findSelectiveWithChoose" resultType="com.tsing.model.Blog">
        SELECT * FROM blog WHERE `status` = 1
        <choose>
            <when test="title != null">
                AND title like #{title}
            </when>
            <when test="summary != null">
                AND summary like #{summary}
            </when>
            <otherwise>
                AND content =  'tsing'
            </otherwise>
        </choose>
    </select>

@Test
public void testFindSelectiveWithChoose() {
        SqlSessionFactory factory = DataBaseTools.getSqlSessionFactory();

        SqlSession session = factory.openSession(true);
        BlogMapper mapper = session.getMapper(BlogMapper.class);
        
        Blog blog = new Blog();
        
        mapper.findSelectiveWithChoose(blog);

    }

日志输出

==> Preparing: SELECT * FROM blog WHERE `status` = 1 AND content = 'tsing'

==> Parameters:

WHERE标签

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG 
  WHERE 
  <if test="state != null">
    state = #{state}
  </if> 
  <if test="title != null">
    AND title like #{title}
  </if>

</select>

上述的sql语句可能导致以下的问题出现： SELECT * FROM BLOG WHERE AND title like ?

where元素只会在至少有一个子元素的条件返回SQL子句的情况下才会插入where子句。若子句的开头是and或者or,where元素也会将其移除。

<select id="findSelectiveWithWhere" resultType="com.tsing.model.Blog">
        SELECT * FROM blog
        <where>
            <if test="title != null ">
                and title like #{title}
            </if>
            <if test="summary != null ">
                or summary like #{summary}
            </if>
        </where>
</select>

@Test
public void testFindSelectiveWithWhere() {
        SqlSessionFactory factory = DataBaseTools.getSqlSessionFactory();

        SqlSession session = factory.openSession(true);
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        Blog blog = new Blog();
        blog.setSummary("");
        mapper.findSelectiveWithWhere(blog);

    }

日志输出

==> Preparing: SELECT * FROM blog WHERE summary like ?

==> Parameters: (String)

TRIM标签

<select id="findSelectiveWithTrim" resultType="com.tsing.model.Blog">
        SELECT * FROM blog
        <trim prefix="WHERE" prefixOverrides="AND |OR ">
            <if test="title != null ">
                and title like #{title}
            </if>
            <if test="summary != null ">
                or summary like #{summary}
            </if>
        </trim>
</select>

@Test
public void testFindSelectiveWithTrim() {
        SqlSessionFactory factory = DataBaseTools.getSqlSessionFactory();

        SqlSession session = factory.openSession(true);
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        Blog blog = new Blog();
        blog.setTitle("");
        //blog.setSummary("");
        mapper.findSelectiveWithTrim(blog);

    }

日志输出

==> Preparing: SELECT * FROM blog WHERE title like ?

==> Parameters: (String)

SET标签

动态前置SET关键字，同时删除无关的逗号。

<update id="updateIfNecessary">
        update blog
        <set>
            <if test="type != null">type=#{type},</if>
            <if test="status != null">status=#{status},</if>
            <if test="title != null">title=#{title},</if>
            <if test="authorId != null">author_id=#{authorId}</if>
        </set>
        where id=#{id}
</update>

@Test
public void testUpdateIfNecessary() {
        SqlSessionFactory factory = DataBaseTools.getSqlSessionFactory();

        SqlSession session = factory.openSession(true);

        String statement = "com.tsing.mapping.BlogMapper.updateIfNecessary";
        Blog blog = new Blog();
        blog.setType((short) 2);
        blog.setStatus((short) 1);
        blog.setTitle("ss");
        blog.setId(1);
        session.update(statement, blog);
    }

日志输出

==> Preparing: update blog SET type=?, status=?, title=? where id=?

==> Parameters: 2(Short), 1(Short), ss(String), 1(Integer)

与trim标签等价：

<update id="updateIfNecessary2">
        update blog
        <trim prefix="SET" suffixOverrides=",">
            <if test="type != null">type=#{type},</if>
            <if test="status != null">status=#{status},</if>
            <if test="title != null">title=#{title},</if>
            <if test="authorId != null">author_id=#{authorId}</if>
        </trim>
        where id=#{id}
</update>

FOREACH标签

<select id="selectBlogsInStatus" resultMap="BaseResultMap">
        SELECT * FROM BLOG WHERE status in
        <foreach item="item" index="index" collection="list" open="("
            separator="," close=")">
            #{item}
        </foreach>
</select>

@Test
public void testSelectBlogsByMap() {
        SqlSessionFactory factory = DataBaseTools.getSqlSessionFactory();

        SqlSession session = factory.openSession(true);

        String statement = "com.tsing.mapping.BlogMapper.selectBlogsInStatus";
        List<Short> status = new ArrayList<>();
        status.add((short) 2);
        status.add((short) 3);
        status.add((short) 3);
        session.selectList(statement, status);
    }

日志输出

==> Preparing: SELECT * FROM BLOG WHERE status in ( ? , ? , ? )

==> Parameters: 2(Short), 3(Short), 3(Short)

<insert id="saveBlog" parameterType="java.util.Map">
        insert into blog
        <foreach collection="params.keys" item="key" open="(" close=")"
            separator=",">
            ${key}
        </foreach>
        values
        <foreach collection="params.keys" item="key" open="(" close=")"
            separator=",">
            #{params[${key}]}
        </foreach>
</insert>

 public void saveBlog(@Param("params") Map<String, Object> params);

@Test
public void testSaveBlog() {
        SqlSessionFactory factory = DataBaseTools.getSqlSessionFactory();

        SqlSession session = factory.openSession(true);
        BlogMapper mapper = session.getMapper(BlogMapper.class);
        Map<String, Object> map = new HashMap<>();
        map.put("code", "abcdefg");
        map.put("title", "aaa");
        map.put("status", 12);
        map.put("content", "fff");
        map.put("author_id", 12);
        map.put("create_time", new Date());
        mapper.saveBlog(map);

    }

日志输出

==> Preparing: insert into blog ( code , create_time , title , author_id , content , status ) values ( ? , ? , ? , ? , ? , ? )

==> Parameters: abcdefg(String), 2018-12-30 15:01:46.343(Timestamp), aaa(String), 12(Integer), fff(String), 12(Integer)

ognl表达式

<select id="selectBlogsLike" resultMap="BaseResultMap">
        <bind name="pattern" value="'%' + title + '%'" />
        SELECT * FROM BLOG
        WHERE title LIKE #{pattern}
</select>

    /**
     * 从OGNL表达式中创建一个变量并将其绑定到上下文。
     */
    @Test
    public void testSelectBlogsLike() {
        SqlSessionFactory factory = DataBaseTools.getSqlSessionFactory();

        SqlSession session = factory.openSession(true);

        String statement = "com.tsing.mapping.BlogMapper.selectBlogsLike";
        Blog blog = new Blog();

        blog.setTitle("xxx");
        List<Blog> horns = session.selectList(statement, blog);

        logger.info(horns);
    }