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

最新推荐文章于 2022-04-27 21:00:15 发布

ocaicai

最新推荐文章于 2022-04-27 21:00:15 发布

阅读量306

点赞数

分类专栏：数据库文章标签： mybatis xml sql

本文链接：https://blog.csdn.net/ocaicai/article/details/84054961

版权

数据库专栏收录该内容

17 篇文章 0 订阅

订阅专栏

[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>