mybatis的动态sql和二级缓存

￡神魔★判官ぃ 2022-02-13 10:45 122阅读 0赞

mybatis的\#\{\}和 的 区 别 就 像 是 P r e p a r e d S t a t e m e n t 和 s t a t e m e n t , 使 用 \{\}的区别就像是 PreparedStatement 和statement , 使用 的区别就像是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)

映射文件

<mapper namespace="com.liy.dao.IUserDao">
      	 <resultMap type="emp" id="empDept">
      	 	<id property="empId" column="empId" />
      	 	<result property="empName" column="empName" />
      	 	
      	 	
      	 	<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" />
      	 	
      	 	
      	 	<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();
    	
    	}