Mybatis进阶

2017-08-03  本文已影响97人  stutterr

基本知识

resultMap

http://blog.csdn.net/wxwzy738/article/details/24742495

一、一对一关系association

  1. association
    association通常用来映射一对一的关系,例如,有个类user,对应的实体类如下:(getter,setter方法省略)
    private String id;//主键
    private String userName;//用户姓名
    

    有个类Article,对应的实体类如下:
    private String id;//主键
    private String articleTitle;//标题
    private String articleContent;//内容
    

    当我想查询一个用户的时候,也查到他写的一篇文章应该如何写呢?在user类中加入一个属性article
    private String id;//主键
    private String userName;//用户姓名
    private Article article;//新增的文章属性
    
  2. mapper.xml 配置
    在user类的mapper.xml中这样配置
    <resultMap id="userResultMap"  type="test.mybatis.entity.User">
      <id column="id" property="id" jdbcType="VARCHAR" javaType="java.lang.String"/>
      <result column="userName" property="userName"/>
    //这里把user的id传过去
       <association property="article" column="id"                          select="test.mybatis.dao.articleMapper.selectArticleByUserId" />//test.mybatis.dao.articleMapper为命名空间
     </resultMap>
    

    同时,article类的mapper.xml
    <resultMap id="articleResultMap" type="test.mybatis.entity.Article">
      <id column="id" property="id" jdbcType="VARCHAR" javaType="java.lang.String"/>
      <result column="articleTitle" property="articleTitle" jdbcType="VARCHAR" javaType="java.lang.String"/>
     <result column="articleContent" property="articleContent" jdbcType="VARCHAR" javaType="java.lang.String"/>
     </resultMap>
      (当然,这里还有查询user表的语句,省略)
    

    继续在article的mapper.xml写下如下
     <select id="selectArticleByUserId"
     parameterType="java.lang.String"
     resultMap="ArticleResultMap" >
     select * from
     tb_article where userId=#{userId}
     </select>
    

二、一对多关系collection

 private String id;//主键
 private String userName;//用户姓名
 private List<Article> articleList;
<resultMap id="userResultMap" type="test.mybatis.entity.User">
  <id column="id" property="id" jdbcType="VARCHAR" javaType="java.lang.String"/>
  <result column="userName" property="userName" jdbcType="VARCHAR" javaType="java.lang.String"/>
//这里把user的id传过去
   <collection property="articleList" column="id"                       
            select="test.mybatis.dao.articleMapper.selectArticleListByUserId" />
 </resultMap>
    <!-- User 连接查询 方法配置 (一个问题对多个问题选项)  -->
        <resultMap type="Question" id="questionMap">
        <id column="id" property="id"/>
        <result column="display_id" property="displayId"/>
        <result column="question_description" property="description"/>
        <result column="is_deleted" property="isDeleted"/>
        <collection resultMap="optionMap" property="questionOptionList" ofType="QuestionOption"/>
    </resultMap>
    
    <resultMap type="QuestionOption" id="optionMap">
            <id column="option_id" property="id"/>
            <result column="question_id" property="questionId"/>
            <result column="option_descritpion" property="optionDescription" javaType="java.lang.String"/>
            <result column="is_correct" property="isCorrect"/>
    </resultMap>

    <select id="getQuestionById" resultMap="questionMap" parameterType="int">
        <![CDATA[
            select
            B.id as id,
            B.display_id as display_id, 
            B.description as question_description,
            B.is_deleted as is_deleted,
            P.id as option_id,
            P.question_id as question_id,
            P.option_description as option_descritpion,
            P.is_correct as is_correct
            from question B
            left outer join question_option P on (B.id = P.question_id)
            where B.id = #{id}
        ]]>
    </select>

几点注意

三、批量更新

  1. 第一种
     <update id="updateQuestionOptions" parameterType="java.util.List">
             <foreach collection="list" item="item" index="index" separator=";">
             UPDATE 
                 question_option
             SET option_description = #{item.optionDescription},is_correct = #{item.isCorrect}
                 
                     WHERE id = #{item.id}
             </foreach>
     </update>
    
    注意末尾要加号separator=";"
  2. 第二中
    <update id="deleteQuestionByIds" parameterType="java.util.List">
            UPDATE 
                question
            SET
                is_deleted = 1,
                updated_time = NOW()
            WHERE id in 
            <foreach collection="list" item="item" index="index"  open="(" separator="," close=")">
                #{item}
            </foreach>
    </update>

注意注意前后要加括号open="(" separator="," close=")"

上一篇 下一篇

猜你喜欢

热点阅读