MyBatis介绍2——动态SQL语句

一、优化MyBatis配置

1、外部引用数据库

创建db.properties文件

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/db_mybatis?serverTimezone=UTC
jdbc.username=root
jdbc.password=root

在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" />
    <!-- 配置环境，默认的环境id为mysql -->
	<environments default="mysql">
	    <!-- 配置id为mysql的数据库环境 -->
		<environment id="mysql">
			<!--使用JDBC的事务管理 -->
			<transactionManager type="JDBC"/>
			<!-- 数据库连接池 -->
			<dataSource type="POOLED">
				<property name="driver" value="${jdbc.driver}"/>
				<property name="url" value="${jdbc.url}"/>
				<property name="username" value="${jdbc.username}"/>
				<property name="password" value="${jdbc.password}"/>
			</dataSource>
		</environment>
	</environments>
	<!-- 引入映射配置文件 -->
	<mappers>
		<mapper resource="com/ssm/mapper/UserMapper.xml"/>
	</mappers>
</configuration>

2、创建工具类

创建工具类MybatisUtils.java，封装创建SqlSessionFactory实例对象和创建SqlSession实例对象的过程，实现解耦

package com.ssm.util;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MybatisUtils {
	private static SqlSessionFactory sqlSessionFactory=null;//单例模式创建SqlSessionFactory实例
	static {
		try {
			String resource="mybatis-config.xml";
			InputStream  inputStream = Resources.getResourceAsStream(resource);
			sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
		} catch (IOException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
	}
	
	public static SqlSession getSession() {
		return sqlSessionFactory.openSession();
	}
}

二、动态SQL语句

1、<if>元素

常见判断语句，如果传入的查询条件非空，则进行动态SQL组装

<!-- 根据姓名和职业的组合条件查询用户 (if元素)-->
   <!-- 判断全部条件 -->
   <select id="findUserByNameAndJobs" parameterType="com.ssm.po.User" resultType="com.ssm.po.User">
     select * from t_user where 1=1
         <if test="username!=null and username!=''">
           and username like concat('%',#{username},'%')
         </if>
         <if test="jobs!=null and jobs !=''">
           and jobs=#{jobs}
         </if>
   </select>

	//根据姓名和职业的组合条件查询用户
	public void findUserByNameAndJobsTest() throws Exception{
		//通过工具类生成SqlSession对象
		SqlSession sqlSession=MybatisUtils.getSession();
		//创建User对象，封装需要组合查询的条件
		User user=new User();
		user.setUsername("zhangsan");
		user.setJobs("teacher");
		//执行SqlSession的查询方法，返回结果集
		List<User> users=sqlSession.selectList("com.ssm.mapper.UserMapper.findUserByNameAndJobs",user);
		//输出查询结果
		for(User u:users) {
			System.out.println(u.toString());
		}
		sqlSession.close();
	}

2、<choose>、<when>和<otherwise>元素

若判断结果为真，则只动态组装当前SQL语句，其他判断语句不执行；若判断结果为假，则继续向下判断。

   <!-- 根据姓名或职业的组合条件查询用户(choose元素) -->
   <!-- 第一个when为真则只动态组装第一个when中的SQL片段；否则就继续向下判断 -->
   <select id="findUserByNameOrJobs" parameterType="com.ssm.po.User" resultType="com.ssm.po.User">
     select * from t_user where 1=1
         <choose>
             <when test="username!=null and username!=''">
               and username like concat('%',#{username},'%')
             </when>
             <when test="jobs!=null and jobs !=''">
               and jobs=#{jobs}
             </when>
             <otherwise>
               and phone is not null
             </otherwise>
         </choose>
   </select>

	//根据姓名或职业的组合条件查询用户
	public void findUserByNameOrJobsTest() throws Exception{
		//通过工具类生成SqlSession对象
		SqlSession sqlSession=MybatisUtils.getSession();
		//创建User对象，封装需要组合查询的条件
		User user=new User();
		user.setJobs("doctor");
		//执行SqlSession的查询方法，返回结果集
		List<User> users=sqlSession.selectList("com.ssm.mapper.UserMapper.findUserByNameOrJobs",user);
		//输出查询结果
		for(User u:users) {
			System.out.println(u.toString());
		}
		sqlSession.close();
	}

3、<where>和<trim>元素

用于代替映射文件中的“where 1=1”条件

   <!-- 根据姓名和职业的组合条件查询用户(where元素简化版) -->
   <select id="findUserByNameAndJobs2" parameterType="com.ssm.po.User" resultType="com.ssm.po.User">
     select * from t_user
      <where>
         <if test="username!=null and username!=''">
           and username like concat('%',#{username},'%')
         </if>
         <if test="jobs!=null and jobs !=''">
           and jobs=#{jobs}
         </if>
      </where>
   </select>

4、<set>元素

用于组装update语句，同时消除SQL语句中最后一个多余的逗号

注：使用<set>元素进行字段信息更新时，确保传入的更新字段不能都为空，否则会出现SQL语法错误

   <!-- 更新用户信息 -->
   <update id="updateUser" parameterType="com.ssm.po.User">
     update t_user
       <set>
         <if test="username!=null and username!=''">
           username=#{username},
         </if>
         <if test="jobs!=null and jobs !=''">
           jobs=#{jobs},
         </if>
         <if test="phone!=null and phone !=''">
           phone=#{phone},
         </if>
      </set>
      where id=#{id}
   </update>

5、<foreach>元素

对传入的集合进行遍历以及动态SQL组装
• item:配置的是循环中当前的元素
• index：配置的是当前元素在集合中的位置下标
• collection：配置的是list传过来的参数类型（首字母小写），可以是一个array、list（或collection）、Map集合的键、POJO包装类中的数组或集合类型的属性名等
• open和close：配置的是以什么符号将这些集合元素包装起来
• separator：配置的是各个元素的间隔符

注：
当使用可迭代对象或者数组时，index是当前迭代的次数，item的值是本次迭代获取的元素。
当使用字典（或MapEntry对象集合）时，index是键，item是值

   <!-- 批量查询用户信息 -->
   <select id="findUserByIds" parameterType="List" resultType="com.ssm.po.User">
     select * from t_user where id in
         <foreach item="id" index="index" collection="list" open="(" separator="," close=")">
             #{id}
         </foreach>
   </select>

	//批量查询用户信息
	public void findUserByIdsTest() throws Exception{
		//通过工具类生成SqlSession对象
		SqlSession sqlSession=MybatisUtils.getSession();
		//创建List集合，封闭查询id
		List<Integer>ids=new ArrayList<Integer>();
		ids.add(1);
		ids.add(2);
		//执行SqlSession的查询方法，返回结果集
		List<User> users=sqlSession.selectList("com.ssm.mapper.UserMapper.findUserByIds",ids);
		//输出查询结果
		for(User user:users) {
			System.out.println(user.toString());
		}
		sqlSession.close();
	}

6、<bind>元素

通过OGNL表达式来创建一个上下文变量，不必使用数据库语言就可以完成参数连接
（MySQL数据库中使用concat函数进行字符串拼接，Oracle数据库中使用连接符号“||”进行字符串拼接）

    <!-- 使用bind模糊查询 -->
   <select id="findUserByName2" parameterType="com.ssm.po.User" resultType="com.ssm.po.User">
       <!-- _parameter.getUsername()也可以直接写成传入的字段属性名，即username -->
       <bind name="p_username" value="'%'+_parameter.getUsername()+'%'" />
       select * from t_user
       where username like #{p_username}
   </select>

	//利用bind元素模糊查询
	public void findUserByName2Test() throws Exception{
		//通过工具类生成SqlSession对象
		SqlSession sqlSession=MybatisUtils.getSession();
		User user=new User();
		user.setUsername("s");
		List<User>users=sqlSession.selectList("com.ssm.mapper.UserMapper.findUserByName2",user);
		//输出查询结果
		for(User u:users) {
			System.out.println(u.toString());
		}
		sqlSession.close();
	}