练习mybatis多对一关系

x33g5p2x  于2021-09-24 转载在 其他  
字(3.4k)|赞(0)|评价(0)|浏览(114)

  • 多个学生,对应一个老师
  • 对于学生这边而言,关联, 多个学生,关联一个老师【多对一】
  • 对于老师而言,集合,一个老师有很多学生【一对多】

SQL:

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`, `name`) VALUES (1, 计算机老师); 

CREATE TABLE `student` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO `student` (`id`, `name`, `tid`) VALUES (1, 小明, 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (2, 小红, 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (3, 小张, 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (4, 小李, 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (5, 小王, 1);

测试环境搭建
1.导入lombok
2.新建实体类Teacher,Student

@Data
public class Student{
	private int id;
	private String name;
	//学生需要关联一个老师!
	private Teacher teacher;
}
@Data
public class Teacher{
	private int id;
	private String name;

}

3.建立Dao接口TeacherDao,StudentDao

public interface TeacherDao{
	@Select("select * from teacher where id=#{tid}")
	Teacher getTeacher(@Param("tid") int id);
}
public interface StudentDao{

}

4.建立Mapper.xml文件
在resources目录创建com.jialidun.dao包

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jialidun.dao.TeacherDao">
	
</mapper>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jialidun.dao.StudentDao">
	
</mapper>

5.在核心配置文件中绑定注册我们的Mapper接口或者文件!

<mappers>
	<mapper resource="com/jialidun/dao/*.xml"/>
	<mapper class="com.jialidun.dao.*"/>
</mappers>

6.测试查询成功

public class TestDemo{
	@Test
	public void test01(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		TeacherDap mapper = sqlSession.getBean(TeacherDao.class);
		Teacher teacher = mapper.getTeacher(1);
		System.out.println(teacher);
		sqlSession.close();
	}

}

按照查询嵌套处理

<!-- 思路: 1.查询所有的学生信息 2.根据查询出来的学生的tid,寻找对应的老师! 子查询 -->
<select id="getStudent" resultMap="StudentTeacher">
	select * from student
</select>
<resultMap id="StudentTeacher" type="Student">
	<!--property指的是java对象的属性 column指的是数据库字段 -->
	<result property="id" column="id"/>
	<result property="name" column="name"/>
	<!--复杂的属性,我们需要单独处理对象:association 集合:collection-->
	<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<!--============================================-->
<select id="getStudent2" resultMap="StudentTeacher2">
	select s.id as sid,s.name as sname,t.name as tname
	from student as s,teacher as t
	where s.tid=t.id;
</select>
<resultMap id="StudentTeacher2" type="Student">
	<result property="id" column="sid"/>
	<result property="name" column="sname"/>
	<association property="teacher" javaType="Teacher">
		<select property="name" column="tname"/>
	</association>
</resultMap>

小结

1.关联 - association【多对一】
2.集合 - collection【一对多】
3.javaType & ofType

  • javaTyoe用来指定实体类中属性的类型
  • ofType用来指定映射到List或者集合中的pojo类型,泛型中的约束类型!

注意点:

  • 保证SQL的可读性,尽量保证通俗易懂
  • 注意一对多和多对一中,属性名和字段的问题!
  • 如果问题不好排查错误,可以使用日志,建议使用Log4j

面试高频

  • Mysql引擎
  • InnoDB底层原理
  • 索引
  • 索引优化!

相关文章

最新文章

更多