一、一对一
示例以个人和身份证之间的一对一关联关系。
1.创建数据表：
create table tb_idcard( id int primary key auto_increment, code varchar(18));
create table tb_person( id int primary key auto_increment, name varchar(32),age int,sex varchar(8),card_id int unique,foreign key(card_id) references tb_idcard(id));
insert into tb_idcard(code) values("110238733008844444");
insert into tb_idcard(code) values("110238733008855555");
insert into tb_person(name,age,sex,card_id) values("kangxg",29,"男",1);
insert into tb_person(name,age,sex,card_id) values("kangxy",27,"女",2);
2.在com.kangxg.po包下创建持久化类 IdCard 和 Person
package com.kangxg.po;
public class IdCard {
private Integer id;
private String code;
public Integer getId()
{
return this.id;
}
public void setId(Integer id)
{
this.id = id;
}
public String getCode()
{
return this.code;
}
public void setCode(String code)
{
this.code = code;
}
@Override
public String toString()
{
return "Customer [id =" + id +"," +"code =" +code +"]";
}
}
package com.kangxg.po;
public class Person {
private Integer id;
private String name;
private Integer age;
private String sex;
private IdCard card;
public Integer getId()
{
return this.id;
}
public void setId(Integer id)
{
this.id = id;
}
public Integer getAge()
{
return this.age;
}
public void setAge(Integer age)
{
this.age = age;
}
public String getName()
{
return this.name;
}
public void setName(String name)
{
this.name = name;
}
public String getSex()
{
return this.sex;
}
public void setSex(String sex)
{
this.sex = sex;
}
public IdCard getCard()
{
return this.card;
}
public void setCard(IdCard card)
{
this.card = card;
}
@Override
public String toString()
{
return "Customer [id =" + id +"," +"name =" +name +", age =" +age +", sex =" +sex +", card =" +card +"]";
}
}
3.在com.kangxg.mapper包中创建映射文件IdCardMapper.xml 和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">
<!-- namespace 表示命名空间 -->
<mapper namespace="com.kangxg.mapper.IdCardMapper">
<!-- 根据id 查询证件信息 -->
<select id="findCodeById" parameterType = "Integer" resultType="IdCard">
select * from tb_idcard where id = #{id}
</select>
</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="com.kangxg.mapper.PersonMapper">
<!-- 根据id 查询证件信息 -->
<select id="findPersonById" parameterType = "Integer" resultMap="IdCardWithPersonResult">
select * from tb_person where id = #{id}
</select>
<resultMap type="Person" id="IdCardWithPersonResult">
<id property = "id" column = "id"/>
<result property = "name" column = "name"/>
<result property = "age" column = "age"/>
<result property = "sex" column = "sex"/>
<association property = "card" column = "card_id" javaType = "IdCard"
select = "com.kangxg.mapper.IdCardMapper.findCodeById"/>
</resultMap>
</mapper>4.配置核心文件，引入Mapper映射文件并设置别名
<?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"/>
<!-- 使用扫描的形式定义别名 -->
<typeAliases>
<package name = "com.kangxg.po"/>
</typeAliases>
<!-- 1.配置环境，默认环境id 为 mysql -->
<environments default="mysql">
<!-- 1.2.配置id 为 mysql 的数据库环境-->
<environment id="mysql">
<!-- JDBC 事务管理-->
<transactionManager type="JDBC"/>
<!-- 数据库连接池-->
<dataSource type="POOLED">
<!-- 数据库驱动 -->
<property name = "driver" value = "${jdbc.driver}" />
<!-- 连接数据库URL-->
<property name = "url" value = "${jdbc.url}" />
<!-- 连接数据库的用户名-->
<property name = "username" value = "${jdbc.username}" />
<!-- 连接数据库的密码-->
<property name = "password" value = "${jdbc.password}" />
</dataSource>
</environment>
</environments>
<!-- 2.配置Mapper的位置-->
<mappers>
<mapper resource="com/kangxg/mapper/IdCardMapper.xml"/>
<mapper resource="com/kangxg/mapper/PersonMapper.xml"/>
</mappers>
</configuration>5.在测试文件中添加测试方法
@Test
public void findPersonByIdTest()throws Exception
{
SqlSession sqlSession = MybatisUtils.getSession();
Person person = sqlSession.selectOne("com.kangxg.mapper"+".PersonMapper.findPersonById",1 );
System.out.println(person);
sqlSession.close();
}6.debug 运行程序
从上图中可以看出来执行了多条查询，对于大型数据结合和列表展示会导致成百上千条关联的SQL语句执行，从而极大地消耗数据库性能并且会降低查询效率。为此做如下修改：
<select id="findPersonById2" parameterType = "Integer" resultMap="IdCardWithPersonResult2">
select p.*,idcard.code
from tb_person p,tb_idcard idcard
where p.card_id = idcard.id and p.id = #{id}
</select>
<resultMap type="Person" id="IdCardWithPersonResult2">
<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> @Test
public void findPersonByIdTest2()throws Exception
{
SqlSession sqlSession = MybatisUtils.getSession();
Person person = sqlSession.selectOne("com.kangxg.mapper"+".PersonMapper.findPersonById2",1 );
System.out.println(person);
sqlSession.close();
}
多学一招：MyBatis延迟加载的设置
在使用MyBatis嵌套查询方式进行MyBatis关联查询映射时，使用MyBatis的延迟加载在一定程度上可以降低运行消耗并提高查询效率。在MyBatis默认没有开启延迟加载，需要在核心配置文件中的<setting>元素内进行配置
<properties resource = "db.properties"/>
<settings>
<!-- 打开延迟加载开关 -->
<setting name="lazyLoadingEnabled" value="True"/>
<!--将积极加载改为消息加载，即按需加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<!-- 使用扫描的形式定义别名 -->
<typeAliases>
<package name = "com.kangxg.po"/>
</typeAliases>二、一对多
1.创建两个数据表，并插入测试数据
create table tb_user( id int(32) primary key auto_increment, username varchar(32),address varchar(256));
create table tb_orders( id int(32) primary key auto_increment, number varchar(32) NOT NULL,user_id int(32) NOT NULL,foreign key(user_id) references tb_user(id));
insert into tb_user values('1','蔡英文','台北');
insert into tb_user values('2','马英九','高雄');
insert into tb_user values('3','陈水扁','监狱');
insert into tb_orders values('1','1000011','1');
insert into tb_orders values('2','1000012','1');
insert into tb_orders values('3','1000013','2');2.在com.kangxg.po包中创建持久化类Orders 和 User
package com.kangxg.po;
public class Orders {
private Integer id;
private String number;
public Integer getId()
{
return this.id;
}
public void setId(Integer id)
{
this.id = id;
}
public String getNumber()
{
return this.number;
}
public void setNumber(String number)
{
this.number = number;
}
@Override
public String toString()
{
return "Orders [id =" + id +"," +"number =" +number +"]";
}
}
package com.kangxg.po;
import java.util.List;
public class User {
private Integer id;
private String username;
private String address;
private List<Orders> ordersList;
public Integer getId()
{
return this.id;
}
public void setId(Integer id)
{
this.id = id;
}
public String getUsername()
{
return this.username;
}
public void setUsername(String username)
{
this.username = username;
}
public String getAddress()
{
return this.address;
}
public void setAddress(String address)
{
this.address = address;
}
public List<Orders> getOrders()
{
return this.ordersList;
}
public void setOrders(List<Orders> ordersList)
{
this.ordersList= ordersList;
}
@Override
public String toString()
{
return "User [id =" + id +"," +"username =" +username +", address =" +address +", ordersList =" +ordersList +"]";
}
}
3.在 com.kangxg.mapper 包中创建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">
<!-- namespace 表示命名空间 -->
<mapper namespace="com.kangxg.mapper.UserMapper">
<!-- 根据id 查询证件信息 -->
<select id="findUserWithOrders" parameterType = "Integer" resultMap="UserWithOrdersResult">
select u.*,o.id as orders_id,o.number
from tb_user u,tb_orders o
where u.id = o.user_id
and u.id = #{id}
</select>
<resultMap type="User" id="UserWithOrdersResult">
<id property = "id" column = "id"/>
<result property = "username" column = "username"/>
<result property = "address" column = "address"/>
<collection property = "ordersList" ofType = "Orders">
<id property ="id" column ="orders_id"/>
<result property ="number" column = "number"/>
</collection>
</resultMap>
</mapper>4.测试类中创建 测试方法
@Test
public void findUserTest()throws Exception
{
SqlSession sqlSession = MybatisUtils.getSession();
User user = sqlSession.selectOne("com.kangxg.mapper"+".UserMapper.findUserWithOrders",1 );
System.out.println(user);
sqlSession.close();
}5. debug 运行程序
三、多对多
以订单和商品为例，一个订单可以包含多种商品，而一种商品又可以属于多个订单，订单和商品就属于多对多的关联关系。
1.创建两个数据表，并插入测试数据
create table tb_product( id int primary key auto_increment, name varchar(32),price double);
create table tb_ordersitem( id int (32) primary key auto_increment, orders_id int(32),product_id int(32),foreign key(orders_id) references tb_orders(id),foreign key(product_id) references tb_product(id));
insert into tb_product values('1','java基础教程','44.5');
insert into tb_product values('2','iOS开发秘籍','78');
insert into tb_product values('3','C现代编程','48');
insert into tb_product values('4','Android开发实战','50');
insert into tb_ordersitem values('1','1','1');
insert into tb_ordersitem values('2','2','2');
insert into tb_ordersitem values('3','3','3');2.在com.kangxg.po包中创建持久化类Product
package com.kangxg.po;
import java.util.List;
public class Product {
private Integer id;
private String name;
private Double price;
private List<Orders> orders;
public Integer getId()
{
return this.id;
}
public void setId(Integer id)
{
this.id = id;
}
public String getName()
{
return this.name;
}
public void setName(String name)
{
this.name = name;
}
public Double getPrice()
{
return this.price;
}
public void setPrice(Double price)
{
this.price = price;
}
public List<Orders> getOrders()
{
return this.orders;
}
public void setOrders(List<Orders> orders)
{
this.orders= orders;
}
@Override
public String toString()
{
return "Product [id =" + id +"," +"name =" +name +", price =" +price +"]";
}
}
在Orders类中进行修改
private List<Product> productList;
public List<Product> getProducList()
{
return this.productList;
}
public void setProductList(List<Product> productList)
{
this.productList = productList;
}
@Override
public String toString()
{
return "Orders [id =" + id +"," +"number =" +number+"," +"productList =" +productList+"]";
}3.在com.kangxg.mapper包中创建OrdersMapper.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="com.kangxg.mapper.OrdersMapper">
<select id="findOrdersWithProduct" parameterType = "Integer" resultMap="OrdersWithProductResult">
select o.*,p.id as pid,p.name,p.price
from tb_orders o,tb_product p,tb_ordersitem oi
where oi.orders_id = o.id
and oi.product_id=p.id
and o.id = #{id}
</select>
<resultMap type="Orders" id="OrdersWithProductResult">
<id property = "id" column = "id"/>
<result property = "number" column = "number"/>
<collection property = "productList" ofType = "Product">
<id property = "id" column = "pid"/>
<result property = "name" column = "name"/>
<result property = "price" column = "price"/>
</collection>
</resultMap>
</mapper>4.在核心配置文件中增加配置
<mapper resource="com/kangxg/mapper/OrdersMapper.xml"/>5.在测试类中增加测试方法
@Test
public void findOrdersTest()throws Exception
{
SqlSession sqlSession = MybatisUtils.getSession();
Orders orders = sqlSession.selectOne("com.kangxg.mapper."+"OrdersMapper.findOrdersWithProduct",1 );
System.out.println(orders);
sqlSession.close();
}6.debug运行程序
DEBUG [main] - ==> Preparing: select o.*,p.id as pid,p.name,p.price from tb_orders o,tb_product p,tb_ordersitem oi where oi.orders_id = o.id and oi.product_id=p.id and o.id = ?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 1
Orders [id =1,number =1000011,productList =[Product [id =1,name =java基础教程, price =44.5]]]
1479
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
