建库建表===>引入依赖===>配置===>编码===>测试

一、使用MyBatis-plus提供的SQL

1. 创建数据库数据表

#创建数据库
create table user (  id BIGINT(20) PRIMARY key not null comment '主键', 
name varchar(30) default null comment '姓名',
age int(11) default null comment '年龄', 
email varchar(50) default null comment '邮箱',   
manager_id BIGINT(20) default null comment '直属上级id',   
create_time DATETIME default null comment '创建时间',  
CONSTRAINT manager_fk foreign key (manager_id)          
REFERENCES user (id)) ENGINE=INNODB CHARSET=UTF8;#数据初始化
INSERT INTO user (id,name,age,email,manager_id,create_time)
VALUES (1087982257332887553, '猪头', 20, 'boss@baomidou.com', NULL, 
'2019-01-11 14:20:20'),            
(1088248166370832385,'小懒猪',20,'wtf@baomidou.com', 1087982257332887553,
'2019-02-05 11:12:22'),            
(1088250446457389058,'小白',18,'lyw@baomidou.com', 1088248166370832385,
'2019-02-14 08:31:16'),            
(1094590409767661570,'小黑',21,'zyq@baomidou.com', 1088248166370832385,
'2019-01-14 09:15:15'),            
(1094592041087729666,'小可耐',22,'lhm@baomidou.com', 1088248166370832385,
'2019-01-14 09:48:16');

2. 导入依赖

<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.1.4</version></dependency><!-- Lombok --><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><!-- Mybatis-Plus启动器 --><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.1.0</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><scope>test</scope></dependency>
</dependencies>

3. 配置application.yml

spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/mp?useSSL=false&serverTimezone=GMT%2B8username: rootpassword: 1214

4. 编写实体类

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("user")
public class User {// 主键private Long id;// 姓名@TableField(value = "name", condition = SqlCondition.LIKE)private String name;// 年龄@TableField(condition = "%s<#{%s}")private Integer age;// 邮箱private String email;// 直属上级idprivate Long managerId;// 创建时间private Date createTime;// 备注（不与数据库字段对应） # transient 不参与序列化@TableField(exist = false)private String remark;
}

5. 编写dao接口：UserMapper.java

public interface UserMapper extends BaseMapper<User> {
}

6. 编写启动类

@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest {@Autowiredprivate UserMapper userMapper;@Testpublic void select() {List<User> list = userMapper.selectList(null);Assert.assertEquals(5, list.size());list.forEach(System.out::println);}
}

7. 运行结果

二、使用自定义的SQL

第一种自定义SQL方式

自定义SQL

@Component
public interface UserMapper extends BaseMapper<User> {// ${ew.customSqlsegment}可以使条件构造器构造的sql被执行。@Select("select * from user ${ew.customSqlSegment}")List<User> selectBySql(@Param(Constants.WRAPPER) Wrapper<User> userWrapper);
}

编写启动类

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperSqlTest {@Autowiredprivate UserMapper userMapper;@Testpublic void selectBySql(){LambdaQueryWrapper<User> userLambdaQueryWrapper = new LambdaQueryWrapper<>();// 在这里对select查询语句做条件查询，后面给出这些方法的作用userLambdaQueryWrapper.like(User::getUsername , "k").lt(User::getAge , 40).last("limit 4");List<User> userList = userMapper.selectBySql(userLambdaQueryWrapper);userList.forEach(System.out::println);}
}

第二种自定义SQL方式

把UserMapper接口中@Select注解那一行删掉。

@Component
public interface UserMapper extends BaseMapper<User> {List<User> selectBySql(@Param(Constants.WRAPPER) Wrapper<User> userWrapper);
}

在resources/mappers/UserMapper.xml下编写SQL语句

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.kaven.mybatisplus.dao.UserMapper"><select id="selectBySql" resultType="com.kaven.mybatisplus.entity.User">select * from user ${ew.customSqlSegment}</select>
</mapper>

记得在核心配置文件中添加这些配置信息，缺啥补啥

spring:application:name: mybatis-plusdatasource:driver-class-name: com.mysql.jdbc.Driverusername: rootpassword: ITkaven@123url: jdbc:mysql://47.112.7.219:3306/test?characterEncoding=utf-8&useSSL=falseserver:port: 8085logging:level:root: warncom.kaven.mybatisplus.dao: tracepattern:console: '%p%m%n'mybatis-plus:mapper-locations: classpath:mappers/*.xml

编写启动类

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperSqlTest {@Autowiredprivate UserMapper userMapper;@Testpublic void selectBySql(){LambdaQueryWrapper<User> userLambdaQueryWrapper = new LambdaQueryWrapper<>();// 在这里对select查询语句做条件查询，后面给出这些方法的作用userLambdaQueryWrapper.like(User::getUsername , "k").lt(User::getAge , 40).last("limit 4");List<User> userList = userMapper.selectBySql(userLambdaQueryWrapper);userList.forEach(System.out::println);}
}