(一)关联关系概述
(二)一对一实例演示
项目文件结构图
01创建t_idcard和t_person数据表
CREATE TABLE `t_idcard` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `t_idcard` VALUES ('1', '4319090909090');
INSERT INTO `t_idcard` VALUES ('2', '4301919191919191');
----------
CREATE TABLE `t_person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`card_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`card_id`),
CONSTRAINT `t_person_ibfk_1` FOREIGN KEY (`card_id`) REFERENCES `t_idcard` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `t_person` VALUES ('1', '张三', '1');
INSERT INTO `t_person` VALUES ('2', '李四', '2');
02.创建表对应的 JavaBean 对象
IdCard.java类的代码如下:
package com.wang.po;
public class IdCard {
private int id;
private String code;
----省略setter和getter----
@Override
public String toString() {
return "IdCard [id=" + id + ", code=" + code + "]";
}
}
Person.java类的代码如下:
package com.wang.po;
public class Person {
private int id;
private String name;
private IdCard card;
----省略setter和getter----
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", card=" + card + "]";
}
}
03.在Mapper.xml编写SQL语句
查询的两种方式如图所示:
IdCardMapper.xml(嵌套查询SQL)
<?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+id确定一条SQL语句 -->
<mapper namespace="com.wang.mapper.IdCardMapper">
<select id="findCodeById" parameterType="Integer"
resultType="com.wang.po.IdCard">
select*from t_idcard where id=#{id}
</select>
</mapper>
PersonMapper.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="com.wang.mapper.PersonMapper">
<!--从t_person表中查询数据映射到Person类-->
<select id="findPersonById" parameterType="Integer"
resultMap="IdCardWithPersonResult">
select*from t_person where id=#{id}
</select>
<!--t_person表中的数据映射到Person类的实现方式-->
<resultMap type="com.wang.po.Person" id="IdCardWithPersonResult">
<!--注:如果数据表字段名于POJO类的属性名一致,下面元素配置可以省略-->
<id property="id" column="id"/>
<result property="name" column="name"/>
<!--1.card是Person类中的属性,用于实现一对一关联映射 -->
<!--2.card_id是t_person的外键 -->
<association property="card" column="card_id"
javaType="com.wang.po.IdCard"
<!--嵌套一个子查询SQL语句-->
select="com.wang.mapper.IdCardMapper.findCodeById"/>
</resultMap>
</mapper>
PersonMapper.xml的作用(嵌套结果推荐使用)
- 将t_person和t_idcard表查询出来,封装在Person类中。
- 深入理解Mybatis中的resultType和resultMap的区别。
<?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.wang.mapper.PersonMapper">
<select id="findPersonById" parameterType="Integer"
resultMap="IdCardWithPersonResult">
<!--编写多表关联查询SQL语句,将获取数据库中的数据映射到Person实体类中-->
select*from t_person p,t_idcard idcard
where p.id=idcard.id and p.id=#{id}
</select>
<resultMap type="com.wang.po.Person"
id="IdCardWithPersonResult">
<!--Person属性和t_person表字段一一对应-->
<id property="id" column="id" />
<result property="name" column="name" />
<!--association元素用于实现一对一关联映射-->
<association property="card" javaType="com.wang.po.IdCard">
<!--IdCard属性和t_idcard表字段一一对应-->
<id property="id" column="card_id" />
<result property="code" column="code" />
</association>
</resultMap>
</mapper>
<association> 元素中的属性使用
属性 | 说明 |
---|---|
property | POJO实体类的属性,与表字段一一对应 |
column | 数据表字段名,与POJO实体类的属性一一对应 |
javaType | 指实体对象属性的数据类型,比如card属于IdCard类型 |
select | 引入嵌套查询的子SQL语句 |
fetchType | 指定关联查询是否启用延迟加载。fetchType属性有lazy和eager两个值,默认为lazy表示延迟加载 |
04.测试程序运行
package com.wang.Test;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.wang.Utils.MybatisUtils;
import com.wang.po.Person;
public class MybatisTest {
@Test
public void findPersonById() {
SqlSession sqlSession = MybatisUtils.getSession();
Person p=sqlSession.selectOne("com.wang.mapper.PersonMapper.findPersonById",1);
System.out.println(p);
sqlSession.close();
}
}
05.嵌套查询的结果
DEBUG [main] - ==> Preparing: select*from t_person where id=?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - ====> Preparing: select*from t_idcard where id=?
DEBUG [main] - ====> Parameters: 1(Integer)
DEBUG [main] - <==== Total: 1
DEBUG [main] - <== Total: 1
Person [id=1, name=张三, card=IdCard [id=1, code=4319090909090]]
06.嵌套结果的结果
DEBUG [main] - ==> Preparing: select*from t_person p,t_idcard idcard
where p.id=idcard.id and p.id=?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 1
Person [id=1, name=张三, card=IdCard [id=1, code=4319090909090]]
(三)一对多实例演示
使用<collection> 元素处理一对多的关联关系,要是的类于类之间有一对多的关联关系,那么一个类中的属性必须是另一个类的集合。
01.比如一个客户对应多个订单,创建t_user和t_order数据表
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`pnumber` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `t_user` VALUES ('1', '张三', '长沙', '18390991212');
INSERT INTO `t_user` VALUES ('2', '李四', '深圳', '13713780808');
----------
CREATE TABLE `t_order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`number` varchar(255) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `t_order` VALUES ('1', '323423424', '1');
INSERT INTO `t_order` VALUES ('2', '132432333', '2');
02.创建表对应的 JavaBean 对象
Order.java
package com.wang.po;
public class Order {
private int id;
private String number;
----省略setter和getter----
@Override
public String toString() {
return "User [id=" + id + ", number=" + number + "]";
}
}
User.java
package com.wang.po;
import java.util.List;
public class User {
private int id;
private String name;
private String address;
private String pnumber;
private List<Order> ordersList;//实现一对多关联映射
----省略setter和getter----
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ",
address=" + address + ", pnumber=" + pnumber + ", ordersList="
+ ordersList + "]";
}
}
03.在UserMapper.xml编写SQL语句(开发大部分使用:嵌套结果查询)
<?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.wang.mapper.UserMapper">
<!-- 一对多:查看某一用户及其关联的订单信息 注意:当关联查询出的列名相同,则需要使用别名区分 -->
<!-- user表中id字段名和order中的id字段同名,必须为一个字段取别名-->
<select id="findUserById" parameterType="Integer"
resultMap="UserWithOrdersResult">
select u.*,
o.id as order_id,
o.number
from t_user u,t_order o
where u.id=o.user_id and u.id=#{id}
</select>
<resultMap type="com.wang.po.User" id="UserWithOrdersResult">
<!--数据表字段与User属性对应 -->
<id property="id" column="id" />
<result property="name" column="name" />
<result property="address" column="address" />
<result property="pnumber" column="pnumber" />
<!-- 一对多关联映射:collection ofType表示属性集合中元素的类型,
List<Order>属性即Order类 -->
<collection property="ordersList"
ofType="com.wang.po.Order">
<!-- 使用数据表字段别名和Order属性对应 -->
<id property="id" column="order_id" />
<result property="number" column="number" />
</collection>
</resultMap>
</mapper>
04.测试程序运行
package com.wang.Test;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.wang.Utils.MybatisUtils;
import com.wang.po.User;
public class MybatisTest {
@Test
public void findUserById() {
SqlSession sqlSession = MybatisUtils.getSession();
User user=sqlSession.selectOne("com.wang.mapper.UserMapper.findUserById",1);
System.out.println(user);
sqlSession.close();
}
}
05.嵌套结果的结果
DEBUG [main] - ==> Preparing: select u.*, o.id as order_id, o.number
from t_user u,t_order o where u.id=o.user_id and u.id=?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 1
User [id=1, name=张三, address=长沙, pnumber=18390991212,
ordersList=[User [id=1, number=323423424]]]
(四)多对多实例演示( 注:下载多对多项目源码 )
项目文件结构图所示:
01创建 t_product、t_order和t_orderitem数据表
CREATE TABLE `t_product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20003 DEFAULT CHARSET=utf8;
INSERT INTO `t_product` VALUES ('20001', 'Mybatis入门', '59.90');
INSERT INTO `t_product` VALUES ('20002', 'SSH整合教程', '68.90');
----------
CREATE TABLE `t_order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`number` varchar(255) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `t_order` VALUES ('1', '323423424', '1');
INSERT INTO `t_order` VALUES ('2', '132432333', '2');
----------
CREATE TABLE `t_orderitem` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) DEFAULT NULL,
`product_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `order_id` (`order_id`),
KEY `product_id` (`product_id`),
CONSTRAINT `t_orderitem_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `t_order` (`id`),
CONSTRAINT `t_orderitem_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `t_product` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8;
INSERT INTO `t_orderitem` VALUES ('1001', '1', '20001');
INSERT INTO `t_orderitem` VALUES ('1002', '2', '20002');
02.创建Order.java和Product.java实现多对多
Order.java
package com.wang.po;
import java.util.List;
public class Order {
private int id;
private String number;
private List<Product> productlist;//实现多对多
------省略setter和getter------
@Override
public String toString() {
return "Order [id=" + id + ", number=" + number + ",
productlist=" + productlist + "]";
}
}
Product.java
package com.wang.po;
import java.util.List;
public class Product {
private int id;
private String name;
private double price;
private List<Order> orders;//实现多对多
------省略setter和getter------
@Override
public String toString() {
return "Product [id=" + id + ", name=" + name + ", price=" + price + "]";
}
}
03.在OrderMapper.xml编写SQL语句,把数据库中的数据封装到Order.java类
<?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.wang.mapper.OrderMapper">
<select id="findOrderandProductById" parameterType="integer"
resultMap="OrderWithProduct">
SELECT
p.*, o.id AS order_id,
o.number
FROM
t_product p,
t_order o
WHERE
o.id =#{id}
</select>
<!-- 映射到的类型是一个普通Java类 -->
<resultMap type="com.wang.po.Order" id="OrderWithProduct">
<id property="id" column="order_id" />
<result property="number" column="number" />
<collection property="productlist"
ofType="com.wang.po.Product">
<id property="id" column="id" />
<result property="name" column="name" />
<result property="price" column="price" />
</collection>
</resultMap>
</mapper>
04.mybatis-config.xml中引入OrderMapper.xml
<mappers>
<mapper resource="com/wang/mapper/OrderMapper.xml"/>
</mappers>
05.测试程序运行
package com.wang.Test;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.wang.Utils.MybatisUtils;
import com.wang.po.Order;
public class MybatisTest {
@Test
public void findUserById() {
SqlSession sqlSession = MybatisUtils.getSession();
Order order=sqlSession.selectOne("com.wang.mapper."
+ "OrderMapper.findOrderandProductById", 1);
System.out.println(order);
sqlSession.close();
}
}
06运行结果
DEBUG [main] - ==> Preparing: SELECT p.*, o.id AS order_id, o.number FROM
t_product p, t_order o WHERE o.id =?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 2
Order [id=1, number=323423424, productlist=
[Product [id=20001, name=Mybatis入门, price=59.9],
Product [id=20002, name=SSH整合教程, price=68.9]]]