【Mybatis】一对一与一对多的关联查询

一、一对一关联查询

背景：

表1：user包含id，username。

表2：orders包含id，user_id

需求：查询每个用户下的订单ID。

sql：

select 
u.id,
u.username,
o.id
from `user` as u
LEFT JOIN orders as o on u.id = o.user_id

方法一：通过继承，类的组合，对应于需求要查询的信息。如继承user类中再加入orders的对象

package com.test.domain;

public class UserOrders extends User {

	private orders order;

	public orders getOrder() {
		return order;
	}

	public void setOrder(orders order) {
		this.order = order;
	}

	public UserOrders() {
		super();
	}
	
	
	
}

映射文件UserOrdersMapper. 配置。注意配置文件，这里是因为发生了一个问题。如果配置文件仍然使用原来数据库的字段，就会使orders的对象填充user的ID，这是错误。我把数据库user 的ID改为uid,orders的id改为oid。然后就正确填充了。也可能是我配置错误。如果你懂的话，请留言给我

<?  version=\"1.0\" encoding=\"UTF-8\" ?>
<!-- 注意不要弄错了，配置文件是config，这个是映射mapper -->
<!DOCTYPE mapper
PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\"
\"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">
<mapper namespace=\"com.test.mapper.UserOrdersMapper\">

<resultMap type=\"com.test.domain.UserOrders\" id=\"UOlist\">
<!-- user class <==> user table  -->
<id property=\"id\" column=\"uid\" />
<result property=\"username\" column=\"username\"/>
<association property=\"order\" javaType=\"com.test.domain.orders\" >
<!-- orders table -->
<id property=\"id\" column=\"oid\"/>
<result property=\"userid\" column=\"user_id\"/>

</association>

</resultMap>
<!-- 查询用户下的订单 -->
<select id=\"queryUserOrders\" resultMap=\"UOlist\">
select 
u.uid,
u.username,
o.oid
from `user` as u
LEFT JOIN orders as o on u.uid = o.user_id
</select>

</mapper>

测试

package com.test.test;

import java.io.IOException;
import java.util.List;

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 org.junit.Test;

import com.test.domain.UserOrders;
import com.test.mapper.UserOrdersMapper;

public class testUserOrders {

	
	public static SqlSession sqlSession;
	public static UserOrdersMapper mapper;
	public static void init() throws IOException {
		SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
		SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(Resources.getResourceAsStream(\"sqlMapConfig. \"));
		
		sqlSession = sqlSessionFactory.openSession();
		
		mapper = sqlSession.getMapper(UserOrdersMapper.class);
		System.out.println(\"testVo init \");
	}
	
	@Test
	public void testQueryUserOrdersList() throws Exception {
		init();
		
		
		 List<UserOrders> list = mapper.queryUserOrders();
		 for (UserOrders userOrders : list) {
			System.out.println(userOrders + \" order:\" + userOrders.getOrder());
			//System.out.println(\"++++++);
		}
	}
}