Mybatis一对多联表查询Demo
说明
熟悉Mybatis框架的使用
熟悉数据库表关联关系的处理原则：多方持有一方的对象，一方持有多方的集合
1. 物理建模：数据库建库建表
#创建数据库ssm
CREATE DATABASE ssm DEFAULT CHARSET utf8;
#使用(打开)ssm数据库
use ssm;
#创建顾客表
Create table customer(
id int primary key auto_increment,
name varchar(32),
age int
);
insert into customer values(1,'张三',22);
insert into customer values(2,'李四',23);
insert into customer values(3,'王五',24);
#创建订单表
Create table orders(
id int primary key auto_increment,
orderNumber varchar(16),orderPrice double,
customer_id int ,
foreign key (customer_id) references customer(id)
);
insert into orders values(11,20,22.22,1);
insert into orders values(12,60,16.66,1);
insert into orders values(13,90,19.99,2);
select * from customer;
select * from orders;
2. Demo目录结构：
3. 创建实体类
3.1. 订单实体类：Order.java
package cn.kbkwwk.mybatis.pojo;
public class Order {
private Integer id;//主键id
private String orderNumber;//商品总数量
private Double orderPrice;//商品总结
private Integer customerId;//订单所属顾客id
// 多方持有一方的对象
private Customer customer;//订单所属的顾客
}
3.2. 顾客实体类属性：Customer.java
package cn.kbkwwk.mybatis.pojo;
public class Customer {
private Integer id;//主键id
private String name;//名称
private Integer age;//年龄
//一对多，一方持有多方的集合
private List<Order> ordersList;//顾客所有的订单信息，可为空
}
4. 数据库配置文件：jdbc.properties
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm?useUnicode=true&characterEncoding=utf8&useSSL=true&allowMultiQueries=true
jdbc.username=root
jdbc.password=123456
5. 引入依赖：Pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>cn.kbkwwk.mybatis</groupId>
<artifactId>mybatis-one2many</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<properties>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<!--手动设置资源文件路径，否则打包之后会丢失静态资源配置文件-->
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
</resource>
</resources>
</build>
<!-- 引入依赖-->
<dependencies>
<!--mybatis依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
<scope>compile</scope>
</dependency>
<!--mysql数据库链接驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
<scope>compile</scope>
</dependency>
<!--单元测试依赖-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
6. Mybatis核心配置文件：SqlMapConfig.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>
<!-- 1. 配置数据库源文件的路径-->
<properties resource="jdbc.properties"></properties>
<!-- 2. 配置日志处理-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!-- 3. 设置实体类的映射别名-->
<typeAliases>
<package name="cn.kbkwwk.mybatis.pojo"/>
</typeAliases>
<!-- 4. 设置环境-->
<environments default="env">
<environment id="env">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!-- 5. 批量设置mapper的映射路径-->
<mappers>
<package name="cn.kbkwwk.mybatis.mapper"/>
</mappers>
</configuration>
7. 创建实体类功能相关mapper(关键)
7.1. 顾客相关接口及其映射：
7.1.1. 接口：CustomerMapper.java
package cn.kbkwwk.mybatis.mapper;
import cn.kbkwwk.mybatis.pojo.Customer;
public interface CustomerMapper {
// 根据id查找顾客
Customer getOne(Integer id);
// 根据订单中的顾客id查找顾客，为order特地封装的函数，简化orderMapper.xml的封装
Customer findCustomerForOrderMap(Integer id);
}
7.1.2. sql映射：CustomerMapper.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.kbkwwk.mybatis.mapper.CustomerMapper">
<!-- // 根据id查找顾客
Customer getOne(Integer id);
左外连接 联表查询，查找顾客及其相关的订单，封装到顾客实体类中
顾客类
private Integer id;
private String name;
private Integer age;
//一对多，一方持有多方的集合
private List<Order> ordersList;//顾客所有的订单信息，可为空
订单类
private Integer id;
private String orderNumber;
private Double orderPrice;
private Integer customerId;
// 多方持有一方的对象
private Customer customer;//订单所属的顾客
-->
<!-- 方案一：手工映射顾客实体类-->
<!-- <resultMap id="customerMap" type="customer">-->
<!--<!– 主键列映射–>-->
<!-- <id property="id" column="cid"></id>-->
<!--<!– 非主键映射–>-->
<!-- <result property="name" column="name"></result>-->
<!-- <result property="age" column="age"></result>-->
<!--<!– 额外查询的映射,List–>-->
<!-- <collection property="ordersList" ofType="order">-->
<!-- <id property="id" column="oid"></id>-->
<!-- <result property="orderNumber" column="orderNumber"></result>-->
<!-- <result property="orderPrice" column="orderPrice"></result>-->
<!-- <result property="customerId" column="customer_id"></result>-->
<!-- </collection>-->
<!-- </resultMap>-->
<!-- <select id="getOne" parameterType="integer" resultMap="customerMap">-->
<!-- select c.id cid, name, age, o.id oid, orderNumber, orderPrice, customer_id-->
<!-- from customer c left join `order` o on c.id = o.customer_id-->
<!-- where c.id = #{id}-->
<!-- </select>-->
<!-- 方案二：不做联表查询，做两次单表查询，将两次查询结果封装到实体类中-->
<!-- <resultMap id="customerMap" type="customer">-->
<!-- <id property="id" column="cid"></id>-->
<!-- <result property="name" column="name"></result>-->
<!-- <result property="age" column="age"></result>-->
<!-- <collection property="ordersList" ofType="order" column="id" select="findOrderForCustomerMap" ></collection>-->
<!-- </resultMap>-->
<!-- <select id="getOne" parameterType="integer" resultMap="customerMap">-->
<!-- select id,name,age-->
<!-- from customer-->
<!-- where id = #{id}-->
<!-- </select>-->
<!-- <select id="findOrderForCustomerMap" parameterType="integer" resultType="order">-->
<!-- select id, orderNumber, orderPrice, customer_id-->
<!-- from `order`-->
<!-- where customer_id = #{id}-->
<!-- </select>-->
<!-- 方案三：优化方案二，在OrderMapper中为customerMap特地写一个查询并封装order的接口-->
<resultMap id="customerMap" type="customer">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<result property="age" column="age"></result>
<!--
ofType:返回值类型
column:需要传给被调方法作为参数值的列名
select：被调用的接口全类名.方法名
-->
<collection property="ordersList" ofType="order" column="id"
select="cn.kbkwwk.mybatis.mapper.OrderMapper.findOrderForCustomerMap" ></collection>
</resultMap>
<select id="getOne" parameterType="integer" resultMap="customerMap">
select id,name,age from customer where id = #{id}
</select>
<!--
// 根据订单中的顾客id查找顾客，为order特地封装的函数，简化orderMapper.xml的封装
Customer findCustomerForOrderMap(Integer id);
-->
<select id="findCustomerForOrderMap" parameterType="integer" resultType="customer">
select id,name,age from customer where id=#{customerId}
</select>
</mapper>
7.2. 订单相关接口及其映射：
7.2.1. 接口：OrderMapper.java
package cn.kbkwwk.mybatis.mapper;
import cn.kbkwwk.mybatis.pojo.Order;
public interface OrderMapper {
// 根据id查询订单
Order getOne(Integer id);
// 根据顾客订单编号查找订单，为customer特地封装的函数，简化customerMapper.xml的封装
Order findOrderForCustomerMap(Integer customerId);
}
7.2.2. 映射：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">
<mapper namespace="cn.kbkwwk.mybatis.mapper.OrderMapper">
<!--
// 根据id查询订单
Order getOne(Integer id);
顾客类
private Integer id;
private String name;
private Integer age;
订单类
private Integer id;
private String orderNumber;
private Double orderPrice;
private Integer customerId;
// 多方持有一方的对象
private Customer customer;//订单所属的顾客
-->
<!-- <resultMap id="orderMap" type="order">-->
<!-- <id property="id" column="oid"></id>-->
<!-- <result property="orderNumber" column="orderNumber"></result>-->
<!-- <result property="orderPrice" column="orderPrice"></result>-->
<!-- <result property="customerId" column="customer_id"></result>-->
<!-- <association property="customer" javaType="customer">-->
<!-- <id property="id" column="cid"></id>-->
<!-- <result property="name" column="name"></result>-->
<!-- <result property="age" column="age"></result>-->
<!-- </association>-->
<!-- </resultMap>-->
<!-- <select id="getOne" parameterType="integer" resultMap="orderMap">-->
<!-- select o.id oid, orderNumber , orderPrice, customer_id, c.id cid, name,age-->
<!-- from `order` o inner join `customer` c on o.customer_id = c.id-->
<!-- where o.id = #{id}-->
<!-- </select>-->
<!-- 方案二：两次单表查询拼接-->
<!-- <resultMap id="orderMap" type="order">-->
<!-- <id property="id" column="id"></id>-->
<!-- <result property="orderNumber" column="orderNumber"></result>-->
<!-- <result property="orderPrice" column="orderPrice"></result>-->
<!-- <result property="customerId" column="customerId"></result>-->
<!-- <association property="customer" javaType="customer" select="findCustomerForOrderMap"-->
<!-- column="customerId"></association>-->
<!-- </resultMap>-->
<!-- <select id="getOne" parameterType="integer" resultMap="orderMap">-->
<!-- select id ,orderNumber , orderPrice, customer_id customerId-->
<!-- from `order`-->
<!-- where id = #{id}-->
<!-- </select>-->
<!-- <select id="findCustomerForOrderMap" parameterType="integer" resultType="customer">-->
<!-- select id,name,age from customer where id = #{id}-->
<!-- </select>-->
<!-- 方案三：优化方案二-->
<resultMap id="orderMap" type="order">
<id property="id" column="id"></id>
<result property="orderNumber" column="orderNumber"></result>
<result property="orderPrice" column="orderPrice"></result>
<result property="customerId" column="customerId"></result>
<association property="customer" javaType="customer"
select="cn.kbkwwk.mybatis.mapper.CustomerMapper.findCustomerForOrderMap"
column="customerId"></association>
</resultMap>
<select id="getOne" parameterType="integer" resultMap="orderMap">
select id ,orderNumber , orderPrice, customer_id customerId
from `order`
where id = #{id}
</select>
<!--
// 为customer特地封装的函数，简化customerMapper.xml的封装
Order findOrderForCustomerMap(Integer customer_id);
-->
<select id="findOrderForCustomerMap" parameterType="integer" resultType="order">
select id, orderNumber, orderPrice,customer_id customerId
from `order`
where customer_id =#{customerId}
</select>
</mapper>
8. 单元测试
8.1. 顾客接口功能测试：TestCustomer.java
package cn.kbkwwk.mybatis;
import cn.kbkwwk.mybatis.mapper.CustomerMapper;
import cn.kbkwwk.mybatis.pojo.Customer;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
public class TestCustomer {
SqlSession sqlSession = null;
CustomerMapper customerMapper = null;
@Before
public void openSqlSession() throws IOException {
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(in);
sqlSession = sessionFactory.openSession();//手动提交事务
customerMapper = sqlSession.getMapper(CustomerMapper.class);//创建动态代理对象
}
@After
public void closeSqlSession(){
sqlSession.close();
}
@Test
public void testGetOne1(){
Integer id = 1;
Customer customer = customerMapper.getOne(id);
System.out.println(customer);
}
}
8.2. 订单接口功能测试：TestOrder.java
package cn.kbkwwk.mybatis;
import cn.kbkwwk.mybatis.mapper.OrderMapper;
import cn.kbkwwk.mybatis.pojo.Order;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
public class TestOrder {
SqlSession sqlSession = null;
OrderMapper orderMapper = null;
@Before
public void openSqlSession() throws IOException {
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(in);
sqlSession = sessionFactory.openSession();//手动提交事务
orderMapper = sqlSession.getMapper(OrderMapper.class);//创建动态代理对象
}
@After
public void closeSqlSession(){
sqlSession.close();
}
@Test
public void testGetOne(){
Integer id = 13;
Order order = orderMapper.getOne(id);
System.out.println(order);
}
}
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
