表blog
实体类
（使用了Lombok）
package com.zy.pojo;
?
import lombok.Data;
?
import java.util.Date;
?
@Data
public class Blog {
?
private String id;
private String title;
private String author;
private Date createTime;
private int views;
?
}
jdbc.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/testmybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8
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核心配置文件-->
<configuration>
<properties resource="jdbc.properties"/>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--是否开启自动驼峰命名规则(camel case)映射-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
?
<typeAliases>
<!-- 方法1、定义一个alias别名，缺点在于需要一个实体类分别指定
<typeAlias type="com.zy.pojo.User" alias="user" />-->
<!-- 方法2、也可以使用package来给某个包下面的所有实体类自动创建别名，
自动创建的别名规则是类的类名并将首字母改为小写 -->
<package name="com.zy.pojo"/>
</typeAliases>
?
<!--配置环境-->
<environments default="mysql">
<!--有多个环境时，通过修改default="id"来实现选择-->
?
<environment id="mysql">
<!--JDBC事务管理-->
<transactionManager type="JDBC"/>
<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>
?
<!--每一个Mapper.XML都需要Mybatis核心配置文件中注册-->
<!--如果文件放在resources下，则这么写-->
<mappers>
<mapper resource="mapper/BlogMapper.xml"></mapper>
</mappers>
?
</configuration>
二、< where >
当查询时，select * from xxx where id=#{id}
如果出现多条件时，比如 select * from xxx where id=#{id} and name=#{name}
但是当 id 或者 name 未知的情况下，就没有办法实现查询
所以使用动态SQL中的 < where > 进行查询
< if >
BlogMapper
package com.zy.mapper;
import com.zy.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
//查询博客
List<Blog> queryBlogIF(Map map);
}
BlogMapper.xml：
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
<if test="views != null">
and views = #{views}
</if>
</where>
</select>
测试类
@Test
public void queryBlogIF() {
?
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
?
HashMap map = new HashMap();
map.put("title","45325");
map.put("author","efg");
List<Blog> blogs = blogMapper.queryBlogIF(map);
?
for (Blog blog : blogs) {
System.out.println(blog);
}
?
}
结果
如果不添加任何元素进入map
@Test
public void queryBlogIF() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
?
HashMap map = new HashMap();
List<Blog> blogs = blogMapper.queryBlogIF(map);
?
for (Blog blog : blogs) {
System.out.println(blog);
}
?
}
结果
< choose >
BlogMapper
package com.zy.mapper;
?
import com.zy.pojo.Blog;
?
import java.util.List;
import java.util.Map;
?
public interface BlogMapper {
?
List<Blog> queryBlogChoose(Map map);
?
}
BlogMapper.xml
<select id="queryBlogChoose" parameterType="map" resultType="blog">
select * from blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
测试类
@Test
public void queryBlogChoose() {
?
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
?
HashMap map = new HashMap();
map.put("title","45325");
map.put("author","efg");
map.put("views","322");
List<Blog> blogs = blogMapper.queryBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
?
}
结果
存在多个成立的条件
比如：这个sql中，不使用choose的话会查出3条记录
但只取choose中的第一个
使用 < trim >代替 < where > + SQL片段使用
BlogMapper.xml
<sql id="sql-title-author-views">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
AND author = #{author}
</if>
<if test="views != null">
AND views = #{views}
</if>
</sql>
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog
<trim prefix="where" prefixOverrides="AND">
<include refid="sql-title-author-views"></include>
</trim>
</select>
< set >
BlogMapper
package com.zy.mapper;
?
import com.zy.pojo.Blog;
?
import java.util.List;
import java.util.Map;
?
public interface BlogMapper {
?
//更新博客
int 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>
<if test="views != null">
views = #{views},
</if>
</set>
where id = #{id}
</update>
测试类
@Test
public void queryBlogSet() {
?
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
?
HashMap map = new HashMap();
map.put("title","6546");
map.put("author","fasfd");
map.put("views","6243");
map.put("id","d4303d0f-43f2-422e-8dbf-9a61ad872ff9");
?
blogMapper.updateBlog(map);
?
List<Blog> blogs = blogMapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
?
}
原本的数据库
更新后
使用 < trim >代替 < set >
BlogMapper.xml
<update id="updateBlogSet" parameterType="map">
update blog
<trim prefix="set" suffixOverrides=",">
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author},
</if>
<if test="views != null">
views = #{views},
</if>
</trim>
where id = #{id}
</update>
SQL片段
有时候，可以将一些功能相同的部分抽出，方便使用
抽取例子：
抽取前
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
<if test="views != null">
and views = #{views}
</if>
</where>
</select>
抽取后
<sql id="if-title-author-views">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
<if test="views != null">
and views = #{views}
</if>
</sql>
?
<!--where标签 if-->
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog
<where>
<include refid="if-title-author-views"></include>
</where>
</select>
测试
依旧能正常查出数据
注意点
1.最好基于单表定义SQL片段，尽量不要太复杂
2.不要存在 < where >
< ForEach >
对集合进行遍历使用 ForEach
对 blog表 的 id 修改一下
BlogMapper
package com.zy.mapper;
?
import com.zy.pojo.Blog;
?
import java.util.List;
import java.util.Map;
?
public interface BlogMapper {
?
//查询1-2-3号记录的博客
List<Blog> queryBlogForeach(Map map);
?
}
BlogMapper.xml
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from blog
<trim prefix="where" prefixOverrides="AND">
<foreach collection="ids" item="id" open="AND (" close=")" separator="or">
id = #{id}
</foreach>
</trim>
</select>
测试类
@Test
public void queryBlogForEach(){
?
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
?
HashMap map = new HashMap();
?
ArrayList<Integer> ids = new ArrayList<Integer>();
?
map.put("ids",ids);
?
List<Blog> blogs = blogMapper.queryBlogForeach(map);
?
for (Blog blog : blogs) {
System.out.println(blog);
}
?
sqlSession.close();
}
结果
通过给ArrayList添加元素来实现想要的元素查询
比如想查 id 为1，2，3的
@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);
ids.add(3);
?
map.put("ids",ids);
?
List<Blog> blogs = blogMapper.queryBlogForeach(map);
?
for (Blog blog : blogs) {
System.out.println(blog);
}
?
sqlSession.close();
}
结果