association联查(一对一)
使用resultMap的一个用处在于可以实现关联查询。
关联查询可以通过嵌套查询与分段查询两种方式实现。
User类
public class User {
Integer id;
String userName;
String email;
Company company;
}
Company类
public class Company {
Integer id;
String companyName;
}
嵌套查询
UserMapper.xml 关联嵌套查询,一个用户拥有一个所在公司信息。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sample.dao.UserDao">
<resultMap id="UserWithCompanyResultMap" type="User">
<id column="id" property="id"/>
<result column="username" property="userName"/>
<result column="email" property="email"/>
<!--assocication可以指定联合的JavaBean对象
property="company"指定哪个属性是联合的对象
javaType:指定这个属性对象的类型
-->
<association property="company" javaType="Company">
<id column="companyId" property="id"/>
<result column="companyName" property="companyName"/>
</association>
</resultMap>
<select id="findAllUserWithCompany_XML" resultMap="UserWithCompanyResultMap">
SELECT * FROM user u
LEFT JOIN company c ON
u.companyId = c.id;
</select>
</mapper>
分段查询
UserMapper.xml 关联分段查询,多条SQL分段查询。
使用Select指定的方法(传入column指定的这列参数的值)查出对象,并封装给property指定的属性。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sample.dao.UserDao">
<resultMap id="UserWithCompanyResultMap" type="User">
<id column="id" property="id"/>
<result column="username" property="userName"/>
<result column="email" property="email"/>
<!--assocication可以指定联合的JavaBean对象
select 表明当前属性是调用select指定的方法查询出结果
column:指定将那一列的值传递给这个方法
-->
<association property="company" select="com.sample.dao.CompanyDao.findCompanyById" column="companyId"></association>
</resultMap>
<select id="findAllUserWithCompany_XML" resultMap="UserWithCompanyResultMap">
SELECT * FROM user;
</select>
</mapper>
这里我传入的方法是CompanyDao.findCompanyById(),该方法是通过注解注入的SQL,或者用XML也是可以的。
@Select("select * from company where id = #{id}")
Company findCompanyById(Integer id);
collection联查(一对多)
一个公司Company有多个员工(用户User)。
User类
public class User {
Integer id;
String userName;
String email;
Company company;
}
Company类
public class Company {
Integer id;
String companyName;
List<User> users;
}
CompanyMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sample.dao.CompanyDao">
<resultMap id="CompanyWithUserResultMap" type="Company">
<id property="id" column="cid"/>
<result property="companyName" column="companyName"/>
<collection property="users" ofType="User">
<id property="id" column="uid"/>
<result property="userName" column="username"/>
<result property="email" column="email"/>
</collection>
</resultMap>
<select id="findCompanyWithUserById" resultMap="CompanyWithUserResultMap">
SELECT
c.id cid,
companyName,
u.id uid,
u.username,
u.email
FROM company c
LEFT JOIN user u ON c.id = u.companyId
WHERE c.id=#{id};
</select>
</mapper>