mybatis一对多两种mapper写法
第一种
<resultMap type="com.example.demo.model.TuserModel" id="extendMapper">
<id column="id" property="id" />
<result column="user_name" property="userName" />
<result column="nick_name" property="nickName" />
<result column="avatar" property="avatar" />
<result column="email" property="email" />
<result column="signature" property="signature" />
<result column="create_time" property="createTime" />
<result column="update_time" property="updateTime" />
<result column="del_flag" property="delFlag" />
<collection property="tpluginModels" ofType="com.example.demo.model.TpluginModel"
column="id">
<id column="pid" property="id" />
<result column="user_id" property="userId" />
<result column="name" property="name" />
<result column="icon" property="icon" />
<result column="vsersion" property="vsersion" />
<result column="tags" property="tags" />
<result column="description" property="description" />
<result column="bcreate_time" property="createTime" />
<result column="bupdate_time" property="updateTime" />
<result column="del_flag" property="delFlag" />
</collection>
</resultMap>
sql语句用联表查询
u.*,p.id
as
pid,p.user_id,p.name,p.icon,p.vsersion,p.tags,p.description,p.create_time
as bcreate_time,p.update_time as bupdate_time,p.del_flag from t_user u
LEFT
JOIN t_plugin p ON u.id=p.user_id and u.del_flag=0 and
p.del_flag=0 WHERE
u.user_name LIKE CONCAT('%',#{name},'%') OR
u.nick_name LIKE
CONCAT('%',#{name},'%')
第二种
<resultMap type="com.example.demo.model.TuserModel" id="extendMapper">
<id column="id" property="id" />
<result column="user_name" property="userName" />
<result column="nick_name" property="nickName" />
<result column="avatar" property="avatar" />
<result column="email" property="email" />
<result column="signature" property="signature" />
<result column="create_time" property="createTime" />
<result column="update_time" property="updateTime" />
<result column="del_flag" property="delFlag" />
<collection property="tpluginModels" column="id" ofType="com.example.demo.model.TpluginModel"
select="pluginByUid" /> //column='id' 为关联查询所需条件
</resultMap>
sql语句使用两个sql语句返回结果
<select id="selectTuserBynameOrNick" resultMap="extendMapper"> SELECT
* FROM t_user WHERE del_flag = 0 AND ( user_name LIKE CONCAT( '%', #{name},'%')
OR nick_name LIKE CONCAT( '%', #{name},'%')) </select>
//下个sql语句依赖上个
<select id="pluginByUid" resultType="com.example.demo.model.TpluginModel"> SELECT id,user_id as
userId,name,icon,vsersion,tags,description,
create_time as createTime ,update_time as updateTime ,del_flag as delFlag
FROM t_plugin WHERE del_flag = 0 AND user_id = #{id}
</select>