where标签
当我们拼接动态SQL时,如果一个查询条件都没有,那我们就不需要where子句,而如果有至少一个条件我们就需要where子句。
示例
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG
<where>
state = #{state}
</where>
</select>
if标签
示例:
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG WHERE state = 'ACTIVE'
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
如果我们的state字段也是动态拼接的,那这里就有问题了,比如我三个条件都没有时,拼出来的sql语句就是SELECT * FROM BLOG WHERE显然是无法执行的,这就要用到我们的where标签:
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG
<where>
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</where>
</select>
当一个查询条件都没有拼接时, mybatis会自动将where关键字和拼接多个条件之间的诸如AND、OR这些多余的关键字去掉
choose\when\otherwise标签
有时我们不想应用到所有的条件语句,而只想从中择其一项。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
使用示例:
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG WHERE state = 'ACTIVE'
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
set标签
set 元素会动态前置 SET 关键字,同时也会删掉无关的逗号(如:语句最后的逗号)
使用示例:
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">
username=#{username},
</if>
<if test="password != null">
password=#{password},
</if>
<if test="email != null">
email=#{email},
</if>
<if test="bio != null">
bio=#{bio}
</if>
</set>
where id=#{id}
</update>
trim标签
trim标签常用属性有:
-
prefix: 添加指定前缀
-
prefixOverrides: 删除指定前缀
-
suffixOverrides: 删除指定后缀
示例一:用标签实现标签功能<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>
prefixOverrides的作用是移除字符串开头的内容中所有指定在 prefixOverrides 属性中的内容,并且插入 prefix 属性中指定的内容。
示例二: 用标签实现标签功能
<trim prefix="SET" suffixOverrides=","> ... </trim>
注意这里我们删去的是后缀值,同时添加了前缀值。
foreach
foreach标签用于通过循环的方式动态拼接SQL,使用方法如下:
<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>
- item: 指定在循环中可以用的item属性名称。当循环的对象是List、Set、数组时item是当前迭代的对象的元素值;而当循环对象是Map时,item是key对应的值
- index: 指定在循环中可以用的index属性名称。当循环的对象是List、Set、数组时index是当前迭代的次数;而当循环对象是Map时,index是key
动态SQL示例
jdbc.properties数据源
jdbc.url=jdbc:mysql://localhost:3306/yanfa5
jdbc.driver=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.password=root
jdbc.characterEncoding=utf8
CityMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.CityDao">
<!-- where、if、choose标签示例 -->
<select id="queryCity" resultType="com.lanou3g.mybatis.bean.City">
select * from city
<where>
<if test="id != null">
id = #{id}
</if>
<choose>
<when test="cname != null">
and cname = #{cname}
</when>
<otherwise>
and pid = #{pid}
</otherwise>
</choose>
</where>
</select>
<!-- set标签示例 -->
<update id="updateCity">
update city
<set>
<if test="id != null">
pid = #{pid}
</if>
</set>
where id = #{id}
</update>
<!-- trim标签示例 -->
<update id="updateCityByTrim">
update city
<!-- 删除set pid = #{pid},句首的‘set’和句尾的‘,’
<trim prefix="set" prefixOverrides="set" suffixOverrides=",">
set pid = #{pid},
</trim>
where id = #{id}
</update>
<!-- foreach标签示例 -->
<select id="selectByForEach" resultType="com.lanou3g.mybatis.bean.City">
select * from city where id in
<foreach collection="list" item="city" open="(" separator="," close=")">
#{city.id}
</foreach>
</select>
</mapper>
mybatis.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" />
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/CityMapper.xml" />
</mappers>
</configuration>
对应数据库表的java类
@Getter
@Setter
public class City {
private Integer id;
private String cname;
private Integer pid;
@Override
public String toString() {
return "City{" +
"id=" + id +
", cname='" + cname + '\'' +
", pid=" + pid +
"}\n";
}
}
dao层接口(操作数据库)
public interface CityDao {
//where、if、choose标签示例
List<City> queryCity(City city);
//set标签示例
int updateCity(City city);
//trim标签示例
int updateCityByTrim(City city);
//foreach标签示例
List<City> selectByForEach(List<City> cityList);
}
程序运行入口
public class App {
public static void main(String[] args) throws IOException {
//1、读入配置文件
String confPath = "mybatis.xml";
InputStream is = Resources.getResourceAsStream(confPath);
//2、构建SqlSessionFactory(用于获取sqlSession)
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is,"development");
//3、获取sqlSession对象(用于具体的RUID)
SqlSession sqlSession = sessionFactory.openSession(true);
//4、具体的RUID
CityDao cityDao = sqlSession.getMapper(CityDao.class);
City city = new City();
//where、if、choose标签示例
city.setId(6);
city.setPid(2);
System.out.println(cityDao.queryCity(city));
//set标签示例
city.setId(10);
city.setPid(6);
System.out.println("影响行数:" + cityDao.updateCity(city));
//trim标签示例
city.setId(10);
city.setPid(12);
System.out.println("影响行数:" + cityDao.updateCityByTrim(city));
//foreach标签示例
List<City> cityList = new ArrayList<>();
for(int i = 0;i < 10 ;i++){
City c = new City();
c.setId(i + 1);
cityList.add(c);
}
System.out.println(cityDao.selectByForEach(cityList));
}
}