mybatis的动态sql和二级缓存

mybatis的#{}和$的区别就像是PreparedStatement和statement,使用${}接收的参数还需用@Param注解修饰 , 推荐用#{}

动态SQL

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
虽然在以前使用动态 SQL 并非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形。
动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。

代码演示

<mapper namespace="com.liy.dao.IUserDao">
  		<resultMap type="User" id="bs">
  			<id property="id" column="id"/>
  			<result property="name" column="name"/>
  			<result property="pwd" column="password"/>
  			<result property="address" column="address"/>
  		</resultMap> 
  		 <select id="query" resultMap="bs" resultType="user" parameterType="user">
  		 	select * from user
  		 		<where>
  		 			<if test="id!=null">
  		 				  id = #{id}
  		 			</if>
  		 		</where>
  		 </select>
  		 
  		 
  		 <insert id="insert" parameterType="user" >
  		 	<selectKey keyProperty="id" resultType="int">
  		 		select last_insert_id()
  		 	</selectKey>
  		 		insert into user(name,password,address)values(#{name},#{pwd},#{address})
  		 </insert>
  		 
  		 <insert id="insert1" parameterType="user" useGeneratedKeys="true" keyProperty="id">
  		 		insert into user(name,password,address)values(#{name},#{pwd},#{address})
  		 </insert>
  		 
  		 
  		 <update id="update" parameterType="user">
  		 	update user 
  		 		<set>
  		 			<if test="name!=null">
  		 				name = #{name},
  		 			</if>
  		 			<if test="pwd!=null">
  		 				password = #{pwd},
  		 			</if>
  		 			<if test="address!=null">
  		 				address = #{address}
  		 			</if>
  		 		</set>
  		 			where id = #{id}
  		 </update>
  		 
  	 
  	 	<select id="query1" resultMap="bs" resultType="user">
  	 		select *from user
  	 			<trim prefix="where" prefixOverrides="and | or" suffixOverrides=",">
  	 				<if test="name != null"> and name=#{name}  </if>
  	 				<if test="pwd != null">  and password=#{pwd}   </if>
  	 				<if test="address != null">  and address=#{address}   </if>
  	 			</trim>
  	 	</select>
  	 	
  	 	<select id="query2" resultMap="bs" resultType="user">
  	 		select *from user
  	 			 where id in
  	 			 <foreach collection="ids" open="("  close=")" item="id" separator=",">
  	 			 	#{id}
  	 			 </foreach>
  	 	</select>
  	 	
  	 	<select id="query3" resultMap="bs" resultType="user">
  	 		select *from user
  	 			<trim prefix="where" prefixOverrides="and | or" suffixOverrides=",">
  	 				<if test="name != null"> and name=#{name}  </if>
  	 				<if test="pwd != null">  and password=#{pwd}   </if>
  	 				<if test="address != null">  and address=#{address}   </if>
  	 			</trim>
  	 	</select>
  	 	
  	 	
  	 	<insert id="insert2" parameterType="user">
  	 		insert into user(name,password,address)
  	 		values
  	 		<foreach collection="list" item="user" separator=",">
  	 			(#{user.name},#{user.pwd},#{user.address})
  	 		</foreach>
  	 	</insert>

</mapper>
 

延迟查询

开启配置(1-1)

	
	<settings>
	 <!-- 开启延迟加载 -->
	    <setting name="lazyLoadingEnabled" value="true"/>
	     <setting name="aggressiveLazyLoading" value="false"/> 
	 </settings>

映射文件

 <mapper namespace="com.liy.dao.IUserDao">
  	 <resultMap type="emp" id="empDept">
  	 	<id property="empId" column="empId" />
  	 	<result property="empName" column="empName" />
  	 	
  	 	<!-- 建立1对1的关系 -->
  	 	<association property="dept" javaType="dept"  column="deptId" select="queryDept">
  	 			<id property="deptId" column="deptId" />
  	 		<result property="deptName" column="deptName" />
  	 	</association>
  	 </resultMap>
  	 
  	 <select id="queryDept" resultType="dept" parameterType="int">
  	 	select deptId,deptName from dept where deptId = #{deptId}
  	 </select>
  	 
  	 <select id="query" resultMap="empDept" resultType="emp">
  	 		select 
  	 			 e.empId empId
  	 			,e.empName empName
  	 			,e.deptId deptId
  	 		from emp e 
  	 </select>
  	 
  </mapper>

延迟查询

映射文件(一对多)

  <mapper namespace="com.liy.dao.IUserDao">
  	 <resultMap type="dept" id="de">
  	 	<id property="deptId" column="deptId" />
  	 	<result property="deptName" column="deptName" />
  	 	
  	 	<!-- 建立1对多的关系 -->
  	 	<collection  property="list" ofType="emp" column="deptId" select="queryEmp">
  	 			<id property="empId" column="empId" />
  	 		<result property="empName" column="empName" />
  	 	</collection >
  	 </resultMap>
  	 
  	 <select id="queryEmp" parameterType="int" resultType="emp">
  	 	select empId,empName from emp where deptId = #{deptId}
  	 </select>
  	 
  	 <select id="query" resultMap="de" resultType="dept">
  	 		select 
  	 			 d.deptId deptId
  	 			,d.deptName deptName 			
  	 		from dept d
  	 </select>
  	 
  </mapper>

测试

	@Test
	public void test1() throws IOException {
		SqlSession session = Dbutil.getSqlSession();
		
		List<Dept> list  = session.getMapper(IUserDao.class).query();
	
		for (Dept dept : list) {
			System.out.println(dept.getList());
		}
		
		session.close();
	
	}

 

缓存开启二级缓存

<mapper namespace="com.liy.dao.IUserDao">
  	 
  	 <cache type="org.mybatis.caches.ehcache.EhcacheCache"></cache>
 
  	 <select id="query"   parameterType="int" resultType="emp">
  	 		select  empId, empName,deptId from emp  where empId = #{empId}
  	 </select>
  	 
  </mapper>

测试

@Test
	public void test1() throws IOException {
		SqlSession session = Dbutil.getSqlSession();
		
		List<Emp> list  = session.getMapper(IUserDao.class).query(1);
	
		for (Emp emp : list) {
			System.out.println(emp);
		}
		session.close();
		
		System.out.println("---------------");
		session = Dbutil.getSqlSession();
		
		 list  = session.getMapper(IUserDao.class).query(1);
		
		for (Emp emp : list) {
			System.out.println(emp);
		}
		
		session.close();
	
	}