第一个简单的查询过程
这个介绍 ID result的区别
https://www.cnblogs.com/hamhog/p/3959451.html
<result column="数据库字段名" property="实体类属性" jdbcType="数据库字段类型" />
public class company {
private int id;
private String name;
private int addre;
private Addre addrep;
public class Addre {
private int id;
private String place;
private List<company> company;
结果:
<resultMap id="assocResult" type="testdo.company">
<result column="r_id" property="addrep.id"/>
<result column="r_name" property="addrep.place"/>
</resultMap>
<select id="selectDepartWithRegion" resultMap="assocResult">
select d.id,d.name,r.id r_id,r.name r_name
from s_dept d,s_region r
where d.region_id = r.id
</select>
使用有参构造器来调用语句:
<constructor>
<idArg column="id" javaType="_int"/>
<arg column="name" javaType="string"/>
</constructor>
association是用于一对一和多对一,而collection是用于一对多的关系
第一种 Association
<resultMap id="assocResult" type="testdo.company" >
<result column="id" property="id"/>
<result column="name" property="name"/>
<association property="addrep" javaType="testdo.Addre">
<id column="r_id" property="id"/>
<result property="place" column="r_name"/>
</association>
</resultMap>
在association里可以用构造器来代替ID和result
<association property="addrep" javaType="testdo.Addre">
<constructor>
<idArg column="r_id" javaType="_int"/>
<arg column="r_name" javaType="string"/>
</constructor>
<!-- <id column="r_id" property="id"/>
<result property="place" column="r_name"/>-->
</association>
两者效果一样
也可以在建一个resultMap
<resultMap type="testdo.Addre" id="regionMap">
<id column="id" property="id"/>
<result column="name" property="place"/>
</resultMap>
然后在原来的resultMap里加
<association property="addrep" resultMap="regionMap"/>
也可以达到同样的效果
更简单一种方法:
类似封装公共的部分 然后调用它:
这是公共的部分
<resultMap type="testdo.Addre" id="regionMap">
<id column="id" property="id"/>
<result column="name" property="place"/>
</resultMap>
通过两个表查询查询一个部门的信息及部门对于地区的信息:
association是用来处理多对一或一对一关系的,使用可以在这个地方使用 ,由于要以company类型输出,所以接口里应该以
List<company> 形式返回 ,这是内嵌select方式
<resultMap type="testdo.Addre" id="regionMap">
<id column="id" property="id"/>
<result column="name" property="place"/>
</resultMap>
<resultMap type="testdo.company" id="assocSelect">
<!-- id,name属性值和列名一致,可以自动映射 -->
<id property="id" column="id"/>
<result column="name" property="name"/>
<association property="addrep" column="region_id"
select="selectRegionById">
</association>
</resultMap>
<select id="selectDepart" resultMap="assocSelect">
select id,name,region_id
from s_dept
</select>
<select id="selectRegionById" resultMap="regionMap">
select id ,name
from s_region
where id = #{id}
</select>
Collection一对多的查询:
<select id="selectRegionWithDeparts" resultMap="regionMap">
select r.id,r.name,d.id d_id,d.name d_name
from s_region r,s_dept d
where r.id = d.region_id(+)
</select>
<resultMap id="assocResultc" type="testdo.company">
<id column="id" property="id"/>
<result property="name" column="name"/>
</resultMap>
<resultMap id="regionMap" type="testdo.Addre">
<id column="id" property="id"/>
<result property="place" column="name"/>
<collection property="company" resultMap="assocResultc" columnPrefix="d_"/>
</resultMap>
一对多的两表查询:
<resultMap id="collSelect" type="testdo.Addre">
<id column="id" property="id"/>
<result property="place" column="name"/>
<collection property="company" column="id"
select="selectDepartByRegionId" />
</resultMap>
<select id="selectDepartByRegionId" resultMap="assocResultc">
select id,name
from s_dept
where region_id = #{id}
</select>
<select id="selectRegion" resultMap="collSelect">
select id,name
from s_region
</select>
多对多和一对多是一样的