MyBatis框架映射 一对一与一对多

目录

一、数据准备

二、一对多

三、一对一

---

一、数据准备

创建如下两张表并添加数据：

--user_info表
create table user_info(
      id int(2) primary key,
      user_name varchar(12) unique,
      password varchar(15) not null,
      real_name varchar(8) not null,
      age int(3)
);

--address表
create table address(
      id int(2) primary key,
      user_id int(2) not null,
      real_name varchar(8),
      mobile char(11),
      address varchar(150)
);

insert into user_info values(1,'浅唱灬幸福','8912@321','王晓明',12);
insert into address values(1,1,'王小明','15516472282','山西太原');
insert into address values(2,1,'王鑫','18404905139','山西大同');
insert into address values(3,1,'任建','15333021730','山西晋城');

insert into user_info values(2,'ぅ浮生若梦〤','56701wz','王楠',36);
insert into address values(4,2,'王楠','15010303314','北京海淀');
insert into address values(5,2,'赵婕','18435224278','山西长治');

insert into user_info values(3,'街角の风铃','27w4921','李晓飞',9);
insert into address values(6,6,'刘倩','13159775555','吉林长春');

其中一个用户可能对应多个收货地址： 

二、一对多

当通过某一用户的id查询该用户对应的多个收货地址的信息时，首先在UserInfo封装类中添加存储收货信息的list集合：

package club.affengkuang.vo;

import java.util.ArrayList;
import java.util.List;

public class UserInfo {

	private int id;
	private int age;
	private String userName;
	private String password;
	private String realName;
	private List<Address> list = new ArrayList<Address>();
	public List<Address> getList() {
		return list;
	}
	public void setList(List<Address> list) {
		this.list = list;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getRealName() {
		return realName;
	}
	public void setRealName(String realName) {
		this.realName = realName;
	}
}

在dao层接口中创建get用于查询的get方法，通过id查询，返回值类型为UserInfo封装类：

package club.affengkuang.userinfo.dao;

import org.apache.ibatis.annotations.Param;

import club.affengkuang.vo.UserInfo;

public interface IUserInfoDao {

	UserInfo get(@Param("id")int id);
}

SQL语句：由于两个表内id、real_name字段都重名，所以在多表查询时要取别名；user_info是主表所以使用左外连接；

多表查询见博客： MySQL多表查询

select 
ui.id ui_id,user_name userName,password,ui.real_name ui_real_name,age,
addr.id addr_id,user_id,mobile,address
from user_info ui
LEFT JOIN address addr on user_id=ui.id
where ui.id = #{id}

配置XML映射文件：

由于UserInfo封装类中的属性有集合，所以不能再简单的使用resultType属性了，而是使用resultMap子标签，type属性是返回值类型，其中：

id标签代表着主键，也就是SQL语句查询出来的ui_id值，result标签代表表内其余的数据项；

collocation标签里面是从address表中查询出来的数据，也就是要赋值给UserInfo封装类中list集合中的数据，property属性是list集合的属性名，ofType是集合的泛型；

其中标签中的column是从表中查询出来的字段名，property是封装类中相对的属性名；

并在select标签中通过resultMap属性通过resultMap标签的id将其获取；

<?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">
<!-- namespace:命名空间; -->
<mapper namespace="club.affengkuang.userinfo.dao.IUserInfoDao">
	<resultMap type="club.affengkuang.vo.UserInfo" id="ui">
		<id column="ui_id" property="id"/>
		<result column="user_name" property="userName"/>
		<result column="password" property="password"/>
		<result column="ui_real_name" property="realName"/>
		<result column="age" property="age"/>
		<collection property="list" ofType="club.affengkuang.vo.Address">
			<id column="addr_id" property="id"/>
			<result column="user_id" property="userId"/>
			<result column="mobile" property="mobile"/>
			<result column="address" property="address"/>
		</collection>
	</resultMap>
	
	<select id="get" resultMap="ui">
		select 
		ui.id ui_id,user_name userName,password,ui.real_name ui_real_name,age,
		addr.id addr_id,user_id,mobile,address
		from user_info ui
		LEFT JOIN address addr on user_id=ui.id
		where ui.id = #{id}
	</select>
	
</mapper>

测试类通过某一用户的id查询该用户对应的多个收货地址的信息：

package club.affengkuang.test;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import club.affengkuang.userinfo.dao.IUserInfoDao;
import club.affengkuang.vo.UserInfo;

public class Test {

	public static void main(String[] args) {
		try {
			InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
			SqlSession sqlSession = sqlSessionFactory.openSession();
			IUserInfoDao userInfoDao = sqlSession.getMapper(IUserInfoDao.class);
			System.out.println(userInfoDao.get(1).getList().size());
			sqlSession.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

三、一对一

首先将address表内的数据处理到与user_info中的用户一对一：

将UserInfo封装类中的list集合改为Address类并生成相应get和set方法：

private Address address = new Address();

在XML映射文件中只需将collocation标签改为association标签即可：

<?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">
<!-- namespace:命名空间; -->
<mapper namespace="club.affengkuang.userinfo.dao.IUserInfoDao">
	<resultMap type="club.affengkuang.vo.UserInfo" id="ui">
		<id column="ui_id" property="id"/>
		<result column="user_name" property="userName"/>
		<result column="password" property="password"/>
		<result column="ui_real_name" property="realName"/>
		<result column="age" property="age"/>
		<association property="address" javaType="club.affengkuang.vo.Address">
			<id column="addr_id" property="id"/>
			<result column="user_id" property="userId"/>
			<result column="mobile" property="mobile"/>
			<result column="address" property="address"/>
		</association>
	</resultMap>
	
	<select id="get" resultMap="ui">
		select 
		ui.id ui_id,user_name userName,password,ui.real_name ui_real_name,age,
		addr.id addr_id,user_id,mobile,address
		from user_info ui
		LEFT JOIN address addr on user_id=ui.id
		where ui.id = #{id}
	</select>
	
</mapper>