sql
CREATE TABLE `class` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号', `name` varchar(30) DEFAULT NULL COMMENT '班级名', `floor` int(3) DEFAULT NULL COMMENT '楼层', `teacher_id` int(11) DEFAULT NULL COMMENT '老师编号(FK)', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='班级信息表';INSERT INTO `class` VALUES ('1', '1年级2班', '4', '2');INSERT INTO `class` VALUES ('2', '1年级3班', '4', '2');INSERT INTO `class` VALUES ('3', '2年级1班', '1', '4');INSERT INTO `class` VALUES ('4', '2年级2班', '2', '5');INSERT INTO `class` VALUES ('5', '2年级3班', '3', '6');INSERT INTO `class` VALUES ('6', '3年级1班', '4', '7');INSERT INTO `class` VALUES ('7', '3年级2班', '1', '8');INSERT INTO `class` VALUES ('8', '3年级3班', '2', '9');INSERT INTO `class` VALUES ('9', '4年级1班', '3', '10');INSERT INTO `class` VALUES ('10', '4年级2班', '4', '11');INSERT INTO `class` VALUES ('11', '4年级3班', '1', '12');INSERT INTO `class` VALUES ('12', '5年级1班', '2', '13');INSERT INTO `class` VALUES ('13', '5年级3班', '4', '15');INSERT INTO `class` VALUES ('14', '6年级2班', '2', '17');INSERT INTO `class` VALUES ('15', '6年级3班', '3', '18');INSERT INTO `class` VALUES ('16', '1年级4班', '4', '99');INSERT INTO `class` VALUES ('17', '2年级4班', '2', '99');INSERT INTO `class` VALUES ('18', '3年级4班', '1', '99');INSERT INTO `class` VALUES ('19', '4年级1班', '1', '20');INSERT INTO `class` VALUES ('30', '4年级1班', '22', '20');CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号', `name` varchar(30) DEFAULT NULL COMMENT '名字', `age` int(2) DEFAULT NULL COMMENT '年龄', `title` varchar(30) DEFAULT NULL COMMENT '职称', `manager` int(11) DEFAULT NULL COMMENT '上司编号(FK)', `salary` int(6) DEFAULT NULL COMMENT '工资', `comm` int(6) DEFAULT NULL COMMENT '奖金', `gender` char(1) DEFAULT NULL COMMENT '性别', `subject_id` int(11) DEFAULT NULL COMMENT '科目编号(FK)', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='老师信息表';INSERT INTO `teacher` VALUES ('1', '刘苍松', '55', '总监', '20', '10000', '5000', '男', '1');INSERT INTO `teacher` VALUES ('2', '范传奇', '33', '三级讲师', '1', '3000', null, '男', '2');INSERT INTO `teacher` VALUES ('3', '王克晶', '32', '一级讲师', '1', '8000', '3000', '女', '3');INSERT INTO `teacher` VALUES ('4', '刘国斌', '29', '二级讲师', '1', '7300', '3400', '男', '4');INSERT INTO `teacher` VALUES ('5', '成恒', '35', '三级讲师', '1', '5200', '4600', '男', '5');INSERT INTO `teacher` VALUES ('6', '张皓岚', '33', '二级讲师', '1', '7800', '700', '男', '2');INSERT INTO `teacher` VALUES ('7', '胡悦', '25', '一级讲师', '1', '9000', '2800', '女', '1');INSERT INTO `teacher` VALUES ('8', '齐雷', '45', '总监', '20', '9800', '7800', '男', '3');INSERT INTO `teacher` VALUES ('9', '王海涛', '44', '二级讲师', '8', '3100', '1200', '男', '4');INSERT INTO `teacher` VALUES ('10', '张久军', '39', '一级讲师', '8', '2000', '6000', '男', '5');INSERT INTO `teacher` VALUES ('11', '于健', '27', '二级讲师', '12', '3800', null, '男', '2');INSERT INTO `teacher` VALUES ('12', '张立志', '34', '总监', '20', '13000', '900', '男', '3');INSERT INTO `teacher` VALUES ('13', '吴华', '46', '总监', '20', '8700', '6900', '男', '5');INSERT INTO `teacher` VALUES ('14', '李大帅', '29', '三级讲师', '13', '7000', null, '男', '1');INSERT INTO `teacher` VALUES ('15', '田浩', '26', '二级讲师', '13', '5600', '1900', '男', '4');INSERT INTO `teacher` VALUES ('16', '肖旭伟', '36', '一级讲师', '12', '6800', '600', '男', '2');INSERT INTO `teacher` VALUES ('17', '张敏', '29', '三级讲师', '13', '6100', '400', '女', '3');INSERT INTO `teacher` VALUES ('18', '赵微', '26', '二级讲师', '13', '4600', '6500', '女', '4');INSERT INTO `teacher` VALUES ('19', '李源', '25', '三级讲师', '13', '6300', '0', '男', '4');INSERT INTO `teacher` VALUES ('20', '韩少云', '55', '老板', null, '1', '0', '男', null);pom.xml
mysql mysql-connector-java 8.0.28 com.alibaba druid 1.2.8 org.projectlombok lombok 1.18.24 com.baomidou mybatis-plus-boot-starter 3.5.3 com.github.yulichang mybatis-plus-join-boot-starter 1.4.6 application.yml
spring: # 数据库连接配置 datasource: driver-class-name: com.mysql.cj.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource url: jdbc:mysql://localhost:3306/hedu?useSSL=false&serverTimeZone=Asia/Shanghai username: root password: root# mybatis-plus配置mybatis-plus: mapper-locations: classpath:mappers/*.xml configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImplMybatis-plus的分页插件的配置
package cn.highedu.boot.config;import com.baomidou.mybatisplus.annotation.DbType;import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;@Configurationpublic class MybatisPlusConfig { /** * 分页插件的配置 * @return */ @Bean public MybatisPlusInterceptor paginationInterceptor(){ MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); /** * DbType.XXX * XXX 为具体的数据库类型如MYSQL,ORACLE */ interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); return interceptor; }}创建基本类Class
package cn.highedu.boot.entity;import com.baomidou.mybatisplus.annotation.IdType;import com.baomidou.mybatisplus.annotation.TableId;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;@Data@AllArgsConstructor@NoArgsConstructorpublic class Class { @TableId(type = IdType.AUTO) private Integer id; private String name; private Integer floor; private Integer teacherId;}ClassMapper
package cn.highedu.boot.mapper;import cn.highedu.boot.entity.Class;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.github.yulichang.base.MPJBaseMapper;import org.apache.ibatis.annotations.*;@Mapperpublic interface ClassMapper extends BaseMapper, MPJBaseMapper {}ClassService
package cn.highedu.boot.service;import cn.highedu.boot.entity.Class;import com.baomidou.mybatisplus.extension.service.IService;public interface ClassService extends IService {}ClassServiceImpl
package cn.highedu.boot.service.impl;import cn.highedu.boot.entity.Class;import cn.highedu.boot.mapper.ClassMapper;import cn.highedu.boot.service.ClassService;import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;import org.springframework.stereotype.Service;@Servicepublic class ClassServiceImpl extends ServiceImpl implements ClassService {}Teacher
package cn.highedu.boot.entity;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;@Data@AllArgsConstructor@NoArgsConstructorpublic class Teacher { private Integer id; private String name; private Integer age; private String title; private Integer manager; private Integer salary; private Integer comm; private Character gender; private Integer subjectId;}TeacherMapper
package cn.highedu.boot.mapper;import cn.highedu.boot.entity.Teacher;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.github.yulichang.base.MPJBaseMapper;import org.apache.ibatis.annotations.*;@Mapperpublic interface TeacherMapper extends BaseMapper, MPJBaseMapper {}TeacherService
package cn.highedu.boot.service;import cn.highedu.boot.entity.Teacher;import com.baomidou.mybatisplus.extension.service.IService;public interface TeacherService extends IService {}TeacherServiceImpl
package cn.highedu.boot.service.impl;import cn.highedu.boot.entity.Teacher;import cn.highedu.boot.mapper.TeacherMapper;import cn.highedu.boot.service.TeacherService;import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;import org.springframework.stereotype.Service;@Servicepublic class TeacherServiceImpl extends ServiceImpl implements TeacherService {}TeacherDTO
package cn.highedu.boot.pojo.dto;import com.baomidou.mybatisplus.annotation.IdType;import com.baomidou.mybatisplus.annotation.TableId;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;@Data@AllArgsConstructor@NoArgsConstructorpublic class TeacherDTO { @TableId(type = IdType.AUTO) private Integer id; private String name; private Integer age; private String title; private Integer manager; private Integer salary; private Integer comm; private Character gender; private Integer subjectId; private String className; private Integer floor;}单表操作SingleTableOperationTest
package cn.highedu.boot.service;import cn.highedu.boot.entity.Class;import cn.highedu.boot.mapper.ClassMapper;import cn.highedu.boot.service.impl.ClassServiceImpl;import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;import com.baomidou.mybatisplus.core.toolkit.Wrappers;import org.junit.jupiter.api.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import java.util.List;@SpringBootTestclass ClassServiceTest extends ClassServiceImpl { @Autowired private ClassMapper classMapperSimple; //基本操作 //insert /** * 新增一条记录 */ @Test void simpleAddOne(){ Class addOneClazz = new Class(); addOneClazz.setName("4年级1班"); addOneClazz.setFloor(22); addOneClazz.setTeacherId(20); //INSERT INTO class ( name, floor, teacher_id ) VALUES ( ?, ?, ? ); int addOneResult = classMapperSimple.insert(addOneClazz); System.out.println(addOneResult); } //delete /** * 根据Id删除一条记录 */ @Test void simpleDeleteOneById(){ //DELETE FROM class WHERE id=?; int deleteByIdResult = classMapperSimple.deleteById(1); System.out.println(deleteByIdResult); } //update /** * 根据Id更改一条记录 */ @Test void simpleUpdateOneById(){ Class updateClass = new Class(); updateClass.setId(2); updateClass.setName("1年级3班"); updateClass.setFloor(4); updateClass.setTeacherId(2); //UPDATE class SET name=?, floor=?, teacher_id=? WHERE id=?; int updateByIdResult = classMapperSimple.updateById(updateClass); System.out.println(updateByIdResult); } //select /** * 根据Id查询一条记录 */ @Test void simpleQueryOneById(){ //SELECT id,name,floor,teacher_id FROM class WHERE id=?; Class queryOneClass = classMapperSimple.selectById(1); System.out.println(queryOneClass); } /** * 查询所有记录 */ @Test void simpleQueryAll() { //SELECT id,name,floor,teacher_id FROM class; List queryClasses = classMapperSimple.selectList(null); System.out.println(queryClasses); } /** *| 函数名 | 说明 | *| ----------- | ----------- | *| eq | 等于 | *| ne | 不等 | *| gt | 大于 | *| it | 小于 | *| between | 在值1到值2之间 | *| like | 模糊查询 | *| isNull | 字段为NULL | */ @Autowired private ClassMapper classMapperComplex; //进阶操作 //单表操作 //select /** * 根据Id查询条数据 */ @Test void complexQueryOne(){ LambdaQueryWrapper queryWrapper = Wrappers.lambdaQuery(); queryWrapper.eq(Class::getId, 1); //SELECT id,name,floor,teacher_id FROM class WHERE (id = ?); Class complexQueryOne = classMapperComplex.selectOne(queryWrapper); System.out.println(complexQueryOne); } /** * 查询相同条件的数据 */ @Test void complexQueryAll(){ LambdaQueryWrapper queryWrapper = Wrappers.lambdaQuery(); queryWrapper.eq(Class::getFloor,1); //SELECT id,name,floor,teacher_id FROM class WHERE (floor = ?); List complexQueryAll = classMapperComplex.selectList(queryWrapper); System.out.println(complexQueryAll); } /** * 查询范围内的数据 */ @Test void complexBetweenQuery(){ LambdaQueryWrapper queryWrapper = Wrappers.lambdaQuery(); queryWrapper.between(Class::getFloor,1,3); //SELECT id,name,floor,teacher_id FROM class WHERE (floor BETWEEN ? AND ?); List complexQueryAll = classMapperComplex.selectList(queryWrapper); System.out.println(complexQueryAll); } /** * 模糊查询 * like():前后加百分号,如 %1年级% * likeLeft():前面加百分号,如 %1年级 * likeRight():后面加百分号,如 1年级% */ @Test void complexLikeQuery(){ LambdaQueryWrapper queryWrapper = Wrappers.lambdaQuery(); queryWrapper.likeRight(Class::getName,"1年级"); // SELECT id,name,floor,teacher_id FROM class WHERE (name LIKE ?); List complexLikeQuery = classMapperComplex.selectList(queryWrapper); System.out.println(complexLikeQuery); } /** * 按照楼层升序排列,从小到大 */ @Test void complexOrderQuery(){ LambdaQueryWrapper queryWrapper = Wrappers.lambdaQuery(); //condition ：条件，返回boolean，当condition为true，进行排序，如果为false，则不排序 //isAsc：是否为升序，true为升序，false为降序 //columns：需要操作的列 queryWrapper.orderBy(true,true,Class::getFloor); //SELECT id,teacher_id,name,floor FROM class ORDER BY floor ASC; List complexOrderByAscQuery= classMapperComplex.selectList(queryWrapper); System.out.println(complexOrderByAscQuery); } /** * 分页查询,查询第1页,3条数据 */ @Test void complexPageQuery(){ Page page = new Page(1,3); classMapperComplex.selectPage(page, null); System.out.println(page.getRecords());//每页数据list集合 System.out.println(page.getCurrent());//当前页 System.out.println(page.getSize());//每页显示记录数 System.out.println(page.getTotal());//总记录数 System.out.println(page.getPages());//总页数 System.out.println(page.hasPrevious());//上一页 System.out.println(page.hasNext());//下一页 }}多表操作MultipleTableOperationTest
package cn.highedu.boot.service;import cn.highedu.boot.pojo.dto.TeacherDTO;import cn.highedu.boot.pojo.entity.Class;import cn.highedu.boot.pojo.entity.Teacher;import cn.highedu.boot.mapper.TeacherMapper;import cn.highedu.boot.service.impl.TeacherServiceImpl;import com.baomidou.mybatisplus.core.metadata.IPage;import com.baomidou.mybatisplus.extension.plugins.pagination.Page;import com.github.yulichang.wrapper.MPJLambdaWrapper;import org.junit.jupiter.api.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import java.util.List;@SpringBootTestpublic class MultipleTableOperationTest extends TeacherServiceImpl { @Autowired private TeacherMapper teacherMapper; /** *| 函数名 | 说明 | *| ----------- | ----------- | *| eq | 等于 | *| ne | 不等 | *| gt | 大于 | *| it | 小于 | *| between | 在值1到值2之间 | *| like | 模糊查询 | *| isNull | 字段为NULL | */ /** * 查询楼层大于2的所有老师及各个老师所在的班级信息,并按楼层的升序排列 */ @Test void selectTeachersWhichOverFloor(){ MPJLambdaWrapper wrapper = new MPJLambdaWrapper(); wrapper.selectAll(Teacher.class)//查询Teacher表全部字段 .select(Class::getFloor, Class::getName) .rightJoin(Class.class, Class::getTeacherId, Teacher::getId) .gt(Class::getFloor,2) .orderBy(true,true,Class::getFloor); /* SELECT * t.id,t.name,t.age,t.title,t.manager,t.salary,t.comm,t.gender, * t.subject_id,t1.floor,t1.name * FROM * teacher t * RIGHT JOIN * class t1 * ON * (t1.teacher_id = t.id); * WHERE * (t1.floor > ?) * ORDER BY * t1.floor ASC */ List teacherList = teacherMapper.selectJoinList(TeacherDTO.class, wrapper); System.out.println(teacherList); } /** * 在上面的基础上,增添了分页的功能,查询第2页,每页5条数据 */ @Test void selectTeachersWhichOverFloorPage(){ MPJLambdaWrapper wrapper = new MPJLambdaWrapper(); wrapper.selectAll(Teacher.class)//查询Teacher表全部字段 .select(Class::getFloor, Class::getName) .rightJoin(Class.class, Class::getTeacherId, Teacher::getId) .gt(Class::getFloor,1) .orderBy(true,true,Class::getFloor); /* SELECT * t.id,t.name,t.age,t.title,t.manager,t.salary,t.comm,t.gender, * t.subject_id,t1.floor,t1.name * FROM * teacher t * RIGHT JOIN * class t1 * ON * (t1.teacher_id = t.id); * WHERE * (t1.floor > ?) * ORDER BY * t1.floor ASC * LIMIT * ?,? */ IPage teacherList = teacherMapper.selectJoinPage(new Page(2, 5), TeacherDTO.class,wrapper); System.out.println(teacherList); }}参考—- MyBatis-Plus-Join官网—-MyBatis-Plus官网