MyBatis笔记(动态Sql语句、多表查询)

目录

动态Sql语句

用法

标签

dao映射配置

注解

标签

dao映射配置

标签

dao映射配置

标签

dao映射配置

简写Sql片段

定义代码

引用代码

多表查询

一对一查询

方法一

方法二

一对多查询

方法二

多对多查询

注解

总结

---

动态Sql语句

用法

用标签表达类似于Java中的if、where等语句

如果注解使用，则是xml配置文件中的sql语句拼接到"<script></script>"中

<if>标签

dao映射配置

• test：判定条件，其中username等都是parameterType里的user中的
• 标签内填通过了之后要拼接的条件

<select id="findByUser" resultType="user" parameterType="user">
 select * from user where 1=1
 <if test="username != null and username != '' ">
 and username like "%${username}%"
 </if>
 <if test="address != null">
 and address like #{address}
 </if>
</select>

注解

@Select("<script>" +
	" select * from user where 1=1\n" +
	" <if test=\"username != null and username != '' \">\n" +
	" and username like \"%${username}%\"\n" +
	" </if>\n" +
	" <if test=\"address != null\">\n" +
	" and address like #{address}\n" +
	" </if>"
	+"</script>")
	List<User> findByUser(User user);

<where>标签

dao映射配置

• where标签会自动识别，删除多余的and，但是and要放在条件前面

<select id="findByUser" resultType="user" parameterType="user">
 select * from user
 <where>
 <if test="username != null and username != '' ">
 and username like #{username}
 </if>
 <if test="address != null">
 and address like #{address}
</if>
 </where>
</select>

<set>标签

dao映射配置

• 同理<where>标签，但是逗号要放在条件后面

<update id="update" parameterType="User">
        update user
        <set>
            <if test="username!=null">
                username = #{username},
            </if>
            <if test="birthday!=null">
                birthday = #{birthday},
            </if>
            <if test="sex!=null">
                sex = #{sex},
            </if>
            <if test="address!=null">
                address = #{address},
            </if>
        </set>
        where id = #{id}
    </update>

<foreach>标签

dao映射配置

collection：要遍历的集合类型，常见的有“list”和“array”

open：在遍历前拼接的内容

iteam：每次遍历得到的元素

separator：分割符

close：在遍历结束后拼接的内容

<select id="findIdIn" parameterType="User" resultType="User">
        select last_insert_id()
        <foreach collection="list" open="where id in (" item="aid" separator="," close=")">
            #{aid}
        </foreach>
    </select>

简写Sql片段

定义代码

<sql id="defaultSql">
 select * from user
</sql>

引用代码

<select id="findAll" resultType="user">
 <include refid="defaultSql"></include><!--引用代码片段-->
</select>
<!-- 根据 id 查询 -->
<select id="findById" resultType="user" parameterType="int"> <include refid="defaultSql"></include>
 where id = #{uid}
</select>

多表查询

一对一查询

方法一

• 直接联合查询出所有数据（效率低）

<resultMap id="AccountUser" type="account">
        <id column="aid" property="id"/>
        <result column="money" property="money"/>
        <association property="user">
            <id column="uid" property="id"/>
            <result column="username" property="username"/>
            <result column="birthday" property="birthday"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </association>
    </resultMap>
    <select id="findById" parameterType="int" resultMap="AccountUser">
        select a.id aid, money, uid, username, birthday, sex, address from `account` a join user u on a.uid = u.id where a.id = #{id};
    </select>

方法二

• 懒加载，等要用到这个对象时才去加载（利用外键查询另一张表）
• fetchType：lazy懒加载 defult默认 eager立即加载

<resultMap id="lazyLoadUserMap" type="account">
        <id column="id" property="id"/>
        <result column="money" property="money"/>
        <association property="user"  select="com.zzxx.dao.UserDao.findById" column="uid" fetchType="lazy"/>
    </resultMap>
    <select id="findById" parameterType="int" resultMap="lazyLoadUserMap">
        select * from account where id = #{id}
    </select>

<!--com.zzxx.dao.UserDao.findById-->
<select id="findById" parameterType="int" resultMap="lazyLoadAccountsMap" >
        select * from user where id = #{id}
    </select>

如果用懒加载，编译运行时拿到的Account对象的User值为空，当要使用到User时，再去数据库中查（效率高、避免死循环）

一对多查询

同理一对一，将association改成collection

方法二

• 懒加载

<resultMap id="lazyLoadAccountsMap" type="user">
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="address" property="address"/>
        <result column="birthday" property="birthday"/>
        <result column="sex" property="sex"/>
        <collection property="accounts" ofType="account" select="com.zzxx.dao.AccountDao.findByUid" column="id" fetchType="lazy"/>
        <collection property="position" ofType="role" select="com.zzxx.dao.RoleDao.findByUid" column="id" fetchType="lazy"/>
    </resultMap>
    <select id="findById" parameterType="int" resultMap="lazyLoadAccountsMap" >
        select * from user where id = #{id}
    </select>



<!--com.zzxx.dao.AccountDao.findByUid-->
<resultMap id="lazyLoadUserMap" type="account">
        <id column="id" property="id"/>
        <result column="money" property="money"/>
        <association property="user"  select="com.zzxx.dao.UserDao.findById" column="uid" fetchType="lazy"/>
    </resultMap>
    <select id="findById" parameterType="int" resultMap="lazyLoadUserMap">
        select * from account where id = #{id}
    </select>

<!--com.zzxx.dao.RoleDao.findByUid-->
<resultMap id="RoleUser2" type="role">
        <id column="id" property="id"/>
        <result column="role_name" property="name"/>
        <result column="role_desc" property="desc"/>
    </resultMap>
<select id="findByUid" parameterType="int" resultMap="RoleUser2">
        select * from role where id in (select rid from user_role where uid = #{uid})
    </select>

多对多查询

同理一对多，一般需要中间表

注解

• @Results：存放Result的集合
• @Result：id是否主键  column数据库中的字段名  property对象中对应的参数名
• 当加了one = @One或者many = @Many后，column代表要将什么参数传递过去（数据库中的字段名），property代表要将返回值赋值到什么参数里
• @ResultMap：引用@Results的id值，代表使用这个注解

public interface UserDao {
	@Results(id = "userMap", value = {
	@Result(id = true, column = "id", property = "id"),
	@Result(column = "username", property = "username"),
	@Result(column = "birthday", property = "birthday"),
	@Result(column = "sex", property = "sex"),
	@Result(column = "address", property = "address"),
	@Result(column = "id", property = "accounts", many = @Many(select = "com.zzxx.dao.AccountDao.findByUid", fetchType = FetchType.LAZY)),
	@Result(column = "id", property = "position", many = @Many(select = "com.zzxx.dao.RoleDao.findByUid", fetchType = FetchType.LAZY))
	})
	@Select("select * from user where id = #{id}")
	User findById(int id);

	@ResultMap("userMap")
	@Select("select * from user where id in (select uid from user_role where rid = #{rid})")
	List<User> findByRid(int rid);
	
}

总结

动态sql语句要在注解内使用，要在拼接到<script></script>中

懒加载指的是在使用到这个参数时才去数据库查询，这样做效率高，但是之后的二次查询会比直接查询要费时

mybatis的一级缓存在SqlSession对象中，当执行了DML语句后会清空一级缓存，一级缓存默认开启；二级缓存在SqlSessionFactory中，当SqlSession对象被关闭时存入，任何一个SqlSession对象执行DML语句时，二级缓存都会清空，二级缓存默认关闭