mybatis中虽然有外键但是查询的字段就是主键表的字段,没有必要JOIN表查询

[color=green]mybatis中虽然有外键但是查询的字段就是主键表的字段,没有必要JOIN表查询[/color]

sql语句


<!-- 根据用户id查询某一个人的寻宝 -->
<select id="selectTreasureLogListByUseId" parameterType="string"
resultMap="huntTreasureLogResult">
SELECT h.id,h.userId,h.treasureId,h.huntedCount FROM
hunt_treasure_log h
WHERE userId=#{userId}
</select>


resultMap


<!-- hunt_treasure_log查询HuntTreasureLog对象时的huntTreasureLogResult -->
<resultMap type="user" id="treasureLogUserResult">
<id property="id" column="userId" />
</resultMap>

<resultMap type="huntTreasureLog" id="huntTreasureLogResult">
<id property="id" column="id" />
<result property="huntedCount" column="huntedCount" />

<association property="treasure" column="id" javaType="treasure"
resultMap="treasureResult">
</association>

<association property="userByUserId" column="id" javaType="user"
resultMap="treasureLogUserResult">
</association>

</resultMap>



值得注意的就是列名要对应统一


使用resultMap关联4个表查询



<!-- ++++++++++++宝贝交换记录exchange_record表CRUD部分++++++++++++ -->

<!-- start: exchage_record查询ExchageRecord对象时的exchangeRecordResult -->

<resultMap type="user" id="recordHostUserResult">
<id property="id" column="hostUserId" />
<result property="userName" column="hostUserName" />
<result property="headImagePath" column="hostUserHeadImg" />
</resultMap>
<resultMap type="user" id="recordFriendUserResult">
<id property="id" column="friendUserId" />
<result property="userName" column="friendUserName" />
<result property="headImagePath" column="friendUserHeadImg" />
</resultMap>
<resultMap type="chip" id="recordHostChipResult">
<id property="id" column="hostChipId" />
</resultMap>
<resultMap type="chip" id="recordFriendChipResult">
<id property="id" column="friendChipId" />
</resultMap>

<resultMap type="huntTreasureLog" id="exchangeRecordResult">
<id property="id" column="id" />
<result property="huntedCount" column="huntedCount" />

<association property="hostUserId" column="id" javaType="user"
resultMap="recordHostUserResult">
</association>

<association property="friendUserId" column="id" javaType="user"
resultMap="recordFriendUserResult">
</association>

<association property="hostChipId" column="id" javaType="chip"
resultMap="recordHostChipResult">
</association>

<association property="friendChipId" column="id" javaType="chip"
resultMap="recordFriendChipResult">
</association>

</resultMap>

<!-- end: exchage_record查询ExchageRecord对象时的exchangeRecordResult -->

<!-- 插入一条交换记录 -->
<insert id="insertExchangeRecord" parameterType="exchangeRecord">
INSERT
exchange_record
(hostUserId,hostChipId,friendUserId,friendChipId,createDate)
VALUES(#{hostUserId.id},#{hostChipId.id},#{friendUserId.id},#{friendChipId.id},NOW())
</insert>

<!-- 根据id删除一条记录 -->
<delete id="deleteExchangeRecord" parameterType="string">
DELETE FROM
exchange_record WHERE id=#{exchangeRecordId}
</delete>

<!-- 获取交换记录作为提示信息给用户 -->
<select id="selectExchangeRecordList" parameterType="string"
resultMap="exchangeRecordResult">
SELECT e.id,
hostUser.id AS hostUserId,host.headImagePath AS hostUserHeadImg,
friendUser.id AS friendUserId,friendUser.headImagePath AS friendUserHeadImg,
hostChip.id AS hostChipId,friendChip.id AS friendChipId
FROM exchange_record e
LEFT
OUTER JOIN user hostUser
ON
e.houstUserId=hostUser.id
LEFT
OUTER JOIN user friendUser
ON
e.friendUserId=friendUser.id
LEFT
OUTER JOIN chip hostChip
ON
e.hostChipId=hostChip.id
LEFT
OUTER JOIN chip friendChip
ON
e.friendChipId=friendChip.id
WHERE
friendUserId=#{friendUserId}
</select>




.
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值