一 建表,提供初始数据。
表说明:t_customer为客户信息表;t_orders为订单表。一个客户可以有多个订单,一个订单只属于一个客户。多方使用外键(t_orders表的customer_id)来约束。这里并没有建立强制的外键约束,做更新,删除操作时会很麻烦。
CREATE TABLE `t_customer` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, `cell_phone` varchar(30) NOT NULL, PRIMARY KEY (`id`) ); INSERT INTO `t_customer` VALUES ('1', 'bing', '652346543'); INSERT INTO `t_customer` VALUES ('2', 'jade', '76345'); CREATE TABLE `t_orders` ( `id` int(10) NOT NULL AUTO_INCREMENT, `number` varchar(40) NOT NULL, `address` varchar(40) NOT NULL, `customer_id` int(10) NOT NULL, PRIMARY KEY (`id`) ); INSERT INTO `t_orders` VALUES ('1', 'GASDF235', '北京朝阳', '1'); INSERT INTO `t_orders` VALUES ('2', 'JHGFVSD34', '河南郑州', '1'); INSERT INTO `t_orders` VALUES ('3', 'KJHGFDC234', '北京海淀', '2');
表模型如下
二 实体类
Customer
package com.alex.app.entity; import java.util.List; /** * 客户信息 * @author leileiyuan * */ public class Customer { private Integer id; private String name; private String cellPhone; //一对多 private List<Orders> orders; public List<Orders> getOrders() { return orders; } public void setOrders(List<Orders> orders) { this.orders = orders; } // 略 getter setter方法 @Override public String toString() { return "Customer [id=" + id + ", name=" + name + ", cellPhone=" + cellPhone + "]"; } }
Orders
package com.alex.app.entity; /** * 客户订单信息 * @author leileiyuan * */ public class Orders { private Integer id; private String number; private String address; // 多对一 private Customer customer; public Customer getCustomer() { return customer; } public void setCustomer(Customer customer) { this.customer = customer; } // 略 getter setter方法 @Override public String toString() { return "Orders [id=" + id + ", number=" + number + ", address=" + address + ", customer=" + customer + "]"; } }
三 多对一映射
1)问题简单分析
从orders 到 customer 多对一的关联关联。
就是说 我们要查询orders的信息,然后经该orders可以导航到它对应的customer
考虑这个sql
select o.*, c.* from t_orders o join t_customer c on o.customer_id = c.id where o.id = 2
查询是没有问题的
映射文件配置时可以。
<select id="selectById" parameterType="int" resultMap="???????"> select c.id cid, c.name name, c.cell_phone cellPhone, o.id oid, o.number number, o.address address from t_orders o join t_customer c on o.customer_id = c.id where o.id = #{id} </select>
因为我们查询的结果集中包含的是orders和customer的所有列,所以返回的结果集应该使用resultMap而不是resultType(也可以也写一个VO,来封装结果中的列,那么就可以使用resultType指向那个VO)。
2)映射结果集
我们来写个resultMap 来封装这个复杂结果集
Orders.xml的映射文件如下。t_orders和t_customer的id列,要使用别名来区分
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.alex.app.dao.OrdersDao"> <resultMap type="Orders" id="OrdersResultMap"> <id property="id" column="oid"/> <result property="number" column="number"/> <result property="address" column="address"/> </resultMap> <select id="selectById" parameterType="int" resultMap="OrdersResultMap"> select c.id cid, c.name name, c.cell_phone cellPhone, o.id oid, o.number number, o.address address from t_orders o join t_customer c on o.customer_id = c.id where o.id = #{id} </select> </mapper>
定义了一个resultMap,id为OrdersResultMap,查询的结果集指向这个resultMap。
来测试下
@Test public void test() { SqlSession session = null; try { session = MyBatisUtil.openSession(); OrdersDao ordersDao = session.getMapper(OrdersDao.class); Orders orders = ordersDao.selectById(1); System.out.println(orders); } catch (Exception e) { e.printStackTrace(); session.rollback(); }finally{ MyBatisUtil.coloseSession(session); } }
结果如下
Orders [id=1, number=GASDF235, address=北京朝阳, customer=null]
customer为null。
3)表达关联关系
我们还需要把orders到customer多对一,这个一方,也表示在resultMap里。使用association 标签。
t_orders和t_customer的id列,要使用别名来区分
<resultMap type="Orders" id="OrdersResultMap"> <id property="id" column="oid"/> <result property="number" column="number"/> <result property="address" column="address"/> <association property="customer" javaType="Customer"> <id property="id" column="cid"/> <result property="name" column="name"/> <result property="cellPhone" column="cell_phone"/> </association> </resultMap>
association解释
属性:property,当然是Orders实体中定义的属性名customer;
javaType,是customer所要映射成那个Java对象,这可以使用别名
子标签:
id,表示主键,property是实体类的属性名,column是对应表的字段列名
result,表示普通属性
4)再运行上面的测试代码,得到结果
Orders [id=1, number=GASDF235, address=北京朝阳, customer=Customer [id=1, name=bing, cellPhone=null, orders=null]]看到我们已经关联上了,也返回了正确的结果。
5)Orders.xml完整内容。t_orders和t_customer的id列,要使用别名来区分
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.alex.app.dao.OrdersDao"> <resultMap type="Orders" id="OrdersResultMap"> <id property="id" column="oid"/> <result property="number" column="number"/> <result property="address" column="address"/> <association property="customer" javaType="Customer"> <id property="id" column="cid"/> <result property="name" column="name"/> <result property="cellPhone" column="cell_phone"/> </association> </resultMap> <select id="selectById" parameterType="int" resultMap="OrdersResultMap"> select c.id cid, c.name name, c.cell_phone cellPhone, o.id oid, o.number number, o.address address from t_orders o join t_customer c on o.customer_id = c.id where o.id = #{id} </select> </mapper>
四 一对多关联映射
从customer到orders一对多的关联关系。一个客户对多个订单信息
1)问题分析
考虑这个sql
select c.*, o.* from t_customer c join t_orders o on c.id = o.customer_id where c.id = 1
查询结果bing这个客户拥有两个订单
2)映射结果集
映射文件的sql
<select id="selectById" parameterType="int" resultMap="CustomerResultMap"> select c.id cid, c.name name, c.cell_phone cellPhone, o.id oid, o.number number, o.address address from t_customer c, t_orders o where c.id = o.customer_id and c.id = #{id} </select>
3)一对多关联
上面我们需要提供一个resultMap="CustomerResultMap"。t_orders和t_customer的id列,要使用别名来区分
<resultMap type="Customer" id="CustomerResultMap"> <id property="id" column="cid" /> <result property="name" column="name" /> <collection property="orders" javaType="ArrayList" ofType="Orders"> <id property="id" column="oid" /> <result property="number" column="number" /> </collection> </resultMap>
一对多关联,多的一方使用collection表示
collection 属性 解释:
property,是实体类中的属性orders;
javaType,这里的JavaType代表的是实体类中的属性orders的类型是ArrayList
//一对多 private List<Orders> orders;
ofType,是集合中的放置的内容的类型,这里集合放的东西是Orders对象
4)测试下
@Test public void testSelectCustomer() { SqlSession session = null; try { session = MyBatisUtil.openSession(); CustomerDao customerDao = session.getMapper(CustomerDao.class); Customer customer = customerDao.selectById(1); System.out.println(customer); List<Orders> orders = customer.getOrders(); System.out.println(orders); } catch (Exception e) { e.printStackTrace(); }finally{ MyBatisUtil.coloseSession(session); } }
结果
DEBUG 2015-05-10 08:59:28,640 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select c.id cid, c.name name, c.cell_phone cellPhone, o.id oid, o.number number, o.address address from t_customer c, t_orders o where c.id = o.customer_id and c.id = ? DEBUG 2015-05-10 08:59:28,687 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 1(Integer) DEBUG 2015-05-10 08:59:28,718 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 2 Customer [id=1, name=bing, cellPhone=null, orders=[Orders [id=1, number=GASDF235, address=null, customer=null], Orders [id=2, number=JHGFVSD34, address=null, customer=null]]] [Orders [id=1, number=GASDF235, address=null, customer=null], Orders [id=2, number=JHGFVSD34, address=null, customer=null]]
5)Customer.xml完整内容。t_orders和t_customer的id列,要使用别名来区
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.alex.app.dao.CustomerDao"> <resultMap type="Customer" id="CustomerResultMap"> <id property="id" column="cid" /> <result property="name" column="name" /> <collection property="orders" javaType="ArrayList" ofType="Orders"> <id property="id" column="oid" /> <result property="number" column="number" /> </collection> </resultMap> <select id="selectById" parameterType="int" resultMap="CustomerResultMap"> select c.id cid, c.name name, c.cell_phone cellPhone, o.id oid, o.number number, o.address address from t_customer c, t_orders o where c.id = o.customer_id and c.id = #{id} </select> </mapper>
相关推荐
MyBatis关联映射:一对一、一对多-代码
赠送jar包:mybatis-paginator-1.2.15.jar; 赠送原API文档:mybatis-paginator-1.2.15-javadoc.jar; 赠送源代码:mybatis-paginator-1.2.15-sources.jar; 赠送Maven依赖信息文件:mybatis-paginator-1.2.15.pom;...
MyBatis-Flex: 一个优雅的 MyBatis 增强框架。很轻量,MyBatis-Flex 整个框架只依赖 MyBatis,再无其他任何第三方依赖。只增强,MyBatis-Flex 支持 CRUD、分页查询、多表查询、批量操作,但不丢失 MyBatis 原有的...
环境搭建,源代码,因为使用的是maven构建的项目,所以不含jar包 博文链接:https://yuanleilei628.iteye.com/blog/2205232
文件放的位置和数据库信息需要在配置文件中自己配置,不然是用不了的
实现Mybatis框架中一对多关联映射的查询操作。 User用户表 - Order_form订单表属于 1-N 的关系。 一个用户对象包含一批订单信息
Mybatis实现关联查询一对一和一对多实现,具体效果看博文 http://blog.csdn.net/evankaka/article/details/45674101
下面小编就为大家带来一篇MyBatis拦截器:给参数对象属性赋值的实例。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
MyBatis简单的一对一和一对多测试,表根据JavaBean自己创建,测试已通过
Mybatis实现一对一、一对多关联查询,关联查询:多个表联合查询,只查询一次,通过resultMap里面的、标签配置一对一、一对多;涉及到的一对一、一对多关系: - 班级classes、班主任teacher是一对一的关系 - 班级...
SpringBoot中mybatis表关联映射关系(一对多嵌套:结果方式)
SpringMVC+mybatis入门项目:旅游网站
mybatis一对多的查询方法详解! mybatis一对多的查询方法详解! mybatis一对多的查询方法详解! mybatis一对多的查询方法详解!
mybatis-3.2.6(最新mybatis jar).zip
MyBatis 源码解析:通过源码深入理解 SQL 的执行过程 抓下来打包成了HTML文件, 方便离线观看
MyBatis in Practice: A Step by Step Approach for Learning MyBatis Framework一书的源代码
mybatis 3.4.5 ,想用自取,mybatis 3.4.5 ,想用自取,mybatis 3.4.5 ,想用自取,mybatis 3.4.5 ,想用自取。
Mybatis编程示例:基于注解的实现 使用mysql数据库,配置好数据库后即可直接使用 mybatis基于注解的实现有以下步骤: 步骤1、定义sql映射接口(在src/main/java下创建mapper包) 步骤2、将sql映射接口添加到Mybatis...
MyBatis高级映射(一对多查询)