九.1 Lombok注解
@Getter and @Setter
@FieldNameConstants
@ToString
@EqualsAndHashCode
@AllArgsConstructor, @RequiredArgsConstructor and @NoArgsConstructor
@Log, @Log4j, @Log4j2, @Slf4j, @XSlf4j, @CommonsLog, @JBossLog, @Flogger, @CustomLog
@Data
@Builder
@SuperBuilder
@Singular
@Delegate
@Value
@Accessors
@Wither
@With
@SneakyThrows
九.2 使用步骤
- 在IDEA中安装Lombok插件
- 在项目中导入Lombok包:
<!-- lombok-->
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.26</version>
</dependency>
- 在实体类添加注解
@Data
@AllArgsConstructor//有参构造
@NoArgsConstructor//无参构造
public class User {
private int id;
private String name;
private String password;
}
十.多对一处理
多对一:
多个学生对应一个老师
对于学生而言,关联···多个学生关联一个老师【多对一】
对于老师而言,集合···一个老师有很多学生【一对多】
- 数据库
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-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
</dependency>
- 在bean包下创建实体类
@Data
public class Teacher {
private int id;
private String name;
}
@Data
public class Student {
private int id;
private String name;
//学生需要关联一个老师
private Teacher teacher;
}
4.创建Mapper接口
public interface StudentMapper {
}
public interface TeacherMapper {
@Select("select *from teacher where id=#{tid}")
Teacher getTeacher(@Param("tid") int id);
}
在resource目录的mybatis里面创建xml配置文件
TeacherMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--核心配置文件-->
<mapper namespace="com.example.mybatistest.dao.TeacherMapper">
</mapper>
StudentMapper
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--核心配置文件-->
<mapper namespace="com.example.mybatistest.dao.StudentMapper">
</mapper>
- 绑定注册我们的Mapper接口和配置文件
<mapper class="com.example.mybatistest.dao.TeacherMapper"/>
<mapper class="com.example.mybatistest.dao.StudentMapper"/>
- 测试
package com.example.mybatistest;
import com.example.mybatistest.bean.User;
import com.example.mybatistest.dao.UserDao;
import com.example.mybatistest.dao.UserMapper;
import com.example.mybatistest.util.MybatisUntils;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.HashMap;
import java.util.List;
@SpringBootTest
class MybatisTestApplicationTests {
@Test
void contextLoads() {
// 获取用户数据列表
// 第一步 获取SqlSession对象
SqlSession sqlSession = MybatisUntils.getSqlSession();
// 执行sql
// 方式一: getMapper
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> userList = userDao.getUserList();
for (User user:userList
) {
System.out.println(user);
}
// 关闭sqlSession会话
sqlSession.close();
}
// 通过id查询用户对象
@Test
public void getUserById(){
SqlSession sqlSession = MybatisUntils.getSqlSession();
UserDao mapper= sqlSession.getMapper(UserDao.class);
User user = mapper.getUserById(1);
System.out.println(user);
sqlSession.close();
}
// 增加用户
@Test
void addUserTest(){
SqlSession sqlSession = MybatisUntils.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
User user01 = new User(4,"用户4","password4");
mapper.addUser(user01);
List<User> userList = mapper.getUserList();
for (User user:userList
) {
System.out.println(user);
}
sqlSession.close();
}
// 测试map形式插入数据
@Test
void mapTest(){
SqlSession sqlSession = MybatisUntils.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
HashMap<String, Object> map = new HashMap<>();
map.put("userid",5);
map.put("username","用户5");
map.put("password","password5");
mapper.addUser2(map);
sqlSession.commit();
sqlSession.close();
}
// 测试分页
@Test
public void getUserByLimit(){
SqlSession sqlSession = MybatisUntils.getSqlSession();
UserDao mapper = sqlSession.getMapper(UserDao.class);
HashMap<String, Integer> map = new HashMap<>();
map.put("startIndex",1);
map.put("pageSize",2);
List<User> list = mapper.getUserByLimit(map);
for (User user : list) {
System.out.println(user.toString());
}
sqlSession.close();
}
//测试RowBounds
@Test
public void getRowBounds(){
//RowBounds实现
RowBounds rowBounds = new RowBounds(0, 2);
SqlSession sqlSession = MybatisUntils.getSqlSession();
List<User> userList = sqlSession.selectList("com.example.mybatistest.dao.UserDao.getRowBounds",null,rowBounds);
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
// 测试
@Test
public void getUsers(){
SqlSession sqlSession = MybatisUntils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.getUsers();
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
}
十.2 按照查询嵌套处理
select s.id,s.name,t.name from student s,teacher t where s.tid=t.id
<!--
思路:
1. 查询所有的学生信息
2. 根据查询出来的学生的tid,寻找对应的老师 子查询
-->
<select id="getStudent" resultMap="StudentTeacher">
select * from student;
</select>
<resultMap id="StudentTeacher" type="student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!--复杂的属性,需要单独处理 对象:association 集合:collection -->
<association property="teacher" column="tid" javaType="student" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="teacher">
select * from teacher where id = #{tid}
</select>
resultType后面的类型报错就先给该类起别名
十.3 按照结果嵌套处理
<!-- 按照结果嵌套处理-->
<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" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
十一. 一对多处理
- 对实体类处理
@Alias("teacher")
@Data
public class Teacher {
private int id;
private String name;
//一个老师拥有多个学生
private List<Student> students;
}
@Alias("student")
@Data
public class Student {
private int id;
private String name;
//学生需要关联一个老师
private int tid;
}
- mapper和配置绑定
十一. 1 按照结果嵌套处理
<!--按结果嵌套查询-->
<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"/>
<!--复杂的属性我们需要单独处理-->
<!--对象:association-->
<!-- 集合:collection -->
<!-- javaType=""指定属性的类型 集合中的泛型信息,我们使用ofType获取-->
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
十一.2 按照查询嵌套处理
<!--子查询-->
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from teacher where id = #{tid}
</select>
<resultMap id="TeacherStudent2" type="teacher">
<collection property="students" javaType="ArrayList" ofType="student" select="TeacherStudentByTeacherId" column="id"/>
</resultMap>
<select id="TeacherStudentByTeacherId" resultType="student">
select * from student where tid = #{tid}
</select>
十一.3 小结
- 关联-association 【多对一】
- 集合-collection 【一对多】
- javaType & ofType
. javaType用来指定实体类中属性的类型
. ofType用来指定映射到List或者集合中的pojo类型,泛型中的约束类型
注意点:
保证SQL的可读性,尽量保证通俗易懂
注意一对多和多对一中,属性名和字段的问题
如果问题不好排查错误,可以使用日志,建议使用log4j
面试重点
- MySql引擎
- InnoDB底层原理
- 索引
- 索引优化