数据库设计
CREATE TABLE `blog` (
`id` varchar(50) NOT NULL COMMENT ‘博客id‘,
`title` varchar(100) NOT NULL COMMENT ‘博客标题‘,
`author` varchar(30) NOT NULL COMMENT ‘博客作者‘,
`create_time` datetime NOT NULL COMMENT ‘创建时间‘,
`views` int NOT NULL COMMENT ‘浏览量‘
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mybatis-config.xml
<settings>
<!--开启日志以及自动转换驼峰命名-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
实体类
package com.pbx.pojo;
import java.util.Date;
/**
* @author BruceXu
* @date 2020/11/8
*/
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
}
id工具类
package com.pbx.utils;
import java.util.UUID;
/**
* @author BruceXu
* @date 2020/11/8
*/
public class IDUtils {
public static String getId() {
return UUID.randomUUID().toString().replaceAll("-","");
}
}
BlogMapper接口
package com.pbx.mapper;
import com.pbx.pojo.Blog;
import org.apache.ibatis.annotations.Insert;
/**
* @author BruceXu
* @date 2020/11/8
*/
public interface BlogMapper {
@Insert("insert into blog (id, title, author, create_time, views) values (#{id},#{title},#{author},#{createTime},#{views});")
int addBlog(Blog blog);
}
插入数据
@Test
public void addBlog() {
SqlSession session = MyBatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IDUtils.getId());
blog.setTitle("Mybatis如此简单");
blog.setAuthor("BruceXu");
blog.setCreateTime(new Date());
blog.setViews(9999);
mapper.addBlog(blog);
blog.setId(IDUtils.getId());
blog.setTitle("Java如此简单");
mapper.addBlog(blog);
blog.setId(IDUtils.getId());
blog.setTitle("Spring如此简单");
mapper.addBlog(blog);
blog.setId(IDUtils.getId());
blog.setTitle("微服务如此简单");
mapper.addBlog(blog);
blog.setId(IDUtils.getId());
blog.setTitle("狂神说Java真的好");
mapper.addBlog(blog);
session.commit();
session.close();
}
这个if和高级语言里面的判断语句if是一样的功能
需求
BlogMapper接口
List<Blog> getBlog(Map map);
BlogMapper.xml
<mapper namespace="com.pbx.mapper.BlogMapper">
<select id="getBlog" parameterType="map" resultType="Blog">
select * from blog where 1=1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
</mapper>
测试
@Test
public void getBlog() {
SqlSession session = MyBatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<>();
// map.put("title", "Mybatis如此简单");
// map.put("author", "BruceXu");
List<Blog> blogList = mapper.getBlog(map);
for (Blog blog : blogList) {
System.out.println(blog);
}
session.close();
}
如果说if中的条件有满足的话，则拼接上SQL语句如果没有满足的话，则不进行拼接
choose的作用类似于高级语言中的switch~case语句块
需求：根据author或title来查询博客，有谁查谁，如果都没有就按照views查询
BlogMapper接口
List<Blog> getBlog2(Map map);
BlogMapper.xml
<mapper>
<select id="getBlog2" parameterType="map" resultType="Blog">
select * from blog where 1=1
<choose>
<when test="title != null">
and title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = 9999
</otherwise>
</choose>
</select>
</mapper>
测试
@Test
public void getBlog2() {
SqlSession session = MyBatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<>();
// map.put("title", "Java如此简单");
// map.put("author", "BruceXu");
List<Blog> blogList = mapper.getBlog2(map);
for (Blog blog : blogList) {
System.out.println(blog);
}
session.close();
}
可以看到，如果when中的条件没有一个匹配上，那么拼接上otherwise中的内容。如果when中有多个匹配，那么则只拼接第一个匹配的
如果仔细看上面两个测试环境中使用的SQL语句，都会发现存在这么一点 where 1=1，这个是为了保证在进行拼接的时候不出现这样的错误：select * from blog where and author = ...
那么有没有这样的一种方法，能够自动判断是不是要加上and或者空格等符号以保证SQL语句的正确性？
trim标签，当然，MyBatis也为我们提前封装好了where和set标签
<trim prefix="前缀匹配" prefixOverrides="前缀替换" suffix="后缀匹配" suffixOverrides="后缀替换">...</trim>
where和set示例
List<Blog> getBlog3(Map map);
int updateBlog(Map map);
<mapper>
<select id="getBlog3" 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>
<update id="updateBlog" parameterType="map">
update blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author},
</if>
<if test="view != null">
views = #{view},
</if>
</set>
</update>
</mapper>
@Test
public void getBlog3() {
SqlSession session = MyBatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<>();
// map.put("title", "Mybatis如此简单");
map.put("author", "BruceXu");
List<Blog> blog3 = mapper.getBlog3(map);
for (Blog blog : blog3) {
System.out.println(blog);
}
session.close();
}
@Test
public void updateBlog() {
SqlSession session = MyBatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String, String> map = new HashMap<>();
map.put("view", "6666");
map.put("id", "ccfb770fc1b64dbf8fd7379a564dcd9f");
map.put("title", "狂神Java说的是真的好");
mapper.updateBlog(map);
List<Blog> blog3 = mapper.getBlog3(map);
for (Blog blog : blog3) {
System.out.println(blog);
}
session.commit();
session.close();
}
有些时候，在业务的处理过程中，可能某一部分SQL语句使用比较频繁，所以我们可以将其抽取出来，单独成为一个片段。然后在需要的使用通过 <include>标签导入即可
提取SQL片段
<sql id="if-title-author">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
引用SQL片段
<select id="queryBlogIf" parameterType="map" resultType="blog">
select * from blog
<where>
<!-- 引用 sql 片段，如果refid 指定的不在本文件中，那么需要在前面加上 namespace-->
<include refid="if-title-author" />
<!-- 在这里还可以引用其他的 sql 片段 -->
</where>
</select>
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
foreach 元素的功能非常强大，
提示
原文：https://www.cnblogs.com/primabrucexu/p/13950289.html