关联查询
改造User实体类
public class User implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
private String username;
private String sex;
private Date birthday;
private String address;
//一对多
private List<Orders> ordersList;
。。。省略了get/set
}
改造Orders实体类
public class Orders implements Serializable{
/**
*
*/
private static final long serialVersionUID = 1L;
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
//一对一
private User user;
...省略了get/set
}
一对一
编写Mapper.xml
<!--
一对一映射
这里的一对一要使用的不是基本类型,这里需要手动映射类型,这里使用的是resultMaps
type 是要映射的类型 也就是这里的orders这个类
id 就是select中的resultMap的对应的值
-->
<resultMap type="Orders" id="order">
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<!--
一对一的使用标签
property是orders中的User声明的那个名字 private User user;
javaType是user对应的java类型
-->
<association property="user" javaType="User">
<!-- id:声明主键,表示user_id是关联查询对象的唯一标识 -->
<id column="user_id" property="id"/>
<result column="username" property="username"/>
</association>
</resultMap>
<select id="getOrdersList" resultMap="order">
select o.id,o.user_id,o.number,o.createtime,o.note,u.username from orders o left join user u on o.user_id = u.id;
</select>
编写mapper接口
测试代码
//一对一
@Test
public void testGetOrdersList() throws Exception {
String res = "SqlMapConfig.xml";
InputStream inputSteam = Resources.getResourceAsStream(res);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputSteam);
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
List<Orders> ordersList = mapper.getOrdersList();
for (Orders orders : ordersList) {
System.out.println(orders);
System.out.println(orders.getUser());
}
}
测试结果
一对多
编写mapper接口
编写mapper.xml
<!--
一对多的映射
-->
<resultMap type="User" id="user">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!--
一对多映射
property 这里是User类中的List的声明 ofType这里是List中的泛型类
-->
<collection property="ordersList" ofType="orders">
<id column="id" property="id"/>
<result column="number" property="number"/>
<result column="user_id" property="userId"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
</collection>
</resultMap>
<select id="getUserList" resultMap="user">
select
o.id,o.user_id,o.number,o.createtime,o.note,
u.username,u.birthday,u.sex,u.address
from user u left join orders o on o.user_id = u.id;
</select>
测试方法
//一对多
@Test
public void testGetUserList() throws Exception {
String res = "SqlMapConfig.xml";
InputStream inputSteam = Resources.getResourceAsStream(res);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputSteam);
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
List<User> userList = mapper.getUserList();
for (User user : userList) {
System.out.println(user);
if(user.getOrdersList().size()>0) {
for (Orders o : user.getOrdersList()) {
System.out.println(o);
}
}
}
}
测试结果