mybatis 学习笔记

最新推荐文章于 2022-11-21 21:14:08 发布

ImpressedGoddess

最新推荐文章于 2022-11-21 21:14:08 发布

阅读量366

点赞数

分类专栏： Mybatis 文章标签： Mybatis

本文链接：https://blog.csdn.net/u011310774/article/details/47373271

版权

Mybatis 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1. <sql></sql>标签可以用来定义可重复使用的sql代码段，可以包含在其它语句中使用

<sql id="userColumns"> id,username,password </sql>
<select id="selectUser" parameterType="int" resultType="User">
    select <include refid="userColumns"/> from t_user where id = #{id}
</select>

2. <resultMap></resultMap>设置JavaBean属性与表字段的对应

<resultMap id="userResultMap" type="User">
    <id property="userId" column="user_id" />
    <result property="userName" column="user_name"/>
    <result property="password" column="hashed_password"/>
</resultMap>
<select id="selectUser" parameterType="int" resultMap="userResultMap">
    select user_id, user_name, hashed_password from t_user where id = #{id}
</select>
<!-- 如果JavaBean属性名与表字段名一致，mybatis会在幕后自动创建一个ResultMap，用来匹配属性与字段 -->
<select id="selectUser" parameterType="int" resultType="com.someapp.model.User">
    select id, name, password from t_user where id = #{id}
</select>
<!-- 如果列名没有精确匹配，你可以在列名上使用select字句的别名（一个标准的SQL特性）来匹配标签 -->
<select id="selectUser" parameterType="int" resultType="com.someapp.model.User">
    select user_id userId, user_name userName, password from t_user where id = #{id}
</select>

3. 动态sql

3.1 if

<select id="selectBlog" parameterType="String" resultType="Blog">
    select * from t_blog where id = 1
    <if test="title != null">
        and status like #{status}
    </if>
</select>

3.2 choose, when, otherwise

<select id="selectBlog" parameterType="Blog" resultType="Blog">
    select * from t_blog where id = 1
    <choose>
        <when test="title != null">
         AND title like #{title}
        </when>
        <when test="author != null and author.name != null">
        AND title like #{author.name}
        </when>
        <otherwise>
        AND featured = 1
        </otherwise>
    </choose>
</select>

3.3 where, trim, set

<select id="selectBlog" parameterType="String" resultType="Blog">
    select * from t_blog
    <where>  <!-- 如果第一个if条件不成立，第二个成立，where检查以AND或OR开头，就会省去之，以保证sql语句的正确性 -->
        <if test=”state != null”>
            state = #{state}
        </if>        
        <if test="title != null">
            and status like #{status}
        </if>
    </where>
 </select>
<trim prefix="WHERE" prefixOverrides="AND |OR ">  <！-- trim元素可以自定义要省去的关键字 -->
    …
</trim>

<update id="updateAuthorIfNecessary" parameterType="domain.blog.Author">
    update Author
    <set>  <!-- set元素会动态前置SET关键字，并且也会消除任意无关的逗号 -->
        <if test="username != null">username=#{username},</if>
        <if test="password != null">password=#{password},</if>
        <if test="email != null">email=#{email},</if>
        <if test="bio != null">bio=#{bio}</if>
    </set>
    where id=#{id}
</update>
<trim prefix="SET" suffixOverrides=",">  <!-- 也可以自定义 -->
    …
</trim>

3.4 foreach

<select id="selectPostIn" resultType="domain.blog.Post">
    SELECT * FROM POST P WHERE ID in
    <foreach item="item" index="index" collection="list"
        open="(" separator="," close=")">  <!-- open,close指定开头结尾字符串，separator在迭代的元素之间指定分隔符 -->
        #{item}
    </foreach>
</select>
<!-- foreach常用于in条件中 -->

4. 关联查询

4.1 关联一:<association></association>

<!-- 
    方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集，封装联表查询的数据(去除重复的数据)
    select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=1 -->

<select id="getClass" parameterType="int" resultMap="ClassResultMap">
    select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" column="teacher_id" javaType="_Teacher">
       <id property="id" column="t_id"/>
       <result property="name" column="t_name"/>
    </association>
</resultMap>

<!-- 
     方式二：嵌套查询：通过执行另外一个SQL 映射语句来返回预期的复杂类型
     SELECT * FROM class WHERE c_id=1;
     SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的teacher_id 的值 -->

<select id="getClass2" parameterType="int" resultMap="ClassResultMap2">
    select * from class where c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap2">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" column="teacher_id" javaType="_Teacher" select="getTeacher">
    </association>
</resultMap>
<select id="getTeacher" parameterType="int" resultType="_Teacher">
    SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>

4.2 关联多:<collection></collection>

<!--
    方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集
    SELECT * FROM class c, teacher t,student s WHERE c.teacher_id=t.t_id AND c.C_id=s.class_id AND c.c_id=1 -->

<select id="getClass3" parameterType="int" resultMap="ClassResultMap3">
    select * from class c, teacher t,student s where c.teacher_id=t.t_id and c.C_id=s.class_id and c.c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap3">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" column="teacher_id" javaType="_Teacher">
        <id property="id" column="t_id"/>
        <result property="name" column="t_name"/>
    </association>
<!-- ofType 指定students 集合中的对象类型 -->
    <collection property="students" ofType="_Student">
        <id property="id" column="s_id"/>
        <result property="name" column="s_name"/>
    </collection>
</resultMap>

<!--
    方式二：嵌套查询：通过执行另外一个SQL 映射语句来返回预期的复杂类型
    SELECT * FROM class WHERE c_id=1;
    SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的teacher_id 的值
    SELECT * FROM student WHERE class_id=1 //1 是第一个查询得到的c_id 字段的值 -->
<select id="getClass4" parameterType="int" resultMap="ClassResultMap4">
    select * from class where c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap4">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" column="teacher_id" javaType="_Teacher" select="getTeacher2">
    </association>
    <collection property="students" ofType="_Student" column="c_id" select="getStudent">
    </collection>
</resultMap>
<select id="getTeacher2" parameterType="int" resultType="_Teacher">
    SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>
<select id="getStudent" parameterType="int" resultType="_Student">
    SELECT s_id id, s_name name FROM student WHERE class_id=#{id}
</select>