动态SQL
表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>
<properties resource="jdbc.properties"/>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
<setting name="mapUnderscoreToCamelCase" value="true"/>
settings>
?
<typeAliases>
<package name="com.zy.pojo"/>
typeAliases>
?
<environments default="mysql">
?
<environment id="mysql">
<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>
?
<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 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 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 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 queryBlogChoose(Map map);
?
}
BlogMapper.xml
测试类
@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 blogs = blogMapper.queryBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
?
}
结果
存在多个成立的条件
比如：这个sql中，不使用choose的话会查出3条记录
但只取choose中的第一个
使用 < trim >代替 < where > + SQL片段使用
BlogMapper.xml
<if test="title != null"> title = #{title} if > <if test="author != null"> AND author = #{author} if> <if test="views != null"> AND views = #{views} if>
< 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 blog > <if test="author != null"> author = #{author}, if> <if test="views != null"> views = #{views}, if> where id = #{id}<if test="title != null"> title = #{title}, if
测试类
@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 blogs = blogMapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
?
}
原本的数据库
更新后
使用 < trim >代替 < set >
BlogMapper.xml
update blog > <if test="author != null"> author = #{author}, if> <if test="views != null"> views = #{views}, if> where id = #{id}<if test="title != null"> title = #{title}, if
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>
?
<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 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 ids = new ArrayList();
?
map.put("ids",ids);
?
List 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 ids = new ArrayList();
ids.add(1);
ids.add(2);
ids.add(3);
?
map.put("ids",ids);
?
List blogs = blogMapper.queryBlogForeach(map);
?
for (Blog blog : blogs) {
System.out.println(blog);
}
?
sqlSession.close();
}
结果