一、引言

通过动态SQL我们可以进行复杂SQL的编写，但之前的例子都是单表查询，在实际开发中，当然不可能都是单表，很多时候我们需要进行关联多表查询（有些公司为了性能还是尽量的使用单表查询），表与表之间的关系分为一对一，一对多，多对多，我们讲讲这三种是如何编写的。

先进行表和实体的创建：

CREATETABLE`user` (

  `id` int(11)NOTNULL AUTO_INCREMENT,

  `username` varchar(32)NOTNULLCOMMENT'用户名称',

  `birthday` date DEFAULTNULLCOMMENT'生日',

  `sex` char(1)DEFAULTNULLCOMMENT'性别',

  `address` varchar(256)DEFAULTNULLCOMMENT'地址',

  PRIMARYKEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=27DEFAULTCHARSET=utf8;-- ------------------------------ Records of user-- ----------------------------INSERTINTO`user`VALUES('1','王五',null,'2',null);INSERTINTO`user`VALUES('10','张三','2014-07-10','1','北京市');INSERTINTO`user`VALUES('16','张小明',null,'1','河南郑州');INSERTINTO`user`VALUES('22','陈小明',null,'1','河南郑州');INSERTINTO`user`VALUES('24','张三丰',null,'1','河南郑州');INSERTINTO`user`VALUES('25','陈小明',null,'1','河南郑州');INSERTINTO`user`VALUES('26','王五',null,null,null);DROPTABLEIFEXISTS`order`;CREATETABLE`order` (

  `id` int(11)NOTNULL AUTO_INCREMENT,

  `user_id`int(11)NOTNULLCOMMENT'下单用户id',

  `number`varchar(32)NOTNULLCOMMENT'订单号',

  `createtime` datetimeNOTNULLCOMMENT'创建订单时间',

  `note` varchar(100)DEFAULTNULLCOMMENT'备注',

  PRIMARYKEY (`id`),

  KEY`FK_order_1` (`user_id`),

  CONSTRAINT`FK_order_id`FOREIGNKEY(`user_id`)REFERENCES`user` (`id`)ONDELETENO ACTIONONUPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=6DEFAULTCHARSET=utf8;-- ------------------------------ Records of order-- ----------------------------INSERTINTO`order`VALUES('3','1','1000010','2015-02-04 13:22:35',null);INSERTINTO`order`VALUES('4','1','1000011','2015-02-03 13:22:41',null);INSERTINTO`order`VALUES('5','10','1000012','2015-02-12 16:13:23',null);

User：

@DatapublicclassUserimplements Serializable {

    private Integer id;

    // 用户姓名private String username;

    // 性别private String sex;

    // 生日private Date birthday;

    // 地址private String address;

}

Order：

@Datapublicclass Order {

    // 订单idprivateint id;

    // 用户idprivate Integer userId;

    // 订单号private String number;

    // 订单创建时间private Date createtime;

    // 备注private String note;

}

二、一对一

订单表与用户表，一个订单是由一个客户创建的，当我们通过订单去反查用户时，就是一对一关系。

有两种方式可以实现

2.1、使用resultType

可以改造订单pojo类，此pojo类中包括了订单信息和用户信息，这样返回对象的时候，mybatis自动把用户信息也注入进来了，创建OrderUserVO：

@DatapublicclassOrderUserVOextends Order {

    /**    * 客户名称

    */private String username;

    /**    * 客户地址

    */private String address; 

}

xml文件：

<select id="queryOrderUser" resultType="com.yuanqinnan.pojo.OrderUserVO">    SELECTo.id,o.user_id,userId,o.number,o.createtime,o.note,u.username,u.address

    FROM

    `order` o

    LEFT JOIN `user` u ON o.user_id = u.id

  </select>

接口：

publicinterface OrderMapper {

    List queryOrderAll();

    List queryOrderAll2();

    List queryOrderUser();

}

测试：

@Testpublicvoid queryOrderUser(){

    SqlSession sqlSession =this.sqlSessionFactory.openSession();

    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);

    List list = orderMapper.queryOrderUser();

    for (OrderUserVO u : list) {

        System.out.println(u);

    }

    sqlSession.close();

}

结果：

定义专门的pojo类作为输出类型，其中定义了sql查询结果集所有的字段。此方法较为简单，企业中使用普遍。

2.2、使用resultMap

使用resultMap，定义专门的resultMap用于映射一对一查询结果，改造Order类，在Order类中加入User属性，user属性中用于存储关联查询的用户信息，因为订单关联查询用户是一对一关系，所以这里使用单个User对象存储关联查询的用户信息。

@Datapublicclass Order {

    // 订单idprivateint id;

    // 用户idprivate Integer userId;

    // 订单号private String number;

    // 订单创建时间private Date createtime;

    // 备注private String note;

    /**    * 客户

    */private User user;

}

先定义resultMap：

<resultMap type="com.yuanqinnan.model.Order" id="orderUserResultMap"><id property="id" column="id"/><result property="userId" column="user_id"/><result property="number" column="number"/><result property="createtime" column="createtime"/><result property="note" column="note"/><!-- association ：配置一对一属性 --><!-- property:order里面的User属性名 --><!-- javaType:属性类型 --><association property="user" javaType="user"><!-- id:声明主键，表示user_id是关联查询对象的唯一标识--><id property="id" column="user_id"/><result property="username" column="username"/><result property="address" column="address"/></association></resultMap>

再添加查询

<!-- 一对一关联，查询订单，订单内部包含用户属性 --><select id="queryOrderUserResultMap" resultMap="orderUserResultMap">    SELECT o.id,o.user_id,o.number,o.createtime,o.note,u.username,u.address

    FROM

    `order` o

    LEFT JOIN `user` u ON o.user_id = u.id</select>

接口：

List<Order> queryOrderUserResultMap();

测试：

@Testpublicvoid queryOrderUserResultMap(){

    SqlSession sqlSession =this.sqlSessionFactory.openSession();

    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);

    List list = orderMapper.queryOrderUserResultMap();

    for (Order u : list) {

        System.out.println(u);

    }

    sqlSession.close();

}

结果：

三、一对多

一个客户会创建多个订单，当我们查询客户订单时，就会产生一对多的情况，我们以此为例。

首先，我们在User类中增加订单列表

@DatapublicclassUserimplements Serializable {

    //idprivate Integer id;

    //用户姓名private String username;

    //性别private String sex;

    //生日private Date birthday;

    //地址private String address;

    //订单列表privateList orderList;

}

然后增加resultMap：

<resultMap type="com.yuanqinnan.model.Order" id="orderUserResultMap"><id property="id" column="id"/><result property="userId" column="user_id"/><result property="number" column="number"/><result property="createtime" column="createtime"/><result property="note" column="note"/><!-- association ：配置一对一属性 --><!-- property:order里面的User属性名 --><!-- javaType:属性类型 --><association property="user" javaType="user"><!-- id:声明主键，表示user_id是关联查询对象的唯一标识--><id property="id" column="user_id"/><result property="username" column="username"/><result property="address" column="address"/></association></resultMap>

增加查询：

<!-- 一对一关联，查询订单，订单内部包含用户属性 --><select id="queryOrderUserResultMap" resultMap="orderUserResultMap">    SELECT o.id,o.user_id,o.number,o.createtime,o.note,u.username,u.address

    FROM

    `order` o

    LEFT JOIN `user` u ON o.user_id = u.id</select>

测试：

@Testpublicvoid queryUserOrder(){

    SqlSession sqlSession =this.sqlSessionFactory.openSession();

    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

    List list = userMapper.queryUserOrder();

    for (User u : list) {

        System.out.println(u);

    }

    sqlSession.close();

}

结果：

四、多对多

多对多关系都需要中间表来维护，例如，一个订单包含多个产品，一种产品又可以出现在多个订单中，那这个时候我们就需要一个订单产品表作为中间表。这样我们需要新增两个表，产品表和订单产品表。

CREATETABLE `product` (

  `id` int(11)NOTNULL AUTO_INCREMENT,

  `name` varchar(50)DEFAULTNULLCOMMENT'产品名称',

  PRIMARYKEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULTCHARSET=utf;-- ------------------------------ Records of product-- ---------------------------INSERTINTOproductVALUES('1','笔记本电脑');INSERTINTOproductVALUES('2','小米手机');CREATETABLE `order_product` (

  `id` int(11)NOTNULL AUTO_INCREMENT,

  `oid` int(11)DEFAULTNULLCOMMENT'订单id',

  `pid` int(11)DEFAULTNULLCOMMENT'产品id',

  PRIMARYKEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULTCHARSET=utf8-- ------------------------------ Records of order_product-- ---------------------------INSERTINTOorder_productVALUES('1','3','1');INSERTINTOorder_productVALUES('2','3','2');INSERTINTOorder_productVALUES('3','4','1');

我们可能遇到的多对多查询是：查询这个订单的所有产品或者查询这个产品的所有订单

那这个时候我们的订单和产品表里都应该对应多个中间表，实体就应该这么设计：

order实体增加中间表：

@Datapublicclass Order {

    // 订单idprivateint id;

    // 用户idprivate Integer userId;

    // 订单号private String number;

    // 订单创建时间private Date createtime;

    // 备注private String note;

    //客户private User user;

    //中间表privateList products;

}

OrderProduct：

@Datapublicclass OrderProduct {

    private Integer id;

    //订单主键private Integer oid;

    //产品主键private Integer pid;

    //订单private Order order;

    //产品private Product product;

}

product：

@Datapublicclass Product {

    //产品idprivate Integer id;

    //产品名称private String name;

    //中间表List orders;

}

resultmap:

<resultMap type="com.yuanqinnan.model.Order" id="orderBean"><id column="id" property="id"/><result property="userId" column="user_id"/><result property="number" column="number"/><result property="createtime" column="createtime"/><result property="note" column="note"/><collection property="products" ofType="com.yuanqinnan.model.OrderProduct"><id column="oiid" property="id"/><result column="oid" property="oid"/><result column="pid" property="pid"/><association property="product" javaType="com.yuanqinnan.model.Product"><id column="pid" property="id"/><result column="pname" property="name"/></association></collection></resultMap>

新增查询订单的产品信息方法：

<select id="listOrder" resultMap="orderBean">    SELECT

        o.*,

        o.id oid,

        p.id pid,

        oi.id oiid,

        p.NAME pname

    FROM

        `order` o

        LEFT JOIN order_product oi ON o.id = oi.oid

        LEFT JOIN product p ON p.id = oi.pid</select>

测试：

@Testpublicvoid listOrder(){

    SqlSession sqlSession =this.sqlSessionFactory.openSession();

    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);

    List list = orderMapper.listOrder();

    for (Order u : list) {

        System.out.println(u);

    }

    sqlSession.close();

}

结果：