mybatis_day4

一、动态SQL
有时候，静态的 SQL 语句并不能满足应用程序的需求。我们可以根据一些条件，来动态地构建 SQL 语句。
如果你有任何JDBC或类似的框架经验,你就会了解拼接一个有条件的Sql字符串是是多么痛苦,尤其需要确保不要忘记空格或不要忘记列表中的逗号等。MyBatis最强大的特性之一就是它的动态SQL功能，它的动态SQL可以帮我们解决这种痛苦。

MyBatis通过使用
	1.<if>
	2.<choose>(<when>,<otherwise>)
	3.<trim>(<where>,<set>)
	4.<foreach>
元素提供了对构造动态SQL语句的高级别支持

1.1)
元素被用来有条件地嵌入SQL片段，如果测试条件被赋值为true，则相应地SQL片段将会被添加到SQL语句中
例如：
我们需要从s_emp表中查询指定条件员工的信息，条件如下：
1.指定title,固定会有的查询条件
2.指定薪资范围
3.指定雇用日期范围

<select id="selectEmpByCondition" resultMap="empResult">
	select id,last_name,first_name,title,salary,start_date,
		   comments,commission_pct
	from s_emp
	where title = #{param1}
	<if test="param2!=null">
		and salary >= #{param2}
	</if>
	<if test="param3!=null">
		and salary<![CDATA[<=]]> #{param3}
	</if>
	<if test="param4!=null">
		and start_date >= #{param4}
	</if>
	<if test="param5!=null">
		and start_date <![CDATA[<=]]> #{param5}
	</if>
</select>
	
测试：
	SqlSession session = openSession();
		DynamicSqlMapper mapper = 
				session.getMapper(DynamicSqlMapper.class);
		List<Employee> list = 
				mapper.selectEmpByCondition(
				"Stock Clerk", 900d,1500D , null, null);
		for(Employee e:list){
			System.out.println(e);
		}

1.2)choose,when 和 otherwise 条件
有时候，查询功能是以查询类别为基础的。首先，用户需要先选择希望通过什么类型作为查询条件类别来进行查询(例如按照员工Title，员工雇用日期，员工工资中的一个)，然后根据选择的查询类别，输入相应的参数。在这样的情景中，我们【需要只使用其中一种】查询类别。
MyBatis提供了元素支持此类型的SQL预处理。

<select id="selectByConditionType" resultMap="empResult">
	select id,last_name,first_name,title,salary,start_date,
		   comments,commission_pct
	from s_emp
	<choose>
		<when test="selectBy == 'title'">
			where title = #{title}
		</when>
		<when test="selectBy == 'startDate'">
			where start_date >= #{startDate}
		</when>
		<otherwise>
			where salary >= 2000
		</otherwise>
	</choose>
</select>

测试：
	HashMap<String,Object> map = new HashMap<>();
	map.put("selectBy", "title");
	map.put("title", "Stock Clerk");
	List<Employee> list = mapper.selectByConditionType(map);

1.3) where
有时候，所有的查询条件应该是可选的。此时，我们应该使用WHERE子句。并且如果有多个条件，我们需要在条件中添加AND或OR。

MyBatis提供了<where>元素支持这种类型的动态SQL语句。	
<select id="selectWhere" resultMap="empResult">
	select id,last_name,first_name,title,salary,start_date,
		   comments,commission_pct
	from s_emp
	<where>
		<if test="title!=null">
			and title = #{title}
		</if>
		<if test="minSalary!=null">
			and salary >= #{minSalary}
		</if>
		<if test="maxSalary!=null">
			and salary<![CDATA[<=]]> #{maxSalary}
		</if>
		<if test="minDate!=null">
			and start_date >= #{minDate}
		</if>
		<if test="maxDate!=null">
			and start_date <![CDATA[<=]]> #{maxDate}
		</if>
	</where>
</select>

where元素只有在其内部标签有返回内容时才会在动态语句上插入WHERE条件语句。并且，如果WHERE子句以AND或者OR打头，则打头的AND或OR将会被移除。

1.4) set标记
元素和元素类似，如果其内部条件判断有任何内容返回时，他会插入SET SQL 片段。

 < update id="updateEmployee" parameterType="dynamic.Employee">
		update s_emp
		<set> 
			<if test="lastName != null">last_name=#{lastName},</if> 
			<if test="firstName != null">first_name=#{firstName},</if> 
			<if test="comments != null">comments=#{comments},</if> 
			<if test="startDate != null">start_date=#{startDate},</if> 
			<if test="salary != null">salary=#{salary},</if> 
			<if test="title != null">title=#{title},</if> 
			<if test="commission_pct != null">commission_pct=#{commission_pct},</if> 
		</set> 
		where id=#{id} 
	</ update>

这里，如果条件返回了任何文本内容，将会插入set关键字和其文本内容，并且会剔除将末尾的“，”。

1.5) trim 条件
元素和元素类似，但是提供了在添加前缀/后缀或者移除前缀/后缀方面提供更大的灵活性。

<select id="selectTrim" resultMap="empResult">
		select id,last_name,first_name,title,salary,start_date,
			   comments,commission_pct
		from s_emp
		<trim prefix="where" prefixOverrides="And|OR">
			<if test="title!=null">
				and title = #{title}
			</if>
			<if test="minSalary!=null">
				or salary >= #{minSalary}
			</if>
		</trim>
	 </select>
	
	 title!= null:
	 sql:select id,last_name,first_name,title,salary,start_date,
			   comments,commission_pct
		from s_emp
		where title = #{title}

1.6) foreach 循环
另外一个强大的动态SQL语句构造标签即是。它可以迭代遍历一个数组或者列表，构造AND/OR条件或一个IN子句。

	假设我们想找到id为 1，3，6的员工的信息，我们可以传递一个id组成的列表给映射语句，然后通过<foreach>遍历此列表构造动态SQL。
	<select id="selectForeach" resultMap="empResult">
		select id,last_name,first_name,title,salary,start_date,
			   comments,commission_pct
		from s_emp
		<where>
			<!-- 此处代表传递参数类型为list集合-->
			<if test="list!=null">
				<foreach collection="list" item="empid">
					or id = #{empid}
				</foreach>
			</if>	
		</where>
	</select>
	测试：
		SqlSession session = openSession();
			DynamicSqlMapper mapper = 
					session.getMapper(DynamicSqlMapper.class);
			List<Integer> ids = new ArrayList<>();
			ids.add(1);
			ids.add(5);
			ids.add(8);
			List<Employee> list = mapper.selectForeach(ids);
			for(Employee e:list){
				System.out.println(e);
			}
	
	<!--此处代表传递参数类型是map-->
		<select id="selectForeach" resultMap="empResult">
			select id,last_name,first_name,title,salary,start_date,
				   comments,commission_pct
			from s_emp
			<where>
				<!-- 此处代表传递参数类型为map集合-->
				<if test="ids!=null">
					<foreach collection="ids" item="empid">
						or id = #{empid}
					</foreach>
				</if>	
			</where>
		</select>
	测试：
		SqlSession session = openSession();
			DynamicSqlMapper mapper = 
					session.getMapper(DynamicSqlMapper.class);
			List<Integer> ids = new ArrayList<>();
			ids.add(1);
			ids.add(5);
			ids.add(8);
			HashMap<String, Object> o = new HashMap<>();
			o.put("ids", ids);
			List<Employee> list = mapper.selectForeach(o);
			for(Employee e:list){
				System.out.println(e);
			}
		
	使用foreach标签生成in语句：
		<if test="ids!=null">
				id in 
				<foreach collection="ids" item="empid" 
					open="(" separator="," close=")">
					#{empid}	
				</foreach>
		</if>

四、Mapper文件中的其他标记

4.1.sql标记
	这个元素用来定义一些可以包含在其他语句中的可重用的sql片段，比如
	<sql id="empColumn">
		select id,last_name,first_name,title,salary,start_date,
			   comments,commission_pct
		from s_emp
	</sql>
	如上sql片段可以包含在另外一个语句中，例如：
	<select id="selectForeach" resultMap="empResult">
		<include refid="empColumn"></include>
		<where>
			<if test="ids!=null">
				id in 
				<foreach collection="ids" item="empid" 
					open="(" separator="," close=")">
					#{empid}	
				</foreach>
			</if>
		</where>
	</select>
4.2.include标签用来引入<sql>标记定义的语句
4.3.bind
	bind元素可以从OGNL表达式中创建一个变量并将其绑定到上下文。比如：
	<select id="selectByLike"  resultMap="empResult">
		select id,last_name,first_name,title,salary,start_date,
		   comments,commission_pct
		from s_emp
		<if test="_parameter!=null">
			<bind name="title" value="'%'+_parameter+'%'"/>
			where title like #{title}
		</if>
	</select>

五、 mybatis的一些特殊功能

5.1 多行结果集映射成Map
	如果你有一个映射语句返回多行记录，并且你想以HashMap的形式存储记录的值，使用记录列名作为key值，而记录对应值或为value值。我们可以使用sqlSession.selectMap(),如下所示：
	
	<select id="findAllStudents" resultMap="StudentResult"> 
		select * from Students 
	</select> 

	Map<Integer, Student> studentMap = sqlSession.selectMap("com.briup.mappers.StudentMapper.findAllStudents", "studId"); 

	这里studentMap将会将studId作为key值，而Student对象作为value值。
	
	@MapKey("id")
	Map<Integer,Emp> findByFirstName(String last_name);
	
	如果希望自定义结果返回：
		session.select("statement",param,ResultHandler);

5.2 使用RowBounds对结果集进行分页
	有时候，我们会需要跟海量的数据打交道，比如一个有数百万条数据级别的表。由于计算机内存的限制我们不可能一次性加载这么多数据，我们可以获取到数据的一部分。特别是在Web应用程序中，分页机制被用来以一页一页的形式展示海量的数据。
	MyBatis可以使用RowBounds逐页加载表数据。RowBounds对象可以使用offset和limit参数来构建。参数offset表示开始位置，而limit表示要取的记录的数目。假设如果你想每页加载并显示25条学生的记录，你可以使用如下的代码：
	
	<select id="findAllStudents" resultMap="StudentResult"> 
		select * from Students 
	</select> 
	然后，你可以加载如下加载第一页数据（前25条）：
	int offset =0 , limit =25; 
	RowBounds rowBounds = new RowBounds(offset, limit); 
	List<Student> = studentMapper.getStudents(rowBounds); 
	若要展示第二页，使用offset=25,limit=25;第三页，则为offset=50,limit=25。
	
	但是其实Mybatis的分页功能是很弱的，它是基于内存的分页（查出所有记录再按偏移量和limit取结果），在大数据量的情况下这样的分页基本上是没有用的。(我们 也可以借助于各种类型的数据库自定义逻辑分页，比如oracle使用rownum)。 而且我们可以直接下载mybatis分页相关的插件(就是几个jar包)来用
	
	 select id,last_name
	 from (select id,last_name,rownum rw
		   from s_emp) r
	 where r.rw between 3 and 5;

	PageHelper


5.3 缓存
	将从数据库中加载的数据缓存到内存中，是很多应用程序为了提高性能而采取的一贯做法。MyBatis对通过映射的SELECT语句加载的查询结果提供了内建的缓存支持。默认情况下，启用一级缓存；即，如果你使用同一个SqlSession接口对象调用了相同的SELECT语句，则直接会从缓存中返回结果，而不是再查询一次数据库。
	
	我们可以在SQL映射器XML配置文件中使用<cache />元素添加全局二级缓存。
	
	二级缓存：

	二级缓存就是global caching,它超出session范围之外,可以被所有sqlSession共享,它的实现机制和mysql的缓存一样,
	开启它只需要在mybatis的配置文件开启settings里的
		<setting name="cacheEnabled" value="true"/>

	以及在相应的Mapper文件(例如userMapper.xml)里开启
	<mapper namespace="dao.userdao">
	   ...  select statement ...
		   <!-- Cache 配置 -->
		<cache eviction="FIFO" flushInterval="5000"></cache>
	</mapper>
	
	二级缓存: 在不同的session对象之间可以共享缓存数据
	1.mybatis-config.xml文件中保证<setting name="cacheEnabled" value="true"/>设置中是缓存功能是开启的,默认就是开启的true
	2.在需要二级缓存的xml映射文件中,手动开启缓存功能,在根元素中加入一个标签即可:<cache/>
	3.一个session查询完数据之后,需要调用commit或者close方法后,这个数据才会进入到缓存中,然后其他session就可以共享到这个缓存数据了
	
	注意:默认情况下,被二级缓存保存的对象需要实现序列化接口,可以通过cache标签的readOnly属性进行设置
	
	cache标签常用属性：
	<cache 
	eviction="FIFO"  <!--回收策略为先进先出-->
	flushInterval="60000" <!--自动刷新时间60s-->
	size="512" <!--最多缓存512个引用对象-->
	readOnly="true"/> <!--true表示对象不能被写出去,即不可以被序列化,false表示可以写出去,即可以被序列化,默认值是false-->

	
	当你加入了<cache />元素，将会出现以下情况：
	所有的在映射语句文件定义的<select>语句的查询结果都会被缓存
	所有的在映射语句文件定义的<insert>,<update> 和<delete>语句将会刷新缓存
	缓存根据最近最少被使用（Least Recently Used，LRU）算法管理
	缓存会根据指定的时间间隔来刷新。
	缓存会存储1024个对象

六 MyBatis中的注解
之前我们都是在映射器MapperXML配置文件中配置映射语句的。除此之外MyBatis也支持使用注解来配置映射语句。当我们使用基于注解的映射器接口时，我们不再需要在XML配置文件中配置了。如果你愿意，你也可以同时使用基于XML和基于注解的映射语句。

6.1 在映射器Mapper接口上使用注解
	MyBatis对于大部分的基于XML的映射器元素（包括<select>,<update>）提供了对应的基于注解的配置项。然而在某些情况下，基于注解配置还不能支持基于XML的一些元素。

6.2 映射语句
	MyBatis提供了多种注解来支持不同类型的语句(statement)如 SELECT,INSERT,UPDATE,DELETE。让我们看一下具体怎样配置映射语句
	
	6.2.1 @Insert 
	我们可以使用 @Insert注解来定义一个INSERT映射语句：
	 
	package com.briup.mappers; 
	public interface StudentMapper{ 

		@Insert("INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL,ADDR_ID, PHONE) 
				VALUES(#{studId},#{name},#{email},#{address.addr Id},#{phone})") 
		int insertStudent(Student student); 

	} 

	自动生成主键

	可以使用 @Options注解的userGeneratedKeys和keyProperty属性让数据库产生 auto_increment（自增长）列的值，然后将生成的值设置到输入参数对象的属性中。

	@Insert("INSERT INTO STUDENTS(NAME,EMAIL,ADDR_ID, PHONE) 
			VALUES(#{name},#{email},#{address.addr Id},#{phone})") 
	@Options(useGeneratedKeys = true, keyProperty = "studId") 
	int insert Student(Student student); 

	这里STUD_ID列值将会通过MySQL数据库自动生成。并且生成的值将会被设置到student对象的studId属性中。
	StudentMapper mapper = sqlSession.getMapper(Student Mapper.class); 
	mapper.insertStudent(student); 
	int studentId = student.getStudId(); 
	
	有一些数据库如Oracle，并不支持AUTO_INCREMENT列属性，它使用序列（SEQUENCE）来产生主键的值。
	我们可以使用 @SelectKey注解来为任意SQL语句来指定主键值，作为主键列的值。假设我们有一个名为STUD_ID_SEQ的序列来生成STUD_ID主键值。
	 
	@Insert("INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL,ADDR_ID, PHONE)  
	VALUES(#{studId},#{name},#{email},#{address.addrId},#{phone})") 
	@SelectKey(statement="SELECT STUD_ID_SEQ.NEXTVAL FROM DUAL",  
	keyProperty="studId", resultType=int.class, before=true) 
	int insertStudent(Student student); 

	这里我们使用了 @Select Key来生成主键值，并且存储到了student对象的studId属性上。由于我们设置了before=true,该语句将会在执行INSERT语句之前执行。如果你使用序列作为触发器来设置主键值，我们可以在INSERT语句执行后，从sequence_name.currval获取数据库产生的主键值。

	@Insert("INSERT INTO STUDENTS(NAME,EMAIL,ADDR_ID, PHONE)  
	VALUES(#{name},#{email},#{address.addrId},#{phone})") 
	@SelectKey(statement="SELECT STUD_ID_SEQ.CURRVAL FROM DUAL",  
	keyProperty="studId", resultType=int.class, before=false) 
	int insertStudent(Student student); 



	6.2.2 @Update 
	我们可以使用 @Update注解来定义一个UPDATE映射语句，如下所示：
	@Update("UPDATE STUDENTS SET NAME=#{name}, EMAIL=#{email},  
	PHONE=#{phone} WHERE STUD_ID=#{studId}") 
	int updateStudent(Student student); 


	StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); 
	int noOfRowsUpdated = mapper.updateStudent(student); 



	6.2.3 @Delete 
	我们可以使用 @Delete注解来定义一个DELETE映射语句，如下所示：
	@Delete("DELETE FROM STUDENTS WHERE STUD_ID=#{studId}") 
	int deleteStudent(int studId);

	@Delete("DELETE FROM STUDENTS WHERE STUD_ID=#{studId}") 
	int deleteStudent(int studId); 


	6.2.4 @Select
	我们可以使用 @Select注解来定义一个SELECT映射语句。
	 
	package com.briup.mappers; 
	public interface StudentMapper{ 
		@Select("SELECT STUD_ID AS STUDID, NAME, EMAIL, PHONE FROM 
				STUDENTS WHERE STUD_ID=#{studId}") 
		Student findStudentById(Integer studId); 
	} 
	为了将列名和Student bean属性名匹配，我们为stud_id起了一个studId的别名。如果返回了多行结果，将抛出TooManyResultsException异常。


6.3 结果映射
	我们可以将查询结果通过别名或者是 @Results注解与Java Bean属性映射起来。
	 
	package com.briup.mappers; 
	public interface StudentMapper{ 
		@Select("select * from student")
		@Results(id="studentResult",value={
				@Result(id=true,column="id",property="id"),
				@Result(column="name",property="name"),
				@Result(column="gender",property="gender"),
				@Result(column="birthday",property="birthday")
		})
		public Student findAll(int id); 
	} 
	
	注意:
	@Results注解和映射器XML配置文件元素<resultMap> 对应。然而，早期版本不支持id属性，所以不能重复使用
	目前我们使用的版本时3.4.1，可以给@Results注解添加ID属性，因此可以在目前的版本中重复使用。
	
	例如，看下面的 findById()和 findAll()方法：

	@Select("select * from student where id = #{id}")
	@ResultMap("studentResult")
	public Student findById(int id);
	
	@Select("select * from student")
	@Results(id="studentResult",value={
			@Result(id=true,column="id",property="id"),
			@Result(column="name",property="name"),
			@Result(column="gender",property="gender"),
			@Result(column="birthday",property="birthday")
	})
	public Student findAll(int id);

	在此处我们可以使用@ResultMap("studentResult")引用定义在findAll()方法上的result。
	
	【NOTE】：早起版本必须得重复定义。如果不想重复，这里有一个解决方法。我们可以创建一个映射器Mapper配置文件，然后配置<resultMap>元素，然后使用 @ResultMap注解引用此<resultMap>。 在StudentMapper.xml中定义一个ID为StudentResult的<resultMap>。
	
	xml配置:
	 
	<mapper namespace="com.briup.mappers.StudentMapper"> 
	  <resultMap type="Student" id="StudentResult"> 
		<id property="id" column="id" /> 
		<result property="name" column="name" /> 
		<result property="gender" column="gender" /> 
		<result property="birthday" column="birthday" /> 
	  </resultMap> 
	</mapper> 

	在StudentMapper.java中,使用 @Result Map引用名为StudentResult的resultMap:
	 
	public interface StudentMapper{ 
		@Select("SELECT * FROM STUDENTS WHERE STUD_ID=#{studId}") 
		@ResultMap("com.briup.mappers.StudentMapper.StudentResult") 
		Student findById(int studId); 

		@Select("SELECT * FROM STUDENTS") 
		@ResultMap("com.briup.mappers.StudentMapper.StudentResult") 
		List<Student> findAll(); 
	} 
	

	
6.4 一对一映射
	MyBatis提供了 @One注解来使用嵌套select语句（Nested-Select）加载一对一关联查询数据。让我们看看怎样使用 @One注解获取妻子及其丈夫信息
	 
	public interface One2OneMapper {

		@Select("select * from husband where id = #{hid}")
		@ResultType(Husband.class)
		public Husband selectHusbandById(int hid);
		
		@Select("select * from wife where id = #{id}")
		@Results(id="wifeAndhusResult",value={
			@Result(property="husband",column="h_id",one=@One(select="selectHusbandById"))
		})
		public Wife selectWifeByWid(int wid);
	}
	
	这里我们使用了 @One注解的select属性来指定一个使用了完全限定名的方法上，该方法会返回一个Husband对象。使用column=”h_id”,则wife表中列h_id的值将会作为输入参数传递给selectHusbandById()方法。如果 @One SELECT查询返回了多行结果，则会抛出TooManyResultsException异常。

	SqlSession session = openSession();
	One2OneMapper mapper = session.getMapper(One2OneMapper.class);
	Wife list = mapper.selectWifeByWid(5);	
	System.out.println(list);
	session.close();

	我们可以通过基于XML的映射器配置，使用嵌套结果ResultMap来加载一对一关联的查询。而3.5.5版本之前并没有对应的注解支持。但是我们可以在映射器Mapper配置文件中配置<resultMap>并且使用 @ResultMap注解来引用它。
	3.5.5版本之后可以写：
		@Select("select e.id,e.last_name,e.first_name,e.salary,"
		+ " m.id m_id,m.last_name m_last_name "
		+ " from s_emp e, s_emp m "
		+ " where e.manager_id = m.id "
		+ " and e.id = #{id}")
		/* @ResultMap("baseMap") */
		@Results(id = "empWithManager",value = {
				@Result(column = "id",property = "id"),
				@Result(property = "manager",one = @One(resultMap = "baseMap",columnPrefix = "m_")) //3.5.5以后支持。
		})
		Emp selectEmpWithManagerById(int id);


6.5 一对多映射
MyBatis提供了 @Many注解，用来使用嵌套Select语句加载一对多关联查询。现在让我们看一下如何使用 @Many注解获取一个班级及班级中所有学生列表信息：
 
public interface Many2OneMapper {
	@Select("select id cid,name cname from s_clz where id = #{cid}")
	@Results(id="clzAndstuResult",value={
		@Result(property="id",column="cid"),
		@Result(property="name",column="cname"),
		@Result(property="stus",column="cid",many=@Many(select="selectStuByCid"))
	})
	public Clz selectByCid(int cid);
	
	@Select("select * from s_stu where c_id = #{cid}")
	public List<Student> selectStuByCid(int cid);
}


这里我们使用了 @Many注解的select属性来指向一个完全限定名称的方法，该方法将返回一个 List<Student>对象。使用column="cid"，s_clz表中的检索出的别名列cid列值将会作为输入参数传递给selectByCid方法。 
之前我们使用过嵌套结果ResultMap来加载一对多关联的查询。而 My 3.5.5版本之前，并没有对应的注解支持。但是我们可以在映射器Mapper配置文件中配置<resultMap>并且使用 @ResultMap注解来引用它。

3.5.5以后可以写：
	@Select("select r.id,r.name,d.id d_id,d.name d_name "
		+ " from s_region r,s_dept d "
		+ " where r.id = d.region_id ")
	@Results(id="regionWithDepart",value = {
			@Result(column = "id",property = "id"),
			@Result(column = "name",property = "name"),
			@Result(property = "departs",many = @Many(columnPrefix = "d_",resultMap = "deptMap")) //3.5.5版本才有了resultMap属性和columnPrefix属性
	})
	List<Region> selectRegionWithDepart();