多对一：
多对一的理解：
- 多个女神对应一个男神
- 对于女神这边，就是一个多对一的现象，即从女神这边关联一个男神
数据库设计
# 女神表 CREATE TABLE `beauty` ( // 编号 `id` int(11) NOT NULL AUTO_INCREMENT, // 姓名 `name` varchar(50) NOT NULL, // 生日 `bornDate` datetime DEFAULT '1987-01-01 00:00:00', // 电话 `phone` varchar(11) NOT NULL, // 男神id `boyfriendId` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `beauty` values (1,'柳岩','1988-02-03 00:00:00','18209876577',5), (2,'苍老师','1987-12-30 00:00:00','18219876577',5), (3,'Angelababy','1989-02-03 00:00:00','18209876567',3), (4,'热巴','1993-02-03 00:00:00','18209876579',2), (5,'周冬雨','1992-02-03 00:00:00','18209179577',9), (6,'周芷若','1988-02-03 00:00:00','18209876577',1), (7,'岳灵珊','1987-12-30 00:00:00','18219876577',9), (8,'小昭','1989-02-03 00:00:00','18209876567',1), (9,'双儿','1993-02-03 00:00:00','18209876579',9), (10,'王语嫣','1992-02-03 00:00:00','18209179577',4), (11,'夏雪','1993-02-03 00:00:00','18209876579',9), (12,'赵敏','1992-02-03 00:00:00','18209179577',1), (13,'紫霞仙子','1999-12-10 00:00:00','17303773603',7), (14,'嫦娥','1234-02-03 00:00:00','1234',3), (15,'阳丽','1999-09-21 00:00:00','0987654',5), (16,'关晓彤','1999-12-10 00:00:00','1234',5), (17,'张靓颖','1999-01-11 00:00:00','145678',6), (18,'貂蝉','1978-12-12 00:00:00','098765',1954), (19,'不知火舞','1999-12-13 00:00:00','2345689',5), (20,'阿珂','1223-08-09 00:00:00','12456789',5); #男神表 CREATE TABLE `boys` ( // 男神id `id` int(11) NOT NULL AUTO_INCREMENT, // 男神姓名 `boyName` varchar(20) DEFAULT NULL, // 魅力值 `userCP` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `boys` values (1,'张无忌',100), (2,'鹿晗',800), (3,'黄晓明',50), (4,'段誉',300), (5,'齐菁菁',360), (6,'刘祥',260), (7,'牛辉',300), (8,'武怡',123), (10,'王一帆',10), (12,'詹三',100), (17,'齐元松',180), (20,'朱辉',246);
编写实体类
@Data @NoArgsConstructor @AllArgsConstructor public class Beauty { /** * 女神id */ private int id; /** * 女神姓名 */ private String name; /** * 出生日期 */ private Date bornDate; /** * 手机号 */ private String phone; /** * 对应男神id */ private int boyfriendId; /** * 对应男神信息 */ private Boys boys; }
@Data @AllArgsConstructor @NoArgsConstructor public class Boys { /** * 男神id */ private int id; /** * 男神姓名 */ private String boyName; /** * 魅力值 */ private int userCP; }
编写两个实体类的接口
public interface BeautyMapper { }
public interface BoysMapper { }
编写对应的配置文件
BeautyMapper.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mapper.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.lili.dao.BeautyMapper"> </mapper>
BoysMapper.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mapper.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.lili.dao.BoysMapper"> </mapper>
编写需求代码
1.给BeautyMapper接口增加方法
List<Beauty> findBeautyBoy();
2.编写对应的Mapper文件
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mapper.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.lili.dao.BeautyMapper"> <!--结果集映射--> <resultMap id="findBeautyBoyMap" type="com.lili.entity.Beauty" autoMapping="true"> <!--由于两个表id一样，所以需要用别名来区别--> <id property="id" column="b1_id"/> <association property="boys" javaType="com.lili.entity.Boys" autoMapping="true"> <id property="id" column="b2_id"/> </association> </resultMap> <!--查询所有的女神以及她的男神信息（多对一）--> <select id="findBeautyBoy" resultMap="findBeautyBoyMap"> select b1.id as b1_id, b1.name, b1.bornDate, b1.phone, b1.boyfriendId, b2.id as b2_id, b2.boyName, b2.userCP from beauty b1 left join boys b2 on b1.boyfriendId = b2.id </select> </mapper>
3.测试
@Test public void test1() { // 利用多对一查询所有女神及女神的男神名字 try (SqlSession sqlSession = MybatisUtil.getSqlSession()) { BeautyMapper mapper = sqlSession.getMapper(BeautyMapper.class); List<Beauty> beauty = mapper.findBeautyBoy(); beauty.forEach(System.out::println); } catch (Exception e) { e.printStackTrace(); } }
一对多：
实体类编写
@Data @NoArgsConstructor @AllArgsConstructor public class Beauty { private int id; private String name; private Date bornDate; private String phone; private int boyfriendId; }
@Data @AllArgsConstructor @NoArgsConstructor public class Boys { private int id; private String boyName; private int userCP; private List<Beauty> beautyList; }
编写代码
1.给BeautyMapper接口增加方法
List<Boys> getBoyBeauty();
2.编写对应的Mapper文件
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mapper.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.lili.dao.BoysMapper"> <!--结果集映射--> <resultMap id="getBoyBeautyMap" type="com.lili.entity.Boys" autoMapping="true"> <result property="id" column="b2_id"/> <collection property="beautyList" ofType="com.lili.entity.Beauty" autoMapping="true"> <result property="id" column="b1_id"/> </collection> </resultMap> <!--一对多--> <select id="getBoyBeauty" resultMap="getBoyBeautyMap"> select b1.id as b1_id, b1.name, b1.bornDate, b1.phone, b1.boyfriendId, b2.id as b2_id, b2.boyName, b2.userCP from beauty b1 right join boys b2 on b1.boyfriendId = b2.id </select> </mapper>
3.测试
@Test public void test1() { // 利用一对多查询所有男神的女神名字 try (SqlSession sqlSession = MybatisUtil.getSqlSession()) { BoysMapper mapper = sqlSession.getMapper(BoysMapper.class); List<Boys> boys = mapper.getBoyBeauty(); boys.forEach(System.out::println); } catch (Exception e) { e.printStackTrace(); } }
小结：
1.关联-association
2.集合-collection
3.所以association是用于一对一和多对一，而collection是用于一对多的关系
4.JavaType和ofType都是用来指定对象类型的
- JavaType是用来指定entity中属性的类型
- ofType是用来指定list集合中属性的entity类型
