什么是关联映射
关联关系:现实世界中物体与物体之间的普遍的关系
关系型数据库:物体与物体之间的关系,可以用二张或多张表表示
映射就是一种关联关系,
关系:一对一关系 人和身份证
一对多关系 老师和学生
多对多关系 商品和人之间()
强调的是:从一方出发,导航到另一方
关联映射案列分析
用户表:user
记录购买商品的用户信息
订单表:order
记录了用户所创建的订单
订单明细:orderdetail
记录了订单的详情及构面商品的信息
商品表:items
记录了商品信息
user和orders:一个用户可以创建多个订单 一对多
orders—user:一个订单只由一个用户创建
orders orderdetails:一个订单可以包含多个订单明细,因为一个订单购买多个商品
每个商品的购买信息在orderdetail记录,一对多
orderdetails orders:一个订单明细只能包含在一个订单中 一对一
items: 多对多关系(依托于订单和订单明细)
- 解决方案
创建视图或者是连接查询语句返回一个vo,存放了那个用户下了什么订单的信息。
SELECT orders.*,user.username,user.address
FROM orders,user WHERE orders.user_id = user.id - 工程目录结构
- 导入jar包
- 配置db.properties
oracle.driver=oracle.jdbc.driver.OracleDriver
oracle.url=jdbc:oracle:thin:@localhost:1521:orcl
oracle.user=scott
oracle.pwd=tiger
- 配置mybatis.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>
<!-- 加载属性文件 -->
<properties resource="com/it/config/db.properties"></properties>
<!-- 配置 -->
<settings>
<!-- 开启懒加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 关闭积极加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
<!-- 开启二级缓存 -->
<setting name="cacheEnabled" value="true"/>
</settings>
<!-- 起别名 -->
<typeAliases>
<package name="com.it.bean"/>
</typeAliases>
<!-- 和spring整合后 environments配置将废除-->
<environments default="development">
<environment id="development">
<!-- 使用jdbc事务管理,事务控制由mybatis-->
<transactionManager type="JDBC" />
<!-- 数据库连接池,由mybatis管理-->
<dataSource type="POOLED">
<property name="driver" value="${oracle.driver}" />
<property name="url" value="${oracle.url}" />
<property name="username" value="${oracle.user}" />
<property name="password" value="${oracle.pwd}" />
</dataSource>
</environment>
</environments>
<!--加载映射文件-->
<mappers>
<!-- 当的映射文件的加载 -->
<!-- <mapper resource="com/it/bean/student.xml"/> -->
<!-- <mapper class="com.it.dao.StudentMapper"/> -->
<package name="com.it.dao"></package>
</mappers>
</configuration>
- User.java用户配置信息
package com.it.bean;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
public class User implements Serializable{
private int userid;
private String username;
private String usersex;
private Date userbirth;
private String useraddr;
private List<Order> orderlist;
public int getUserid() {
return userid;
}
public void setUserid(int userid) {
this.userid = userid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getUsersex() {
return usersex;
}
public void setUsersex(String usersex) {
this.usersex = usersex;
}
public Date getUserbirth() {
return userbirth;
}
public void setUserbirth(Date userbirth) {
this.userbirth = userbirth;
}
public String getUseraddr() {
return useraddr;
}
public void setUseraddr(String useraddr) {
this.useraddr = useraddr;
}
public List<Order> getOrderlist() {
return orderlist;
}
public void setOrderlist(List<Order> orderlist) {
this.orderlist = orderlist;
}
public User(int userid, String username, String usersex, Date userbirth, String useraddr, List<Order> orderlist) {
super();
this.userid = userid;
this.username = username;
this.usersex = usersex;
this.userbirth = userbirth;
this.useraddr = useraddr;
this.orderlist = orderlist;
}
public User() {
super();
// TODO Auto-generated constructor stub
}
@Override
public String toString() {
return "User [userid=" + userid + ", username=" + username + ", usersex=" + usersex + ", userbirth=" + userbirth
+ ", useraddr=" + useraddr + ", orderlist=" + orderlist + "]";
}
}
- Order.java订单信息
package com.it.bean;
import java.util.Date;
import java.util.List;
public class Order {
private Integer order_id;
private Integer user_id;
private String order_number;
private Date createtime;
private String note;
//关联查询对象
private User user;
//关联到订单明细
private List<OrderDetail> orderdetail;
public Integer getOrder_id() {
return order_id;
}
public void setOrder_id(Integer order_id) {
this.order_id = order_id;
}
public Integer getUser_id() {
return user_id;
}
public void setUser_id(Integer user_id) {
this.user_id = user_id;
}
public String getOrder_number() {
return order_number;
}
public void setOrder_number(String order_number) {
this.order_number = order_number;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Order(Integer order_id, Integer user_id, String order_number, Date createtime, String note, User user) {
super();
this.order_id = order_id;
this.user_id = user_id;
this.order_number = order_number;
this.createtime = createtime;
this.note = note;
this.user = user;
}
public Order() {
super();
// TODO Auto-generated constructor stub
}
@Override
public String toString() {
return "Order [order_id=" + order_id + ", user_id=" + user_id + ", order_number=" + order_number
+ ", createtime=" + createtime + ", note=" + note + ", user=" + user + "]";
}
public List<OrderDetail> getOrderdetail() {
return orderdetail;
}
public void setOrderdetail(List<OrderDetail> orderdetail) {
this.orderdetail = orderdetail;
}
}
- Orderdetail.java订单详情
package com.it.bean;
public class OrderDetail {
private int id;
private int orders_id;
private int items_id;
private int items_num;
private Items items;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getOrders_id() {
return orders_id;
}
public void setOrders_id(int orders_id) {
this.orders_id = orders_id;
}
public int getItems_id() {
return items_id;
}
public void setItems_id(int items_id) {
this.items_id = items_id;
}
public int getItems_num() {
return items_num;
}
public void setItems_num(int items_num) {
this.items_num = items_num;
}
public Items getItems() {
return items;
}
public void setItems(Items items) {
this.items = items;
}
public OrderDetail(int id, int orders_id, int items_id, int items_num, Items items) {
super();
this.id = id;
this.orders_id = orders_id;
this.items_id = items_id;
this.items_num = items_num;
this.items = items;
}
public OrderDetail() {
super();
// TODO Auto-generated constructor stub
}
@Override
public String toString() {
return "OrderDetail [id=" + id + ", orders_id=" + orders_id + ", items_id=" + items_id + ", items_num="
+ items_num + ", items=" + items + "]";
}
}
- Items.java商品信息
package com.it.bean;
import java.util.Date;
public class Items {
private int id;
private String name;
private float price;
private String pic;
private Date createtime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public String getPic() {
return pic;
}
public void setPic(String pic) {
this.pic = pic;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public Items(int id, String name, float price, String pic, Date createtime) {
super();
this.id = id;
this.name = name;
this.price = price;
this.pic = pic;
this.createtime = createtime;
}
public Items() {
super();
// TODO Auto-generated constructor stub
}
@Override
public String toString() {
return "Items [id=" + id + ", name=" + name + ", price=" + price + ", pic=" + pic + ", createtime=" + createtime
+ "]";
}
}
- 创建statement,并使用resutMap进行映射
- 创建StudentMapper.java
package com.it.dao;
import java.util.List;
import com.it.bean.Order;
import com.it.bean.User;
public interface StudentMapper {
/**
* 通过用户查询订单
* @return
*/
public List<Order> findUserByOrders();
/**
* 通过订单明细查询顶大
* @return
*/
public List<Order> findOrderDetailsByOrders();
/**
* 通过用户查询商品
* @return
*/
public List<User> findUserAndItems();
/**
* 查询订单
* @return
*/
public List<Order> findOrder();
/**
* 通过id查询用户
* @param id
* @return
*/
public User findUserById(String id);
}
- 创建StudentMapper.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.it.dao.StudentMapper">
<!-- 引入二级缓存 -->
<cache></cache>
<!-- 延迟加载 通过订单查询用户信息 开始-->
<resultMap type="com.it.bean.Order" id="findOrderMapping">
<id column="order_id" property="order_id"></id>
<result column="user_id" property="user_id"></result>
<result column="order_number" property="order_number"></result>
<result column="createtime" property="createtime"></result>
<result column="note" property="note"></result>
<association property="user" javaType="com.it.bean.User" select="findUserById" column="user_id">
</association>
</resultMap>
<select id="findOrder" resultMap="findOrderMapping">
select * from orderInfo
</select>
<!-- 测试延迟加载 一级缓存 -->
<select id="findUserById" parameterType="java.lang.String" resultType="com.it.bean.User" useCache="true" flushCache="true">
select * from userInfo where userid=#{id}
</select>
<!-- 延迟加载 通过订单查询用户信息 结束-->
<resultMap type="com.it.bean.Order" id="findUserByOrdersMap">
<id column="order_id" property="order_id"></id>
<result column="user_id" property="user_id"></result>
<result column="order_number" property="order_number"></result>
<result column="createtime" property="createtime"></result>
<result column="note" property="note"></result>
<!-- association就是用于说明关联的对象(user)的映射关系(User类和user表)
association表示返回值只有一个对象的。
property:就是Order类中的user属性
-->
<association property="user" javaType="com.it.bean.User">
<id column="user_id" property="userid"></id>
<result column="username" property="username"></result>
<result column="usersex" property="usersex"></result>
<result column="userbirth" property="userbirth"></result>
<result column="useraddr" property="useraddr"></result>
</association>
</resultMap>
<!-- 通过查询订单查询用户 -->
<select id="findUserByOrders" resultMap="findUserByOrdersMap">
SELECT
orders.*,users.username,users.useraddr,users.usersex,users.userbirth
FROM orderInfo orders left join userInfo users
on orders.user_id = users.userid
</select>
<!-- 查询订单 关联查询订单明细 开始 -->
<resultMap type="com.it.bean.Order" id="findOrderDetailsByOrdersMap">
<id property="order_id" column="order_id"/>
<result property="user_id" column="user_id"/>
<result property="order_number" column="order_number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>
<!-- 一对一 -->
<association property="user" javaType="com.it.bean.User">
<id property="userid" column="user_id"/>
<result property="username" column="username"/>
<result property="usersex" column="usersex"/>
<result property="userbirth" column="userbirth"/>
<result property="useraddr" column="useraddr"/>
</association>
<!-- 一对多 -->
<collection property="orderdetail" ofType="com.it.bean.OrderDetail">
<id property="id" column="orderdetail_id"/>
<result property="orders_id" column="order_id"/>
<result property="items_id" column="items_id"/>
<result property="items_num" column="items_num"/>
</collection>
</resultMap>
<select id="findOrderDetailsByOrders" resultMap="findOrderDetailsByOrdersMap">
SELECT orders.*,
us.username,us.usersex,us.useraddr,us.userbirth,
ordd.id AS orderdetail_id,
ordd.items_id,
ordd.items_num,
ordd.order_id as orders_id
FROM orderInfo orders,userInfo us,orderdetail ordd
WHERE orders.user_id=us.userid AND ordd.order_id=orders.order_id
</select>
<!-- 查询订单 关联查询订单明细 结束 -->
<!-- 多对多关联映射 开始-->
<resultMap type="com.it.bean.User" id="findUserAndItemsMapping">
<!-- 用户 -->
<id property="userid" column="user_id"></id>
<result property="username" column="username"></result>
<result property="usersex" column="usersex"></result>
<result property="userbirth" column="userbirth"></result>
<result property="useraddr" column="useraddr"></result>
<collection property="orderlist" ofType="com.it.bean.Order">
<!-- 订单 -->
<id property="order_id" column="order_id"></id>
<result property="user_id" column="user_id"></result>
<result property="order_number" column="order_number"></result>
<result property="createtime" column="createtime"></result>
<result property="note" column="note"></result>
<collection property="orderdetail" ofType="com.it.bean.OrderDetail">
<!-- 订单明细 -->
<id property="id" column="orderdetail_id"/>
<result property="orders_id" column="order_id"/>
<result property="items_id" column="items_id"/>
<result property="items_num" column="items_num"/>
<association property="items" javaType="com.it.bean.Items">
<!-- 商品 -->
<id property="id" column="orderdetail_id"/>
<result property="name" column="items_name"/>
<result property="price" column="items_price"/>
<result property="pic" column="items_pic"/>
<result property="createtime" column="items_time"/>
</association>
</collection>
</collection>
</resultMap>
<select id="findUserAndItems" resultMap="findUserAndItemsMapping">
SELECT orders.*,
us.username,us.usersex,us.useraddr,us.userbirth,
ordd.id AS orderdetail_id,
ordd.items_id,
ordd.items_num,
ordd.order_id as orders_id,
it.name AS items_name,
it.price AS items_price,
it.pic AS items_pic,
it.createtime AS items_time
FROM orderInfo orders,userInfo us,orderdetail ordd,items it
WHERE orders.user_id=us.userid AND ordd.order_id=orders.order_id and ordd.order_id=it.id
</select>
<!-- 多对多关联映射 结束-->
</mapper>
- 测试类
package com.it.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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.Before;
import org.junit.Test;
import com.it.bean.Order;
import com.it.bean.User;
import com.it.dao.StudentMapper;
public class TestMybatis {
private InputStream is=null;
private SqlSessionFactory sqlSessionFactory=null;
private SqlSession sqlSession=null;
private SqlSession sqlSession1=null;
private StudentMapper studao=null;
@Before
public void before() throws IOException{
String resource="com/it/config/mybatis.xml";
is=Resources.getResourceAsStream(resource);
//创建会话工厂
sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
//通过工厂创建sqlsession
sqlSession=sqlSessionFactory.openSession();
sqlSession1=sqlSessionFactory.openSession();
studao=sqlSession.getMapper(StudentMapper.class);
}
@Test
public void query(){
List<Order> list=studao.findUserByOrders();
System.out.println(list.size());
for (Order order : list) {
System.out.println(order.toString());
//System.out.println(order.getUser().toString());
}
for (int i = 0; i <list.size(); i++) {
System.out.println(list.get(i).getUser().toString());
}
}
//延迟加载
@Test
public void findOrderDetailsByOrders(){
List<Order> list=studao.findOrderDetailsByOrders();
for (Order order : list) {
System.out.println(order.toString());
}
System.out.println(list.size());
}
@Test
public void aaa(){
List<User> list=studao.findUserAndItems();
for (User user : list) {
System.out.println(user.toString());
}
}
//延迟加载·
@Test
public void findOrder(){
List<Order> list=studao.findOrder();
for (Order order : list) {
System.out.println(order.toString());
}
System.out.println(list.size());
}
//测试一级缓存
@Test
public void findUserById(){
User user=studao.findUserById("10");
System.out.println(user.getUsername());
User user1=studao.findUserById("10");
System.out.println(user1.getUsername());
}
//测试二级缓存
@Test
public void findUserById2(){
User user=studao.findUserById("10");
System.out.println(user.getUsername());
sqlSession.close();
StudentMapper studao1=sqlSession1.getMapper(StudentMapper.class);
User user1=studao1.findUserById("10");
System.out.println(user1.getUsername());
}
}