Mybatis collection Multi-condition query implementation method, mybatiscollection
Implementation of mybatis collection Multi-condition Query
Preface:
The business needs to return Nested Sets through mybatis query. It is too troublesome to query multiple times. Solve the problem with the built-in advanced query. below is the code, which has passed the test.
To put it bluntly, a primary query result set is nested with a subquery result set, which can be multiple subqueries. The conditions of each subquery are obtained from the primary query result set, the return values are defined separately. The property of the collection tag is the name of the Set in the main query. If there are multiple sets, write a collection, and column is the subquery parameter. A single parameter is directly written to the main query and returned results, for example, write user_id directly. If the database field is enclosed by multiple conditions, for example, {userId = user_id, theme = theme}, and then write "java. util. map ", multi-condition query seems to be available only after mybatis3.0. I have not verified the multi-condition query. ofType is the object in the Set, and select corresponds to the following statement.
Implementation Code:
<resultMap id="BaseResultMap" type="web.model.UserMessage" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="user_id" property="userId" jdbcType="INTEGER" /> <result column="theme" property="theme" jdbcType="VARCHAR" /> <result column="status" property="status" jdbcType="INTEGER" /> <result column="theme_time" property="themeTime" jdbcType="TIMESTAMP" /> <result column="create_time" property="createTime" jdbcType="TIMESTAMP" /> <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" /> <result column="yn" property="yn" jdbcType="INTEGER" /> </resultMap> <resultMap id="BaseVoResultMap" type="web.model.vo.UserMessageVo" extends="BaseResultMap"> <collection property="userMessageDetailList" column="{userId=user_id,theme=theme}" javaType="java.util.ArrayList" ofType="web.model.UserMessageDetail" select="selectUserMessageDetailById"/> </resultMap> <resultMap id="BaseDetailResultMap" type="web.model.UserMessageDetail" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="user_message_id" property="userMessageId" jdbcType="INTEGER" /> <result column="sponsor_id" property="sponsorId" jdbcType="INTEGER" /> <result column="user_id" property="userId" jdbcType="INTEGER" /> <result column="user_type" property="userType" jdbcType="INTEGER" /> <result column="provider_id" property="providerId" jdbcType="INTEGER" /> <result column="message" property="message" jdbcType="VARCHAR" /> <result column="status" property="status" jdbcType="INTEGER" /> <result column="message_time" property="messageTime" jdbcType="TIMESTAMP" /> <result column="create_time" property="createTime" jdbcType="TIMESTAMP" /> <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" /> <result column="yn" property="yn" jdbcType="INTEGER" /> </resultMap> <select id="selectUserMessageById" resultMap="BaseVoResultMap" parameterType="java.lang.Integer"> select * from user_message where user_id = #{userId,jdbcType=INTEGER} </select> <select id="selectUserMessageDetailById" resultMap="BaseDetailResultMap" parameterType="java.util.Map"> select * from user_message_detail where user_id = #{userId,jdbcType=INTEGER} and message = #{theme,jdbcType=VARCHAR} </select>
SelectUserMessageById. This is the primary query. VO only has one more private List <UserMessageDetail> userMessageDetailList,
The following is the returned value structure.
If you have any questions, please leave a message or go to the community on this site for discussion. Thank you for reading this article. Thank you for your support!