[Java知识库]mybatis动态sql语句(重点)

(一) 动态sql介绍

1. 动态sql的概念

顾名思义，SQL 是动态拼接成的，根据传入的变量值进行逻辑操作并动态拼接，方便实现多条件下的数据库操作。在业务逻辑复杂，即简单 SQL 无法完成时，需要拼接时就要使用动态SQL。

2. 动态sql解决的问题

动态sql主要解决根据条件判断附加条动态sql主要解决多条件变化查询，实现自动判断记录字段是否需要更新，根据条件判断附加条sql条件，实现批量添加数据、批量修改数据、批量修删除数据等，优化sql语句，提高执行效率。

(二) 构建测试环境

1. 创建maven项目

添加依赖

<dependencies>
  <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.6</version>
  </dependency>
  <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
      <scope>runtime</scope>
  </dependency>
  <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
  </dependency>
  <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
  </dependency>
</dependencies>

创建bean

public class User  implements Serializable {

private Integer id;
private String name;
private String gender;
private Integer age;
private Date birthday;

public User() {}

public User(String name, String gender, Integer age, Date birthday) {
  this.name = name;
  this.gender = gender;
  this.age = age;
  this.birthday = birthday;
}
//生成getter和setter方法
}

创建接口

public interface UserMapper {
}

2.编写框架配置文件和sql映射文件

编写框架配置文件

<?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>
<!-- 配置 mybatis的环境 -->
<environments default="development">
  <!-- 配置环境 -->
  <environment id="development">
      <!-- 配置事务的类型 -->
      <transactionManager type="JDBC"></transactionManager>
      <!-- 配置连接数据库的信息:用的是数据源【连接池】-->
      <dataSource type="POOLED">
          <property name="driver" value="com.mysql.jdbc.Driver"/>
          <property name="url" value="jdbc:mysql://localhost:3306/mybatis001"/>
          <property name="username" value="root"/>
          <property name="password" value="root"/>
      </dataSource>
  </environment>
</environments>
<!--  注册UserDao接品映射文件位置 -->
<mappers>
  <mapper resource="cn/edu/mapper/UserMapper.xml"/>
</mappers>
</configuration>x

sql映射文件

<?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">
<mapper namespace="cn.edu.mapper.UserMapper">
</mapper>

(三) where标签

1.where标签简介

where标签用于代替sql中的where关键字，可以根据条件判断是否附加where关键字。如果where标签中有条件成立就会附加where关键字，如果没有成立的条件就不会附加where关键字. 可以去掉离他最近一个无关的and 或or关键字.where标签的书写格式为<where>添写附加条件</where>
1.where标签可以被解析为where 关键字
2.可以去掉离他最近的一个无用的and

2.where标签使用

1)编写接口方法findByUser

/**
 ? ? * 根据User中的字段进行查询
 ? ? * @return
 ? ? */
 ? ?List<User> getUserByNameAndSex(Map map);

2) 使用where标签进行sql处理

<!--where 标签的使用-->
 <select id="getUserByNameAndSex" parameterType="map" resultType="user">
 ?  select * from user
 ? ?<where>
 ? ? ?  and uname like '%${username}%' and sex=#{usex}
 ? ?</where>
 </select>

3) 测试findByUser方法

@Test
 ?public void demo01(){
 ? ? ? ?SqlSession sqlSession = MybatisUtils.getSqlSession();
 ? ? ? ?UserMapper mapper = sqlSession.getMapper(UserMapper.class);
 ? ? ? ?Map map=new HashMap<String,Object>();
 ? ? ? ?map.put("username","赵");
 ? ? ? ?map.put("usex","男");
 ? ? ? ?List<User> userList = mapper.getUserByNameAndSex(map);
 ? ? ? ?System.out.println(userList.size());
 ? ? ? ?sqlSession.close();
 ?  }
?

(四) if标签标签的应用

1. if标签简介

if标签表示逻辑条件判断，如果条件成立就附加<if></if>之间的sql语句,如果条件不成立就不附加<if></if>之间的sql语句。书写格式为:<if test="表达式">sql语句</if>

2. if标签使用

1)编写接口方法findByUser

/**
 ? ? * 根据User中的字段进行查询
 ? ? * @return
 ? ? */
 ? ?List<User> getUserDy01(Map map);

2) 使用if标签进行sql处理

<!--if  uname ? ? sex nan-->
 <select id="getUserDy01" parameterType="map" resultType="user">
 ?  select * from user
 ? ?<where>
 ? ? ?<if test="uname!=null and uname!=''">
 ? ? ? ? uname like '%${uname}%'
 ? ? ?</if>
 ? ? ?<if test="sex!=null and sex!=''">
 ? ? ? ?  and sex=#{sex}
 ? ? ?</if>
 ? ?</where>
 </select>

3) 测试findByUser方法

@Test
 public void demo02(){
 ? ? SqlSession sqlSession = MybatisUtils.getSqlSession();
 ? ? UserMapper mapper = sqlSession.getMapper(UserMapper.class);
 ? ? Map map=new HashMap<String,Object>();
 ? ? //map.put("uname","赵");
 ? ? //map.put("sex","男");
?
 ? ? List<User> userList = mapper.getUserDy01(map);
 ? ? System.out.println(userList.size());
 ? ? sqlSession.close();
 }
?

(五) set标签的应用

1. set标签简介

set标签用于更新语句中，代替set关键字，可以有效对指定字段进行更新，提升sql的执行效率。，当set标签中有条件成立时就会附加set标签，set标签会去除无关的逗号。set标签中一般嵌套if标签进行使用其格式为
<set>
<if test="name">
name=#{name},
</if>
<if test="age">
age=#{age},
</if>
......
</set>
1.set标签用于更新语句中
2.set标签解析为set关键字
3.set可以去除跟新语句中无用的逗号
4.通常是和if标签一起使用

2. set标签使用

1) 编写接口方法updateUser

/**
 ? ? * 更新user
 ? ? * @param user
 ? ? */
 ? int updateUserById(User user);

2) 使用set标签进行sql处理

<update id="updateUserById" parameterType="user">
 ? ? update user
 ? ? <set>
 ? ? ? ? <if test="uid!=null">
 ? ? ? ? ? uid=#{uid},
 ? ? ? ? </if>
 ? ? ? ? <if test="uname!=null and uname!=''">
 ? ? ? ? ? ? uname=#{uname},
 ? ? ? ? </if>
 ? ? ? ? <if test="sex!=null and sex!=''">
 ? ? ? ? ? ? sex=#{sex},
 ? ? ? ? </if>
 ? ? ? ? <if test="password!=null and password!=''">
 ? ? ? ? ? ? password=#{password},
 ? ? ? ? </if>
 ? ? ? ? <if test="birthday!=null">
 ? ? ? ? ? ? birthday=#{birthday}
 ? ? ? ? </if>
 ? ? </set>
 ? ? where uid=#{uid}
 </update>

3) 测试updateUser方法

 @Test
 public void demo03(){
 ? ? SqlSession sqlSession = MybatisUtils.getSqlSession();
 ? ? UserMapper mapper = sqlSession.getMapper(UserMapper.class);
?
 ? ? User user=new User("","女","",null);
 ? ? user.setUid(6);
?
 ? ? int i=mapper.updateUserById(user);
 ? ? System.out.println(i);
 ? ? sqlSession.close();
 }
通过产生的sql语句可以看出，当set标签中有条件成立时就会附加set关键字，字段为null时该列不会被更新。set可以忽略与sql无关的逗号。

(六) trim标签

1. trim标签简介

trim标签为万能标签，可用于set或where等。prefix表示要附加的前缀关键字，suffix表示要附加的后缀关键字,prefixOverrides表示要忽略前置字符，suffixOverrides表示要忽略后置字符。
格式:
<trim prefix="where" prefixOverrides=",">
 ? ?  <if test="name!=null">
 ? ? ? ?  name=#{name}
 ? ?  </if>
 ? ?  <if test="age!=null">
 ? ? ? ?  age=#{age}
 ? ?  </if>
 ? ?  <if test="name!=null">
 ? ? ? ?  gender=#{gender}
 ? ?  </if>
</trim>

2. trim标签使用

1) 修改where标签和set标签

1)应用于where
<select id="getUserDy01" parameterType="map" resultType="user">
 ? ? select * from user
 ? ? <trim prefix="where" prefixOverrides="and">
 ? ? ? ? <if test="uname!=null and uname!=''">
 ? ? ? ? ? ? and  uname like '%${uname}%'
 ? ? ? ? </if>
 ? ? ? ? <if test="sex!=null and sex!=''">
 ? ? ? ? ? ? and sex=#{sex}
 ? ? ? ? </if>
 ? ? </trim>
 </select>
2)用于set标签 
<update id="updateUserById" parameterType="user">
 ? ? update user
 ? ? <trim prefix="set" suffixOverrides="," suffix="where uid=#{uid}">
 ? ? ? ? <if test="uid!=null">
 ? ? ? ? ? ? uid=#{uid},
 ? ? ? ? </if>
 ? ? ? ? <if test="uname!=null and uname!=''">
 ? ? ? ? ? ? uname=#{uname},
 ? ? ? ? </if>
 ? ? ? ? <if test="sex!=null and sex!=''">
 ? ? ? ? ? ? sex=#{sex},
 ? ? ? ? </if>
 ? ? ? ? <if test="password!=null and password!=''">
 ? ? ? ? ? ? password=#{password},
 ? ? ? ? </if>
 ? ? ? ? <if test="birthday!=null">
 ? ? ? ? ? ? birthday=#{birthday}
 ? ? ? ? </if>
 ? ? </trim>
 </update>

2) 测试方法测试updateUser和findUsersByCondition方法

@Test
 public void demo02(){
 ? ? SqlSession sqlSession = MybatisUtils.getSqlSession();
 ? ? UserMapper mapper = sqlSession.getMapper(UserMapper.class);
 ? ? Map map=new HashMap<String,Object>();
 ? ? map.put("uname","赵");
 ? ? //map.put("sex","男");
?
 ? ? List<User> userList = mapper.getUserDy01(map);
 ? ? System.out.println(userList.size());
 ? ? sqlSession.close();
 }
?
 @Test
 public void demo03(){
 ? ? SqlSession sqlSession = MybatisUtils.getSqlSession();
 ? ? UserMapper mapper = sqlSession.getMapper(UserMapper.class);
?
 ? ? User user=new User("","女","",null);
 ? ? user.setUid(6);
?
 ? ? int i=mapper.updateUserById(user);
 ? ? System.out.println(i);
 ? ? sqlSession.close();
 }

(七) choose标签

1. choose标签简介

choose标签作用条件判断来拼接指定的条件，它和if不太相同，choose似类于java中的switch语句用法，直要有条件成立，其它判断将得不到执行，如果所有条件都不成立则执行otherwise标签中的内容。
格式:
<choose>
　　<when test=条件1>
　　　　执行的代码；
　　</when>
<when test=条件2>
　　　　执行的代码；
　　</when>
　　......
　　<otherwise>
　　 ?  执行的代码；
　　</when>
　　</otherwise>
</choose>
注意：直要有条件成立，其它判断将得不到执行

2. choose标签使用

1) 编写接口方法getInfoByUser

/**
 ? ? * 查询符合条件的所有user对象 
 ? ? * @param user
 ? ? * @return
 ? ? */
 ? ?List<User> getUserDy02(Map map);

2) 使用choose标签进行sql处理

<!--choose:只要一个判断条件成立，其他判断不会执行-->
 <select id="getUserDy02" parameterType="map" resultType="user">
 ? ? select * from user
 ? ? <where>
 ? ? ? ? <choose>
 ? ? ? ? ? ? <when test="uname!=null and uname!=''">
 ? ? ? ? ? ? ? ? uname like '%${uname}%'
 ? ? ? ? ? ? </when>
 ? ? ? ? ? ? <when test="sex!=null and sex!=''">
 ? ? ? ? ? ? ? ? and sex=#{sex}
 ? ? ? ? ? ? </when>
 ? ? ? ? ? ? <otherwise>
 ? ? ? ? ? ? ? ? 1=1
 ? ? ? ? ? ? </otherwise>
 ? ? ? ? </choose>
 ? ? </where>
 </select>

3.测试接口方法getInfoByUser

@Test
 public void demo04(){
 ? ? SqlSession sqlSession = MybatisUtils.getSqlSession();
 ? ? UserMapper mapper = sqlSession.getMapper(UserMapper.class);
 ? ? Map map=new HashMap<String,Object>();
 ? ? //map.put("uname","赵");
 ? ? //map.put("sex","男");
?
 ? ? List<User> userList = mapper.getUserDy02(map);
 ? ? System.out.println(userList.size());
 ? ? sqlSession.close();
 }
?

(八) foreach标签

1. foreach标签简介

foreach标签表示循环，对sql中有重复的部分可以使用此循环来动态拼接sql语句。可以实现批量添加、批量删除、批量更新操作。foreach标签中有很多的属性，请参考下面的Foreach标签属性表。

Foreach标签属性表

2. foreach标签使用

1) 在接口中创建 addBatchUser、updateBatchUser、deleteBatchUser

/**
 ? ? * 批量添加
 ? ? * @param userList
 ? ? */
 ? ?int saveMulti(List<User> userList);
?
 ? ?/**
 ? ? * 批量更新
 ? ? * @param userList
 ? ? */
 ? ?int delMulti(List<Integer> ids);
?
 ? /**
 ? ? * 批量删除
 ? ? * @param ids
 ? ? */
 ? ?int updateMulti(List<User> ulist);

2) 使用forcach标签进行sql处理

<!--批量删除 1,4,6-->
<delete id="delMulti" parameterType="list">
 ?  delete from user where uid in
 ? ? <foreach collection="list" item="userid" separator="," open="(" close=")">
 ? ? ?  #{userid}
 ? ? </foreach>
</delete>
?
 <!--批量添加-->
 <insert id="saveMulti" parameterType="list">
 ?  insert into user values
 ? ? <foreach collection="list" item="user" separator=",">
 ? ? ? ? (null,#{user.uname},#{user.sex},#{user.password},#{user.birthday})
 ? ? </foreach>
 </insert>
?
 <!--批量更新-->
 <update id="updateMulti" parameterType="list">
 ? ?<foreach collection="list" item="user" separator=";">
 ? ? ?  update user set uname=#{user.uname},sex=#{user.sex} where uid=#{user.uid}
 ? ?</foreach>
 </update>

3) 测试

//批量删除 
@Test
 public void demo05(){
 ? ? SqlSession sqlSession = MybatisUtils.getSqlSession();
 ? ? UserMapper mapper = sqlSession.getMapper(UserMapper.class);
?
 ? ? List<Integer> ids=new ArrayList<Integer>();
 ? ? ids.add(1);
 ? ? ids.add(4);
 ? ? ids.add(6);
 ? ? int i = mapper.delMulti(ids);
 ? ? System.out.println(i);
 ? ? sqlSession.close();
 }
?
//批量添加
 @Test
 public void demo06(){
 ? ? SqlSession sqlSession = MybatisUtils.getSqlSession();
 ? ? UserMapper mapper = sqlSession.getMapper(UserMapper.class);
?
 ? ? List<User> ulist=new ArrayList<User>();
 ? ? ulist.add(new User("张","女","aaaa",new Date()));
 ? ? ulist.add(new User("张plus","女","aaaa",new Date()));
 ? ? ulist.add(new User("张++","女","aaaa",new Date()));
 ? ? int i = mapper.saveMulti(ulist);
 ? ? System.out.println(i);
 ? ? sqlSession.close();
 }
?
//批量更新
 @Test
 public void demo08(){
 ? ? SqlSession sqlSession = MybatisUtils.getSqlSession();
 ? ? UserMapper mapper = sqlSession.getMapper(UserMapper.class);
?
 ? ? List<User> ulist=new ArrayList<User>();
 ? ? ulist.add(new User(2,"张d","女","aaaa",new Date()));
 ? ? ulist.add(new User(3,"张plus","女","aaaa",new Date()));
 ? ? ulist.add(new User(5,"张++","女","aaaa",new Date()));
 ? ? int i = mapper.updateMulti(ulist);
 ? ? System.out.println(i);
 ? ? sqlSession.close();
 }
?
注意：mysql本身不支持批量更新，如果需要批量更新时在url中附加allowMultiQueries=true
<property name="url" value="jdbc:mysql:///mybatis002?allowMultiQueries=true"/>

##