Mybatis学习-day02关联映射

笔记 专栏收录该内容
8 篇文章 0 订阅

 1 一对一映射

   1.1 数据库准备:订单表order

CREATE TABLE `order` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL COMMENT '下单用户id',
  `number` VARCHAR(32) NOT NULL COMMENT '订单号',
  `createtime` DATETIME NOT NULL COMMENT '创建订单时间',
  `note` VARCHAR(100) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `FK_orders_1` (`user_id`),
  CONSTRAINT `FK_order_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `order` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', NULL);
INSERT INTO `order` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', NULL);
INSERT INTO `order` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', NULL);

                用户表user

CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(32) NOT NULL COMMENT '用户名称',
  `birthday` DATE DEFAULT NULL COMMENT '生日',
  `sex` CHAR(1) DEFAULT NULL COMMENT '性别',
  `address` VARCHAR(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;


INSERT INTO `user` VALUES ('1', '王五', NULL, '2', NULL);
INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市');
INSERT INTO `user` VALUES ('16', '张小明', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('22', '陈小明', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('24', '张三丰', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('25', '陈小明', NULL, '1', '河南郑州');
INSERT INTO `user` VALUES ('26', '王五', NULL, NULL, NULL);

1.2 实现一对一映射的两种方式:利用resultType,利用resultMap

      1.2.2利用resultType实现

             A:POJO对象:Order

package entity;

import java.util.Date;

public class Order {
	private Integer id;

	private Integer userId;

	private String number;

	private Date createtime;

	private String note;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public Integer getUserId() {
		return userId;
	}

	public void setUserId(Integer userId) {
		this.userId = userId;
	}

	public String getNumber() {
		return number;
	}

	public void setNumber(String number) {
		this.number = number == null ? null : number.trim();
	}

	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 == null ? null : note.trim();
	}

	@Override
	public String toString() {
		return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
				+ ", note=" + note + "]";
	}

}

POJO对象:User

package entity;
import java.util.Date;
public class User {

	private Integer id;
	private String username;// 用户姓名
	private String sex;// 性别
	private Date birthday;// 生日
	private String address;// 地址

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address="
				+ address + "]";
	}

}

POJO对象:OrderUser,继承Order类,这样就包含了Order类的所有字段,

                                         里面写上User类的2个属性

package entity;
//OrderUser继承了order后,就包含了order中的信息
public class OrderUser  extends Order{
    private String username;
    private String address;
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	@Override
	public String toString() {
		return "OrderUser [username=" + username + ", address=" + address
				+ ", getUsername()=" + getUsername() + ", getAddress()="
				+ getAddress() + ", getId()=" + getId() + ", getUserId()="
				+ getUserId() + ", getNumber()=" + getNumber()
				+ ", getCreatetime()=" + getCreatetime() + ", getNote()="
				+ getNote() + ", toString()=" + super.toString()
				+ ", getClass()=" + getClass() + ", hashCode()=" + hashCode()
				+ "]";
	}
	
    
}

B:Mapper映射文件OrderUserMapper.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="Mapper.UserMapper">
	<!-- 关联查询-->
	<select id="queryOrderUser" resultType="entity.OrderUser" >
		SELECT 	o.id, 
	o.user_id, 
	o.number, 
	o.createtime, 
	o.note,
        u.username,
	u.birthday
	 FROM  `order` o  LEFT JOIN  USER u ON o.user_id=u.id
	</select>	
</mapper>

C:接口UserMapper

package Mapper;

import java.util.List;

import entity.OrderUser;
import entity.User;

public interface UserMapper {
	//方法的名字要和映射文件的sql id相同
	 List<OrderUser> queryOrderUser();
	
}

D:在SqlMapConfig.xml中加载OrderUserMapper.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>
    <typeAliases>
         <!-- 别名包扫描器,别名是类的全称,不区分大小写 -->
    	<package name="entity"/>
    </typeAliases>
	<!--和spring整合后 environments配置将废除 -->
	<environments default="development">
		<environment id="development">
			<!-- 使用jdbc事务管理 -->
			<transactionManager type="JDBC" />
			<!-- 数据库连接池 -->
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url"
					value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8" />
				<property name="username" value="root" />
				<property name="password" value="root" />
			</dataSource>
		</environment>
	</environments>
	<!-- 加载sql映射文件 -->
	<mappers>
		<mapper resource="sqlmap/UserMapper.xml"/>
	</mappers>
</configuration>

E:测试类

package test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;
import Mapper.UserMapper;
import entity.OrderUser;
import entity.User;
import util.MybatisUtil;
public class OrderUserTest {
	@Test
	public void testQueryOrderUser(){
		SqlSessionFactory sessionFactory=MybatisUtil.getSqlSessionFactory();
		SqlSession sqlSession=sessionFactory.openSession();
		UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
		List<OrderUser> orderUser=userMapper.queryOrderUser();
		for(OrderUser orderUsers:orderUser){
			System.out.println(orderUsers);
		}
	}
}

运行结果:

OrderUser [username=王五, address=null, getUsername()=王五, getAddress()=null, getId()=3, getUserId()=null, getNumber()=1000010, getCreatetime()=Wed Feb 04 13:22:35 CST 2015, getNote()=null, toString()=Order [id=3, userId=null, number=1000010, createtime=Wed Feb 04 13:22:35 CST 2015, note=null], getClass()=class entity.OrderUser, hashCode()=81009902]
OrderUser [username=王五, address=null, getUsername()=王五, getAddress()=null, getId()=4, getUserId()=null, getNumber()=1000011, getCreatetime()=Tue Feb 03 13:22:41 CST 2015, getNote()=null, toString()=Order [id=4, userId=null, number=1000011, createtime=Tue Feb 03 13:22:41 CST 2015, note=null], getClass()=class entity.OrderUser, hashCode()=57748372]
OrderUser [username=张三, address=null, getUsername()=张三, getAddress()=null, getId()=5, getUserId()=null, getNumber()=1000012, getCreatetime()=Thu Feb 12 16:13:23 CST 2015, getNote()=null, toString()=Order [id=5, userId=null, number=1000012, createtime=Thu Feb 12 16:13:23 CST 2015, note=null], getClass()=class entity.OrderUser, hashCode()=674483268]
1.2.3 利用resultMap实现

        A:需要的POJO类:只需要一个Order类,Order类中加入User属性,User属性用于存储关联查询的用户信息

package entity;
import java.util.Date;
public class Order {
	private Integer id;
	private Integer userId;
	private String number;
	private Date createtime;
	private String note;
    //因为一个订单只能由一个用户创建,是一对一关系,所以用单个User对象存储关联查询的用户信息
	private User user;
	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public Integer getUserId() {
		return userId;
	}
	public void setUserId(Integer userId) {
		this.userId = userId;
	}
	public String getNumber() {
		return number;
	}
	public void setNumber(String number) {
		this.number = number == null ? null : number.trim();
	}
	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 == null ? null : note.trim();
	}
	@Override
	public String toString() {
		return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
				+ ", note=" + note + "]";
	}

}

B:Mapper映射文件UserMapper.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="Mapper.UserMapper">
	<resultMap type="order" id="order_user_map">
	<!-- 先配置主题表的属性 -->
		<!-- id用于主键映射 -->
		<id property="id" column="id"/>
		<!-- result用于普通字段映射property是类的属性,column是数据库字段 -->
		<result property="userId" column="user_id"/>
		<result property="number" column="number"/>
		<result property="createtime" column="createtime"/>
		<result property="note" column="note"/>
		<!-- association用于配置一对一关系
			 property:order里面的User属性
			 javaType:user的数据类型,支持别名user 
		 -->
		 <!-- 用association配置关联表的属性 -->
		<association property="user" javaType="entity.User">
			<!-- 配置关联的对象的主键
			property是关联对象的主键
			column是Order类引用的use的主键名字
			 -->
			<id property="id" column="user_id"/>
			<result property="username" column="username"/>
			<result property="address" column="address"/>
		</association>
	</resultMap>
	
	<!-- 一对一关联查询:resultMap使用 -->
	<select id="getOrderUserMap" resultMap="order_user_map">
		SELECT 
		  o.`id`,
		  o.`user_id`,
		  o.`number`,
		  o.`createtime`,
		  o.`note`,
		  u.username,
		  u.address
		FROM
		  `order` o 
		  LEFT JOIN `user` u 
		    ON u.id = o.user_id 
	</select>
	
</mapper>

C:UserMapper接口

package Mapper;
import java.util.List;
import entity.Order;
import entity.OrderUser;
import entity.User;
public interface UserMapper {

	 List<Order> getOrderUserMap();
}

D:测试类

package test;

import java.util.List;


import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;

import Mapper.UserMapper;

import entity.Order;
import entity.OrderUser;
import entity.User;

import util.MybatisUtil;

public class OrderUserTest {
	@Test
	public void testQueryOrderUserMap(){
		SqlSessionFactory sessionFactory=MybatisUtil.getSqlSessionFactory();
		SqlSession sqlSession=sessionFactory.openSession();
		UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
		
		List<Order> orders=userMapper.getOrderUserMap();
		for(Order order:orders){
			System.out.println(order);
		}
	}
}

 

  • 0
    点赞
  • 2
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

评论2
请先登录 后发表评论~
©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页

打赏作者

liyunfei456

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值