一对一查询
MyBatis中使用association标签来解决一对一的关联查询,association标签可用的属性如下:
- property:对象属性的名称
- javaType:对象属性的类型
- column:所对应的外键字段名称
- select:使用另一个查询封装的结果
准备案例数据模型
- 用户表
- 订单表
- 订单详情表
- 商品表
实现案例:查询所有订单和用户信息。
以订单为观点看:一个订单信息只会是一个人下的订单
从订单看用户就是 一对一 的关系 但重用户看订单是一对多的关系
实现方法一:通过resultType
实现方法二:通过resultMap
1.方法一:通过resultType
//ordersMapper.xml
<select id="findOrderResultType" resultType="OrdersCustom">
select orders.*,u.username,u.address from orders,t_user u
where orders.userid = u.id
</select>
需要建一个pojo (OrdersCustom.java)
可以是继承 Custom 再建立Orders的字段,建一个联合javabean进行查询再建一个接口可以用以前的方法进行测试
2.方法二:通过resultMap
方法a,新建一个联合类OrdersUser
<!--resultMap="ordersUserResultMap"与下面的resultMap标签id对应-->
<select id="getOrdersList" resultMap="ordersUserResultMap">
select o.*,u.username,u.birthday,u.address ad from orders o, t_user u where o.userid=u.id
</select>
<!--通过查询把数据库上column="userid"的值赋值到OrdersUser的字段property="userId"-->
<!--确定赋值对象 未避免名字出现相同可以查询的时候使用别称来替换colemm值
比如顾客和订单都有 id [select id uuid...] column="uuid" 即可
-->
<resultMap type="OrdersUser" id="ordersUserResultMap">
<!--主键用id标签-->
<id column="id" property="id"/>
<result column="userid" property="userId"/>
<result column="orderid" property="orderId"/>
<result column="createtime" property="createDate"/>
<result column="note" property="note"/>
<result column="username" property="userName"/>
<result column="birthday" property="birthDay"/>
<result column="ad" property="address"/>
</resultMap>
方法b,在Orders中新建一个Tuser的对象字段
//Orders.java
TUser tu=new TUser();
public TUser getTu() {
return tu;
}
public void setTu(TUser tu) {
this.tu = tu;
}
<select id="getOrdersList2" resultMap="ordersUserResultMap2">
select o.*,u.username,u.birthday,u.address ad from orders o, t_user u where o.userid=u.id
</select>
<!--此时type是Orders -->
<resultMap type="Orders" id="ordersUserResultMap2">
<id column="id" property="id"/>
<result column="userid" property="userId"/>
<result column="orderid" property="orderId"/>
<result column="createtime" property="createDate"/>
<result column="note" property="note"/>
<!--上面对order的简单字段进行一对一对应赋值-->
<!--对TUser tu 进行特殊对应赋值javaType,property需要一一对应-->
<association property="tu" javaType="TUser">
<id column="userid" property="id"></id>
<result column="username" property="userName"/>
<result column="birthday" property="birthDay"/>
<result column="ad" property="address"/>
</association>
</resultMap>
//接口
package myBatisTest.dao;
import ....
public interface OrdersDao {
public List<Orders> getOrdersList2();
}
进行测试
//test.java
package myBatisTest.dao;
import ....
public class test {
public static void main(String[] args) throws IOException, SQLException {
// TODO Auto-generated method stub
InputStream is = Resources.getResourceAsStream("myBatisTest/dao/SqlMapConfig.xml");
SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sf.openSession();
OrdersDao od = sqlSession.getMapper(OrdersDao.class);//获取代理对象
List<Orders> odList = od.getOrdersList2();
for (Orders orders : odList) {
System.out.println(orders);
}
}
}
结果
Opening JDBC Connection
Created connection 1699113578.
Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@65466a6a]
==> Preparing: select o.*,u.username,u.birthday,u.address ad from orders o, t_user u where o.userid=u.id
==> Parameters:
<== Columns: ID, USERID, ORDERID, CREATETIME, NOTE, USERNAME, BIRTHDAY, AD
<== Row: 1, 2, 1101, 2017-10-30 14:17:29.0, computer, dddddd, null, null
<== Row: 2, 2, 1102, 2017-10-30 14:17:29.0, aaaaaaaa, dddddd, null, null
<== Row: 3, 22, 1103, 2017-10-30 14:17:29.0, ccccccc, ggggggg, null, null
<== Total: 3
Orders [id=1, orderId=1101, userId=2, createDate=Mon Oct 30 14:17:29 CST 2017, note=computer]
Orders [id=2, orderId=1102, userId=2, createDate=Mon Oct 30 14:17:29 CST 2017, note=aaaaaaaa]
Orders [id=3, orderId=1103, userId=22, createDate=Mon Oct 30 14:17:29 CST 2017, note=ccccccc]
<!--可以在orders.java 的toString方法加上tu.get...输出用户信息-->