mybatis动态sql

环境准备

数据库信息采用外部引用的方式：db.properties

#Global logging configuration#
log4j.rootLogger=ERROR,stdout

#mybatis logging configuration#
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

核心配置文件：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"></properties>
  	<!-- 配置环境，默认的环境id为mysql -->
  	<environments default="mysql">
  	<!-- 配置id为mysql的数据库环境 -->
  		<environment id="mysql">
  		<!-- 使用jdbc事务管理 -->
  			<transactionManager type="JDBC"></transactionManager>
  			<!-- 数据库连接池 -->
  			<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>
  	<!-- 配置mapper的环境 -->
  	<mappers>
  		<mapper resource="com/ssm/mapper/UserMapper.xml"/>
  	</mappers>
  </configuration>

创建一个工具类：MybatisUtil.java

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 MybatisUtil {
	private static SqlSessionFactory sqlSessionFactory = null;
	static {
		try {
			InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static SqlSession getSession() {
		return sqlSessionFactory.openSession();
	}
}

POJO类

package com.ssm.po;

public class User {
	private Integer 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 "User [id=" + id + ", username=" + username + ", jobs=" + jobs + ", phone=" + phone + "]";
	}
	
}

映射文件UserMapper.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.ssm.mapper.UserMapper">

	<!-- <where>、<if>元素使用 -->
	<select id="findUserByNameAndJobs" 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},'%')  <!-- concat的作用是将多个字符连接成一个字符串-->
			</if>
			<if test="jobs != null and jobs != ''">
				and jobs = #{jobs}
			</if>
		</where>
	</select>
	
	<!-- <choose>、<when>、<otherwise>元素使用 -->
	<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>
	
	<!-- <set>元素使用 -->
	<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>
				<if test="id !=null and id !=''">
					id =#{id}
				</if>
			</where>
	</update>
	
	<!-- <foreach>元素使用 -->
	<!-- 
	item:配置的是循环中当前的元素
	index:配置的是当前元素在集合中的位置下标
	collection：配置的list是传递过来的参数类型（首字母小写），可以是一个array、list（或collection）、Map集合的键、pojo包装类中的数组或集合类型的属性名等
	open和close：配置的是以什么符号将这些集合元素包装起来
	separator：配置的是各个元素的间隔符
	-->
	<select id="findUserByIds" parameterType="list" resultType="com.ssm.po.User">
		select * from t_user where id in
		<foreach collection="list" item="id" index="index" open="(" separator="," close=")">
			#{id}
		</foreach>
	</select>
	
	<!-- <bind>元素使用 -->
	<!--解决了数据库兼容的问题-->
	<select id="findUserByName2" parameterType="com.ssm.po.User" resultType="com.ssm.po.User">
		<bind name="p_username" value="'%'+_parameter.getUsername()+'%'"/>
		select * from t_user where username like #{p_username}
	</select>
</mapper>

测试

package com.ssm.test;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.ssm.po.User;
import com.ssm.util.MybatisUtil;

public class MybatisTest {
	@Test
	public void findUserByNameAndIdTest() {
		SqlSession sqlSession = MybatisUtil.getSession();
		User user = new User();
		user.setJobs("teacher");
		user.setUsername("zhangsan");
		List<User> list = sqlSession.selectList("com.ssm.mapper.UserMapper.findUserByNameAndJobs", user);
		for (User user2 : list) {
			System.out.println(user2);
		}
		sqlSession.close();
	}
	@Test
	public void findUserByNameOrJobsTest() {
		SqlSession sqlSession = MybatisUtil.getSession();
		User user = new User();
		user.setUsername("zhangsan");
		user.setJobs("teacher");
		List<User> list = sqlSession.selectList("com.ssm.mapper.UserMapper.findUserByNameOrJobs", user);
		for (User user2 : list) {
			System.out.println(user2);
		}
		sqlSession.close();
	}
	@Test
	public void updateUserTest() {
		SqlSession sqlSession = MybatisUtil.getSession();
		User user = new User();
		user.setId(1);
		user.setUsername("zs");
		user.setJobs("worker");
		user.setPhone("123465798");
		int i = sqlSession.update("com.ssm.mapper.UserMapper.updateUser", user);
		if(i>0) {
			System.out.println("修改成功");
		}else {
			System.out.println("修改失败");
		}
		sqlSession.commit();
		sqlSession.close();
	}
	@Test
	public void findUserByIdsTest() {
		SqlSession sqlSession = MybatisUtil.getSession();
		List<Integer> list = new ArrayList<Integer>();
		list.add(1);
		list.add(2);
		list.add(3);
		List<User> list2 = sqlSession.selectList("com.ssm.mapper.UserMapper.findUserByIds", list);
		for (User user2 : list2) {
			System.out.println(user2);
		}
		sqlSession.close();
	}
	
	@Test
	public void findUserByName2Test() {
		SqlSession sqlSession = MybatisUtil.getSession();
		User user = new User();
		user.setUsername("s");
		List<User> list2 = sqlSession.selectList("com.ssm.mapper.UserMapper.findUserByName2", user);
		for (User user2 : list2) {
			System.out.println(user2);
		}
		sqlSession.close();
	}
}

注意

1. 在映射文件中使用set和if元素组合进行update语句动态sql组装时，如果set元素内包含的内容为空，就会出现sql语法错误。所以在使用set元素进行字段信息更新时，要确保传入的更新字段不能都为空。
2. 使用foreach是，最关键、最容易出错的就是collection属性，该属性必须制定的，而且在不同的情况下该属性的值是不一样的，主要由以下3种情况：
   1、如果传入的是单参数且参数类型是一个数组或者list的时候，collection属性值分别为array、list（或collection）
   2、如果传入的参数有多个，就需要把他们封装成一个Map，当然单参数也可以封装成Map集合，这时collection属性值就是Map的键
   3、如果传入的参数是POJO包装类，collection属性值就为该包装类中需要进行遍历的数组或集合的属性名

总结

< if>:判断语句，用于单条件分支判断
< choose>(< when>< otherwise>):用于多条件分支判断
< where>、< trim>、< set>：辅助元素，用于处理一些sql封装、特殊字符的问题
< foreach>：循环语句，常用于in语句等列举条件中
< bind>：从OGNL表达式中创建一个变量，并将其绑定到上下文，常用于模糊查询的SQL中