mybatis的3种关联映射查询

一对一查询： 

查询所有订单和用户信息。

一个订单信息只会是一个人下的订单，所以从查询订单信息 出发关联查询用户信息为一对一查询。

一对多查询：

查询所有订单信息及订单下的订单明细信息，订单信息与订单明细为一对多关系。

多对多查询：

查询所有用户信息，关联查询订单及订单明细信息 ，订单明细信息中关联查询商品信息。

一个用户对应多个订单，一个订单对应多个明细，一个订单明细对应一个商品。

 

返回类型可以用 resultType，也可以用resultMap，resultType是直接表示返回类型的，而resultMap则是对外部ResultMap的引用！

 

--用一个小案例来对上面所说的进行实际操作--

项目目录：

首先，创建4个实体类，对应四张表

---

用户表： 

---

商品表：

---

订单表：

---

 

订单详情表：

---

 

一对一关联映射查询：

OrderMapper.xml 

<resultMap type="orders" id="orderAndUser">
		<id column="oids" property="id"></id>
		<result column="orderid" property="orderid"/>
		<result column="createtime" property="createtime"/>
		<result column="note" property="note"/>
		<association property="user" javaType="user">
			<id column="uids" property="id"/>
			<result column="username" property="username"/>
			<result column="birthday" property="birthday"/>
			<result column="address" property="address"/>
		</association>
</resultMap>
<select id="selectOrderAndUser" resultMap="orderAndUser">
		select o.id oids,o.orderid,o.createtime,o.note,u.id             
               uids,u.username,u.birthday,u.address
		from orders o,t_user u
		where o.userid=u.id
</select>

Test:

public class OrderTest {
	public static void main(String[] args) throws IOException {
		InputStream inputStream = Resources.getResourceAsStream("mybatis/mybatis-config.xml");
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
		SqlSession session = factory.openSession();
		OrdersMapper orderMapper = session.getMapper(mapper.OrdersMapper.class);
		
		List<Orders> orders = orderMapper.selectOrderAndUser();
		for (Orders string : orders) {
			System.out.println(string.toString());
		}
		
	}
}

查询后得到的内容：

一对多关联映射查询：

OrderMapper.xml

    <resultMap type="orders" id="orderAndOrdersdetail">
		<id column="oids" property="id"></id>
		<result column="orderid" property="orderid"/>
		<result column="ocreatetime" property="createtime"/>
		<result column="note" property="note"/>
		<association property="user" javaType="user">
			<id column="uids" property="id"/>
			<result column="username" property="username"/>
			<result column="birthday" property="birthday"/>
			<result column="address" property="address"/>
		</association>
		<collection property="ordersdetail" ofType="OrdersDetail">
			<id column="odid" property="id"></id>
			<result column="itemsnum" property="itemsnum"/>
			<association property="items" javaType="items">
				<id column="oids" property="id"/>
				<result column="name" property="name"/>
				<result column="price" property="price"/>
				<result column="pic" property="pic"/>
				<result column="icreatetime" property="createtime"/>
				<result column="detail" property="detail"/>
			</association>
		</collection>
	</resultMap>
	<select id="selectOrdersAndOrdersDetail" resultMap="orderAndOrdersdetail">
		select o.id oids,o.orderid,o.createtime ocreatetime,o.note,
		       u.id uids,u.username,u.birthday,u.address,
		       od.id odid,od.itemsid,od.itemsnum,
		       i.id iid,i.name,i.price,i.pic,i.createtime icreatetime,i.detail
		from orders o,t_user u,orderdetail od,items i
		where o.userid=u.id and o.id=od.ordersid and i.id=od.itemsid
	</select>

Test:

public static void main(String[] args) throws IOException {
		InputStream inputStream = Resources.getResourceAsStream("mybatis/mybatis-config.xml");
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
		SqlSession session = factory.openSession();
		OrdersMapper orderMapper = session.getMapper(mapper.OrdersMapper.class);
		
		List<Orders> orders = orderMapper.selectOrdersAndOrdersDetail();
		for (Orders string : orders) {
			System.out.println(string.toString());
		}
		
	}

查询后得到的内容：

多对多关联映射查询：

UserMapper.xml

<resultMap type="user" id="userAndOrdersAndOrderDetailAndItems">
		<id column="oids" property="id"/>
		<result column="username" property="username"/>
		<result column="birthday" property="birthday"/>
		<result column="address" property="address"/>
		<collection property="orders" ofType="orders">
			<id column="oids" property="id"></id>
			<result column="orderid" property="orderid"/>
			<result column="ocreatetime" property="createtime"/>
			<result column="note" property="note"/>
			<collection property="ordersdetail" ofType="OrdersDetail">
			<id column="odid" property="id"></id>
			<result column="itemsnum" property="itemsnum"/>
			<association property="items" javaType="items">
				<id column="oids" property="id"/>
				<result column="name" property="name"/>
				<result column="price" property="price"/>
				<result column="pic" property="pic"/>
				<result column="icreatetime" property="createtime"/>
				<result column="detail" property="detail"/>
			</association>
		</collection>
		</collection>
	</resultMap>
	<select id="selectUserAndOrdersAndOrderDetailAndItems" resultMap="userAndOrdersAndOrderDetailAndItems">
		select o.id oids,o.orderid,o.createtime ocreatetime,o.note,
		       u.id,u.username,u.birthday,u.address,
		       od.id odid,od.itemsid,od.itemsnum,
		       i.id iid,i.name,i.price,i.pic,i.createtime icreatetime,i.detail
		from orders o,t_user u,orderdetail od,items i
		where o.userid=u.id and o.id=od.ordersid and i.id=od.itemsid
	</select>

 Test:

public static void main(String[] args) throws IOException {
		InputStream inputStream = Resources.getResourceAsStream("mybatis/mybatis-config.xml");
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
		SqlSession session = factory.openSession();
		UserMapper userMapper = session.getMapper(mapper.UserMapper.class);
		
		List<User> user = userMapper.selectUserAndOrdersAndOrderDetailAndItems();
		for (User user2 : user) {
			System.out.println(user2.toString());
		}
	}

查询后得到的内容：