mybatis深入学习--关联映射

什么是关联映射

关联关系:现实世界中物体与物体之间的普遍的关系
关系型数据库:物体与物体之间的关系,可以用二张或多张表表示

映射就是一种关联关系,

关系:一对一关系 人和身份证
一对多关系 老师和学生
多对多关系 商品和人之间()

强调的是:从一方出发,导航到另一方

关联映射案列分析

用户表: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());
		
	}
	
	
	
		
}

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值