javaEE Mybatis，动态sql，if标签、where标签、set标签、foreach标签，sql片段

UserMapper.xml（实体类的Sql配置文件,动态sql(标签),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">
<!-- 配置原生Sql语句   -->
<mapper namespace="com.xxx.mybatis.mapper.UserMapper"> 

<!-- Sql片段 -->
<sql id="selector">
	select id, username, birthday, sex, address from user
</sql>

	<!-- <if>标签, <where>标签 -->
	<!-- 根据性别和名字查询用户(多个where条件)。  <where>标签可以自动去掉条件前面多余的and (条件后面多余的and		不能去掉,所以and要写在条件前面)   -->
	<select id="selectUserBySexAndUsername" parameterType="User" resultType="User">
	<!-- <include refid="selector"/> -->  <!-- 可以通过include标签引入sql片段,避免书写大量重复的sql -->
	select * from user 
	<where>
	 	<if test="sex != null and sex != ''">  <!-- <if>标签。 如果输入参数user对象的sex属性不为null且不为'' -->
			and sex = #{sex} 
		</if>
		<if test="username != null and username != ''">
			and username = #{username}
		</if>
	</where>
</select>

<!-- <foreach>标签 -->
<!-- 根据多个ID查询。  id in (1,2,3) -->
<select id="selectUserByIds" resultType="User">  <!-- 可以通过包装类属性设置要遍历的集合(或数组)。 parameterType="QueryVo" -->
	<include refid="selector"/>  <!-- 可以通过include标签引入sql片段,避免书写大量重复的sql -->
	<where>
		<!-- id in (1,2,3) -->
		<!-- collection="ids" 可以遍历包装类的ids属性(List类型或数组类型)。 
			collection="list" 表示遍历的是集合。
			collection="array" 表示遍历的是数组 -->
		<foreach collection="list" item="id" separator=","  open="id in ("  close=")">
			#{id}
		</foreach>
	</where>
</select>

<!-- <set>标签 -->
<!-- 修改 -->
<update id="updateCustomerById" parameterType="Customer">
	update customer 
	<set>
		<if test="cust_name != null">
			cust_name = #{cust_name},
		</if>
		<if test="cust_linkman != null">
			cust_linkman = #{cust_linkman},
		</if>  <!-- set标签会自动将最后多余的逗号","去除 -->
	</set>
	<where>
		cust_id = #{cust_id}
	</where>
</update>

QueryVo.java（包装类,封装sql占位符的输入参数）：

package com.xxx.mybatis.pojo;

import java.io.Serializable;
import java.util.List;

// pojo包装对象
public class QueryVo implements Serializable {

private static final long serialVersionUID = 1L;

//private User user;  // 简单pojo对象作为包装对象的属性

List<Integer> ids;  //动态sql;在mapper.xml文件中可以通过foreach标签遍历生成动态sql。

//Integer[] ids;


public List<Integer> getIdsList() {
	return ids;
}
public void setIdsList(List<Integer> ids) {
	this.ids = ids;
}
public User getUser() {
	return user;
}
public void setUser(User user) {
	this.user = user;
}

}

Test.java（测试类）：

package com.xxx.mybatis.junit;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

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

import com.xxx.mybatis.mapper.UserMapper;
import com.xxx.mybatis.pojo.User;

public class Test {

//根据性别和名字查询用户
@Test
public void testfindUserBySexAndUsername() throws Exception {
	String resource = "sqlMapConfig.xml";
	InputStream in = Resources.getResourceAsStream(resource);
	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
	SqlSession sqlSession = sqlSessionFactory.openSession();
	//---------------------------------------------------------------------
	
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	User user = new User();
	user.setSex("1");
	//user.setUsername("张小明");
	List<User> users = userMapper.selectUserBySexAndUsername(user);
	for (User user2 : users) {
		System.out.println(user2);
	}
}

//根据多个ID查询
@Test
public void testfindUserIDs() throws Exception {
	String resource = "sqlMapConfig.xml";
	InputStream in = Resources.getResourceAsStream(resource);
	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
	SqlSession sqlSession = sqlSessionFactory.openSession();
	//---------------------------------------------------------------------
	
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	
	//Integer[] ids = new Integer[]{16,24,22};
	List<Integer> ids  = new ArrayList<Integer>();
	ids.add(16);
	ids.add(22);
	ids.add(24);
	List<User> users = userMapper.selectUserByIds(ids);  //ids可以是List也可以是数组

	/* QueryVo vo = new QueryVo();  //包装类
	vo.setIdsList(ids);
	List<User> users = userMapper.selectUserByIds(vo); */
	
	for (User user : users) {
		System.out.println(user);
	}
}

}
UserMapper.java（遵循四个原则的Dao层接口）：
package com.xxx.mybatis.mapper;

import java.util.List;

import com.xxx.mybatis.pojo.QueryVo;
import com.xxx.mybatis.pojo.User;
 

//其实就是Dao层接口
public interface UserMapper {

//遵循四个原则：
//UserMapper.xml中配置的命名空间要与该接口的全类名保持一致(com.xxx.mybatis.mapper.UserMapper) 
//接口中的方法名  == UserMapper.xml中配置的sql语句的id名
//返回值类型  与  UserMapper.xml文件中配置的返回值类型(resultType)要一致
//方法的输入参数类型 与UserMapper.xml中配置的输入参数的类型(parameterType)要一致


//根据性别和名字查询用户
public List<User> selectUserBySexAndUsername(User user);
//根据多个id查询用户信息  
//public List<User> selectUserByIds(Integer[] ids);  //参数是数组
public List<User> selectUserByIds(List<Integer> ids);   //参数是List
//public List<User> selectUserByIds(QueryVo vo);  //参数是包装类

}