注:以下内容中的代码仅部分代码,并不全面,仅作为笔记。
一对一查询
示例代码:
<resultMap type="Orders" id="orders">
<!-- Orders中的userId与数据库表中的user_id不匹配,所以在这里进行匹配 -->
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="id" property="id"/>
<result column="createTime" property="createTime"/>
<result column="number" property="number"/>
<!-- 一对一映射 -->
<association property="user" javaType="User">
<!-- user表中的id,此时column的值uId为查询时指定的字段的别名 -->
<id column="uId" property="id"/>
<result column="username" property="username"/>
</association>
</resultMap>
<!-- 一对一关联映射 -->
<select id="selectOrders" resultMap="orders">
select
o.id,
o.createtime,
o.number,
u.id as uId,
u.username
from orders o
left join user u
on o.user_id=u.id
</select>
public class Orders {
private Integer id;
private Integer userId;
private String number;
private Date createTime;
private String note;
// 一个订单对应一个用户.
private User user;
// getter() setter()
}
测试:
@Test
public void testSelectList1() throws IOException {
// 加载核心配置文件.
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
// 创建SqlSessionFactory.
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 创建sqlSession.
SqlSession sqlSession = sqlSessionFactory.openSession();
// sqlSession给接口生成实现类,这样直接创建DAO接口,不用自己创建DAO的实现类.
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<Orders> list = orderMapper.selectOrders();
for (Orders orders : list) {
System.out.println(orders);
System.out.println(orders.getUser().getId());
}
}
一对多查询
比如用户表和订单表,一个用户可以有多张订单,现在要查询全部用户,若用户有订单,并将用户的订单一块显示出来:
User实体类中:
public class User {
private Integer id;
private String username;
private String sex;
private Date birthday;
private String address;
// 一个用户可以有多个订单.
private List<Orders> ordersList;
// getter() setter()
}
查询语句:
<resultMap type="User" id="user">
<id column="uId" property="id"/>
<result column="username" property="username"/>
<!-- 一对多映射 ofType值代表list集合中每一个元素的类型,此处为Orders类-->
<collection property="ordersList" ofType="Orders">
<id column="id" property="id"/>
<result column="number" property="number"/>
</collection>
</resultMap>
<!-- 一对多 -->
<select id="selectUserList" resultMap="user">
select
u.id uId,
u.username,
o.id,
o.createtime,
o.number
from user u
left join orders o
on o.user_id=u.id
</select>
这样就是将所有的用户包括订单或没有订单的 全部查询出来。