前几天自己配置了Mybatis的高级查询：一对多和多对多，现在记录一下，方便以后用到的时候再回顾，下面是具体的操作步骤
一、首先就是配置Mybatis的xml文件及mapper的xml文件，在这里就不多说了，之前写过这个基本的配置，可以参考：https://www.cnblogs.com/blogs-of-xiu/p/10405407.html
经过配置完基本的xml文件之后，我们就开始实现一对多和多对多的需求。
我这里举的例子是用户和订单，一个用户可以对应多个订单，所以需要的表也是用户表和订单表。数据结构如下：之前的文章中是一对一查询，只用到了user表，现在多了三个表分别是orders【订单表】、order_detail【订单详情】、product【商品表】。
用户与订单的关系为一对多，订单与订单明细的关系是一对多，订单明细与商品表的关系是一对一，用户与商品的关系是多对多。
在这里我们使用的关系是用户与订单【一对多】和用户与商品的关系【多对多】，下面会说明多对多的关系如何配置。
整个数据库的sql结构如下【从逻辑上面推敲，可能有些字段设置的不合适】：
/*
Navicat MySQL Data Transfer
Source Server : xxx
Source Server Version : 50540
Source Host : localhost:3306
Source Database : mybatis
Target Server Type : MYSQL
Target Server Version : 50540
File Encoding : 65001
Date: 2019-03-14 11:07:30
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`order_number` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for order_detail
-- ----------------------------
DROP TABLE IF EXISTS `order_detail`;
CREATE TABLE `order_detail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL,
`product_id` int(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `order_id` (`order_id`),
KEY `product_id` (`product_id`),
CONSTRAINT `order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `product_id` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`product_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 二、经过数据库的设计之后，我们开始进行实现代码，下面是主要的类：
1、实体类中除了product类不需要加入其它类的对象之外，另外几个类中都需要加入其它实体类的对象。
【原因：例如用户 UserBean 中，属性需要加上订单 Orders 的对象，因为订单对于用户来说是多的一方】
具体的代码如下：
UserBean：
package bean;
import java.util.List;
public class UserBean {
private int id;
private String username;
private String password;
private List<Orders> ordersList;
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 getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public List<Orders> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Orders> ordersList) {
this.ordersList = ordersList;
}
@Override
public String toString() {
return "UserBean [id=" + id + ", username=" + username + ", password=" + password + ", ordersList=" + ordersList
+ "]";
}
public UserBean(String username, String password) {
super();
this.username = username;
this.password = password;
}
public UserBean(int id, String username, String password, List<Orders> ordersList) {
super();
this.id = id;
this.username = username;
this.password = password;
this.ordersList = ordersList;
}
public UserBean() {
super();
}
} 订单类 Orders：
package bean;
import java.util.List;
public class Orders {
private int id;
private int user_id;
private String number;
private List<Order_Detail> order_detail;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getUser_id() {
return user_id;
}
public void setUser_id(int user_id) {
this.user_id = user_id;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public List<Order_Detail> getOrder_detail() {
return order_detail;
}
public void setOrder_detail(List<Order_Detail> order_detail) {
this.order_detail = order_detail;
}
@Override
public String toString() {
return "Orders [id=" + id + ", user_id=" + user_id + ", number=" + number + ", order_detail=" + order_detail
+ "]";
}
public Orders(int id, int user_id, String number, List<Order_Detail> order_detail) {
super();
this.id = id;
this.user_id = user_id;
this.number = number;
this.order_detail = order_detail;
}
public Orders() {
super();
}
} 订单明细 Order_Detail：
package bean;
public class Order_Detail {
private int id;
private int order_id;
private int product_id;
private Product product;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getOrder_id() {
return order_id;
}
public void setOrder_id(int order_id) {
this.order_id = order_id;
}
public int getProduct_id() {
return product_id;
}
public void setProduct_id(int product_id) {
this.product_id = product_id;
}
public Product getProduct() {
return product;
}
public void setProduct(Product product) {
this.product = product;
}
@Override
public String toString() {
return "Order_Detail [id=" + id + ", order_id=" + order_id + ", product_id=" + product_id + ", product="
+ product + "]";
}
public Order_Detail(int id, int order_id, int product_id, Product product) {
super();
this.id = id;
this.order_id = order_id;
this.product_id = product_id;
this.product = product;
}
public Order_Detail() {
super();
}
} 商品类 Product：
package bean;
public class Product {
private int id;
private String product_name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getProduct_name() {
return product_name;
}
public void setProduct_name(String product_name) {
this.product_name = product_name;
}
@Override
public String toString() {
return "Product [id=" + id + ", product_name=" + product_name + "]";
}
public Product(int id, String product_name) {
super();
this.id = id;
this.product_name = product_name;
}
public Product() {
super();
}
} 2、mapper 接口和mapper.xml文件：
UserMapper 接口：
package mapper;
import java.util.List;
import bean.Orders;
import bean.UserBean;
public interface UserMapper {
/**
* description select all users
*/
public List<UserBean> selectAllUser();
/**
* description insert
*/
public int insertUser(UserBean userbean);
/**
* description delete user by id
*
*/
public int deleteUserById(int id);
/**
* description update user
*
*/
public int updateUser(UserBean userbean);
/**
* description select user by id
*
*/
public UserBean selectUserById(int id);
// 一对多 查询一个用户对应的多个订单
public List<Orders> selectOrdersOfUser();
// 多对多 查询一个用户对应的多个商品
public List<Orders> selectProductOfUser();
} xml文件 UserMapper.xml：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mapper.UserMapper">
<resultMap id="usermap" type="UserBean">
<id property="id" column="id" javaType="java.lang.Integer" />
<result property="username" column="username" javaType="java.lang.String" />
<result property="password" column="password" javaType="java.lang.String" />
</resultMap>
<!-- 一对多 查询用户的订单 -->
<resultMap id="ordersmap" type="UserBean">
<!--这个id的column是你要映射到SQL语句中的，这个property是你从真实的beans实体类的属性中的id -->
<id column="id" property="id" />
<result column="username" property="username" />
<result column="password" property="password" />
<!--因为这个地方的是一对多，要关联到的是一个集合所以使用collection -->
<collection property="ordersList" ofType="Orders">
<id column="id" property="id" />
<result column="order_number" property="number" />
</collection>
</resultMap>
<resultMap id="selectProductOfUserMap" type="UserBean">
<id column="id" property="id" />
<result column="username" property="username" />
<result column="password" property="password" />
<!-- 通过用户查询订单 ordersList 字段来自UserBean实体类中声明的Order对象名，以下的情况都是-->
<collection property="ordersList" ofType="Orders">
<id column="id" property="id" />
<result column="order_number" property="number" />
<!-- 通过订单查询订单明细 --> <collection property="order_detail" ofType="Order_Detail">
<id column="id" property="id" />
<result column="order_id" property="order_id" />
<result column="product_id" property="product_id" />
<!-- 通过订单明细查询商品 --> <association property="product" javaType="Product">
<id column="id" property="id" /> <result column="product_name" property="product_name" />
</association> </collection> </collection> </resultMap>
<select id="selectAllUser" resultMap="usermap">
select * from user </select> <insert id="insertUser">
insert into user(username,password)values(#{username},#{password}) </insert>
<delete id="deleteUserById"> delete from user where id=#{id} </delete>
<update id="updateUser"> update user set username=#{username},password=#{password} where id=#{id} </update>
<select id="selectUserById" resultMap="usermap"> select * from user where id=#{id} </select>
<!-- 一对多 查询用户的订单 -->
<select id="selectOrdersOfUser" resultMap="ordersmap"> select t1.*, t2.order_number from user t1, orders t2 where t1.id=t2.user_id </select>
<select id="selectProductOfUser" resultMap="selectProductOfUserMap">
select t1.id as user_id,t1.username, t2.id as order_id,t2.order_number, t3.id as order_detail_id,t3.product_id, t4.id as product_id,t4.product_name
from user t1, orders t2, order_detail t3, product t4 where t1.id=t2.user_id and t3.order_id=t2.id and t3.product_id=t4.id </select> </mapper> 3、逻辑层和测试 我写在了service类，但是这样不太符合规范
UserService类：
package service;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import bean.UserBean;
import mapper.UserMapper;
import tools.DB;
public class UserService {
public static void main(String[] args) {
// selectAllUser();
// insertUser();
// deleteUserById();
// updateUser();
// 一对多 查询用户的订单
// selectOrdersOfUser();
// 多对多 查询用户对应的商品
selectProductOfUser();
}
private static void selectAllUser() {
SqlSession session = DB.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
try {
List<UserBean> user = mapper.selectAllUser();
System.out.println(user.toString());
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}
}
private void insertUser() {
SqlSession session = DB.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
try {
UserBean userbean = new UserBean("zs", "123");
mapper.insertUser(userbean);
System.out.println(userbean.toString());
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}
}
private static void deleteUserById() {
SqlSession session = DB.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
try {
mapper.deleteUserById(3);
System.out.println("删除成功");
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}
}
private static void updateUser() {
SqlSession session = DB.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
UserBean userbean = mapper.selectUserById(4);
System.out.println("修改之前的userbean：" + userbean);
try {
userbean.setUsername("hhh");
userbean.setPassword("111");
mapper.updateUser(userbean);
System.out.println("修改之后的userbean：" + userbean);
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}
}
// 一对多 查询用户订单
private static void selectOrdersOfUser() {
SqlSession session = DB.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
System.out.println(mapper.selectOrdersOfUser().toString());
}
// 多对多 查询用户对应的商品
private static void selectProductOfUser() {
SqlSession session = DB.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
System.out.println(mapper.selectProductOfUser().toString());
}
} 4、mybatis.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>
<typeAliases>
<typeAlias type="bean.UserBean" alias="UserBean"/>
<typeAlias type="bean.Orders" alias="Orders"/>
<typeAlias type="bean.Order_Detail" alias="Order_Detail"/>
<typeAlias type="bean.Product" alias="Product"/>
</typeAliases>
<environments default="cybatis">
<environment id="cybatis">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="mapper"/>
</mappers>
</configuration> 5、一对多的关键点是collection标签的配置，多对多中的关键点事collection和association标签的配置。collection里面property的值是来自一对多中多的那方的对象。
多对多的关系中，我们需要将多对多关系拆分成一对多来实现。
【例如多个用户对应多个商品，可以拆分成 ：
一个用户对应多个订单（一对多）+ 一个订单对应多个订单明细（一对多） + 一个订单明细对应一个商品（一对一），最终实现用户->商品的联系。】
6、以上就是主要的代码实现，但是这样正常运行的话，在控制台出现的结果和我们将sql语句粘贴到数据库中运行得到的结果不一致。不一致的现象就是在数据库中正常得到我们需要的数据，但是在控制台中得出的却是相同用户id多个订单的情况，只能查到一条数据，原因是mybatis查到主键为id的数据，只会查到第一条。解决办法就是将表中的主键为id的修改一下，不让主键名为id，这样查到的结果就是我们得到的全部数据了。
转载于:https://www.cnblogs.com/blogs-of-xiu/p/10528791.html