前面几篇博客我们用 mybatis 能对单表进行增删改查操作了,也能用动态 SQL 书写比较复杂的 sql 语句。但是在实际开发中,我们做项目不可能只是单表操作,往往会涉及到多张表之间的关联操作。那么我们如何用 mybatis 处理多表之间的关联操作呢?请看本篇博客详解。
本篇详细代码:http://pan.baidu.com/s/1eSzmst8 密码:3n3o
我们以用户表 user 和订单表 orders 为例。设定一个订单只能由一个 用户创建,那么由订单到用户就是一对一的关系。
①、创建用户表 user 和订单表 orders
用户表 user
订单表 orders
②、创建项目工程,导入相应的 jar 包
③、创建实体类
User.java
- package com.ys.po;
- public class User {
- //用户ID
- private int id;
- //用户姓名
- private String username;
- //用户性别
- private String sex;
- public int getId() {
- return id;
- }
- public void setId(int id) {
- this.id = id;
- }
- public String getUsername() {
- return username;
- }
- public void setUsername(String username) {
- this.username = username;
- }
- public String getSex() {
- return sex;
- }
- public void setSex(String sex) {
- this.sex = sex;
- }
- @Override
- public String toString() {
- return "User [id=" + id + ", username=" + username + ", sex=" + sex
- + "]";
- }
- }
Orders.java
- package com.ys.po;
- public class Orders {
- //订单ID
- private int id;
- //用户ID
- private int userId;
- //订单数量
- private String number;
- //和用户表构成一对一的关系,即一个订单只能由一个用户创建
- private User user;
- public int getId() {
- return id;
- }
- public void setId(int id) {
- this.id = id;
- }
- public int getUserId() {
- return userId;
- }
- public void setUserId(int userId) {
- this.userId = userId;
- }
- public String getNumber() {
- return number;
- }
- public void setNumber(String number) {
- this.number = number;
- }
- public User getUser() {
- return user;
- }
- public void setUser(User user) {
- this.user = user;
- }
- @Override public String toString() {
- return "Orders [id=" + id + ", userId=" + userId + ", number=" + number + ", user=" + user + "]";
- }
- }
④、创建 OrderMapper 接口和 OrderMapper.xml 文件
由于我们采用 Mapper 代理加载 xxxMapper.xml 文件,这里我们重复一下 Mapper 代理所需的条件,接口和 xml 文件必须满足以下几个条件:
1、接口必须要和 xml 文件同名且在同一个包下,也就是说 xml 文件中的 namespace 是接口的全类名
2、接口中的方法名和 xml 文件中定义的 id 一致
3、接口输入参数类型要和 xml 中定义的 parameterType 一致
4、接口返回数据类型要和 xml 中定义的 resultType 一致
详细介绍参考上一篇博客:http://www.cnblogs.com/ysocean/p/7301548.html
OrderMapper 接口
- package one.to.one.mapper;
- import com.ys.po.Orders;
- import com.ys.po.User;
- public interface OrdersMapper {
- /**
- * 方式一:嵌套结果
- * select * from orders o,user u where o.user_id=u.id and o.id=#{id}
- * @param orderId
- * @return
- */
- //根据订单ID查询订单和用户信息
- public Orders selectOrderAndUserByOrderID(int orderId);
- /**
- * 方式二:嵌套查询
- * select * from order WHERE id=1;//得到user_id
- * select * from user WHERE id=1 //1 是上一个查询得到的user_id的值
- * @param userID
- * @return
- */
- //根据订单ID得到订单信息(包含user_id)
- public Orders getOrderByOrderId(int orderId);
- //根据用户ID查询用户信息
- public User getUserByUserId(int userID);
- }
OrderMapper .xml 文件
- <?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="one.to.one.mapper.OrdersMapper">
- <!--
- 嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集
- 封装联表查询的数据(去除重复的数据)
- select * from orders o,user u where o.user_id=u.id and o.id=#{id}
- -->
- <select id="selectOrderAndUserByOrderID" resultMap="getOrderAndUser">
- select * from orders o,user u where o.user_id=u.id and o.id=#{id}
- </select>
- <resultMap type="com.ys.po.Orders" id="getOrderAndUser">
- <!--
- id:指定查询列表唯一标识,如果有多个唯一标识,则配置多个id
- column:数据库对应的列
- property:实体类对应的属性名
- -->
- <id column="id" property="id"/>
- <result column="user_id" property="userId"/>
- <result column="number" property="number"/>
- <!--association:用于映射关联查询单个对象的信息
- property:实体类对应的属性名
- javaType:实体类对应的全类名
- -->
- <association property="user" javaType="com.ys.po.User">
- <!--
- id:指定查询列表唯一标识,如果有多个唯一标识,则配置多个id
- column:数据库对应的列
- property:实体类对应的属性名
- -->
- <id column="id" property="id"/>
- <result column="username" property="username"/>
- <result column="sex" property="sex"/>
- </association>
- </resultMap>
- <!--
- 方式二:嵌套查询:通过执行另外一个SQL映射语句来返回预期的复杂类型
- select user_id from order WHERE id=1;//得到user_id
- select * from user WHERE id=1 //1 是上一个查询得到的user_id的值
- property:别名(属性名) column:列名 -->
- <select id="getOrderByOrderId" resultMap="getOrderMap">
- select * from order where id=#{id}
- </select>
- <resultMap type="com.ys.po.Orders" id="getOrderMap">
- <id column="id" property="id"/>
- <result column="number" property="number"/>
- <association property="userId" column="id" select="getUserByUserId">
- </association>
- </resultMap>
- <select id="getUserByUserId" resultType="com.ys.po.User">
- select * from user where id=#{id}
- </select>
- </mapper>
⑤、向 mybatis-configuration.xml 配置文件中注册 OrderMapper.xml 文件
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
- <configuration>
- <!-- 加载数据库属性文件 -->
- <properties resource="db.properties"></properties>
- <!-- 定义别名 -->
- <typeAliases>
- <!-- mybatis自动扫描包中的po类,自动定义别名,别名是类名(首字母大写或小写都可以,一般用小写) -->
- <package name="com.ys.po"/>
- </typeAliases>
- <environments default="development">
- <environment id="development">
- <transactionManager type="JDBC"/>
- <!--dataSource 元素使用标准的 JDBC 数据源接口来配置 JDBC 连接对象源 -->
- <dataSource type="POOLED">
- <property name="driver" value="${jdbc.driver}"/>
- <property name="url" value="${jdbc.url}"/>
- <property name="username" value="${jdbc.username}"/>
- <property name="password" value="${jdbc.password}"/>
- </dataSource>
- </environment>
- </environments>
- <mappers>
- <!-- 通过OrdersMapper接口注册OrdersMapper.xml文件,
- 必须保证:接口和xml在同一个包下,而且名字一样
- OrdersMapper接口的方法名和OrdersMapper.xml文件的id一样
- OrdersMapper接口的输出输出参数和OrdersMapper.xml文件resultType,parameterType类型一样
- -->
- <mapper class="one.to.one.mapper.OrdersMapper"/>
- </mappers>
- </configuration>
⑥、测试
- package one.to.one.mapper;
- import java.io.InputStream;
- import org.apache.ibatis.session.SqlSession;
- import org.apache.ibatis.session.SqlSessionFactory;
- import org.apache.ibatis.session.SqlSessionFactoryBuilder;
- import org.junit.Before;
- import org.junit.Test;
- import com.ys.po.Orders;
- public class OneToOneTest {
- //定义 SqlSession
- SqlSession session = null;
- @Before public void init() {
- //定义mybatis全局配置文件
- String resource = "mybatis-configuration.xml";
- //加载 mybatis 全局配置文件
- InputStream inputStream = OneToOneTest.class.getClassLoader().getResourceAsStream(resource);
- //构建sqlSession的工厂
- SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
- //根据 sqlSessionFactory 产生 session
- session = sessionFactory.openSession();
- }
- /**
- * 方式一:嵌套结果
- * select * from orders o,user u where o.user_id=u.id and o.id=#{id}
- */
- @Test public void testSelectOrderAndUserByOrderId() {
- String statement = "one.to.one.mapper.OrdersMapper.selectOrderAndUserByOrderID";
- //创建OrdersMapper对象,mybatis自动生成mapepr代理对象
- OrdersMapper orderMapper = session.getMapper(OrdersMapper.class);
- Orders order = orderMapper.selectOrderAndUserByOrderID(1);
- System.out.println(order);
- session.close();
- }
- /**
- * 方式二:嵌套查询
- * select * from order WHERE id=1;//得到user_id
- * select * from user WHERE id=1 //1 是上一个查询得到的user_id的值
- */
- @Test public void testgetOrderByOrderId() {
- String statement = "one.to.one.mapper.OrdersMapper.getOrderByOrderId";
- //创建OrdersMapper对象,mybatis自动生成mapepr代理对象
- OrdersMapper orderMapper = session.getMapper(OrdersMapper.class);
- Orders order = orderMapper.selectOrderAndUserByOrderID(1);
- System.out.println(order);
- session.close();
- }
- }
还是以用户表 user 和 订单表 orders 为例,一个用户能创建多个订单。故用户和订单构成一对多的关联。
我们在 user.java 中添加一个属性 public List<Orders> orders;
①、创建实体类
user.java 如下,orders.java 保持不变
- package com.ys.po;
- import java.util.List;
- public class User {
- //用户ID
- private int id;
- //用户姓名
- private String username;
- //用户性别
- private String sex;
- //一个用户能创建多个订单,用户和订单构成一对多的关系
- public List < Orders > orders;
- public List < Orders > getOrders() {
- return orders;
- }
- public void setOrders(List < Orders > orders) {
- this.orders = orders;
- }
- public int getId() {
- return id;
- }
- public void setId(int id) {
- this.id = id;
- }
- public String getUsername() {
- return username;
- }
- public void setUsername(String username) {
- this.username = username;
- }
- public String getSex() {
- return sex;
- }
- public void setSex(String sex) {
- this.sex = sex;
- }@Override public String toString() {
- return "User [id=" + id + ", username=" + username + ", sex=" + sex + "]";
- }
- }
②、创建 UserMapper 接口和 UserMapper.xml 文件
UserMapper 接口
- package one.to.many.mapper;
- import com.ys.po.User;
- public interface UserMapper {
- //根据用户id查询用户信息,以及用户下面的所有订单信息
- public User selectUserAndOrdersByUserId(int UserId);
- }
UserMapper.xml
- <?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="one.to.many.mapper.UserMapper">
- <!--
- 方式一:嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集
- 封装联表查询的数据(去除重复的数据)
- select * from user u,orders o where u.id=o.user_id and u.id=#{id}
- -->
- <select id="selectUserAndOrdersByUserId" resultMap="getUserAndOrders">
- select u.*,o.id oid,o.number number from user u,orders o where u.id=o.user_id and u.id=#{id}
- </select>
- <resultMap type="com.ys.po.User" id="getUserAndOrders">
- <!--id:指定查询列表唯一标识,如果有多个唯一标识,则配置多个id
- column:数据库对应的列
- property:实体类对应的属性名 -->
- <id column="id" property="id"/>
- <result column="username" property="username"/>
- <result column="sex" property="sex"/>
- <!--
- property:实体类中定义的属性名
- ofType:指定映射到集合中的全类名
- -->
- <collection property="orders" ofType="com.ys.po.Orders">
- <id column="oid" property="id"/>
- <result column="number" property="number"/>
- </collection>
- </resultMap>
- </mapper>
③、向 mybatis-configuration.xml 配置文件中注册 UserMapper.xml 文件
④、测试
- @Test
- public void testSelectOrderAndUserByOrderId(){
- String statement = "one.to.many.mapper.UserMapper.selectUserAndOrdersByUserId";
- //创建OrdersMapper对象,mybatis自动生成mapepr代理对象
- UserMapper userMapper = session.getMapper(UserMapper.class);
- User user = userMapper.selectUserAndOrdersByUserId(1);
- System.out.println(user.getOrders().size());
- session.close();
- }
这里我们以用户 user 表和 角色 role 表为例,假定一个用户能被分配成多重角色,而一种角色也能分给多个用户,故用户和角色构成多对多的关系。
需求:给定角色 id,查询这个角色所属的所有用户信息
①、在数据库中建立相应的表
user 表和上面的保持不变
role 表
两者之间的关联表 user_role
②、建立对应的实体类
User.java
Role.java
- package com.ys.po;
- import java.util.List;
- public class Role {
- private int id;
- private String name;
- private List < User > users;
- public int getId() {
- return id;
- }
- public void setId(int id) {
- this.id = id;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public List < User > getUsers() {
- return users;
- }
- public void setUsers(List < User > users) {
- this.users = users;
- }
- }
User_Role.java
- package com.ys.po;
- public class User_Role {
- private User user;
- private Role role;
- public User getUser() {
- return user;
- }
- public void setUser(User user) {
- this.user = user;
- }
- public Role getRole() {
- return role;
- }
- public void setRole(Role role) {
- this.role = role;
- }
- }
③、创建 UserMapper 接口和 UserMapper.xml 文件
UserMapper 接口
- package many.to.many.mapper;
- import java.util.List;
- import com.ys.po.User;
- public interface UserMapper {
- //给定一个角色id,要得到具有这个角色的所有用户信息
- public List < User > getUserByRoleId(int roleId);
- }
UserMapper.xml
- <?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="many.to.many.mapper.UserMapper">
- <select id="getUserByRoleId" resultMap="getUserMap">
- select * from user_role ur,user u where ur.user_id=u.id and ur.role_id=#{id}
- </select>
- <resultMap type="com.ys.po.User" id="getUserMap">
- <id column="id" property="id"/>
- <result column="username" property="username"/>
- <result column="sex" property="sex"/>
- </resultMap>
- </mapper>
④、向 mybatis-configuration.xml 配置文件中注册 UserMapper.xml 文件
⑤、测试
- @Test
- public void testGetUserByRoleId(){
- String statement = "many.to.many.mapper.UserMapper.getUserByRoleId";
- //创建OrdersMapper对象,mybatis自动生成mapepr代理对象
- UserMapper userMapper = session.getMapper(UserMapper.class);
- List<User> users = userMapper.getUserByRoleId(1);
- session.close();
- }
多对多主要是关联关系要找好,然后根据关联去查询。
来源: http://www.cnblogs.com/ysocean/p/7309308.html