Mybatis老手复习文档 (3)

以后 要学 Mysql引擎，InnoDB底层原理， 索引 ，索引优化

11、 动态SQL mapUnderscoreToCamelCase 是否开启驼峰命名自动映射，即从经典数据库列名 A_COLUMN 映射到经典 Java 属性名 aColumn。 true | false False

choose我没有设置了，因为 choose是类似于一个switch case一次只会选择一个条件语句进行执行

<select resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <choose> <when test="title != null"> AND title like #{title} </when> <when test="author != null and author.name != null"> AND author_name like #{author.name} </when> <otherwise> AND featured = 1 </otherwise> </choose> </select>

动态SQL就是在拼接SQL语句，我们只要保证SQL的正确性，按照SQL的格式，去排列组合就可以了。

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.pipihao.mapper.BlogMapper"> <cache/> <insert > insert into blog (id,title,author,create_time,view) values ( #{id},#{title},#{author},#{createTime},#{view} ); </insert> <select resultType="blog" parameterType="map"> select * from blog <where> <include refid="ifsql"></include> </where> </select> <sql> <if test="title != null"> and title = #{title} </if> <if test="author != null"> and author= #{author} </if> </sql> <update parameterType="map"> update blog <set> <if test="title != null">title=#{title}</if> <if test="author != null">title=#{author}</if> </set> where id = #{id} </update> <select resultType="blog"> select * from blog <where> <foreach collection="ids" item="id" open="and ( " separator="or" close=")"> id = #{id} </foreach> </where> </select> </mapper>

,choose,when,otherwise 这个类似switch case一次只会执行一个条件，本命的不执行

<select resultType="blog" parameterType="map"> select * from blog <choose> <when test="title != null"> and title = #{title} </when> <when test="author != null"> and author= #{author} </when> <otherwise> <!--类似default--> AND featured = 1 </otherwise> </choose> </select>

trim, 可以 给SQL语句加上前缀和后缀，然后，也可以去除某些前缀或后缀

<select resultMap="resultListUsers" parameterType="Users"> select * from users <trim prefix="where" prefixOverrides="and"> <if test="name!=null"> name=#{name} </if> <if test="address!=null"> and address=#{address} </if> </trim> </select>

foreach,可以遍历拼接sql

<select resultType="blog"> select * from blog <where> <foreach collection="ids" item="id" open="and ( " separator="or" close=")"> id = #{id} </foreach> </where> </select> List<Blog> findBlogByList(@Param("ids")List<String> ids);

include,sql 增加mybatis的xml配置的复用性

<select resultType="blog" parameterType="map"> select * from blog <where> <include refid="ifsql"></include> </where> </select> <sql> <if test="title != null"> and title = #{title} </if> <if test="author != null"> and author= #{author} </if> </sql>

where,if 和上面的代码意义相同，只是上面的代码，在业务复杂的情况下，耦合度更低

<select resultType="blog" parameterType="map"> select * from blog <where> <if test="title != null"> and title = #{title} </if> <if test="author != null"> and author= #{author} </if> </where> </select>

建议：

先在Mysql中写出完整的SQL，再对应的去修改成为我们的动态 SQL实现通用即可！

12、缓存

这个在上面的配置文件配置了

一级缓存

默认开启的