mybatis的3种关联映射查询

快来打我* 2023-05-31 12:19 10阅读 0赞

**一对一查询： **

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

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

**一对多查询：**

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

**多对多查询：**

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

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

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

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

项目目录：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9qeF8_size_16_color_FFFFFF_t_70][]

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

--------------------

用户表：![20191024234044784.png][]

--------------------

商品表：![20191024234114485.png][]

--------------------

订单表：![20191024234132470.png][]

--------------------

订单详情表：![20191024234159650.png][]

--------------------

# 一对一关联映射查询： #

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());
    		}
    		
    	}
    }

查询后得到的内容：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9qeF8_size_16_color_FFFFFF_t_70 1][]

# 一对多关联映射查询： #

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());
    		}
    		
    	}

查询后得到的内容：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9qeF8_size_16_color_FFFFFF_t_70 2][]

# 多对多关联映射查询： #

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());
    		}
    	}

查询后得到的内容：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9qeF8_size_16_color_FFFFFF_t_70 3][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9qeF8_size_16_color_FFFFFF_t_70]: /images/20230531/bcc83eeb77bc41a2a9045be7bd94d9c0.png
[20191024234044784.png]: /images/20230531/a1d875eb87f942efbeec16a9a9c5bd91.png
[20191024234114485.png]: /images/20230531/c6f655ba097b45d4902230983e734b84.png
[20191024234132470.png]: /images/20230531/86dde09a367a470a92839484e2509548.png
[20191024234159650.png]: /images/20230531/91017f356230476f83e4f76ea5e58b81.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9qeF8_size_16_color_FFFFFF_t_70 1]: /images/20230531/31e2cc8defd34e5f948d641091a30b09.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9qeF8_size_16_color_FFFFFF_t_70 2]: /images/20230531/fa590db269ec49eaa774d4ee0f394abb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9qeF8_size_16_color_FFFFFF_t_70 3]: /images/20230531/e5cd231ece4e4d3ba75cebd5696f1eae.png