一对多 - 简书

<collection column="id"
                ofType="com.boolib.model.Animationdrama"
                javaType="ArrayList"
                property="animationdrama"
                select="selectAnimationdramaById">

        <collection column="id"
                    ofType="com.boolib.model.Animationcomment"
                    javaType="ArrayList"
                    property="animationcomments"
                    select="selectAnimationcommentById">

          <collection column="id"
                      ofType="com.boolib.model.Animationreply"
                      javaType="ArrayList"
                      property="animationreplies"
                      select="selectAnimationreplyById">
          </collection>

        </collection>
    </collection>

SELECT t1. `animation_pid` as '动漫名称',t1.`id` as '当前集数',t1.`animation_count` as '当前播放次数' ,t1.`animation_url` as '播放地址 ',t2.`user_to` as '回复接收',t2.`user_from` as '回复发起',t2.`user_from_id` as 'fromid' ,
            t2.`user_to_id` as '回复接收id' ,t2.content as '回复内容',t2.time1 as '回复时间'
FROM
animationplay AS t1 ,
animationcomment AS t2 ,
animationcomment
WHERE t1.`id`=t2.`play_id` AND t1.`id`=2
ORDER BY t2.time1 desc


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.boolib.dao.AnimationplayMapper">

  <resultMap id="BaseResultMap" type="com.boolib.model.Animationplay">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="animation_pid" jdbcType="INTEGER" property="animationPid" />
    <result column="animation_count" jdbcType="INTEGER" property="animationCount" />
    <result column="animation_url" jdbcType="VARCHAR" property="animationUrl" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
  </resultMap>


  <resultMap id="BaseResultMap1" type="com.boolib.model.Animationplay">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="animation_pid" jdbcType="INTEGER" property="animationPid" />
    <result column="animation_count" jdbcType="INTEGER" property="animationCount" />
    <result column="animation_url" jdbcType="VARCHAR" property="animationUrl" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />

    <collection property="animationcomments"  column="play_id" ofType="com.boolib.model.Animationcomment">
      <id column="id" jdbcType="INTEGER" property="id" />
      <result column="play_id" jdbcType="INTEGER" property="playId" />
      <result column="time1" jdbcType="TIMESTAMP" property="time1" />
      <result column="user_to" jdbcType="VARCHAR" property="userTo" />
      <result column="user_from" jdbcType="VARCHAR" property="userFrom" />
      <result column="content" jdbcType="VARCHAR" property="content" />
      <result column="user_to_id" jdbcType="INTEGER" property="userToId" />
      <result column="user_from_id" jdbcType="INTEGER" property="userFromId" />
    </collection>
  </resultMap>


  <sql id="Base_Column_List">
      id, animation_pid, animation_count, animation_url, update_time
  </sql>

  <select id="selectAllCommentByPlay" parameterType="java.lang.Integer" resultMap="BaseResultMap1">
  SELECT
  t1.`id` ,
  t1. `animation_pid`,
  t1.`animation_count`,
  t1.`animation_url`,
  t1.`update_time`,

  t2.`id` ,
  t2.`play_id`,
  t2.time1,
  t2.`user_to`,
  t2.`user_from`,
  t2.content,
  t2.`user_to_id`,
  t2.`user_from_id`

  FROM
  `animationplay` t1 ,
  `animationcomment` t2
  WHERE
  t1.`id`=t2.`play_id` AND t1.`id`=#{id}

  ORDER BY
  t2.time1
</select>







  <select id="selectAllComments" parameterType="java.lang.Integer" resultMap="BaseResultMap1">
    SELECT
    t1.`id` ,
    t1. `animation_pid`,
    t1.`animation_count`,
    t1.`animation_url`,
    t1.`update_time`,

    t2.`id` ,
    t2.`play_id`,
    t2.time1,
    t2.`user_to`,
    t2.`user_from`,
    t2.content,
    t2.`user_to_id`,
    t2.`user_from_id`

    FROM
    `animationplay` t1 ,
    `animationcomment` t2
    WHERE
    t1.`id`=t2.`play_id`

    ORDER BY
    t2.time1
  </select>

















  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from animationplay
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from animationplay
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.boolib.model.Animationplay">
    insert into animationplay (id, animation_pid, animation_count, 
      animation_url, update_time)
    values (#{id,jdbcType=INTEGER}, #{animationPid,jdbcType=INTEGER}, #{animationCount,jdbcType=INTEGER}, 
      #{animationUrl,jdbcType=VARCHAR}, #{updateTime,jdbcType=TIMESTAMP})
  </insert>
  <insert id="insertSelective" parameterType="com.boolib.model.Animationplay">
    insert into animationplay
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="animationPid != null">
        animation_pid,
      </if>
      <if test="animationCount != null">
        animation_count,
      </if>
      <if test="animationUrl != null">
        animation_url,
      </if>
      <if test="updateTime != null">
        update_time,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=INTEGER},
      </if>
      <if test="animationPid != null">
        #{animationPid,jdbcType=INTEGER},
      </if>
      <if test="animationCount != null">
        #{animationCount,jdbcType=INTEGER},
      </if>
      <if test="animationUrl != null">
        #{animationUrl,jdbcType=VARCHAR},
      </if>
      <if test="updateTime != null">
        #{updateTime,jdbcType=TIMESTAMP},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.boolib.model.Animationplay">
    update animationplay
    <set>
      <if test="animationPid != null">
        animation_pid = #{animationPid,jdbcType=INTEGER},
      </if>
      <if test="animationCount != null">
        animation_count = #{animationCount,jdbcType=INTEGER},
      </if>
      <if test="animationUrl != null">
        animation_url = #{animationUrl,jdbcType=VARCHAR},
      </if>
      <if test="updateTime != null">
        update_time = #{updateTime,jdbcType=TIMESTAMP},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.boolib.model.Animationplay">
    update animationplay
    set animation_pid = #{animationPid,jdbcType=INTEGER},
      animation_count = #{animationCount,jdbcType=INTEGER},
      animation_url = #{animationUrl,jdbcType=VARCHAR},
      update_time = #{updateTime,jdbcType=TIMESTAMP}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

数据库结构

image.png

评论回复表

image.png

剧集表

image.png




  <resultMap id="AnimationcommentMap" type="com.boolib.model.Animationcomment">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="play_id" jdbcType="INTEGER" property="playId" />
    <result column="time1" jdbcType="TIMESTAMP" property="time1" />
    <result column="user_to" jdbcType="VARCHAR" property="userTo" />
    <result column="user_from" jdbcType="VARCHAR" property="userFrom" />
    <result column="content" jdbcType="VARCHAR" property="content" />
    <result column="user_to_id" jdbcType="INTEGER" property="userToId" />
    <result column="user_from_id" jdbcType="INTEGER" property="userFromId" />
  </resultMap>







  <resultMap id="BaseResultMap1" type="com.boolib.model.Animationplay" >
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="animation_pid" jdbcType="INTEGER" property="animationPid" />
    <result column="animation_count" jdbcType="INTEGER" property="animationCount" />
    <result column="animation_url" jdbcType="VARCHAR" property="animationUrl" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    <result column="juji" javaType="INTEGER" property="juji" />


    <collection column="id"
                ofType="com.boolib.model.Animationcomment"
                javaType="ArrayList"
                property="animationcommentList"
                select="AnimationcommentByID"
    >

    </collection>
  </resultMap>




  <select id="AnimationcommentByID" resultMap="AnimationcommentMap" parameterType="java.lang.Integer" >
    select
    t2.`id` ,
    t2.`play_id`,
    t2.time1,
    t2.`user_to`,
    t2.`user_from`,
    t2.content,
    t2.`user_to_id`,
    t2.`user_from_id`

    from `animationcomment` t2
    where t2.`play_id`=#{id}
    ORDER BY
    t2.time1
  </select>

  <select id="selectAllComments" parameterType="java.lang.Integer" resultMap="BaseResultMap1">
    SELECT
    t1.`id` ,
    t1. `animation_pid`,
    t1.`animation_count`,
    t1.`animation_url`,
    t1.`update_time`,
    t1.`juji`

    FROM
    `animationplay` t1

  </select>