mybatis表对应关系|mybatis表对应关系一对一一对多多对一多对多同时对多一和多对多 mybatis表对应关系一对一一对多

在之前的项目中改造的，如果不知道怎么创建项目：ssm
改造完成的项目，或者获取sql：mybatis表关联
【mybatis表对应关系|mybatis表对应关系一对一一对多多对一多对多同时对多一和多对多】注解版，我个人习惯于注解形式的，应为不用创建xml。

spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver #com.mysql.jdbc.Driver username: root password: root url: jdbc:mysql://localhost:3306/mydb2?serverTimezone=UTC thymeleaf: cache: false mybatis: type-aliases-package: com.shp.dev mapper-locations: classpath:mapper/*.xml logging: level: com: shp: dev: errorpagehelper: helper-dialect: mysql reasonable: true support-methods-arguments: true params: count=countsqlserver: port: 80

实体类：

@Data @AllArgsConstructor @NoArgsConstructor public class Chard { private Integer id; private String idNumber; private Integer cradId; }@Data @AllArgsConstructor @NoArgsConstructor public class Phone {private Integer phoneId; private String name; private String price; }@ApiModel @Data @AllArgsConstructor @NoArgsConstructor public class Student {@ApiModelProperty("主键") private Long id; @ApiModelProperty("名字") private String name; @ApiModelProperty("年龄") private String age; @ApiModelProperty("性别") private String gender; @ApiModelProperty("班级") private String className; @ApiModelProperty("学生对应的身份证信息") private Chard chard; @ApiModelProperty("学生对应的手机信息") private List phones; }

dao层

@Mapper public interface ChardMapper {@Select("SELECT id,id_number idNumber ,crad_id cradId from chard where crad_id = #{id}") Chard findChardById(Integer id); }

@Mapper public interface PhoneMapper {@Select("SELECT id,name,price,phone_id phoneId from phone where phone_id=#{id}") List findPhoneById(Integer id); }

@Mapper public interface StudentMapper {//一对一查询，一个人对应一个身份证信息 @Select("SELECT * from student where id=#{id}") //property student类中对应的身份证信息的类属性名。 @Results({ @Result(property = "chard", column = "id", one = @One(select = "com.shp.dev.spingbootmybatis.student.mapper.ChardMapper.findChardById")) }) Student findStudentAndChard(Integer id); // 多对一查询，多个学生对应个人的一个身份证信息 @Select("SELECT * from student") //property student类中对应的身份证信息的类属性名。 @Results({ @Result(property = "chard", column = "id", one = @One(select = "com.shp.dev.spingbootmybatis.student.mapper.ChardMapper.findChardById")) }) List> findAllStudentAndChard(); //一对多查询，一个人对应多个手机 @Select("SELECT * from student where id=#{id}") //property student类中对应的身份证信息的类属性名。 @Results({ @Result(property = "phones", column = "id", many = @Many(select = "com.shp.dev.spingbootmybatis.student.mapper.PhoneMapper.findPhoneById")) }) Student findStudentAndPhone(Integer id); //多对多查询，多个人对应多个手机 @Select("SELECT * from student") //property student类中对应的身份证信息的类属性名。 @Results({ @Result(property = "phones", column = "id", many = @Many(select = "com.shp.dev.spingbootmybatis.student.mapper.PhoneMapper.findPhoneById")) }) List> findAllStudentAndPhone(); //多对一，多个学生对应个人的身份证信息，多对多，多个学生对应多个手机 @Select("SELECT * from student") @Results({ @Result(property = "chard", column = "id", one = @One(select = "com.shp.dev.spingbootmybatis.student.mapper.ChardMapper.findChardById")), @Result(property = "phones", column = "id", many = @Many(select = "com.shp.dev.spingbootmybatis.student.mapper.PhoneMapper.findPhoneById")) }) List> findAllStudentAndChardAndPhone(); }

测试类：

@SpringBootTest class StudentTest {@Autowired private StudentMapper studentMapper; @Test void contextLoads() {System.out.println("一对一---------------------------------------------------------------------------------"); Student studentAndChard = studentMapper.findStudentAndChard(1); System.out.println(studentAndChard); System.out.println("多对一---------------------------------------------------------------------------------"); List> allStudent = studentMapper.findAllStudentAndChard(); for (Student student : allStudent) { System.out.println(student); }System.out.println("一对多---------------------------------------------------------------------------------"); Student studentAndPhone = studentMapper.findStudentAndPhone(1); System.out.println(studentAndPhone); System.out.println("多对多---------------------------------------------------------------------------------"); List> allStudentAndPhone = studentMapper.findAllStudentAndPhone(); for (Student student : allStudentAndPhone) { System.out.println(student); }System.out.println("多对一多对多---------------------------------------------------------------------------------"); List> allStudentAndChardAndPhone = studentMapper.findAllStudentAndChardAndPhone(); for (Student student : allStudentAndChardAndPhone) { System.out.println(student); }}

xml版和注解版的实体类/dao层接口一样，去掉@select(“select * from xxx”)这种对数据库操作的注解
xml版，支持驼峰命名转换，不需要自己别名还是很爽

spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver #com.mysql.jdbc.Driver username: root password: root url: jdbc:mysql://localhost:3306/mydb2?serverTimezone=UTC thymeleaf: cache: false mybatis: type-aliases-package: com.shp.dev mapperLocations: classpath*:mybatis/**/*Mapper.xml# 配置mapper的扫描，找到所有的mapper.xml映射文件 configLocation: classpath:mybatis/mybatis-config.xml# 加载全局的配置文件 logging: level: com: shp: dev: errorpagehelper: helper-dialect: mysql reasonable: true support-methods-arguments: true params: count=countsqlserver: port: 80

> ="mapUnderscoreToCamelCase" value="https://www.it610.com/article/true"/> ="cacheEnabled"value="https://www.it610.com/article/true" /> ="useGeneratedKeys"value="https://www.it610.com/article/true" /> ="defaultExecutorType"value="https://www.it610.com/article/REUSE" /> ="logImpl"value="https://www.it610.com/article/SLF4J" />

="findChardById" resultType="Chard" parameterType="Integer">SELECT * from chard where crad_id=#{id}

="findPhoneById" resultType="Phone" parameterType="Integer">SELECT * from phone where phone_id=#{id}

="findAllStudentAndChardAndPhone" resultMap="colle-asso-Map">SELECT * FROM student s,chard c,phone p where s.id=c.crad_id and s.id=p.phone_id ="findStudentAndChard" resultMap="assoMap" parameterType="Integer"> SELECT * FROM student s,chard c where s.id=c.crad_id and s.id=#{id} ="findAllStudentAndChard" resultMap="assoMap" > SELECT * FROM student s,chard c where s.id=c.crad_id ="findStudentAndPhone" resultMap="colleMap" parameterType="Integer"> SELECT * from student s,phone p where s.id=p.phone_id and p.phone_id=#{id} ="findAllStudentAndPhone" resultMap="colleMap"> -- SELECT * from student s LEFT JOIN phone p ON s.id=p.phone_id两者没区别，本人习惯于下面这种 SELECT * from student s,phone p where s.id=p.phone_id