一对一 <!-- 构建一对一关联关系 --> <association property="user" javaType="cn.itcast.mybatis.pojo.User"> 一对多 <!-- collection：一对多关联 ，ofType:集合中包含的类型 --> <collection property="ordersList" ofType="cn.itcast.mybatis.pojo.Orders" >
-- ---------------------------- -- 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(); } }
