本文共 11213 字,大约阅读时间需要 37 分钟。
多表之间的关系有一对一、一对多(多对一)和多对多等关系。
数据库中建表时,可以记住以下口诀:
在 mybatis 中的映射处理:
<association>
标签。<collection>
标签。在 Java 中实体类处理:
下面演示两个实例,演示环境:jdk_1.8
、mybatis_3.5.5
、mysql_8
GitHub地址:
一对一,一对多(多对一)的关系,统一当作一对一关系处理,在 mybatis 中使用 <association>
标签关联。
实例描述:
需求:
-- 建立老师表CREATE TABLE IF NOT EXISTS `t_teacher` ( `id` INT(10) NOT NULL, `t_name` VARCHAR(30) DEFAULT NULL, PRIMARY KEY(`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT INTO `t_teacher` VALUES (1,'李老师');INSERT INTO `t_teacher` VALUES (2,'张老师');-- 建立学生表CREATE TABLE IF NOT EXISTS `t_student` ( `id` INT(10) NOT NULL, `s_name` VARCHAR(30) DEFAULT NULL, `t_id` INT(10) DEFAULT NULL, PRIMARY KEY(`id`), KEY `fktid` (`t_id`), CONSTRAINT `fktid` FOREIGN KEY (`t_id`) REFERENCES `t_teacher` (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT INTO `t_student` VALUES(1,'刘备',1);INSERT INTO `t_student` VALUES(2,'关羽',2);INSERT INTO `t_student` VALUES(3,'张飞',2);INSERT INTO `t_student` VALUES(4,'赵云',1);INSERT INTO `t_student` VALUES(5,'黄忠',1);-- 联表查询测试SELECT s.*,t.* from t_student s,t_teacher t where s.t_id = t.id order by t.id;
老师类:
public class Teacher implements Serializable { private Integer id; private String name; // 省略 getter、setter、toString()等方法}
学生类:
public class Student implements Serializable { private Integer id; private String name; // 从表属性包含主表实体的引用 private Teacher teacher; // 省略 setter、getter、toString() 等方法}
public interface StudentMapper { ListfindStudentList();}
public class Demo5Test { TeacherMapper teacherMapper = null; StudentMapper studentMapper = null; @Before public void loadConfiguration() throws IOException { InputStream in = Resources.getResourceAsStream("com/mapper/demo5/mybatis-config.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); SqlSession sqlSession = factory.openSession(); teacherMapper = sqlSession.getMapper(TeacherMapper.class); studentMapper = sqlSession.getMapper(StudentMapper.class); } @Test public void testFindTwoTable(){ Liststudents = studentMapper.findStudentList(); for (Student student : students){ System.out.println(student); } } /* 控制台输出结果DEBUG [main] - ==> Preparing: SELECT s.*,t.* from t_student s,t_teacher t where s.t_id = t.id order by t.idDEBUG [main] - ==> Parameters:TRACE [main] - <== Columns: id, s_name, t_id, id, t_nameTRACE [main] - <== Row: 5, 黄忠, 1, 1, 李老师TRACE [main] - <== Row: 1, 刘备, 1, 1, 李老师TRACE [main] - <== Row: 4, 赵云, 1, 1, 李老师TRACE [main] - <== Row: 2, 关羽, 2, 2, 张老师TRACE [main] - <== Row: 3, 张飞, 2, 2, 张老师DEBUG [main] - <== Total: 5Student{id=5, name='黄忠', teacher=Teacher{id=1, name='李老师'}}Student{id=1, name='刘备', teacher=Teacher{id=1, name='李老师'}}Student{id=4, name='赵云', teacher=Teacher{id=1, name='李老师'}}Student{id=2, name='关羽', teacher=Teacher{id=2, name='张老师'}}Student{id=3, name='张飞', teacher=Teacher{id=2, name='张老师'}}Process finished with exit code 0 */}
多对多关系可以当作一对多关系处理,在 mybatis 中使用 <collection>
标签关联。
实例描述:
需求:
-- 创建学生表CREATE TABLE IF NOT EXISTS `t_student`( `id` int(11) auto_increment, `name` VARCHAR(10), PRIMARY KEY(`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;-- 创建课程表CREATE TABLE IF NOT EXISTS `t_course`( `id` int(11) auto_increment, `name` VARCHAR(10), PRIMARY KEY(`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;-- 创建多对多关系连接表CREATE TABLE IF NOT EXISTS `t_stu_ref_course`( `id` int(11) auto_increment, `s_id` int(11), `c_id` int(11), PRIMARY KEY(`id`), CONSTRAINT `fk_sid` FOREIGN KEY (`s_id`) REFERENCES `t_student` (`id`), CONSTRAINT `fk_cid` FOREIGN KEY (`c_id`) REFERENCES `t_course` (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;-- 插入数据INSERT INTO `t_commodity` VALUES(1,'电脑');INSERT INTO `t_commodity` VALUES(2,'平板');INSERT INTO `t_commodity` VALUES(3,'手机');INSERT INTO `t_commodity_order` VALUES(1,'订单一');INSERT INTO `t_commodity_order` VALUES(2,'订单二');INSERT INTO `t_commodity_order` VALUES(3,'订单三');INSERT INTO `t_commodity_ref_order` VALUES(1,1,1);INSERT INTO `t_commodity_ref_order` VALUES(2,1,2);INSERT INTO `t_commodity_ref_order` VALUES(3,1,3);INSERT INTO `t_commodity_ref_order` VALUES(4,2,1);INSERT INTO `t_commodity_ref_order` VALUES(5,2,3);INSERT INTO `t_commodity_ref_order` VALUES(6,3,1);INSERT INTO `t_commodity_ref_order` VALUES(7,3,2);-- 查询一种商品所在订单的列表SELECT c.`name` commodity, o.`name` c_orderFROM t_commodity c, t_commodity_order o, t_commodity_ref_order rWHERE r.c_id = c.id AND r.o_id = o.id-- 查询一张订单上的商品数SELECT o.id o_id, c.id c_id, o.`name` order_name, c.`name` commodity_nameFROM t_commodity c, t_commodity_order o, t_commodity_ref_order rWHERE r.c_id = c.id AND r.o_id = o.id
商品类:
public class Commodity implements Serializable { private Integer id; private String name; private ListcommodityOrderList; // 省略 getter、setter、toString()}
订单类:
public class CommodityOrder { private Integer id; private String name; private ListcommodityList; // 省略 getter、setter、toString()}
public interface CommodityMapper { ListfindAll();}
public interface CommodityOrderMapper { ListfindAll();}
商品接口映射文件:
订单接口映射文件:
public class Demo6Test { CommodityMapper commodityMapper = null; CommodityOrderMapper commodityOrderMapper = null; @Before public void loadConfiguration() throws IOException { InputStream in = Resources.getResourceAsStream("com/mapper/demo6/mybatis-config.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); SqlSession sqlSession = factory.openSession(); commodityMapper = sqlSession.getMapper(CommodityMapper.class); commodityOrderMapper = sqlSession.getMapper(CommodityOrderMapper.class); } /** * 测试查询一个商品所在的订单列表 */ @Test public void findTwoTables(){ ListcommodityList = commodityMapper.findAll(); for (Commodity u : commodityList){ System.out.println(u); } } /* 测试一结果:DEBUG [main] - ==> Preparing: SELECT c.id c_id,o.id o_id,c.`name` commodity,o.`name` c_order FROM t_commodity c,t_commodity_order o,t_commodity_ref_order r WHERE r.c_id = c.id AND r.o_id = o.idDEBUG [main] - ==> Parameters:TRACE [main] - <== Columns: c_id, o_id, commodity, c_orderTRACE [main] - <== Row: 1, 1, 电脑, 订单一TRACE [main] - <== Row: 2, 1, 平板, 订单一TRACE [main] - <== Row: 3, 1, 手机, 订单一TRACE [main] - <== Row: 1, 2, 电脑, 订单二TRACE [main] - <== Row: 3, 2, 手机, 订单二TRACE [main] - <== Row: 1, 3, 电脑, 订单三TRACE [main] - <== Row: 2, 3, 平板, 订单三DEBUG [main] - <== Total: 7Commodity{id=1, name='电脑', commodityOrderList=[CommodityOrder{id=1, name='订单一', commodityList=null}, CommodityOrder{id=2, name='订单二', commodityList=null}, CommodityOrder{id=3, name='订单三', commodityList=null}]}Commodity{id=2, name='平板', commodityOrderList=[CommodityOrder{id=1, name='订单一', commodityList=null}, CommodityOrder{id=3, name='订单三', commodityList=null}]}Commodity{id=3, name='手机', commodityOrderList=[CommodityOrder{id=1, name='订单一', commodityList=null}, CommodityOrder{id=2, name='订单二', commodityList=null}]} */ /** * 测试查询一个订单的商品列表 */ @Test public void findTwoTables2(){ List commodityList = commodityOrderMapper.findAll(); for (CommodityOrder o : commodityList){ System.out.println(o); } } /* 测试二结果:DEBUG [main] - ==> Preparing: SELECT o.id o_id,c.id c_id,o.`name` order_name,c.`name` commodity_name FROM t_commodity c,t_commodity_order o,t_commodity_ref_order r WHERE r.c_id = c.id AND r.o_id = o.idDEBUG [main] - ==> Parameters:TRACE [main] - <== Columns: o_id, c_id, order_name, commodity_nameTRACE [main] - <== Row: 1, 1, 订单一, 电脑TRACE [main] - <== Row: 1, 2, 订单一, 平板TRACE [main] - <== Row: 1, 3, 订单一, 手机TRACE [main] - <== Row: 2, 1, 订单二, 电脑TRACE [main] - <== Row: 2, 3, 订单二, 手机TRACE [main] - <== Row: 3, 1, 订单三, 电脑TRACE [main] - <== Row: 3, 2, 订单三, 平板DEBUG [main] - <== Total: 7CommodityOrder{id=1, name='订单一', commodityList=[CommodityOrder{id=1, name='电脑', commodityList=null}, CommodityOrder{id=2, name='平板', commodityList=null}, CommodityOrder{id=3, name='手机', commodityList=null}]}CommodityOrder{id=2, name='订单二', commodityList=[CommodityOrder{id=1, name='电脑', commodityList=null}, CommodityOrder{id=3, name='手机', commodityList=null}]}CommodityOrder{id=3, name='订单三', commodityList=[CommodityOrder{id=1, name='电脑', commodityList=null}, CommodityOrder{id=2, name='平板', commodityList=null}]} */}
MyBatis 中的结果集映射标签为 <resultMap>
,需要指定唯一标识(取名) id
和映射实体类类型 type
,我想补充的是其子标签 <result>
。
上面的代码中,有如下这几段:
执行的查询 sql 如下:
SELECT c.id c_id, o.id o_id, c.`name` commodity, o.`name` c_orderFROM t_commodity c, t_commodity_order o, t_commodity_ref_order rWHERE r.c_id = c.id AND r.o_id = o.id
sql 查询结果表如下:
可以看出 <result>
标签中指定的 column
属性是 sql 查询字段的别名,也就意味着,MyBatis 是先查询获取到结果列表,然后再根据查询结果的字段进行类的封装,顾名思义叫做结果集封装。
所以 column
的取值是查询结果中的字段名,而不是数据库表中的字段,相对应的 property
就是实体类的属性名,最终将 column
字段对应的值封装到了实体类对应的 property
属性上。
以前博主对此有深深的误解,如果读者知道这个细节,那就太好了,至少在对数据进行映射时不会出现映射值为 null 或映射字段冲突(查询结果字段重名,可以取别名以区分)等问题。
转载地址:http://syvzi.baihongyu.com/