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

Mybatis的Jar包下载：https://pan.baidu.com/s/16P-MGgn53e1EtCL6wQ9VWA  密码：1azq

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>

</mapper>
           

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);  //参数是包装类
	
}