mybatis动态查询之foreach,sql标签查询

当中foreach标签使用了基本的List 集合和多态类型

sql 标签同时使用了includ标签。其用法和jstl中的标签用法一致。

dao

package com.cbh.dao;

import java.util.List;

import com.cbh.beans.Student;

public interface IStudent {
List<Student>selectStudentsByCondition(String name,int age,int score);
List<Student>selectStudentsByConditionif(Student student);
List<Student>selectStudentsByConditionwhere(Student student);
//choose标签中可以包含<when/><otherwide/>，也可以包含多个<when/>与一个《otherwise》.
//他们联合起来完成java中的开关语句switch。。case功能。
List<Student> selectStudentsChoose(Student student);
/*
 * foreach标签的使用对用于实现数组与集合的遍历，需要注意的
 * collection表示遍历的集合类型这是数组，即arry。
 * open，close ，separator 为对遍历内容的sql拼接
 */
List<Student> selectStudentForeachArry(Object[] student);
//foreach标签遍历基本类型为list的集合
List<Student> selectStudentForeachArryList(List<Integer> studentsid);
//使用自定义型list类型
List<Student> selectStudentForeachArryList2(List<Student> studentsid);
List<Student> selectStudentForeacSQLFream(List<Student> studentsid);
}

mapper

<?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.cbh.dao.IStudent">
<select id="selectStudentsByConditionif" resultType="Student">
select id,name,age,score 
from student1 
where 1=1
<if test="name !=null and name !=''">
and name like '%' #{name} '%'
</if>
<if test="age>0">
and age> #{age}
</if>
</select> 
<select id="selectStudentsByConditionwhere" resultType="Student">
select id,name,age,score 
from student1 
<where>
<if test="name !=null and name !=''">
name like '%' #{name} '%'
</if>
<if test="age>0">
and age> #{age}
</if>
<if test="score>0">
and score>#{score}
</if>
</where>
</select> 
<!-- 
#{}中可以放入的内容
1.参数对象的属性
2.随意的内容，此时#｛｝是个占位符
3.参数为map时的key
4.参数为map时，若key所对应的value为对象，则可以将该对象的属性放入
5.参数的索引号（本次就是参数的索引号）
 -->
 <select id="selectStudentsChoose" resultType="Student">
 select*
 from student1
 <where>
 <choose>
 <when test="name !=null and name!=''">
 and name like '%' #{name} '%'
 </when>
 <when test="age>0">
 and age > #{age}
 </when>
 <when test="score>0">
 and score>#{score}
 </when>
 <otherwise>
 and 1 !=1
 </otherwise>
 </choose>
 
 </where>
 
 </select>
 <!--遍历数组  -->
	<select id="selectStudentForeachArry" resultType="Student">
		select*
		from student1
		<if test="array!=null and array.length>0">
			where id in
			<foreach collection="array" open="(" close=")" item="myid"
				separator=",">
				#{myid}
			</foreach>
		</if>
	</select>
	<select id="selectStudentForeachArryList" resultType="Student">
	select*
	from student1
	<if test="list!=null and list.size>0">
		where id in
		<foreach collection="list" item="myid" open="(" close=")"
			separator=",">
			#{myid}
		</foreach>
	</if>
</select>
	<select id="selectStudentForeachArryList2" resultType="Student">
	select*
	from student1
	<if test="list!=null and list.size>0">
		where id in
		<foreach collection="list" item="student" open="(" close=")"
			separator=",">
			#{student.id}
		</foreach>
	</if>
</select>
	<sql id="testsql">
		select*from student1
	</sql>
	<select id="selectStudentForeacSQLFream" resultType="Student">
		<include refid="testsql" />
		<if test="list!=null and list.size>0">
			where id in
			<foreach collection="list" item="Student" open="(" close=")"
				separator=",">
				#{Student.id}
			</foreach>
		</if>
	</select>
</mapper>
		

测试类

package com.cbh.test;

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

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

import com.cbh.Utils.MybatisUtils;
import com.cbh.beans.Student;
import com.cbh.dao.IStudent;

public class mytest {
	private IStudent dao;
	@Before
	public void before() {
	SqlSession sqlSession=	MybatisUtils.getSqlSession();
		dao =sqlSession.getMapper(IStudent.class) ;
	}
	
@After
public void after(){
if(sqlsession!=null）｛
sqlsession.close();
｝
	
｝
@Test
public void test8(){
//Student stu=new Student("大",23,0);
Student stu=new Student("",0,0);
List<Student> student=dao.selectStudentsByConditionif(stu );
for (Student student2 : student) {
System.out.println(student2);
}
}
@Testpublic void test9(){
Student stu=new Student("",0,100);
List<Student> student=dao.selectStudentsByConditionwhere(stu);
for (Student student2 : student) {System.out.println(student2);
}
}
 @Testpublic void test10(){
Student stu=new Student("",0,100);
List<Student> student=dao.selectStudentsChoose(stu);
for (Student student2 : student) {
System.out.println(student2);
}
}
@Testpublic void test11(){
Object[] studentIds=new Object[]{1,3,4,20};
List<Student> students=dao.selectStudentForeachArry(studentIds);
for (Student student3 : students) {
System.out.println(student3);
}
}
@Testpublic void test12(){
List<Integer> studentsid=new ArrayList<Integer>();
studentsid.add(2);
studentsid.add(5);
studentsid.add(24);
List<Student> student=dao.selectStudentForeachArryList(studentsid);
for (Student student2 : student) {
System.out.println(student2);
}
}
@Testpublic void test13(){
Student student1=new Student();
Student student2=new Student();
student1.setId(4);
student2.setId(2);
List<Student> studentid=new ArrayList<Student>();
studentid.add(student1);studentid.add(student2);
studentid=dao.selectStudentForeachArryList2(studentid);
for (Student student : studentid) {
System.out.println(student);
}
}
@Testpublic void test14(){
Student student1=new Student();
Student student2=new Student();
student1.setId(4);
student2.setId(2);
List<Student> studentsid=new ArrayList<Student>();
studentsid.add(student1);
studentsid.add(student2);
studentsid=dao.selectStudentForeacSQLFream(studentsid);
for (Student students : studentsid) {
System.out.println(students);
}
}
}