springboot引入mybatis-plus

1、pom依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.2</version>
</dependency>

2、配置文件

mybatis:
  # 在mapper.xml中可以使用别名而不使用类的全路径名
  type-aliases-package: com.pingan.domain
  base-packages: com.pingan.domain
  # 当mybatis的xml文件和mapper接口不在相同包下时,用mapperLocations属性指定xml文件的路径。
  mapper-locations: classpath*:mapper/*.xml
  # 驼峰转换
  configuration:
    map-underscore-to-camel-case: true
    
spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatis_demo?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF8
    password: root
    username: root

3、配置类-默认分页 config

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

//Spring boot方式
@Configuration
public class MybatisPlusConfig {

//    分页插件配置
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
         paginationInterceptor.setOverflow(true);
        // 设置最大单页限制数量，默认 500 条，-1 不受限制
        // paginationInterceptor.setLimit(500);
        // 开启 count 的 join 优化,只针对部分 left join
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }
}

3、主键

在实体类上加上 @TableName("表名")，在实体类id上加上@TableId(type = IdType.AUTO)

@TableName("sys_users")
public class SysUsers implements Serializable {

    @TableId(type = IdType.AUTO)
    private Integer id;
    private String username;
    private String password;
    
    @TableField(value = "order_id",exist = true) // 映射字段，exist 代表数据是否存在
    private Integer dddd;
    get、set...
}

主键ID   解释

AUTO(0), // 数据库id自增

NONE(1), // 未设置主键

INPUT(2), // 手动输入

ID_WORKER(3), // 默认的全局唯一id

UUID(4), // 全局唯一id uuid

ID_WORKER_STR(5); //ID_WORKER 字符串表示法

// 19位有序数字,雪花算法实现
@TableId(type = IdType.ASSIGN_ID)
private String id;        // 可以是String 类型, 可以为Long类型
// 注: mysql数据库须为 vachar 或 Bigint 

//序列
//作用在类上, value为数据库中生成的序列名，class指主键属性类型
@KeySequence(value="seq_user序列名",clazz=Integer.class) 
@TableId(type=IdType.INPUT)     //注意主键类型要指定为Input
private Long id;

// uuid
@TableId(type = IdType.ASSIGN_UUID)     
private String id ;    // 字符串类型 32字节

// 自增
@TableId(type = IdType.AUTO)     //只适合mysql自增
private Integer id;

 

4、sevice层继承IService<xxx>

import com.baomidou.mybatisplus.extension.service.IService;
import com.pingan.domain.entity.SysPermissions;
import com.pingan.domain.entity.SysRoles;
import com.pingan.domain.entity.SysUsers;

import java.util.List;

public interface SysUsersService extends IService<SysUsers> {

    SysUsers selectByUserName(String username);
    List<SysRoles> selectRolesByUsername(String username);
}

5、mapper继承BaseMapper<xxxx>

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.pingan.domain.entity.SysUsers;

public interface SysUsersMapper extends BaseMapper<SysUsers> {
}

6、在main启动类上加上dao接口的包路径 @MapperScan(basePackages = {"com.subject.dao"})

import org.mybatis.spring.annotation.MapperScan;

@MapperScan(basePackages = {"com.subject.dao"})

 

6、查询

单表分页：

// 根据条件查询并分页(单表)
public Page<Parent> selectAllByPageByConditions(String work, String orderBy, Integer pageNum, Integer pageSize) {

    QueryWrapper<Parent> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("work", work);
    queryWrapper.orderByAsc(orderBy);
    Page<Parent> parentPage = parentMapper.selectPage(new Page<>(pageNum, pageSize), queryWrapper);
    return parentPage;
}

 

多表分页

// 多表自定义sql查询并分页
public IPage<StudentParentVo> selectStudentAndParentByPage(Integer pageNum, Integer pageSize, String sex) {
    Page<Object> objectPage = new Page<>(pageNum, pageSize);
    IPage<StudentParentVo> page = studentAndParentMapper.selectStudentAndParentByPage(objectPage,sex);
    return page;
}

mapper接口及xml文件

// mapper接口
public interface StudentAndParentMapper extends BaseMapper<StudentParentVo> {
    IPage<StudentParentVo> selectStudentAndParentByPage(Page<Object> objectPage, String sex);
}


// xml文件
<select id="selectStudentAndParentByPage" resultType="com.pingan.domain.vo.StudentParentVo">
    SELECT
    s.id student_id,s.name student_name,s.age,s.sex,s.describe_text,s.favorite,
    p.id parent_id,p.name parent_name,p.work,p.relation,p.child
    FROM student s
    LEFT JOIN parent p on s.id = p.child
    <where>
        <if test="sex !=null and sex !=''">
            s.sex = #{sex}
        </if>
    </where>
</select>