一、一对一关联查询
背景:
表1:user包含id,username。
表2:orders包含id,user_id
需求:查询每个用户下的订单ID。( 现实中是一对多的关系,即一个用户对应着多个订单,主要是学习mybatis,这里只是借用举例)
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.xml配置。注意配置文件,这里是因为发生了一个问题。如果配置文件仍然使用原来数据库的字段,就会使orders的对象填充user的ID,这是错误。我把数据库user 的ID改为uid,orders的id改为oid。然后就正确填充了。也可能是我配置错误。如果你懂的话,请留言给我
<?xml 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.xml"));
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("++++++);
}
}
}
二、一对多
仍然是前面的例子,改变是:在userorders类中增加了list<orders>类型的属性。并设置get,set方法。修改UserOrdersMapper,增加新方法。并对应在UserOrdersMap.xml中增加对应新方法的select标签。
注意:类必须要有一个无参构造函数。
<resultMap type="com.test.domain.UserOrders" id="UOlist2">
<id column="id" property="uid"/>
<result column="username" property="username"/>
<!--
与映射的类中对应的list相同,
property是对应类中的属性名
javatype填写对应的类型,本次是list。如果是数组的话应填array
ofType是指list存的类型,本次是orders
-->
<collection property="listorders" javaType="list" ofType="orders">
<id column="oid" property="id"/>
<result column="user_id" property="userid"/>
</collection>
</resultMap>
<select id="queryUserOrders2" resultMap="UOlist2">
select
u.uid,
u.username,
o.oid
from `user` as u
LEFT JOIN orders as o on u.uid = o.user_id
</select>