一对一
<!-- 构建一对一关联关系 -->
<association property="user" javaType="cn.itcast.mybatis.pojo.User">
一对多
<!-- collection:一对多关联 ,ofType:集合中包含的类型 -->
<collection property="ordersList" ofType="cn.itcast.mybatis.pojo.Orders" >
注意:一对多关系 ,在一对多的查询中遇到在查询多的一方永远只是查询到一条数据,
明明在mysql数据库中有多条结果集, 多的一方的主键不要和一的一方的主键名相同
-- ----------------------------
-- Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`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_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', null);
INSERT INTO `orders` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', null);
INSERT INTO `orders` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', null);
-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `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;
-- ----------------------------
-- Records of user
-- ----------------------------
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);
user类
package com.it.bean;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
private List<Orders> orderList;
public List<Orders> getOrderList() {
return orderList;
}
public void setOrderList(List<Orders> orderList) {
this.orderList = orderList;
}
public int getId() {
return id;
}
public void setId(int 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 + "]";
}
}
orders订单类
package com.it.bean;
import java.util.Date;
public class Orders {
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
private 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;
}
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;
}
@Override
public String toString() {
return "Orders [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
+ ", note=" + note + ", user=" + user + "]";
}
}
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.it.mapper.OrdersMapper">
<!--//使用表连接sql语句查询 -->
<select id="findOrdersUserList" resultType="ordersUser">
SELECT o.*,u.username,u.sex,u.birthday,u.address
FROM orders o LEFT JOIN USER u ON o.user_id=u.id
</select>
<!--有订单表和用户表。以订单表为主表。就是一个订单对应一个用户,
一对一关系 -->
<resultMap id="ordersResultMap" type="orders">
<!-- column:数据库里面对应的字段
property:Javabean里面的字段
javaType:指定pojo中属性的类型
-->
<id column="id" property="id"/>
<result column="userId" property="user_id"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!-- 构建一对一关联关系 -->
<association property="user" javaType="com.it.bean.User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="findOrdersList" resultMap="ordersResultMap">
SELECT o.*,u.id,u.username,u.sex,u.birthday,u.address
FROM orders o LEFT JOIN USER u ON o.user_id=u.id
</select>
<!--以用户表为主表,一对多关系
,在一对多的查询中遇到在查询多的一方永远只是查询到一条数据,
明明在mysql数据库中有多条结果集,
多的一方的主键不要和一的一方的主键名相同
-->
<resultMap id="userResultMap" type="user">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!--ofType:指定的是映射到list集合属性中pojo的类型 -->
<collection property="orderList" ofType="com.it.bean.Orders" >
<id column="oid" property="id"/>
<result column="userId" property="user_id"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
</collection>
</resultMap>
<select id="findUserList" resultMap="userResultMap">
select u.*,o.oid ,o.number,o.createtime,o.note from user u inner JOIN orders o on o.user_id=u.id
</select>
</mapper>
mapper接口
package com.it.mapper;
import java.util.List;
import com.it.bean.Orders;
import com.it.bean.OrdersUser;
import com.it.bean.User;
public interface OrdersMapper {
//使用表连接sql语句查询
public List<OrdersUser> findOrdersUserList();
//以订单表为主表的一对一关系
public List<Orders> findOrdersList();
//以用户表为主的一对多关系
public List<User> findUserList();
}
测试
package com.it.test;
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.Orders;
import com.it.bean.OrdersUser;
import com.it.bean.User;
import com.it.mapper.OrdersMapper;
public class TestOrdersUser {
SqlSessionFactory sqlSessionFactory = null;
@Before
public void init() throws Exception{
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
sqlSessionFactory = builder.build(Resources.getResourceAsStream("sqlMapConfig.xml"));
}
//使用表连接sql语句查询
@Test
public void testFindOrdersUserList() {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
List<OrdersUser> list = mapper.findOrdersUserList();
for (OrdersUser ordersUser : list) {
System.out.println(ordersUser);
}
sqlSession.close();
}
//以订单表为主表的一对一关系
@Test
public void testFindOrdersList() {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
List<Orders> ordersList = mapper.findOrdersList();
System.out.println(ordersList);
sqlSession.close();
}
//以用户表为主表,一对多关系
@Test
public void testFindUserList() {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
List<User> userList = mapper.findUserList();
System.out.println(userList);
sqlSession.commit();
sqlSession.close();
}
}