Mybatis的if、where和foreach标签，输入参数为集合属性、集合、数组、动态数组

if标签的使用

<select id="selectOne" parameterType="int" resultType="com.mybatis.test.Student">
	select * from student where 
	//如果test中的表达式为真，则执行当中的SQL语句。
	<if test="student_id !='' and student_id != null">
		student_id=#{student_id}
	</if>
</select>

where标签的使用

<select id="selectOne" parameterType="student" resultType="com.mybatis.test.Student">
	select * from student where 
	<if test="student_id !='' and student_id != null">
		student_id=#{student_id}
	</if>
	<if test="student_name !='' and student_name != null">
		and student_name=#{student_name}
	</if>
</select>

当需要多个if标签的时候，当前面的if标签没有接收到参数的时候，则会出现SQL语句缺失的错误（select * from student where and student_name=?）：

Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and student_name='ku'' at line 4
### The error may exist in com/mybatis/test/studentMapper.xml
### The error may involve com.mybatis.mapper.StudentMapper.selectOne-Inline
### The error occurred while setting parameters
### SQL: select * from student where             and student_name=?
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and student_name='ku'' at line 4
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:144)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
	at com.sun.proxy.$Proxy0.selectOne(Unknown Source)
	at com.mybatis.test.Test.selectOne(Test.java:49)
	at com.mybatis.test.Test.main(Test.java:129)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and student_name='ku'' at line 4
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:372)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	... 8 more

则使用where标签：

<select id="selectOne" parameterType="student" resultType="com.mybatis.test.Student">
	select * from student
	<where>
		<if test="student_id !='' and student_id != null">
			student_id=#{student_id}
		</if>
		<if test="student_name !='' and student_name != null">
			and student_name=#{student_name}
		</if>
	</where>
</select>

foreach标签

输入参数为集合属性：

package com.mybatis.test;

import java.util.List;

public class StudentName {

	List<String> l;

	public List<String> getL() {
		return l;
	}

	public void setL(List<String> l) {
		this.l = l;
	}
	
	
}

<!-- 使用foreach循环 -->
<select id="selectOne" parameterType="com.mybatis.test.StudentName" resultType="com.mybatis.test.Student">
	select * from student
	<where>
		<if test="l !=null and l.size >0">
		<!-- collection:是 parameterType="com.mybatis.test.StudentName"包中的要循环的变量
		     open：进行拼接的SQL语句的开头
		     item：collection中实参的别名
		     separator：参数之间的分隔符 -->
			<foreach collection="l" open=" and student_name in (" close=")" item="StudentName" separator=",">
				#{StudentName}
			</foreach>
		</if>
	</where>
</select>

输入参数为集合：

<select id="selectOne" parameterType="list" resultType="com.mybatis.test.Student">
	select * from student
	<where>
		<if test="list !=null and list.size >0">
			<foreach collection="list" open=" and student_name in (" close=")" item="StudentName" separator=",">
				#{StudentName}
			</foreach>
		</if>
	</where>
</select>

输入参数为数组：

<select id="selectOne" parameterType="int[]" resultType="com.mybatis.test.Student">
	select * from student
	<where>
		<!-- 当参数为数组的时候，统一使用array变量名 -->
		<if test="array !=null and array.length >0">
			<foreach collection="array" open=" and id in (" close=")" item="StudentName" separator=",">
				#{StudentName}
			</foreach>
		</if>
	</where>
</select>

输入参数为动态数组：

<!-- 当参数为动态数组时，parameterType应该为"Object[]" -->
<select id="selectOne" parameterType="Object[]" resultType="com.mybatis.test.Student">
	select * from student
	<where>
		<if test="array !=null and array.length >0">
		    <!-- 使用的时候引用该对象的参数 如：#{StudentName.student_name}-->
			<foreach collection="array" open=" and student_name in (" close=")" item="StudentName" separator=",">
				#{StudentName.student_name}
			</foreach>
		</if>
	</where>
</select>