1.一对一
例:人与身份证
一个人只有一个身份证,一个身份证对应一个人。
那么MyBatis如何处理这种关系呢?
(1)创建数据库表
创建数据库 mybatis
创建表idcard
创建表person
分别插入几条数据用于测试
(2)在MyEclipse创建一个名为project03的Web项目,然后导入相关jar包、log4j日志文件、MyBatis工具类以及mybatis-config.xml核心配置文件。
(3)在项目的cn.swjd.entries包下创建持久化类IdCard和Person。
package cn.swjd.entries;
public class IdCard {
private Integer id;
private String code;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
@Override
public String toString() {
return "IdCard [id=" + id + ", code=" + code + "]";
}
}
package cn.swjd.entries;
public class Person {
private Integer id;
private String name;
private Integer age;
private String sex;
private IdCard card;//外键类型定义为相关类
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
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 String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public IdCard getCard() {
return card;
}
public void setCard(IdCard card) {
this.card = card;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", age=" + age
+ ", sex=" + sex + ", card=" + card + "]";
}
}
(4)在cn.swjd.dao包下,创建证件映射文件IdCardMapper.xml和个人映射文件PersonMapper.xml。
IdCardMapper.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="cn.swjd.dao.IdCardMapper">
<!-- 根据id查询证件信息 -->
<select id="findCodeById" parameterType="Integer" resultType="cn.swjd.entries.IdCard">
select * from 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="cn.swjd.dao.PersonMapper">
<!-- 根据id查询个人信息 -->
<!-- 方法一:嵌套查询(即子查询) -->
<select id="findPersonById1" parameterType="Integer" resultMap="IdCardwithPerson1">
select * from person where id=#{id}
</select>
<resultMap type="person" id="IdCardwithPerson1">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<!-- 一对一,使用association引入另一条sql语句 -->
<association property="card" column="card_id" javaType="idCard" select="cn.swjd.dao.IdCardMapper.findCodeById" />
</resultMap>
<!-- 方法二:嵌套结果(即多表关联查询)(推荐) -->
<select id="findPersonById2" parameterType="Integer" resultMap="IdCardwithPerson2">
select p.*,i.code from person p,idcard i where p.id=#{id} and p.id=i.id;
</select>
<resultMap type="person" id="IdCardwithPerson2">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<association property="card" javaType="idCard">
<id property="id" column="card_id"/>
<result property="code" column="code"/>
</association>
</resultMap>
</mapper>
(5)在核心配置文件mybatis-config.xml中,引入数据库连接配置文件db.properties,引入Mapper映射文件并定义别名。
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="db.properties"></properties>
<!-- 使用扫描包形式定义别名 -->
<typeAliases >
<package name="cn.swjd.entries"/>
</typeAliases>
<environments default="sqlserver">
<environment id="sqlserver">
<transactionManager type="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>
<!-- 配置mapper位置 -->
<mappers>
<mapper resource="cn/swjd/dao/IdCardMapper.xml"/>
<mapper resource="cn/swjd/dao/PersonMapper.xml"/>
</mappers>
</configuration>
据库连接配置文件db.properties:
jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://localhost:1433;DataBaseName=mybatis
jdbc.username=sa
jdbc.password=123
(6)在cn.swjd.dao包中,创建类PersonDao,并定义两个查询方法。
package cn.swjd.dao;
import org.apache.ibatis.session.SqlSession;
import cn.swjd.entries.Person;
import cn.swjd.utils.MyBatisUtils;
public class PersonDao {
public void findPersonById1(int id){
SqlSession sqlSession = MyBatisUtils.getSession();
Person p=sqlSession.selectOne("cn.swjd.dao.PersonMapper.findPersonById1",id);
System.out.println(p);
}
public void findPersonById2(int id){
SqlSession sqlSession = MyBatisUtils.getSession();
Person p=sqlSession.selectOne("cn.swjd.dao.PersonMapper.findPersonById2",id);
System.out.println(p);
}
}
(7)在包cn.swjd.test下,创建测试类Test
package cn.swjd.test;
import cn.swjd.dao.PersonDao;
public class Test {
public static void main(String[] args) {
PersonDao p=new PersonDao();
p.findPersonById1(1);
p.findPersonById2(2);
}
}
运行测试类,控制台输出结果:
至此,通过了两种方法实现了MyBatis的一对一关联查询。
2.一对多
例:用户与订单
一个用户可以有多个订单,同时多个订单归一个用户所有。
那么使用MyBatis如何处理这种一对多关联关系呢?
(1)在mybatis创建两个表,分别为users,orders,并插入几条数据用于测试。
create table users
(
id int primary key identity,
username varchar(32),
address varchar(256)
)
insert into users values ('张三','湖南永州');
insert into users values ('李四','湖南衡阳');
insert into users values ('王五','湖南长沙');
create table orders
(
id int primary key identity,
number varchar(32) not null,
user_id int references users (id)
)
insert into orders values ('1000011',1);
insert into orders values ('1000012',2);
insert into orders values ('1000013',3);
(2) 在包cn.swjd.entries下,创建订单持久化类Order和用户持久化类User 。
package cn.swjd.entries;
public class Order {
//订单id
private Integer id;
//订单编号
private String number;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
@Override
public String toString() {
return "Orders [id=" + id + ", number=" + number + "]";
}
}
package cn.swjd.entries;
import java.util.List;
public class User {
private Integer id; //用户编号
private String username; //用户名
private String address; //用户地址
private List<Order> ordersList; //用户关联的订单
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public List<Order> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Order> ordersList) {
this.ordersList = ordersList;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", address="
+ address + ", ordersList=" + ordersList + "]";
}
}
(3)在包cn.swjd.dao下,创建用户实体映射文件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="cn.swjd.dao.UserMapper">
<!-- 一对多,根据用户id查询某一用户及其关联的订单信息 -->
<select id="findUserWithOrders" parameterType="Integer" resultMap="UserWithOrdersResult">
select u.*,o.id as order_id,o.number from users u,orders o where u.id=#{id} and u.id=o.id
</select>
<resultMap type="User" id="UserWithOrdersResult">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="address" column="address"/>
<!-- 一对多关联映射这里要使用collection元素 ,ofType表示元素集合的类型 -->
<collection property="ordersList" ofType="Order">
<id property="id" column="order_id"/>
<result property="number" column="number"/>
</collection>
</resultMap>
</mapper>
(4)将映射文件UserMapper.xml 配置到mybatis-config.xml。
<mapper resource="cn/swjd/dao/UserMapper.xml"/>
(5)在包cn.swjd.dao,创建类UserDao,并写一个方法。
package cn.swjd.dao;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import cn.swjd.entries.User;
import cn.swjd.utils.MyBatisUtils;
public class UserDao {
public void findUserWithOrdersById(int id){
SqlSession sqlSession = MyBatisUtils.getSession();
User user=sqlSession.selectOne("cn.swjd.dao.UserMapper.findUserWithOrders", id);
System.out.println(user);
}
}
(6)在测试类Test里调用方法
package cn.swjd.test;
import cn.swjd.dao.UserDao;
public class Test {
public static void main(String[] args) {
UserDao us=new UserDao();
us.findUserWithOrdersById(1);
}
}
运行结果:
可以看出,使用MyBatis嵌套结果的方式查询出了用户及其关联的订单信息集合。
3.多对多
例:订单与商品
一个订单可以包含多个商品,而一种商品又可以属于多个订单。
那么使用MyBatis如何处理这种多对多关联关系呢?
(1)在mybatis数据库里再创建表商品信息表product,关系表ordersitem,并插入几条数据用于测试。
create table product
(
id int primary key identity,
name varchar(32) not null,
price decimal not null
)
insert into product values('Java 入门',44.3);
insert into product values('Java Web程序入门',36.8);
insert into product values('SSM框架开发教程',36.8);
create table ordersitem
(
id int primary key identity,
order_id int references orders (id),
product_id int references product (id)
)
insert into ordersitem values (1,1);
insert into ordersitem values (1,3);
insert into ordersitem values (3,3);
(2) 在包cn.swjd.entries下,修改订单持久化类Order并创建商品持久化类Product 。
package cn.swjd.entries;
import java.util.List;
public class Order {
//订单id
private Integer id;
//订单编号
private String number;
//关联商品集合信息
private List<Product> productsList;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public List<Product> getproductsList() {
return productsList;
}
public void setproductsList(List<Product> productsList) {
this.productsList = productsList;
}
@Override
public String toString() {
return "Order [id=" + id + ", number=" + number + ", productsList="
+ productsList + "]";
}
}
package cn.swjd.entries;
import java.util.List;
public class Product {
private Integer id; //商品id
private String name; //商品名字
private Double price; //商品单价
private List<Order> ordersList; //与订单的关联属性
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public List<Order> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Order> ordersList) {
this.ordersList = ordersList;
}
@Override
public String toString() {
return "Product [id=" + id + ", name=" + name + ", price=" + price
+ ", ordersList=" + ordersList + "]";
}
}
(3)在包cn.swjd.dao下,创建订单实体映射文件OrdersMapper.xml和商品实体映射文件ProductMapper.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="cn.swjd.dao.OrdersMapper">
<select id="findOrderWithProducts" parameterType="Integer" resultMap="OrderWithProducts">
select o.*,p.id as product_id,p.name,p.price from orders o,ordersitem i,product p where i.order_id=o.id and i.product_id=p.id and i.order_id=#{id};
</select>
<resultMap type="Order" id="OrderWithProducts">
<id property="id" column="id"/>
<result property="number" column="number"/>
<collection property="productsList" ofType="Product">
<id property="id" column="product_id"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
</collection>
</resultMap>
</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">
<mapper namespace="cn.swjd.dao.ProductMapper">
<select id="findProductWithOrdersById" parameterType="Integer" resultMap="ProductWithOrders">
select p.*,o.id as order_id,o.number from orders o,ordersitem i,product p where i.order_id=o.id and i.product_id=p.id and i.product_id=#{id}
</select>
<resultMap type="Product" id="ProductWithOrders">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
<collection property="ordersList" ofType="Order">
<id property="id" column="order_id"/>
<result property="number" column="number"/>
</collection>
</resultMap>
</mapper>
(4)将单实体映射文件OrdersMapper.xml和商品实体映射文件ProductMapper.xml 配置到mybatis-config.xml。
<mapper resource="cn/swjd/dao/OrdersMapper.xml"/>
<mapper resource="cn/swjd/dao/ProductMapper.xml"/>
(5)在包cn.swjd.dao,创建类OrdersDao和ProductDao,分别写一个方法。
package cn.swjd.dao;
import org.apache.ibatis.session.SqlSession;
import cn.swjd.entries.Order;
import cn.swjd.utils.MyBatisUtils;
public class OrdersDao {
public void findOrderWithProducts(int id){
SqlSession sqlSession = MyBatisUtils.getSession();
Order od=sqlSession.selectOne("cn.swjd.dao.OrdersMapper.findOrderWithProducts",id);
System.out.println(od);
}
}
package cn.swjd.dao;
import org.apache.ibatis.session.SqlSession;
import cn.swjd.entries.Product;
import cn.swjd.utils.MyBatisUtils;
public class ProductDao {
public void findProductWithOrdersById(int id){
SqlSession sqlSession = MyBatisUtils.getSession();
Product p=sqlSession.selectOne("cn.swjd.dao.ProductMapper.findProductWithOrdersById", id);
System.out.println(p);
}
}
(6)在测试类Test里调用方法
package cn.swjd.test;
import cn.swjd.dao.OrdersDao;
import cn.swjd.dao.ProductDao;
public class Test {
public static void main(String[] args) {
OrdersDao orderdao=new OrdersDao();
orderdao.findOrderWithProducts(1);
ProductDao productdao=new ProductDao();
productdao.findProductWithOrdersById(3);
}
}
运行结果:
可以看出,实现了根据订单ID查出订单以及其关联的商品信息, 根据商品ID查出商品以及其关联的订单信息, 这就是MyBatis多对多的关联查询。
至此,MyBatis的关联映射就已经介绍完毕。
MyBatis延迟加载配置
在使用MyBatis嵌套查询方式进行MyBatis关联查询映射时,使用MyBatis的延迟加载在一定程度上可以降低运行消耗并提高查询效率。MyBatis默认没有开启延迟加载,需要在核心配置文件mybatis-config.xml中的<settings>元素内配置,具体配置方式如下:
<settings>
<!-- 打开延迟加载的开关 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 将积极加载改为延迟加载,即按需加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>