MyBatis--动态sql练习

StudentDAO类

public interface StudentDAO {
	//<if></if>标签练习
	List<Student> selectStudentByIf(Student student);
	//<where></where>标签练习
	List<Student> selectStudentByWhere(Student student);
	//<choose>标签练习
	List<Student> selectStudentByChoose(Student student);
	//<foreach>标签练习
	List<Student> selectStudentByForeachArray(int[] ids);
	List<Student> selectStudentByForeachList(List<Integer> ids);
	List<Student> selectStudentByForeachListObject(List<Student> students);
	int getCount();
	//<sql>标签练习，可以把重复的sql代码写在<sql>标签了，然后在需要的地方用<include标签>引入
	List<Student> selectStudentBySqlFragment();
}

StudentDAO.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.woniu.dao.StudentDAO">
	<!-- if标签 -->
	<select id="selectStudentByIf" resultType="Student" parameterType="Student">
		select * from student where 1=1
		<if test="sname!=null and sname!='' ">
			and sname like '%' #{sname} '%'
		</if>
		<if test="sage>0">
			and sage>#{sage}
		</if>
	</select>
	<!-- where标签  -->
	<select id="selectStudentByWhere" resultType="Student" parameterType="Student">
		select * from student
		<where>
			<if test="sname!=null and sname!='' ">
			and sname like '%' #{sname} '%'
		</if>
		<if test="sage>0">
			and sage>#{sage}
		</if>
		</where>
	</select>
	<!-- choose标签 -->
	<select id="selectStudentByChoose" resultType="Student" parameterType="Student">
		select * from student
		<where>
			<choose>
				<when test="sname!=null and sname!=''">				 
					and sname like '%' #{sname} '%'
				</when>
				<when test="sage>0">				
					and sage>#{sage}					
				</when>
				<otherwise>
				 and 1=2
				</otherwise>
			</choose>
		</where>
	</select>
	<!-- foreach标签 -->
	<select id="selectStudentByForeachArray" resultType="Student" >
		select * from student
		<if test="array!=null and array.length>0">
			where sid in
			<foreach collection="array" open="(" close=")" separator="," item="id">
				#{id}
			</foreach>
		</if>
	</select>
	
	<select id="selectStudentByForeachList" resultType="Student" >
		select * from student
		<if test="list!=null and list.size>0">
			where sid in
			<foreach collection="list" open="(" close=")" separator="," item="id">
				#{id}
			</foreach>
		</if>
	</select>
	
	<select id="selectStudentByForeachListObject" resultType="Student" >
		select * from student
		<if test="list!=null and list.size>0">
			where sid in
			<foreach collection="list" open="(" close=")" separator="," item="id">
				#{id.sid}
			</foreach>
		</if>
	</select>
	
	<select id="getCount" resultType="int" parameterType="Student">
		select count(*) from student
	</select>
	<!-- sql标签，include引入 -->
	<select id="selectStudentBySqlFragment" resultType="Student" parameterType="Student">
		select  <include refid="myslq" />student
	</select>
	<sql id="myslq">* from</sql>
</mapper>

StudentDAOImpTest.java测试类

public class StudentDAOImpTest {
	SqlSession sqlSession;
	StudentDAO studentDaoImp;
	
	@Before
	public void setUp() {
		sqlSession=MyBatisUtil.getSqlSession();
		//getMapper:底层就是使用动态代理，生成接口的实现类
		studentDaoImp = sqlSession.getMapper(StudentDAO.class);
	}
	
	@After
	public void tearDown() {
		if(sqlSession!=null) {
			sqlSession.close();
		}
	}
	//<if></if>标签练习
	@Test
	public void selectStudentByIf() {
		Student student = new Student();
		student.setSname("小明");
		System.out.println(studentDaoImp.selectStudentByIf(student));
	}
	//<where></where>标签练习
	@Test
	public void selectStudentByWhere() {
		Student student = new Student();
		student.setSname("小明");
		System.out.println(studentDaoImp.selectStudentByWhere(student));
	}
	//<choose>标签练习
	@Test
	public void selectStudentByChoose() {
		Student student = new Student();
		student.setSname("小明");
		System.out.println(studentDaoImp.selectStudentByChoose(student));
	}
	//<foreach>标签练习：int[]数组
	@Test
	public void selectStudentByForeachArray() {
		int[] ids = new int[3];
		ids[0]=1;
		ids[1]=2;
		ids[2]=3;
		System.out.println(studentDaoImp.selectStudentByForeachArray(ids));
	}
	//<foreach>标签练习：list集合，存基本数据类型
	@Test
	public void selectStudentByForeachList() {
		List<Integer> ids = new ArrayList();
		ids.add(1);
		ids.add(2);
		ids.add(3);
		ids.add(4);
		System.out.println(studentDaoImp.selectStudentByForeachList(ids));
	}
	//<foreach>标签练习：list集合，存对象
	@Test
	public void selectStudentByForeachListObject() {
		List<Student> ids = new ArrayList();
		ids.add(new Student(1,null,null,null,null));
		ids.add(new Student(2,null,null,null,null));
		ids.add(new Student(3,null,null,null,null));
		ids.add(new Student(4,null,null,null,null));
		System.out.println(studentDaoImp.selectStudentByForeachListObject(ids));
	}
	//查询所有学生数据
	@Test
	public void getCount() {
		System.out.println(studentDaoImp.getCount());
	}
	//<sql>标签练习，可以把重复的sql代码写在<sql>标签了，然后在需要的地方用<include标签>引入
	@Test
	public void selectStudentBySqlFragment() {
		System.out.println(studentDaoImp.selectStudentBySqlFragment());
	}
}