主要是对之前学习的关联映射做一个案例，自己动手实践一下，可以理解的更好一点。
开发环境
开发工具：idea
Java环境: jdk1.8.0_121
数据库：SQLServer
项目结构，里面包含了三种关联映射的文件，会分别进行测试：
完整的mybatis-config.xml文件，MybatisUtils工具类和db.properties文件
<?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"/>
<settings>
<!--打开延迟加载的开关-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--将积极加载改为消息加载，即按需加载-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<!--使用扫描包创建别名-->
<typeAliases>
<package name="com.ma.po"/>
</typeAliases>
<!--配置环境，默认的环境id为sqlserver-->
<environments default="sqlserver">
<!--配置id为sqlserver的数据库环境-->
<environment id="sqlserver">
<!--使用JDBC的事务管理-->
<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="com/ma/mapper/IdCardMapper.xml"/>
<mapper resource="com/ma/mapper/PersonMapper.xml"/>
<mapper resource="com/ma/mapper/UserMapper.xml"/>
<mapper resource="com/ma/mapper/OrdersMapper.xml"/>
<mapper resource="com/ma/mapper/ProductMapper.xml"/>
</mappers>
</configuration>
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory = null;
//初始化SqlSessionFactory对象
static {
try {
//使用M主Batis提供的Resources类加载MyBatis的配置文件
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
//构建SqlSessionFactory工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取SqlSession对象的静态方法
* @return
*/
public static SqlSession getSession() {
return sqlSessionFactory.openSession();
}
}
jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://localhost:1433;DatabaseName=mybatis
jdbc.username=
jdbc.password=
一对一案例（模拟人和身份证的关系）
1.创建两张数据表，tb_idcard和tb_person，并插入一些测试数据。
create table tb_idcard(
id int identity(1,1) PRIMARY key,
code varchar(18),
);
insert into tb_idcard values ('321321199403012967');
insert into tb_idcard values ('320321188505044526');
create table tb_person(
id int identity(1,1) PRIMARY key,
name varchar(32),
age int,
sex varchar(8),
card_id int UNIQUE,
FOREIGN KEY (card_id) REFERENCES tb_idcard(id)
);
insert into tb_person values ('Rose',29,'女',1);
insert into tb_person values ('tom',26,'男',2);
2.创建两个实体类：IdCard和Person
/**
* @author mz
* @version V1.0
* @Description: 证件持久化类
* @create 2017-11-02 10:12
*/
public class IdCard {
private Integer id;
private String code;
//省略setter和getter方法
}
/**
* @author mz
* @version V1.0
* @Description: 个人持久化类
* @create 2017-11-02 10:13
*/
public class Person {
private Integer id;
private String name;
private Integer age;
private String sex;
private IdCard card; //个人关联的证件
//省略setter和getter方法
}
3.创建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="com.ma.mapper.IdCardMapper">
<!--根据id查询证件信息-->
<select id="findCodeById" parameterType="Integer" resultType="IdCard">
select * from tb_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.ma.mapper.PersonMapper">
<resultMap id="IdCardWithPersonResult" type="Person">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<!--一对一：association使用select属性引入别处一条语句-->
<association property="card" column="card_id" javaType="IdCard"
select="com.ma.mapper.IdCardMapper.findCodeById"/>
</resultMap>
<!--嵌套查询：通过执行另外一条SQL映射语句来返回预期的特殊类型-->
<select id="findPersonById" parameterType="Integer" resultMap="IdCardWithPersonResult">
select * from tb_person where id = #{id}
</select>
<resultMap id="IdCardWithPersonResult2" type="Person">
<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>
<!--嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集-->
<select id="findPersonById2" parameterType="Integer" resultMap="IdCardWithPersonResult2">
select p.*,c.code
from tb_person p,tb_idcard c
where p.card_id = c.id and p.id = #{id}
</select>
4.在测试类中添加测试方法
/**
* 嵌套查询
*/
@Test
public void findPersonByIdTest() {
//获取SqlSession
SqlSession sqlSession = MybatisUtils.getSession();
//使用MyBatis嵌套查询的方式查询id为1的人的信息
Person person = sqlSession.selectOne("com.ma.mapper.PersonMapper.findPersonById",1);
//输出信息
System.out.println(person);
//关闭SqlSession
sqlSession.close();
}
/**
* 嵌套结果
*/
@Test
public void findPersonByIdTest2() {
//获取SqlSession
SqlSession sqlSession = MybatisUtils.getSession();
//使用MyBatis嵌套查询的方式查询id为1的人的信息
Person person = sqlSession.selectOne("com.ma.mapper.PersonMapper.findPersonById2",1);
//输出信息
System.out.println(person);
//关闭SqlSession
sqlSession.close();
}
5.分别运行findPersonByIdTest()和findPersonByIdTest2()的结果如下：
从结果来看，嵌套查询要比嵌套结果多执行一条语句，结果是一样的。
一对多案例（模拟用户与订单的关系）
1.新建表tb_user和tb_orders
create table tb_user(
id int identity(1,1) PRIMARY key,
username varchar(32),
address varchar(256)
);
insert into tb_user values ('詹姆斯','克利夫兰');
insert into tb_user values ('科比','洛杉矶');
insert into tb_user values ('保罗','洛杉矶');
create table tb_orders(
id int identity(1,1) PRIMARY key,
number varchar(32) not null,
user_id int not null,
FOREIGN KEY (user_id) REFERENCES tb_user(id)
);
insert into tb_orders values ('1000011','1');
insert into tb_orders values ('1000012','2');
insert into tb_orders values ('1000013','3');
2.新建持久化类Orders和User。
public class Orders {
private Integer id; //订单id
private String number;//订单编号
private List<Product> products;//关联商品集合信息
//省略setter和getter方法
}
public class User {
private Integer id; //用户编号
private String username; //用户名
private String address; //用户地址
private List<Orders> ordersList;//用户关联的订单
//省略setter和getter方法
}
3.新建User|Mapper.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.ma.mapper.UserMapper">
<resultMap id="UserWithOrdersResult" type="User">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="address" column="address"/>
<!--一对多关联映射：collection ofType表示属性集合中元素的类型，List<Orders>属性即Orders类-->
<collection property="ordersList" ofType="Orders">
<id property="id" column="orders_id"/>
<result property="number" column="number"/>
</collection>
</resultMap>
<!--一对多：查看某一用户及其关联的订单信息，注意：当关联查询出的列名相同，则需要使用别名区分-->
<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>
</mapper>
4.添加测试代码：
/**
* 一对多
*/
@Test
public void findUserTest() {
//获取SqlSession
SqlSession sqlSession = MybatisUtils.getSession();
//查询id为1的人的信息
User user = sqlSession.selectOne("com.ma.mapper.UserMapper.findUserWithOrders",1);
//输出信息
System.out.println(user);
//关闭SqlSession
sqlSession.close();
}
5.测试结果：
多对多（模拟订单和商品的关系）
1.新建数据表tb_product和tb_ordersitem
create table tb_product(
id int identity(1,1) PRIMARY key,
name varchar(32),
price float
);
insert into tb_product values ('java','44.5');
insert into tb_product values ('java web','38.5');
insert into tb_product values ('ssm','50');
create table tb_ordersitem(
id int identity(1,1) PRIMARY key,
orders_id int,
product_id int,
FOREIGN KEY (orders_id) REFERENCES tb_orders(id),
FOREIGN KEY (product_id) REFERENCES tb_product(id)
);
insert into tb_ordersitem values ('1','1');
insert into tb_ordersitem values ('1','3');
insert into tb_ordersitem values ('3','3');
2.创建Product持久化类
public class Product {
private Integer id; //商品id
private String name; //商品名称
private double price; //商品单价
private List<Orders> orders;//与订单的关联属性
//省略setter和getter方法
}
3.创建OrdersMapper.xml和ProductMapper.xml的映射文件
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">
<mapper namespace="com.ma.mapper.OrdersMapper">
<resultMap id="OrdersWithProductResult" type="Orders">
<id property="id" column="id"/>
<result property="number" column="number"/>
<collection property="products" column="id" ofType="Product" select="com.ma.mapper.ProductMapper.findProductById">
</collection>
</resultMap>
<!--多对多嵌套查询：通过执行另外一条SQL映射语句来返回预期的特殊类型-->
<select id="findOrdersWithProduct" parameterType="Integer" resultMap="OrdersWithProductResult">
select * from tb_orders where id = #{id}
</select>
<resultMap id="OrdersWithProductResult2" type="Orders">
<id property="id" column="id"/>
<result property="number" column="number"/>
<collection property="products" ofType="Product">
<id property="id" column="pid"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
</collection>
</resultMap>
<select id="findOrdersWithProduct2" parameterType="Integer" resultMap="OrdersWithProductResult2">
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>
</mapper>
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="com.ma.mapper.ProductMapper">
<select id="findProductById" parameterType="Integer" resultType="Product">
select * from tb_product
where
id in(select product_id from tb_ordersitem where orders_id = #{id})
</select>
</mapper>
4.添加测试代码
/**
* 多对多
*/
@Test
public void findOrdersTest() {
//获取SqlSession
SqlSession sqlSession = MybatisUtils.getSession();
//查询id为1的订单中的商品的信息
Orders orders = sqlSession.selectOne("com.ma.mapper.OrdersMapper.findOrdersWithProduct",1);
//输出信息
System.out.println(orders);
//关闭SqlSession
sqlSession.close();
}
/**
* 多对多
*/
@Test
public void findOrdersTest2() {
//获取SqlSession
SqlSession sqlSession = MybatisUtils.getSession();
//查询id为1的订单中的商品的信息
Orders orders = sqlSession.selectOne("com.ma.mapper.OrdersMapper.findOrdersWithProduct2",1);
//输出信息
System.out.println(orders);
//关闭SqlSession
sqlSession.close();
}
5.分别运行findOrdersTest()和findOrdersTest2()的运行结果
小结
以上是对Mybatis对关联关系处理的一个实践，代码测试没有问题。
