mapper.xml映射关系
ResultMap : 结果集映射配置,colume可以来源于一张表或者多张表,而ResultMap的type必须是一个包含完整属性的实体类。
extends : 新的ResultMap 继承 已有的 ResultMap 属性,类似java继承关系,通常本ResultMap 需要添加其他属性,成为不同集合。
id: 数据库id
result : property 类属性 ,column 数据库字段, javaType java类属性,jdbcType 数据库字段类型, typeHandler 类型转换器(可自定义)
select : 本页抽出一个结果集,可以是List,HashMap,HashSet ; 也可以调用其他Mapper的方法,注意引用时填写正确的Mapper路径名称。
一对一用association
一对多用collection
<resultMap type="com.camelot.core.domain.CmsConfig" id="CmsConfigResultMap" extends="ABCResultMap">
<id property="id" column="config_id"/>
<result property="contextPath" column="context_path"/>
<result property="port" column="port"/>
<association property="m_markConfig" column="config_id" select="getMarkConfigByCfgId"/>
<association property="m_markConfig" column="config_id" select="com.camelot.mybatis.dao.core.CmsSiteDAO.findById"/>
<collection property="registerItems" column="config_id" select="findRegisterItemsByCfgId"/>
</resultMap>
<select id="getCmsConfigBySiteId" resultMap="CmsConfigResultMap" >
select
*
from
jc_config
where
config_id = #{cfgId}
</select>
<resultMap type="com.camelot.core.domain.MarkConfig" id="MarkConfigResultMap">
<result property="on" column="mark_on"/>
<result property="minWidth" column="mark_width"/>
<result property="minHeight" column="mark_height"/>
<result property="imagePath" column="mark_image"/>
<result property="content" column="mark_content"/>
<result property="size" column="mark_size"/>
<result property="color" column="mark_color"/>
<result property="alpha" column="mark_alpha"/>
<result property="pos" column="mark_position"/>
<result property="offsetX" column="mark_offset_x"/>
<result property="offsetY" column="mark_offset_y"/>
</resultMap>
<select id="getMarkConfigByCfgId" resultMap="MarkConfigResultMap">
SELECT
mark_on,
mark_width,
mark_height,
mark_image,
mark_content,
mark_size,
mark_color,
mark_alpha,
mark_position,
mark_offset_x,
mark_offset_y
FROM
jc_config
where
config_id = #{cfgId}
</select>
<resultMap type="java.util.HashMap" id="CmsConfigAttrResultMap">
<result property="name" column="attr_name"/>
<result property="value" column="attr_value"/>
</resultMap>
<select id="findCmsConfigAttrByCfgId" resultMap="CmsConfigAttrResultMap" >
select
attr_name,attr_value
from
jc_config_attr
where
config_id = #{cfgId}
</select>
若UserMenu类中有User类做属性 private User user,那么mapper.xml中用方式 #{user.id} 获取user的属性与 user_id 对应
<insert id="save" parameterType="com.camelot.cms.domain.assist.CmsUserMenu">
insert into jc_user_menu (menu_name,menu_url,priority,user_id)
values (#{name},#{url},#{priority},#{user.id})
</insert>
追加1:公共查询字段的sql 、 以及公共条件判断的sql 可以预封装,在需要的地方直接 <include refid="***"> 引用,【试用于多处调用的sql,相比select * 更直观易读】
<!-- 查询字段公共sql -->
<sql id="selectAllColumns">
<![CDATA[
SELECT
friendlink_id,
site_id,
friendlinkctg_id,
site_name,
domain,
logo,
email,
description,
views,
is_enabled,
priority
FROM jc_friendlink record
]]>
</sql>
<sql id="where_fragement">
where 1=1
<if test="entity!=null">
<if test = "entity.friendlinkId != null and entity.friendlinkId != ''">
<![CDATA[ and record.friendlink_id = #{entity.friendlinkId} ]]>
</if>
<if test = "entity.siteId != null and entity.siteId != ''">
<![CDATA[ and record.site_id = #{entity.siteId} ]]>
</if>
<if test = "entity.friendlinkctgId != null and entity.friendlinkctgId != ''">
<![CDATA[ and record.friendlinkctg_id = #{entity.friendlinkctgId} ]]>
</if>
<if test = "entity.siteName != null and entity.siteName != ''">
<![CDATA[ and record.site_name = #{entity.siteName} ]]>
</if>
<if test = "entity.domain != null and entity.domain != ''">
<![CDATA[ and record.domain = #{entity.domain} ]]>
</if>
<if test = "entity.logo != null and entity.logo != ''">
<![CDATA[ and record.logo = #{entity.logo} ]]>
</if>
<if test = "entity.email != null and entity.email != ''">
<![CDATA[ and record.email = #{entity.email} ]]>
</if>
<if test = "entity.description != null and entity.description != ''">
<![CDATA[ and record.description = #{entity.description} ]]>
</if>
<if test = "entity.views != null and entity.views != ''">
<![CDATA[ and record.views = #{entity.views} ]]>
</if>
<if test = "entity.isEnabled != null and entity.isEnabled != ''">
<![CDATA[ and record.is_enabled = #{entity.isEnabled} ]]>
</if>
<if test = "entity.priority != null and entity.priority != ''">
<![CDATA[ and record.priority = #{entity.priority} ]]>
</if>
</if>
</sql>
<!-- 分页查询数据集合-->
<select id="queryJcFriendlinkDTOListByPage" resultMap="jcFriendlinkDTOMapper" >
<include refid="selectAllColumns"/>
<include refid="where_fragement" />
<if test="pager!=null">
<include refid="pagination_tail" />
</if>
</select>