文章目录
有问题加微信:quanweiSpring
1.搭建环境
1.1创建项目
创建对应的Java文件,项目名字随便起
1.2导入jar包
创建对应的lib文件夹存取对应的jar包
导入对应的jar包
1.3创建核心配置文件
在src下创建对应的Mybatis-config.xml文件
创建对应的jdbc.properties主要作用是用来解耦合
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/day06_db
username=root
password=root
参数看不懂的看MySQL对应的四大配置
Mybatis-config.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="jdbc.properties"></properties>
<!--settings-->
<settings>
<!--开启驼峰自动映射-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!--别名-->
<typeAliases>
<package name="cn.itcast.domain"></package>
</typeAliases>
<!--mybatis环境的配置
一个核心配置文件,可以配置多个运行环境,default默认使用哪个运行环境
-->
<environments default="development">
<!--通常我们只需要配置一个就可以了, id是环境的名字 -->
<environment id="development">
<!--事务管理器:由JDBC来管理-->
<!--
事务管理器type的取值:
1. JDBC:由JDBC进行事务的管理
2. MANAGED:事务由容器来管理,后期学习Spring框架的时候,所有的事务由容器管理
-->
<transactionManager type="JDBC"/>
<!--数据源的配置:mybatis自带的连接池-->
<!--
数据源:
1. POOLED:使用mybatis创建的连接池
2. UNPOOLED:不使用连接池,每次自己创建连接
3. JNDI:由服务器提供连接池的资源,我们通过JNDI指定的名字去访问服务器中资源。
-->
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!--映射器-->
<mappers>
<!--加载其它的映射文件 注:注解开发是点号-->
<!-- <package name="com.itheima.sh.dao"></package>-->
<!--加载其它的映射文件 注:不是点号-->
<!--<mapper resource="org/mybatis/example/BlogMapper.xml"/>-->
<!--
加载其它的映射文件 xml形式
包扫描方式加载mapper映射文件,说明:
1. 要求mapper映射文件,与mapper接口要放在同一个目录
2. 要求mapper映射文件的名称,与mapper接口的名称要一致
-->
<mapper resource="UserMapper.xml"></mapper>
</mappers>
</configuration>
内容看不懂的看对应的Mybatis的核心配置
1.4创建实体类
整体的包结构:
查询用过户创建User.java文件
package cn.itcast.domain;
import java.math.BigInteger;
public class User {
private BigInteger id;
private String userName;
private String password;
private String name;
private Integer age;
private Integer sex;
private Order order;
public User() {
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
", password='" + password + '\'' +
", name='" + name + '\'' +
", age=" + age +
", sex=" + sex +
", order=" + order +
'}';
}
public BigInteger getId() {
return id;
}
public void setId(BigInteger 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 String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
public Order getOrder() {
return order;
}
public void setOrder(Order order) {
this.order = order;
}
}
1.5创建接口
接口中一般为对数据库查询的业务方法,这里不牵涉复杂的业务
package cn.itcast.dao;
import cn.itcast.domain.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface UserMapper {
List<User> findAllUsers();
Map<String, User> dindUserByIdOnMap(@Param("id")String id);
@MapKey("id")
Map<String, User> findUsersByMap();
User findUserByIdMap(@Param("id")Integer id);
/* 【需求】:查询男性用户,如果输入了用户名,按用户名模糊查询,如果没有输入用户名,就查询所有男性用户**。*/
List<User> findUserBySexOrName(@Param("sex") Integer sex, @Param("name") String name);
User findUserAndOrder(@Param("orderNumber") String orderNumber);
List<User> findUserNameOrSex(@Param("sex") Integer sex,@Param("name") String name);
/*根据用户名或者住址查询所有男性用户:
如果输入了用户名则按照用户名模糊查找,
否则就按照住址查找,两个条件只能成立一个,
如果都不输入就查找用户名为“孙悟空”的用户。*/
List<User> findAllUserBysexOrName(@Param("sex") Integer sex,@Param("name") String name);
}
1.6创建Mapper映射文件
<?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">
<!--
映射文件
namespace 指定接口的类全名
-->
<mapper namespace="cn.itcast.dao.UserMapper">
<!--
查询语句
id: 接口中方法的名字
resultType:返回的实体类的类型,类全名
-->
<select id="findAllUsers" resultType="cn.itcast.domain.User">
select * from tb_user
</select>
<select id="dindUserByIdOnMap" resultType="map">
select * from tb_user where id=#{id}
</select>
<select id="findUsersByMap" resultType="map">
select * from tb_user
</select>
<resultMap id="findUserByIdMap1" type="User" autoMapping="true">
<id property="id" column="id"></id>
</resultMap>
<select id="findUserByIdMap" resultMap="findUserByIdMap1">
select * from tb_user where id=#{id}
</select>
<resultMap id="findUserBySexOrName" type="user" autoMapping="true">
<id column="id" property="id"></id>
<result property="userName" column="user_name"></result>
</resultMap>
<select id="findUserBySexOrName" resultMap="findUserBySexOrName">
select * from tb_user where sex=#{sex}
<if test="name !=null">
and name like '%${name}%'
</if>
</select>
<resultMap id="findUserAndOrder" type="User" autoMapping="true">
<id column="id" property="id" ></id>
<association property="order" javaType="order" autoMapping="true">
<id property="id" column="id"></id>
</association>
</resultMap>
<select id="findUserAndOrder" resultMap="findUserAndOrder">
SELECT
o.user_id,
o.order_number,
o.id,
u.id,
u.user_name,
u.`password`,
u.`name`,
u.age,
u.sex
FROM
tb_order AS o
LEFT JOIN tb_user AS u ON o.user_id = u.id
WHERE
o.order_number =${orderNumber};
</select>
<resultMap id="findUserNameOrSex" type="user">
<id column="id" property="id"></id>
<result property="userName" column="user_name"></result>
</resultMap>
<select id="findUserNameOrSex" resultMap="findUserNameOrSex">
select * from tb_user
where sex=#{sex}
<if test="name !=null ">
and name like "%${name}%"
</if>
</select>
<resultMap id="findAllUserBysexOrName" type="user">
<id column="id" property="id"></id>
<result property="userName" column="user_name"></result>
</resultMap>
<select id="findAllUserBysexOrName" resultMap="findAllUserBysexOrName">
select * from tb_user
where
<choose>
<when test="sex!=null">
sex=#{sex}
</when>
<when test="name!=null">
name like "%${name}%"
</when>
<otherwise>
1=1
</otherwise>
</choose>
</select>
</mapper>
1.7创建对应的工具包
提取Mybatis工具包的作用是为了简化开发,减少代码量
package cn.itcast.util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory = null;
static{
try {
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
sqlSessionFactory = sqlSessionFactoryBuilder.build(resourceAsStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession() {
SqlSession sqlSession = sqlSessionFactory.openSession();
return sqlSession;
}
public static void release(SqlSession sqlSession, InputStream inputStream ) {
if (sqlSession != null) {
sqlSession.close();
}
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
1.8创建测试类(确保项目跑通)
测是查询表中的所有数据
package cn.itcast.test;
import cn.itcast.dao.UserMapper;
import cn.itcast.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class UserFindAllDemo {
public static void main(String[] args) throws Exception {
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory build = sqlSessionFactoryBuilder.build(resourceAsStream);
SqlSession sqlSession = build.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> allUsers = mapper.findAllUsers();
for (User allUser : allUsers) {
System.out.println("allUser = " + allUser);
}
}
}
2.数据库之间的表关系
2.1 建表语句
/*
Navicat Premium Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 50727
Source Host : localhost:3306
Source Schema : day06_db
Target Server Type : MySQL
Target Server Version : 50727
File Encoding : 65001
Date: 22/06/2020 21:31:38
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for tb_item
-- ----------------------------
DROP TABLE IF EXISTS `tb_item`;
CREATE TABLE `tb_item` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`item_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品名称',
`item_price` float(6, 1) NOT NULL COMMENT '商品价格',
`item_detail` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '商品描述',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tb_item
-- ----------------------------
INSERT INTO `tb_item` VALUES (1, 'iPhone 6', 5288.0, '苹果公司新发布的手机产品。');
INSERT INTO `tb_item` VALUES (2, 'iPhone 6 plus', 6288.0, '苹果公司发布的新大屏手机。');
-- ----------------------------
-- Table structure for tb_order
-- ----------------------------
DROP TABLE IF EXISTS `tb_order`;
CREATE TABLE `tb_order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`order_number` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单号',
PRIMARY KEY (`id`) USING BTREE,
INDEX `FK_orders_1`(`user_id`) USING BTREE,
CONSTRAINT `FK_orders_1` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tb_order
-- ----------------------------
INSERT INTO `tb_order` VALUES (1, 1, '20140921001');
INSERT INTO `tb_order` VALUES (2, 2, '20140921002');
INSERT INTO `tb_order` VALUES (3, 1, '20140921003');
-- ----------------------------
-- Table structure for tb_orderdetail
-- ----------------------------
DROP TABLE IF EXISTS `tb_orderdetail`;
CREATE TABLE `tb_orderdetail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(32) NULL DEFAULT NULL COMMENT '订单号',
`item_id` int(32) NULL DEFAULT NULL COMMENT '商品id',
`total_price` double(20, 0) NULL DEFAULT NULL COMMENT '商品总价',
`status` int(11) NULL DEFAULT NULL COMMENT '状态',
PRIMARY KEY (`id`) USING BTREE,
INDEX `FK_orderdetail_1`(`order_id`) USING BTREE,
INDEX `FK_orderdetail_2`(`item_id`) USING BTREE,
CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`order_id`) REFERENCES `tb_order` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`item_id`) REFERENCES `tb_item` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tb_orderdetail
-- ----------------------------
INSERT INTO `tb_orderdetail` VALUES (1, 1, 1, 5288, 1);
INSERT INTO `tb_orderdetail` VALUES (2, 1, 2, 6288, 1);
INSERT INTO `tb_orderdetail` VALUES (3, 2, 2, 6288, 1);
INSERT INTO `tb_orderdetail` VALUES (4, 3, 1, 5288, 1);
-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
`password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '密码',
`name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
`age` int(10) NULL DEFAULT NULL COMMENT '年龄',
`sex` int(11) NULL DEFAULT NULL COMMENT '0-女 1-男',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `username`(`user_name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES (1, 'zhangsan', '123456', '张三', 30, 1);
INSERT INTO `tb_user` VALUES (2, 'lisi', '123456', '李四', 21, 0);
INSERT INTO `tb_user` VALUES (3, 'wangwu', '123456', '王五', 22, 1);
INSERT INTO `tb_user` VALUES (4, 'zhangwei', '123456', '张伟', 20, 1);
INSERT INTO `tb_user` VALUES (5, 'lina', '123456', '李娜', 28, 0);
INSERT INTO `tb_user` VALUES (6, '蔡徐坤', '123', '小菜', 18, 1);
SET FOREIGN_KEY_CHECKS = 1;
2.2数据库表之间的关联
3.高级查询
3.1一对一查询
需求:通过订单编号20140921003查询出订单信息,并查询出下单人信息
一对一的思想:
一个订单只能属于一个人
SQL语句分析:
SELECT
o.user_id,
o.order_number,
o.id,
u.id,
u.user_name,
u.password
,
u.name
,
u.age,
u.sex
FROM
tb_order AS o
LEFT JOIN tb_user AS u ON o.user_id = u.id
WHERE
o.order_number IN ( 20140921003 );
在domain实体类中创建对应的Order订单的实体类
package cn.itcast.domain;
import java.math.BigInteger;
public class Order {
private Integer id;
private BigInteger userId;
private String orderNumber;
public Order() {
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", userId=" + userId +
", orderNumber='" + orderNumber + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public BigInteger getUserId() {
return userId;
}
public void setUserId(BigInteger userId) {
this.userId = userId;
}
public String getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(String orderNumber) {
this.orderNumber = orderNumber;
}
}
在User实体类中添加关联:
private Order order;
重新写对应的getSet方法,toString方法
同样在order表中,添加User的关联
创建OrderMapper接口
package cn.itcast.dao;
import cn.itcast.domain.Order;
import org.apache.ibatis.annotations.Param;
public interface OrderMapper {
Order queryOrderAndUser(@Param(“OrderNumber”) String OrderNumber);
}
Order 的实体类,保存商品对应的信息
package cn.itcast.domain;
import java.math.BigInteger;
public class Order {
private Integer id;
private BigInteger userId;
private String orderNumber;
private User user;
public Order() {
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", userId=" + userId +
", orderNumber='" + orderNumber + '\'' +
", user=" + user +
'}';
}
public Integer getId() {
return id;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public void setId(Integer id) {
this.id = id;
}
public BigInteger getUserId() {
return userId;
}
public void setUserId(BigInteger userId) {
this.userId = userId;
}
public String getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(String orderNumber) {
this.orderNumber = orderNumber;
}
}
对应的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">
<!--
映射文件
namespace 指定接口的类全名
-->
<mapper namespace="cn.itcast.dao.OrderMapper">
<!--
查询语句
id: 接口中方法的名字
resultType:返回的实体类的类型,类全名
-->
<resultMap id="queryOrderAndUser" type="Order" autoMapping="true">
<id column="id" property="id"></id>
<association property="user" javaType="User" autoMapping="true">
<id column="id" property="id"></id>
</association>
</resultMap>
<select id="queryOrderAndUser" resultMap="queryOrderAndUser">
SELECT
*
FROM
tb_order tbo
INNER JOIN tb_user tbu ON tbo.user_id = tbu.id
WHERE
tbo.order_number = #{OrderNumber}
</select>
</mapper>
对应的测试类:
java文件:
package cn.itcast.test;
import cn.itcast.dao.OrderMapper;
import cn.itcast.domain.Order;
import cn.itcast.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class Demo02 {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
Order order = mapper.queryOrderAndUser("20140921003");
System.out.println("order = " + order);
}
}
3.2 一对多多表查询
需求:查询id为1的用户及其订单信息
对应的SQL语句:
select * from tb_user tbu inner join tb_order tbo on tbu.id = tbo.user_id where tbu.id=1;
对应的OrderMapper接口
package cn.itcast.dao;
import cn.itcast.domain.Order;
import cn.itcast.domain.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface OrderMapper {
Order findOneToOne(@Param("id") String id);
User oneTomany(@Param("id") Long id);
}
user实体类中添加的信息,用于一对多的关联
OrderMapper的配置文件
<?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">
<!--
映射文件
namespace 指定接口的类全名
-->
<mapper namespace="cn.itcast.dao.OrderMapper">
<!--
查询语句
id: 接口中方法的名字
resultType:返回的实体类的类型,类全名
-->
<resultMap id="OneToOne" type="cn.itcast.domain.Order" autoMapping="true">
<id column="id" property="id"></id>
<association property="user" javaType="User" autoMapping="true">
<id column="id" property="id"></id>
</association>
</resultMap>
<select id="findOneToOne" resultMap="OneToOne">
select * from tb_order as tbo inner join tb_user as tbu on tbo.user_id = tbu.id
where order_number = #{id}
</select>
<resultMap id="oneToMany" type="User" autoMapping="true">
<id property="id" column="uid"></id>
<collection property="orders" javaType="list" ofType="Order" autoMapping="true">
<id column="oid" property="id"></id>
</collection>
</resultMap>
<select id="oneTomany" resultMap="oneToMany">
SELECT
tbu.id as uid,
tbu.user_name,
tbu.password,
tbu.name,
tbu.age,
tbu.sex,
tbo.id as oid,
tbo.order_number
FROM
tb_user AS tbu
INNER JOIN tb_order AS tbo ON tbu.id = tbo.user_id
WHERE
tbu.id = #{id}
</select>
</mapper>
对应的测试类
package cn.itcast.text;
import cn.itcast.dao.OrderMapper;
import cn.itcast.domain.User;
import cn.itcast.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class OneToMany{
public static void main(String[] args){
SqlSession sqlSession = MybatisUtils.getSqlSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
User user = mapper.oneTomany(1L);
System.out.println(user);
}
}
3.3多对多
查询订单号为20140921001的订单的详情信息即查询订单信息+订单中的商品信息;
对应的OrderMapper接口方法:
Order findOrderAndItem(@Param(“orderNumber”) String orderNumber);
对应的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">
<!--
映射文件
namespace 指定接口的类全名
-->
<mapper namespace="cn.itcast.dao.OrderMapper">
<!--
查询语句
id: 接口中方法的名字
resultType:返回的实体类的类型,类全名
-->
<resultMap id="OneToOne" type="cn.itcast.domain.Order" autoMapping="true">
<id column="id" property="id"></id>
<association property="user" javaType="User" autoMapping="true">
<id column="id" property="id"></id>
</association>
</resultMap>
<select id="findOneToOne" resultMap="OneToOne">
select * from tb_order as tbo inner join tb_user as tbu on tbo.user_id = tbu.id
where order_number = #{id}
</select>
<resultMap id="oneToMany" type="User" autoMapping="true">
<id property="id" column="uid"></id>
<collection property="orders" javaType="list" ofType="Order" autoMapping="true">
<id column="oid" property="id"></id>
</collection>
</resultMap>
<select id="oneTomany" resultMap="oneToMany">
SELECT
tbu.id as uid,
tbu.user_name,
tbu.password,
tbu.name,
tbu.age,
tbu.sex,
tbo.id as oid,
tbo.order_number
FROM
tb_user AS tbu
INNER JOIN tb_order AS tbo ON tbu.id = tbo.user_id
WHERE
tbu.id = #{id}
</select>
<resultMap id="manyToMany" type="Order" autoMapping="true">
<id property="id" column="oid"></id>
<collection property="orderDetails" javaType="list" ofType="OrderDetail" autoMapping="true">
<id column="did" property="id"></id>
<association property="item" javaType="Item" autoMapping="true">
<id column="id" property="id"></id>
</association>
</collection>
</resultMap>
<select id="findOrderAndItem" resultMap="manyToMany">
SELECT
tbo.id as oid,
tbo.order_number,
detail.id as did,
detail.order_id,
detail.item_id,
detail.total_price,
detail.status,
item.*
FROM
tb_order AS tbo
INNER JOIN tb_orderdetail AS detail ON tbo.id = detail.order_id
INNER JOIN tb_item AS item ON detail.item_id = item.id
WHERE
tbo.order_number = #{orderNumber}
</select>
</mapper>
对应的测试类:
package cn.itcast.text;
import cn.itcast.dao.OrderMapper;
import cn.itcast.domain.Order;
import cn.itcast.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class ManyToMany {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
OrderMapper sqlSessionMapper = sqlSession.getMapper(OrderMapper.class);
Order orderAndItem = sqlSessionMapper.findOrderAndItem("20140921001");
System.out.println("orderAndItem = " + orderAndItem);
}
}
注意主要了解对应的执行流程,掌握对应的执行流程,才会明白内部的原理