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);
}
}
}