Mybatis动态SQL
1、环境搭建
SQL:
CREATE TABLE `blog` (
`id` VARCHAR ( 50 ) NOT NULL COMMENT '博客id',
`title` VARCHAR ( 100 ) NOT NULL COMMENT '博客标题',
`author` VARCHAR ( 30 ) NOT NULL COMMENT '博客作者',
`creat_time` datetime NOT NULL COMMENT '创建时间',
`views` INT ( 30 ) NOT NULL COMMENT '浏览量'
) ENGINE = INNODB DEFAULT CHARSET = utf8
pojo:
package com.xu.pojo;
import lombok.Data;
import java.util.Date;
@Data
public class Blog {
private String id;
private String title;
private String author;
private Date creatTime;
private int views;
}
utils:
//用UUID当做id
package com.xu.utils;
import java.util.UUID;
public class GetUUID {
public static String getUUId(){
return UUID.randomUUID().toString().replaceAll("-","");
}
}
//获得Sqlsession
package com.xu.utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
//SqlSession 完全包含了面向数据库执行 SQL 命令所需的所有方法。你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句。
//SqlSessionFactoryBuilder--->SqlSessionFactory--->sqlSession
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
//获得sqlSessionFactory对象，通过它来获得sqlSession
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getsqlSession(){
//获得SqlSession
return sqlSessionFactory.openSession(true);//开启事务的自动提交
}
}
dao：
-
BlogMapper：
package com.xu.dao; import com.xu.pojo.Blog; import java.util.List; import java.util.Map; public interface BlogMapper { int addBolg(Blog blog); List<Blog> BlogList(Map map); } -
BlogMapper.xml：
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.xu.dao.BlogMapper"> <insert id="addBolg" parameterType="com.xu.pojo.Blog"> insert into mybatis.blog(id, title, author, creat_time, views) VALUES (#{id},#{title},#{author},#{creatTime},#{views}) </insert> <select id="BlogList" parameterType="map" resultType="com.xu.pojo.Blog"> select * from mybatis.blog <where> <if test="author !=null"> author = #{author} </if> <if test="title != null"> and title = #{title} </if> </where> </select> </mapper>
插入数据：
@Test
public void addBolg(){
Blog blog = new Blog();
blog.setId(GetUUID.getUUId());
blog.setTitle("Mybatis如此简单");
blog.setAuthor("龙傲天");
blog.setCreatTime(new Date());
blog.setViews(9999);
SqlSession sqlSession = MybatisUtils.getsqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
mapper.addBolg(blog);
blog.setId(GetUUID.getUUId());
blog.setTitle("Sping如此简单");
mapper.addBolg(blog);
blog.setId(GetUUID.getUUId());
blog.setTitle("SpingMVC如此简单");
mapper.addBolg(blog);
blog.setId(GetUUID.getUUId());
blog.setTitle("SpingIOC如此简单");
mapper.addBolg(blog);
sqlSession.close();
}
测试：
@Test
public void BlogList(){
SqlSession sqlSession = MybatisUtils.getsqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
//map.put("author","龙傲天");
map.put("title","Mybatis如此简单");
List<Blog> blogs = mapper.BlogList(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
结果：
==> Preparing: select * from mybatis.blog WHERE title = ?
==> Parameters: Mybatis如此简单(String)
<== Columns: id, title, author, creat_time, views
<== Row: 80c47860984a4bb88376752850c4a8a5, Mybatis如此简单, 龙傲天, 2020-02-27 16:38:05.0, 9999
<== Total: 1
Blog(id=80c47860984a4bb88376752850c4a8a5, title=Mybatis如此简单, author=龙傲天, creatTime=Thu Feb 27 16:38:05 CST 2020, views=9999)
2、用法
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
使用JDBC时，比如做一个搜索功能。避免不了的要使用SQL拼接，没有参数的时候查全部，有参数就按照参数查。还有考虑拼接SQL产生的问题，这样非常的麻烦，在Mybatis中用几个标签就解决了
if
<select id="BlogList" parameterType="map" resultType="com.xu.pojo.Blog">
select * from mybatis.blog where
<if test="author !=null">
author = #{author}
</if>
<if test="title != null">
and title = #{title}
</if>
</select>
<!--如果传进来的参数符合要求test的条件，mybatis就会把它自动的拼接上去。有个问题下面会说-->
choose
<select id="BlogList" parameterType="map" resultType="com.xu.pojo.Blog">
select * from mybatis.blog where
<choose>
<when test="author !=null">
author = #{author}
</when>
<when test="title !=null">
author = #{author}
</when>
<otherwise>
author = #{author}
</otherwise>
</choose>
</select>
<!--choose类似我们Java中的Switch语句-->
foreach
<select id="BlogList2" parameterType="map" resultType="com.xu.pojo.Blog">
select * from mybatis.blog where id in
<foreach item="item" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
</select>
<!--你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象传递给 foreach 作为集合参数。当使用可迭代对象或者数组时，index 是当前迭代的次数，item 的值是本次迭代获取的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值-->
trim (where, set)
<select id="BlogList" parameterType="map" resultType="com.xu.pojo.Blog">
select * from mybatis.blog
<trim prefix="where" prefixOverrides="AND | OR">
<if test="author !=null">
author = #{author}
</if>
<if test="title != null">
and title = #{title}
</if>
</trim>
</select>
<!--用来定制一些标签的功能的，where和set就够用了-->
上面if的问题，如果第一个条件不满足的话，sql就变成下面这样了：
select * from mybatis.blog where and title = #{title}
这样sql就出错了，炸裂。我们可以定制个where标签，trim的作用就是这个。where标签就是这样实现的，变成下面这样：
<select id="BlogList" parameterType="map" resultType="com.xu.pojo.Blog">
select * from mybatis.blog
<where>
<if test="author !=null">
author = #{author}
</if>
<if test="title != null">
and title = #{title}
</if>
</where>
</select>
<trim prefix="where" prefixOverrides="AND | OR"></trim>
where 标签就相当于sql后面的where，所以使用where标签后就不用写where了，它还有其它的功能，像上面的例子。如果第一个条件不满足，第二个条件满足但是有and，where标签很只能的会帮你去掉 ！
（前置where关键字 ，若最后一个“if”没有匹配上而前面的匹配上，SQL 语句的最后就会有AND 和 OR 遗留导致sql崩溃 where会只能的去掉）
set 标签会动态前置 SET 关键字，同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。（因为用的是“if”元素，若最后一个“if”没有匹配上而前面的匹配上，SQL 语句的最后就会有一个逗号遗留导致sql崩溃）
<select id="BlogList" parameterType="map" resultType="com.xu.pojo.Blog">
select * from mybatis.blog
<trim prefix="where" prefixOverrides="AND | OR">
<if test="author !=null">
author = #{author}
</if>
<if test="title != null">
and title = #{title}
</if>
</trim>
</select>
===================================================================
<trim prefix="SET" suffixOverrides=",">
...
</trim>
prefix="where" ：前置的关键字
prefixOverrides="AND | OR" ：前面需要去掉的东西
suffixOverrides="," ：后面要去掉的东西
3、SQL片段
<sql id="xxx">
<if test="author !=null">
author = #{author}
</if>
<if test="title != null">
and title = #{title}
</if>
</sql>
<select id="BlogList" parameterType="map" resultType="com.xu.pojo.Blog">
select * from mybatis.blog
<where>
<include refid="xxx"></include>
</where>
</select>
可能我们的程序中好多的地方用到了这个判断条件，我们可以用<sql></sql>标签提取出来
用<include></include>来引用提取的sql片段！