- if
- choose (when, otherwise)
- trim (where, set)
- foreach
- SQl片段
测试环境搭建
- 整体结构
- 数据库
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(30) NOT NULL COMMENT '浏览量'
)ENGINE=INNODB DEFAULT CHARSET=utf8
- dao层
BlogMapper接口
public interface BlogMapper {
//添加数据
void addInitBlog(Blog blog);
}
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.wang.dao.BlogMapper">
<insert id="addInitBlog" parameterType="Blog">
insert into blog (id,title,author,create_time,views) values (#{id},#{title},#{author},#{createTime},#{views})
</insert>
</mapper>
- pojo层
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
public Blog() {
}
public Blog(String id, String title, String author, Date createTime, int views) {
this.id = id;
this.title = title;
this.author = author;
this.createTime = createTime;
this.views = views;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public int getViews() {
return views;
}
public void setViews(int views) {
this.views = views;
}
@Override
public String toString() {
return "Blog{" +
"id='" + id + '\'' +
", title='" + title + '\'' +
", author='" + author + '\'' +
", createTime=" + createTime +
", views=" + views +
'}';
}
}
- utils层
IdUtiles
//自动生成ID工具类
public class IdUtils {
public static String getId(){
return UUID.randomUUID().toString().replaceAll("-","");
}
}
MybatisUtils
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try{
String resource= "mybatis-config.xml";
InputStream inputStream= Resources.getResourceAsStream(resource);
sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
}catch (Exception e){
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
//设置为true目的是自动提交事务,不需要在设置commit
return sqlSessionFactory.openSession(true);
}
}
- db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&;useUnicode=true&;characterEncoding=UTF-8&;serverTimezone=UTC
username=root
password=123456
- 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"></properties>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--驼峰命名规则开启-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<typeAliases>
<typeAlias type="com.wang.pojo.Blog" alias="Blog"></typeAlias>
</typeAliases>
<environments default="mytest">
<environment id="mytest">
<transactionManager type="JDBC"></transactionManager>
<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.wang.dao.BlogMapper"/>
</mappers>
</configuration>
动态SQL === IF语句
- 接口中添加
//查询数据
List<Blog> getBlogByIf(Map map);
- BlogMapper.xml
<!--如果传递了title,则按照title查询。title为空,就全部查出来-->
<select id="getBlogByIf" parameterType="map" resultType="Blog">
select * from blog
<where>
<if test="title != null">
and title=#{title}
</if>
</where>
</select>
- 测试类
@Test
public void test2(){
SqlSession sqlSession=MybatisUtils.getSqlSession();
BlogMapper mapper=sqlSession.getMapper(BlogMapper.class);
HashMap map=new HashMap();
map.put("title","Mybatis");
List<Blog> blogs=mapper.getBlogByIf(map);
for(Blog blog:blogs){
System.out.println(blog);
}
sqlSession.close();
}
- 结果
小结:
- where标签
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
- if标签
进行条件选择
choose (when, otherwise)
choose 元素,它有点像 Java 中的 switch 语句。
- 接口中添加
List<Blog> getBlogByChoose(Map map);
BlogMapper.xml
<!--
when标签按顺序执行且只能执行一个,就相当于switch中的case
如果when标签没有一个执行,则执行下面的otherwise标签
-->
<select id="getBlogByChoose" 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>
<when test="views != null">
and views=#{views}
</when>
<otherwise>
and id=#{id}
</otherwise>
</choose>
</where>
</select>
- 测试类
@Test
public void test3(){
SqlSession sqlSession=MybatisUtils.getSqlSession();
BlogMapper mapper=sqlSession.getMapper(BlogMapper.class);
HashMap map=new HashMap();
map.put("title","Mybatis");
map.put("author","QQ星小天才");
List<Blog> blogs=mapper.getBlogByChoose(map);
for(Blog blog:blogs){
System.out.println(blog);
}
sqlSession.close();
}
- 结果
trim (where, set)
set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)
trim可以自定义where 、set等标签的功能
- 接口中
//更新数据
void updateBlog(Map map);
- BlogMapper.xml
<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}
</where>
</update>
- 测试类
@Test
public void test4(){
SqlSession sqlSession=MybatisUtils.getSqlSession();
BlogMapper mapper=sqlSession.getMapper(BlogMapper.class);
HashMap map=new HashMap();
map.put("title","Mybatis");
map.put("author","QQ星");
map.put("id","1fa77ab5989e4341a9f57c952257bd75");
mapper.updateBlog(map);
sqlSession.close();
}
- 结果
trim
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
prefixOverrides 属性会忽略通过管道符分隔的文本序列(注意此例中的空格是必要的)。上述例子会移除所有 prefixOverrides 属性中指定的内容,并且插入 prefix 属性中指定的内容。
foreach
foreach 元素的功能非常强大,它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符,看它多智能!
你可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时,index 是当前迭代的序号,item 的值是本次迭代获取到的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。
- 接口中
List<Blog> getBlogByForeEache(Map map);
- BlogMapper.xml
<select id="getBlogByForeEache" parameterType="map" resultType="Blog">
select * from blog
<where>
<foreach collection="ids" item="id" open="(" separator="or" close=")">
id=#{id}
</foreach>
</where>
</select>
- 测试类
@Test
public void test5(){
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.getBlogByForeEache(map);
for(Blog blog:blogs){
System.out.println(blog);
}
sqlSession.close();
}
- 结果
SQl片段
一个sql片段
<sql id="sql1">
<if test="title != null">
title=#{title},
</if>
<if test="author !=null">
author=#{author}
</if>
</sql>
我们可以使用include引用这个sql片段
<include refid="sql1"></include>