表结构:
CREATE TABLE `user`(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20)
);
CREATE TABLE user_info(
id INT PRIMARY KEY AUTO_INCREMENT,
sex INT,
age INT,
user_id INT
);
实体类:
public class User{
private Integer id;
private String name;
//get/set方法略
}
public class UserInfo{
private Integer id;
private Integer sex;
private Integer age;
private User user;
//get/set方法略
}
配置文件:
方式一:使用联接查询
<select id="selectAll" resultMap="JoinResultMap">
select ui.*,u.name user_name from user_info ui, user u on ui.user_id=u.id
</select>
<resultMap type="package.UserInfo" id="JoinResultMap">
<id property="id" column="id"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
<association property="user" javaType="package.User">
<id property="id" column="user_id"/>
<result property="name" column="user_name"/>
</association>
</resultMap>
方式二:使用嵌套查询
<select id="selectAllByNest" resultMap="NestResultMap">
select * from user_info
</select>
<resultMap type="pageage.UserInfo" id="NestResultMap">
<id property="id" column="id"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
<association property="user" column="user_id" select="selectUser"/>
</resultMap>
<select id="selectUser" parameterType="int" resultType="package.User">
SELECT * FROM user WHERE id=#{id}
</select>