526互联

最近正在集成SpringBoot与MyBatis-plus,体验感很好啊

发布时间 2023-09-28 11:40:52作者: 随枫而动

image

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='班级信息表';
pom.xml 
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>
		<dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>
		<dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3</version>
        </dependency>
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.StdOutImpl
Mybatis-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;


@Configuration
public 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
@NoArgsConstructor
public 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 org.apache.ibatis.annotations.*;

@Mapper
public interface ClassMapper extends BaseMapper<Class> {
}
ClassService 
package cn.highedu.boot.service;

import cn.highedu.boot.entity.Class;
import com.baomidou.mybatisplus.extension.service.IService;

public interface ClassService extends IService<Class> {
}
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;

@Service
public class ClassServiceImpl extends ServiceImpl<ClassMapper, Class> implements ClassService {
}
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;

@SpringBootTest
class 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<Class> 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<Class> 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<Class> queryWrapper = Wrappers.lambdaQuery();
        queryWrapper.eq(Class::getFloor,1);
        //SELECT id,name,floor,teacher_id FROM class WHERE (floor = ?);
        List<Class> complexQueryAll = classMapperComplex.selectList(queryWrapper);
        System.out.println(complexQueryAll);
    }

    /**
     * 查询范围内的数据
     */
    @Test
    void complexBetweenQuery(){
        LambdaQueryWrapper<Class> queryWrapper = Wrappers.lambdaQuery();
        queryWrapper.between(Class::getFloor,1,3);
        //SELECT id,name,floor,teacher_id FROM class WHERE (floor BETWEEN ? AND ?);
        List<Class> complexQueryAll = classMapperComplex.selectList(queryWrapper);
        System.out.println(complexQueryAll);
    }

    /**
     * 模糊查询
     * like():前后加百分号,如 %1年级%
     * likeLeft():前面加百分号,如 %1年级
     * likeRight():后面加百分号,如 1年级%
     */
    @Test
    void complexLikeQuery(){
        LambdaQueryWrapper<Class> queryWrapper = Wrappers.lambdaQuery();
        queryWrapper.likeRight(Class::getName,"1年级");
        // SELECT id,name,floor,teacher_id FROM class WHERE (name LIKE ?);
        List<Class> complexLikeQuery = classMapperComplex.selectList(queryWrapper);
        System.out.println(complexLikeQuery);
    }

    /**
     * 按照楼层升序排列,从小到大
     */
    @Test
    void complexOrderQuery(){
        LambdaQueryWrapper<Class> queryWrapper = Wrappers.lambdaQuery();
        //condition ：条件，返回boolean，当condition为true，进行排序，如果为false，则不排序
        //isAsc：是否为升序，true为升序，false为降序
        //columns：需要操作的列
        queryWrapper.orderBy(true,true,Class::getFloor);
        List<Class> complexOrderByAscQuery= classMapperComplex.selectList(queryWrapper);
        System.out.println(complexOrderByAscQuery);
    }
    /**
     * 分页查询
     */
    @Test
    void complexPageQuery(){
        Page<Class> 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());//下一页
    }
}

多表操作

teacher 
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='老师信息表';