第九章 关联关系
一对一
引入对方主键,作为外键
一对多
在多的一方建立另一方的主键作为外键
多对多
整一个关系表,引入两张表外键作为主键
9.2 一对一
建立两张表
设置外键,联系起来
导包
建立两个持久化,两个类
package com.xiucai.po;
public class IdCard {
private Integer id;
private String code;
public Integer getId() {
return id;
}
public String getCode() {
return code;
}
public void setId(Integer id) {
this.id = id;
}
public void setCode(String code) {
this.code = code;
}
@Override
public String toString() {
return "IdCard [id=" + id + ", code=" + code + "]";
}
}
package com.xiucai.po;
public class Person {
private Integer id;
private String name;
private Integer age;
private String sex;
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
private IdCard card;//注入关系,把两者联系起来了,你要使用里边的东西
public Integer getId() {
return id;
}
public String getName() {
return name;
}
public Integer getAge() {
return age;
}
public IdCard getCard() {
return card;
}
public void setId(Integer id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setAge(Integer age) {
this.age = age;
}
public void setCard(IdCard card) {
this.card = card;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", age=" + age + ", sex=" + sex + ", card=" + card + "]";
}
}
建立两个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.xiucai.mapper.PersonMapper">
<!-- 嵌套 -->
<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.xiucai.mapper.IdCardMapper.findById"
/>
</resultMap>
<!-- 嵌套结果: 直接使用结果映射-->
<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="IdCard"/>
<result property="code" column="code"/>
</association>
</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="com.xiucai.mapper.IdCardMapper">
<select id="findById" parameterType="Integer" resultType="IdCard">
select * from tb_idcard where id=#{id}
</select>
</mapper>
编写工具类
package com.xiucai.Utils;
import java.io.IOException;
import java.io.InputStream;
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.Test;
public class MybatisUtils {
public static SqlSession getSession() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory =
new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
return sqlSession;
}
}
编写测试类
package com.xiucai.Utils;
import java.io.IOException;
import java.io.InputStream;
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.Test;
public class MybatisUtils {
public static SqlSession getSession() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory =
new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
return sqlSession;
}
}
<?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="com.xiucai.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}" />
<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/xiucai/mapper/IdCardMapper.xml" />
<mapper resource="com/xiucai/mapper/PersonMapper.xml" />
</mappers>
</configuration>
上边引入config文件
9.3 一对多
使用嵌套查询的方式
第一步:建表
先建数据表,这里举例子,顾客对应订单.一个顾客对应多个订单,一个订单只能对应一个顾客
注意一对多在建表的时候我们建立的外键应该建立在"多"上,因为如果建立在少上,一个顾客,只能出现一个orders_id,此时不能达到想要的效果
第二步:建持久化,建立两个类
Person.java
package com.xiucai.po;
import java.util.ArrayList;
public class Person {
private Integer id;
private String name;
private Integer age;
private String sex;
private ArrayList<Orders> ordersList;
public String getSex() {
return sex;
}
public ArrayList<Orders> getPersonsList() {
return ordersList;
}
public void setPersonsList(ArrayList<Person> personsList) {
this.ordersList = ordersList;
}
public void setSex(String sex) {
this.sex = sex;
}
public ArrayList<Orders> getOrdersList() {
return ordersList;
}
public void setOrdersList(ArrayList<Orders> ordersList) {
this.ordersList = ordersList;
}
private IdCard card;//注入关系,把两者联系起来了,你要使用里边的东西
public Integer getId() {
return id;
}
public String getName() {
return name;
}
public Integer getAge() {
return age;
}
public IdCard getCard() {
return card;
}
public void setId(Integer id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setAge(Integer age) {
this.age = age;
}
public void setCard(IdCard card) {
this.card = card;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", age=" + age + ", sex=" + sex + ", ordersList=" + ordersList
+ ", card=" + card + "]";
}
}
Orders.java
package com.xiucai.po;
public class Orders {
private int id;
private String number;
private Person person;
//如果只需要在oders找person只写着一个
public int getId() {
return id;
}
public Person getPerson() {
return person;
}
@Override
public String toString() {
return "Orders [id=" + id + ", number=" + number + "]";
}
public void setId(int id) {
this.id = id;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public void setPerson(Person person) {
this.person = person;
}
}
第三步:建立xml文件, 其中包括嵌套的查询语句
里边的,在这里使用到,处理一对多的关系,也就是查询到的是多个关系的时候中大部分元素是跟association中相同的,只不过把javatype属性更改成了,ofType,具体看下边的实例
<?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.xiucai.mapper.PersonMapper">
<!-- 嵌套 一对多-->
<select id="findOrdesById" parameterType="Integer" resultMap="PersonAndOrders">
select * from tb_person where id=#{id}
</select>
<resultMap type="com.xiucai.po.Person" id="PersonAndOrders">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<!-- 关于外键我们嵌套需要写的内容 -->
<!-- 这里可能出问题 -->
<collection
property="ordersList"
ofType="com.xiucai.po.Orders"
column="id"
select="com.xiucai.mapper.OrdersMapper.findById"/>
</resultMap>
</mapper>
Orders.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.xiucai.mapper.OrdersMapper">
<select id="findById" parameterType="Integer" resultMap="Orders">
select * from tb_orders where user_id=#{id}
</select>
<resultMap type="com.xiucai.po.Orders" id="Orders">
<!--因为java属性跟数据库属性一样所以下边的两个值可以省略-->
<id property="id" column="id"/>
<result property="number" column="number"/>
</resultMap>
</mapper>
第四步:编写测试类
package com.xiucai.Test;
import java.io.IOException;
import java.util.ArrayList;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.xiucai.Utils.MybatisUtils;
import com.xiucai.po.Person;
public class CollectionTest {
@Test
public void findOrdersByIdTest() {
try {
SqlSession session=MybatisUtils.getSession();
Person person=session.selectOne("com.xiucai.mapper.PersonMapper.findOrdesById", 1);
System.out.println(person);
//System.out.println( person.getOrdersList());
session.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
使用嵌套结果查询方式
这对sql语句要求比较高,其中还要涉及一些针对别名的操作
第一步:建表
建表跟上边一样
第二步:持久化
跟上边一样
第三步:建立xml文件
对于属性的相关解释,sql语句的本地测试也是至关重要的
<?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.xiucai.mapper.PersonMapper">
<!-- 写sql语句代替复杂的嵌套操作-->
<select id="findOrdesById" parameterType="Integer" resultMap="PersonAndOrders">
select p.*,o.id as orders_id,o.number
from tb_person p,tb_orders o
where p.id=o.user_id
and p.id=1;
</select>
<resultMap type="com.xiucai.po.Person" id="PersonAndOrders">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<!-- 关于外键我们嵌套需要写的内容 -->
<!-- 这里可能出问题 -->
<collection property="ordersList" ofType="com.xiucai.po.Orders">
<id property="id" column="o.id"/>
<result property="number" column="number"/>
</collection>
</resultMap>
</mapper>
第四步:测试
跟上边一样
9.4 多对多 订单跟商品的例子
使用嵌套查询方式
第一步建表
建立订单表,商品表,以及中间表
表结构.
订单表,
中间表结构,最关键的地方是外键
商品表
第二步:建立持久化,建立两个类
orders类
package com.xiucai.po;
import java.util.ArrayList;
public class Orders {
private int id;
private String name;
//体现多对多的关系
private ArrayList<Product> productsList;
@Override
public String toString() {
return "Orders [id=" + id + ", name=" + name + ", productsList=" + productsList + "]";
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public void setId(int id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
}
product类
package com.xiucai.po;
import java.util.ArrayList;
import com.sun.org.apache.xpath.internal.operations.Or;
public class Product {
private int id;
private String name;
private double price;
private ArrayList<Orders> ordersList;
@Override
public String toString() {
return "Product [id=" + id + ", name=" + name + ", price=" + price + ", ordersList=" + ordersList + "]";
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public double getPrice() {
return price;
}
public ArrayList<Orders> getOrdersList() {
return ordersList;
}
public void setId(int id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setPrice(double price) {
this.price = price;
}
public void setOrdersList(ArrayList<Orders> ordersList) {
this.ordersList = ordersList;
}
}
第三步:建立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.xiucai.mapper.OrdersMapper">
<select id="findById" parameterType="Integer" resultMap="OrdersWithProductResult">
select * from tb_orders where id=#{id}
</select>
<resultMap type="Orders" id="OrdersWithProductResult">
<id property="id" column="id"/>
<result property="number" column="number"/>
<collection property="productsList" column="id" ofType="Product"
select="com.xiucai.mapper.ProductMapper.findProductById">
</collection>
</resultMap>
</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.xiucai.mapper.ProductMapper">
<select id="findProductById" parameterType="Integer" resultType="Product">
select * from tb_product where id in(
select product_id from tb_ordersitem where oders_id=#{id}
)
</select>
</mapper>
修改config文件
第四步:编写测试类
package com.xiucai.Test;
/**
* 测试类:一对多嵌套查询
*/
import java.io.IOException;
import java.lang.annotation.Target;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.xiucai.Utils.MybatisUtils;
import com.xiucai.po.Orders;
import com.xiucai.po.Person;
public class MybatisManyTest3 {
@Test
public void findPersonByIdTest() {
try {
SqlSession session=MybatisUtils.getSession();
Orders orders=session.selectOne("com.xiucai.mapper.OrdersMapper.findById", 1);
System.out.println(orders);
session.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
结果:
出现的问题:
- resultMap跟resultType搞混了
- 查询的数据表名字打错了
- 数据库的名字跟po持久类的没对应起来
使用嵌套结果方式,主要考察sql语句
第一步建表
建立订单表,商品表,以及中间表
表结构.
订单表,
中间表结构,最关键的地方是外键
商品表
第二步:建立持久化,建立两个类
orders类
package com.xiucai.po;
import java.util.ArrayList;
public class Orders {
private int id;
private String name;
//体现多对多的关系
private ArrayList<Product> productsList;
@Override
public String toString() {
return "Orders [id=" + id + ", name=" + name + ", productsList=" + productsList + "]";
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public void setId(int id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
}
product类
package com.xiucai.po;
import java.util.ArrayList;
import com.sun.org.apache.xpath.internal.operations.Or;
public class Product {
private int id;
private String name;
private double price;
private ArrayList<Orders> ordersList;
@Override
public String toString() {
return "Product [id=" + id + ", name=" + name + ", price=" + price + ", ordersList=" + ordersList + "]";
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public double getPrice() {
return price;
}
public ArrayList<Orders> getOrdersList() {
return ordersList;
}
public void setId(int id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setPrice(double price) {
this.price = price;
}
public void setOrdersList(ArrayList<Orders> ordersList) {
this.ordersList = ordersList;
}
}
第三步:建立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.xiucai.mapper.OrdersMapper">
<select id="findById" parameterType="Integer" resultMap="OrdersWithProductResult">
select os.*,pt.id as ptid,pt.name,pt.price
from tb_orders os,tb_ordersitem om,tb_product pt
where om.oders_id=os.id
and om.product_id=pt.id
and os.id=#{id}
</select>
<resultMap type="Orders" id="OrdersWithProductResult">
<id property="id" column="id"/>
<result property="number" column="number"/>
<collection property="productsList" column="id" ofType="Product">
<id property="id" column="pitd"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
</collection>
</resultMap>
</mapper>
第四步:编写测试类
package com.xiucai.Test;
/**
* 测试类:一对多嵌套查询
*/
import java.io.IOException;
import java.lang.annotation.Target;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.xiucai.Utils.MybatisUtils;
import com.xiucai.po.Orders;
import com.xiucai.po.Person;
public class MybatisManyTest3 {
@Test
public void findPersonByIdTest() {
try {
SqlSession session=MybatisUtils.getSession();
Orders orders=session.selectOne("com.xiucai.mapper.OrdersMapper.findById", 1);
System.out.println(orders);
session.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
结果: