多对一:
多个学生,对应一个老师
对于学生, 关联 多个学生关联多个老师【多对一】
对于老师, 集合 一个老师,有很多学生【一对多】
创建数据库的表
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=utf8
INSERT 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');
测试环境搭建:
导入lombok
新建实体类 Teacher Student
新建Mapper接口
新建Mapper.xml文件
在核心配置文件中绑定接口(方式多)
测试是否能够成功
插几句嘴:关于idea中建包不分层的情况,将Compact Middle Packages的勾去了就好了,很无奈,不分层的话,在注册的时候,就扫描不到
按照查询嵌套处理:
<select id="getStudent" resultMap="StudentTeacher">
select * from mybatis.student
</select>
<resultMap id="StudentTeacher" type="Student">
<!--association关联属性 property属性名 javaType属性类型 column在多的一方的表中的列名-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from mybatis.teacher where id=#{id}
</select>
按照结果嵌套处理:
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid, s.name sname , t.name tname
from student s,teacher 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" column="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
一对多:
对于一个老师而言,就是一对多个学生
搭建环境:
实体类:
public class Student {
private int id;
private String name;
private int tid;
}
@Data
public class Teacher {
private int id;
private String name;
//一个老师拥有多个学生
private List<Student> student;
}
哈哈哈,痛苦,没听懂
按照结果嵌套处理
<select id="getTeacher" resultMap="TeacherStudent">
select s.id sid,s.name sname,t.name tname,t.id tid
from mybatis.student s,mybatis.teacher t
where s.tid=t.id AND t.id=#{tid};
</select>
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
按照查询嵌套处理
<select id="getTeacher2" resultMap="TeacherStudent2">
select *
from mybatis.teacher where id=#{tid};
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<collection property="students" javaType="ArrayList" ofType="Student"
select="getStudentByTeacherId" column="id"/>
</resultMap>
<select id="getStudentByTeacherId" resultType="Student">
select * from mybatis.student where tid=#{id};
</select>
1、关联-association
2、集合-collection
3、所以association是用于一对一和多对一,而collection是用于一对多的关系
4、JavaType和ofType都是用来指定对象类型的
-
JavaType是用来指定pojo中属性的类型
-
ofType指定的是映射到list集合属性中pojo的类型。
注意:
1、保证SQL的可读性,尽量通俗易懂
2、根据实际要求,尽量编写性能更高的SQL语句
3、注意属性名和字段不一致的问题
4、注意一对多和多对一 中:字段和属性对应的问题
5、尽量使用Log4j,通过日志来查看自己的错误
面试高频:
Mysql引擎
InnoDB底层原理
索引
索引优化
动态SQL:根据不同的条件生成不同的SQL语句
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
搭建环境:
CREATE TABLE `blog` (
`id` varchar(50) NOT NULL COMMENT '博客id',
`title` varchar(100) NOT NULL COMMENT '博客标题',
`author` varchar(30) NOT NULL COMMENT '博客作者',
`create_time` datetime NOT NULL COMMENT '创建时间',
`views` int(30) NOT NULL COMMENT '浏览量'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
增加了一个随机获取ID的工具类 IDUtil
public class IDUtil {
public static String getID(){
return UUID.randomUUID().toString().replaceAll("-","");
}
}
实体类
Mapper和Mapper.xml
核心配置文件
测试类
IF:
Mapper
List<Blog> queryBlogIF(Map map);
Mapper.xml
<select id="queryBlogIF" parameterType="map" resultType="Blog">
select * from mybatis.blog where 1=1
<if test="title != null">
and title=#{title}
</if>
<if test="author != null">
and author=#{author}
</if>
</select>
Test
@Test
public void testIf()
{
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map=new HashMap();
//map.put("title","Mybatis如此简单");
map.put("author","狂神说");
List<Blog> blogs = mapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
choose (when, otherwise):
//这个就相当于 switch case
<select id="queryBlogChoose" parameterType="map" resultType="Blog">
select * from mybatis.blog
<where>
<choose>
<when test="title != null">
title=#{title}
</when>
<when test="author != null">
and author=#{author}
</when>
<otherwise>
and views =#{views}
</otherwise>
</choose>
</where>
</select>
trim (where, set):
<select id="queryBlogIF" parameterType="map" resultType="Blog">
select * from mybatis.blog
<where>
<if test="title != null">
title=#{title}
</if>
<if test="author != null">
and author=#{author}
</if>
</where>
</select>
<update id="updataBlog" parameterType="map">
update mybatis.blog
<set>
<if test="title != null">
title=#{title},
</if>
<if test="author !=null">
author=#{author}
</if>
</set>
where id=#{id}
</update>
SQL片段:
使用sql标签抽取公共部分,在需要使用的地方,使用include标签引用
<sql id="if-title-author">
<if test="title != null">
title=#{title}
</if>
<if test="author != null">
and author=#{author}
</if>
</sql>
<select id="queryBlogIF" parameterType="map" resultType="Blog">
select * from mybatis.blog
<where>
<include refid="if-title-author"></include>
</where>
</select>
注意:最好基于单表定义SQL片段
不要存在where标签
foreach:
<select id="queryBlogForeach" parameterType="map" resultType="Blog">
select * from mybatis.blog
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id=#{id}
</foreach>
</where>
</select>
动态sql:根据不同的条件生成不同的语句,使用逻辑判断,就是在拼接sql语句,我们只需要保证 sql的正确以及位置正确就可以