SSM框架学习之MyBatis（三）——MyBatis关联关系（多对一）
学习一对多关系，在“SSM框架学习（二）”的基础上，从订单和客户的多对一关系掌握知识
一、创建订单表
在mybatis_test数据库下创建orders表
二、创建实体类（称为javabean/entity/domain）
1、建Order实体类
2、编写实体类的代码
package com.ssm.mybatis.entity;
/**
* 订单实体类
*/
public class Order {
private Integer id;
private String orderno;
private float price;
private int cid;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getOrderno() {
return orderno;
}
public void setOrderno(String orderno) {
this.orderno = orderno;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", orderno='" + orderno + '\'' +
", price=" + price +
", cid=" + cid +
'}';
}
}
三、创建Mapper文件
1、在resources下新建OrderMapper.xml文件
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="OrderMapper">
</mapper>
四、在mybatis-config.xml添加OrderMapper映射
<mappers>
<mapper resource="CustomerMapper.xml"/>
<mapper resource="OrderMapper.xml"/>
</mappers>
五、测试
1、在OrderMapper.xml中编写SQL插入语句
<mapper namespace="OrderMapper">
<insert id="insert">
insert into orders(id,orderno,price,cid) values (#{id},#{orderno},#{price},#{cid})
</insert>
</mapper>
2、在测试类中编写插入方法insertOrder()
//插入订单
@Test
public void insertOrder () throws IOException{
String resource="mybatis-config.xml";
InputStream inputStream=Resources.getResourceAsStream(resource);
SqlSessionFactory sf=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession s=sf.openSession();
Order order=new Order();
order.setId(1);
order.setOrderno("001");
order.setPrice(5.2f);
order.setCid(2);
s.insert("OrderMapper.insert",order);
s.commit();
s.close();
}
查看数据库，插入成功
订单表中的cid为此订单的对应客户id，此时的值为手动输入，考虑订单与客户的多对一关系，应该改为面向对象编程
六、面向对象编程
插入订单
1、修改订单属性
将Order类中的cid更换为Customer的对象
package com.ssm.mybatis.entity;
/**
* 订单实体类
*/
public class Order {
private Integer id;
private String orderno;
private float price;
//private int cid;
private Customer customer;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getOrderno() {
return orderno;
}
public void setOrderno(String orderno) {
this.orderno = orderno;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public Customer getCustomer() {
return customer;
}
public void setCustomer(Customer customer) {
this.customer = customer;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", orderno='" + orderno + '\'' +
", price=" + price +
", customer=" + customer +
'}';
}
}
2、在OrderMapper.xml修改SQL插入语句
<mapper namespace="OrderMapper">
<insert id="insert">
insert into orders(id,orderno,price,cid) values (#{id},#{orderno},#{price},#{customer.id})
</insert>
</mapper>
原插入语句为：
insert into orders(id,orderno,price,cid) values (#{id},#{orderno},#{price},#{cid})
将cid改为customer.id即表示为Customer实体类中的id属性
3、测试
创建order测试类
编写测试方法
//插入订单
@Test
public void insert() throws Exception{
String resource="mybatis-config.xml";
InputStream inputStream=Resources.getResourceAsStream(resource);
SqlSessionFactory sf=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession s=sf.openSession();
Order order=new Order();
order.setId(4);
order.setOrderno("002");
order.setPrice(10f);
Customer customer=s.selectOne("CustomerMapper.findbyid",1);
order.setCustomer(customer);
s.insert("OrderMapper.insert",order);
s.commit();
s.close();
}
查看数据库，可以发现2号订单关联上1号客户。
若在新增订单的同时新增客户，需要在插入完客户之后马上获得客户的id。
在CustomerMapper.xml修改SQL插入语句
<!--插入操作-->
<!--useGeneratedKeys:是否使用生成的key;keyProperty:使用生成的key后复制给那个属性-->
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
insert into customers(id,name,age) values (#{id}, #{name},#{age})
</insert>
编写insert()插入方法
//插入订单
@Test
public void insert() throws Exception{
String resource="mybatis-config.xml";
InputStream inputStream=Resources.getResourceAsStream(resource);
SqlSessionFactory sf=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession s=sf.openSession();
Customer customer=new Customer();
customer.setId(4);
customer.setName("jim");
customer.setAge(15);
s.insert("CustomerMapper.insert",customer);
Order order=new Order();
order.setId(3);
order.setOrderno("003");
order.setPrice(10f);
order.setCustomer(customer);
s.insert("OrderMapper.insert",order);
s.commit();
s.close();
}
查看数据可以发现，3号订单和4号客户关联关系上。
查找订单
1、在OrderMapper.xml添加SQL的select查询语句
<!--查询-->
<select id="findbyid" resultType="_Order" >
select * from orders where id=#{id}
</select>
2、TestOrderCRUD类中编写findbyid()
//查询订单
@Test
public void findbyid() throws Exception{
String resource="mybatis-config.xml";
InputStream inputStream=Resources.getResourceAsStream(resource);
SqlSessionFactory sf=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession s=sf.openSession();
Order order=s.selectOne("OrderMapper.findbyid",1);
System.out.println(order.toString()+","+order.getCustomer().toString());
s.commit();
s.close();
}
观察控制台发现，查找出来的订单对应客户id为空。
知识点
- jdbc的连接方式有内连接、外连接和交叉连接。
- 外连接：左外连接右外连接
- 外连接和内连接的区别：外连接可以补全，没有的值可以用空值补全；内连接不能补全
原因：
- 此处查询为关联查询。
- 在查询订单的时候一同将客户查出，应该用左外连接
查询方法：
- 左外连接sql语句：
select
o.id as oid,
o.orderno as oorderno,
o.price as oprice,
c.id as cid,
c.name as cname,
c.age as cage
from orders o
left outer join customers c on o.cid=c.id
where o.id=#{id}
- 同时需要对查询语句的结果映射，映射出的结果为订单，订单的属性里有客户的属性
<!--查询-->
<!--as能省略-->
<select id="findbyid" resultMap="rm_Order" >
select
o.id as oid,
o.orderno as oorderno,
o.price as oprice,
c.id as cid,
c.name as cname,
c.age as cage
from orders o
left outer join customers c on o.cid=c.id
where o.id=#{id}
</select>
<!--定义订单映射-->
<!--property:对应的订单属性-->
<resultMap id="rm_Order" type="_Order">
<id column="oid" property="id"/>
<result column="oorderno" property="orderno"/>
<result column="oprice" property="price"/>
<!--订单和客户的关系为多对一关联关系-->
<!--column="cid":关联的列为cid-->
<association property="customer" javaType="_Customer" column="cid">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
<result column="cage" property="age"/>
</association>
</resultMap>
3、测试
若一个客户有多个订单，用collection集合表示
查询所有订单以及对应客户
1、在OrderMapper.xml添加SQL的select查询语句
<!--查询所有订单-->
<select id="findall" resultMap="rm_Order" >
select
o.id as oid,
o.orderno as oorderno,
o.price as oprice,
c.id as cid,
c.name as cname,
c.age as cage
from orders o
left outer join customers c on o.cid=c.id
</select>
2、TestOrderCRUD类中编写findall()
//查询所有订单
@Test
public void findall() throws Exception{
String resource="mybatis-config.xml";
InputStream inputStream=Resources.getResourceAsStream(resource);
SqlSessionFactory sf=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession s=sf.openSession();
List<Order> orderList=s.selectList("OrderMapper.findall");
for(Order o:orderList){
System.out.println(o.toString()+o.getCustomer().toString());
}
s.commit();
s.close();
}
3、查看控制台对比数据库
七、多对一关系图解（结果映射关系）
168
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
