动态SQL--MyBatis的强大武器！！！

开发人员在使用JDBC或其他类似的框架进行数据库开发时，通常都要根据需求去手动拼装
SQL,这是一个非常麻烦且痛苦的工作，而MyBatis提供的对SQL语句动态组装的功能，恰能
很好地解决这一麻烦工作。在本篇博客中，我们将对MyBatis框架的动态SQL进行详细讲解。

接下来先小试牛刀一把，先通过案例的展开，在进行元素的分析。

数据库的表格设计：

在Web项目下，创建com.zsj.mapper包，（注意：包名可以随意设置，只要把相关的java类放在相对应的包下）
CustomerMapper.java

package com.zsj.mapper;
import java.util.List;
import com.zsj.po.Customer;
public interface CustomerMapper {

public List<Customer> findCustomerByNameAndJobs(Customer customer) throws Exception;
public List<Customer> findCustomerByNameOrJobs(Customer customer) throws Exception;
public int updateCustomer(Customer customer) throws Exception;
public List<Customer> findCustomerByIds(List<Integer> ids) throws Exception;
public List<Customer> findCustomerByIds2(List<Integer> ids) throws Exception;
public List<Customer> findCustomerByName(Customer customer);
}

CustomerMapper.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">
<mapper namespace="com.zsj.mapper.CustomerMapper">
	<!-- <if>元素使用 -->
	<!-- <select id="findCustomerByNameAndJobs" 
		parameterType="com.zsj.po.Customer" 
		resultType="com.zsj.po.Customer"> 
		select * from t_customer 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> -->
	<!-- <where>元素  -->
 	<!-- <select id="findCustomerByNameAndJobs" parameterType="com.zsj.po.Customer" 
	resultType="com.zsj.po.Customer"> 
		select * from t_customer 
		<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>  -->
	<!-- <where>元素 之定义SQL片段，方便代码重用 -->
 	<!-- <select id="findCustomerByNameAndJobs" 
 		parameterType="com.zsj.po.Customer" 
		resultType="com.zsj.po.Customer"> 
		select * from t_customer 
		<where> 
		<include refid="query_customer_where"></include>
		</where> 
	</select> -->
	<!-- <sql id="query_customer_where">
			<if test="username !=null and username !=''"> 
			and username like concat('%',#{username},'%') 
		</if> 
		<if test="jobs !=null and jobs !=''"> 
			and jobs= #{jobs} 
		</if> 
	</sql>      -->
	<!-- <trim>元素  -->
	 <select id="findCustomerByNameAndJobs" 
		parameterType="com.zsj.po.Customer"
		resultType="com.zsj.po.Customer">
		select * from t_customer
		<trim prefix="where" prefixOverrides="and">
			<if test="username !=null and username !=''">
				and username like concat('%',#{username}, '%')
			</if>
			<if test="jobs !=null and jobs !=''">
				and jobs= #{jobs}
			</if>
		</trim>
	</select> 

	<!--<choose>(<when>、<otherwise>)元素使用 -->
<select id="findCustomerByNameOrJobs" parameterType="com.zsj.po.Customer"
		resultType="com.zsj.po.Customer">
		select * from t_customer 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>
	
	<!-- <set>元素 -->
	<update id="updateCustomer" parameterType="com.zsj.po.Customer">
		update t_customer
		<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>
	
	<!--<foreach>元素使用 -->
	<select id="findCustomerByIds" parameterType="List"
		resultType="com.zsj.po.Customer">
		select * from t_customer where id in
		<foreach collection="list" item="customer_id" index="index" open="(" 
		separator=","  close=")" >
			#{customer_id}
		</foreach>
	</select>
	
	<select id="findCustomerByIds2" parameterType="List"
		resultType="com.zsj.po.Customer">
		select * from t_customer where 
		<foreach collection="list" item="customer_id"  open="("
			 close=")" separator="OR">
			id = #{customer_id}
		</foreach>
	</select>
	
	
	<!--<bind>元素的使用：根据客户名模糊查询客户信息 -->
	<select id="findCustomerByName" 
		parameterType="com.zsj.po.Customer"
		resultType="com.zsj.po.Customer">
		<!--_parameter.getUsername()也可直接写成传入的字段属性名，
		即username -->
		<bind name="pattern_username" 
		value="'%'+_parameter.getUsername()+'%'" />
		select * from t_customer
		where
		username like #{pattern_username}
	</select>
</mapper>```

需要注意的是在调用映射文件中的动态语句时，要注意只允许出现一个id，即id名不能重复，否则在从映射文件执行动态语句时，会报错！！！

核心配置文件 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" />
	<!--1.配置环境 ，默认的环境id为mysql -->
	<environments default="mysql">
		<!--1.2.配置id为mysql的数据库环境 -->
		<environment id="mysql">
			<!-- 使用JDBC的事务管理 -->
			<transactionManager type="JDBC" />
			<!--数据库连接池 -->
			<dataSource type="POOLED">
				<!-- 数据库驱动 -->
				<property name="driver" value="${jdbc.driver}" />
				<!-- 连接数据库的url -->
				<property name="url" value="${jdbc.url}" />
				<!-- 连接数据库的用户名 -->
				<property name="username" value="${jdbc.username}" />
				<!-- 连接数据库的密码 -->
				<property name="password" value="${jdbc.password}" />
			</dataSource>
		</environment>
	</environments>
	<!--2.配置Mapper的位置 -->
	<mappers>
	 <mapper resource="com/zsj/mapper/CustomerMapper.xml" />  
	</mappers>
</configuration>

Customer.java

package com.zsj.po;
public class Customer {
	private Integer id;       // 主键id
	private String username; // 客户名称
	private String jobs;      // 职业
	private String phone;     // 电话
	public Integer getId() {
		return id;}
	public void setId(Integer id) {
		this.id = id;}
	public String getUsername() {
		return username;}
	public void setUsername(String username) {
		this.username = username;}
	public String getJobs() { return jobs;}
	public void setJobs(String jobs) { this.jobs = jobs;}
	public String getPhone() { return phone;}
	public void setPhone(String phone) { this.phone = phone;	}
	@Override
	public String toString() {
		return "Customer [id=" + id + ", username=" + username + 
				       ", jobs=" + jobs + ", phone=" + phone + "]";
	}
}

日志配置文件：

# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.com.itheima=DEBUG
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

动态管理数据库配置文件：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=123456

MybatisTest.java

package com.zsj.test;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.zsj.mapper.CustomerMapper;
import com.zsj.po.Customer;
import com.zsj.utils.MybatisUtils;
public class MybatisTest {
	/*根据客户姓名和职业组合条件查询客户信息列表
	 */
	@Test
	public void findCustomerByNameAndJobsTest() throws Exception{
		// 通过工具类生成SqlSession对象
		SqlSession session = MybatisUtils.getSession();
		// 创建Customer对象，封装需要组合查询的条件
		Customer customer = new Customer();
		customer.setUsername("杨");
		customer.setJobs("大学生");
		// 执行mapper代理对象的的findCustomerByNameAndJobs方法，返回结果集
		CustomerMapper mapper = session.getMapper(CustomerMapper.class);
		List<Customer>customers = mapper.findCustomerByNameAndJobs(customer);
		// 输出查询结果信息
		for (Customer jieguo1 : customers) {
			System.out.println(jieguo1);
		}   session.close();
	}
	/**
	 * 根据客户姓名或职业查询客户信息列表
	 */
	@Test
	public void findCustomerByNameOrJobsTest() throws Exception{
	    SqlSession session = MybatisUtils.getSession();
	    Customer customer = new Customer();
	    //customer.setUsername("朱");
	    //customer.setJobs("大学生");
	    CustomerMapper mapper = session.getMapper(CustomerMapper.class);
		List<Customer>customers = mapper.findCustomerByNameOrJobs(customer);
	    for (Customer customer2 : customers) {
	        System.out.println(customer2);
	    }
	    session.close();
	}

	/**
	 * 更新客户
	 */
	@Test
	public void updateCustomerTest() throws Exception{		
	    // 获取SqlSession
	    SqlSession sqlSession = MybatisUtils.getSession();
	    // 创建Customer对象，并向对象中添加数据
	    Customer customer = new Customer();
	    customer.setId(3);
	    //customer.setUsername("zhangteng");
	    //customer.setJobs("CEO");
	    customer.setPhone("66666");
	    // 执行SqlSession的更新方法，返回的是SQL语句影响的行数
	    CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
	    int rows = mapper.updateCustomer(customer);
	    // 通过返回结果判断更新操作是否执行成功
	    if(rows > 0){
	        System.out.println("zsj先生,您成功修改了"+rows+"条数据！");
	    }else{
	        System.out.println("不好意思,执行修改操作失败！！！");
	    }
	    // 提交事务
	    sqlSession.commit();
	    // 关闭SqlSession
	    sqlSession.close();
	}
	
	/**
	 * 根据客户编号批量查询客户信息
	 * @throws Exception 
	 */
	@Test
	public void findCustomerByIdsTest() throws Exception{
	    SqlSession session = MybatisUtils.getSession();
	    List<Integer> ids=new ArrayList<Integer>();
	    ids.add(1);
	    ids.add(4);
	    ids.add(5);
	    CustomerMapper mapper = session.getMapper(CustomerMapper.class);
		List<Customer>customers = mapper.findCustomerByIds(ids);
	    for (Customer customer1 : customers) {
	        System.out.println(customer1);
	    }
	    session.close();
	}
	@Test
	public void findCustomerByIds2Test() throws Exception{
	    SqlSession session = MybatisUtils.getSession();
	    List<Integer> ids=new ArrayList<Integer>();
	    ids.add(1);
	    ids.add(3);
	    ids.add(5);
	    CustomerMapper mapper = session.getMapper(CustomerMapper.class);
		List<Customer>customers = mapper.findCustomerByIds2(ids);
	    for (Customer customer : customers) {
	        System.out.println(customer);
	    }
	    session.close();
	}

	/**
	 * bind元素的使用：根据客户名模糊查询客户信息 
	 */
	@Test
	public void findCustomerByNameTest(){
	    SqlSession session = MybatisUtils.getSession();
	    Customer customer =new Customer();
	    customer.setUsername("约");
	    CustomerMapper mapper = session.getMapper(CustomerMapper.class);
		List<Customer>customers = mapper.findCustomerByName(customer);
	    for (Customer elements : customers) {
	        System.out.println(elements);
	        System.out.println("模糊查询成功！");
	    }
	    session.close();
	}

}

MybatisUtils.java

package com.zsj.utils;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
/**工具类:创建一个SqlSessionFactory 对象，并且可以通过工具类的getSession()方法获取SqlSession
 */
public class MybatisUtils {
	private static SqlSessionFactory sqlSessionFactory = null;
	// 初始化SqlSessionFactory对象
	static {
		try {
			// 使用MyBatis提供的Resources类加载mybatis的配置文件
			Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
			// 构建sqlSession的工厂
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	// 获取SqlSession对象的静态方法
	public static SqlSession getSession() {
		return sqlSessionFactory.openSession();
	}
}

以上是关于动态SQL语句的案例代码，均位于src目录下。

相关的元素解析如下：

在实际应用中，我们可能会通过多个条件来精确的查询某个数据。例如，要查找某个客户的信息，可以通过姓.名和职业来查找客户，也可以不填写职业直接通过姓名来查找客户，还可以都不填写而查询出所有客户，此时姓名和职业就是非必须条件。

在前两个小节的案例中，映射文件中编写的SQL后面都加入了“where 1=1”的条件，那么到底为什么要这么写呢?如果将where后“1=1”的条件去掉，那么MyBatis所拼接出来的SQL将会如下所示:

select * from t_ _customer where and username like concat(’%’,?, ‘%’)
可以看出上面SQL语句明显存在SQL语法错误，而加入了条件“1=1”后，既保证了where后面的条件成立，又避免了where后面第- -个词是and或者or之类的关键词。不过“where 1=1”这种写法对于初学者来将不容易理解，并且也不够雅观。

以上这些就是动态SQL 所涉及的元素，希望对你理解动态SQL有所帮助！