MyBatis-plus使用版，直接上简单示例

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

一、使用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.Driver
    url: jdbc:mysql://localhost:3306/mp?useSSL=false&serverTimezone=GMT%2B8
    username: root
    password: 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;

	// 直属上级id
    private 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 {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void select() {
        List<User> list = userMapper.selectList(null);
        Assert.assertEquals(5, list.size());
        list.forEach(System.out::println);
    }
}

7. 运行结果

二、使用自定义的SQL

方式一：wapper自定义SQL

wapper的链式方法在下面方式二有总结

@Test
void contextLoads(){
    // 查询name不为null的用户，并且邮箱不为null的永不，年龄大于等于20的用户
    QueryWrapper<User> wrapper =new QueryWrapper<>();
    wrapper.isNotNull("name");
    wrapper.isNotNull("email");
    wrapper.ge("age",12);
    userMapper.selectList(wrapper).forEach(System.out::println);
}

@Test
void test2(){
    // 查询name为shuishui的用户
    QueryWrapper<User> wrapper =new QueryWrapper<>();
    wrapper.eq("name","shuishui");
    User user=userMapper.selectList(wrapper)
    System.out.println（user）;
}

@Test
void test3(){
    // 查询年龄在20~30岁之间的用户
    QueryWrapper<User> wrapper =new QueryWrapper<>();
    wrapper.between("age",20,30);
    Integer count =userMapper.selectCount(wrapper);//查询结果数
    System.out.println（count）;
}

//模糊查询
@Test
void test4(){
    QueryWrapper<User> wrapper =new QueryWrapper<>();
    
    wrapper.notLike("name",“s”);//相当于NOT LIKE '%s%'
    wrapper.likeRight("email",“s”);//相当于LIKE 's%'
    List<Map<String,Object>>maps =userMapper.selectMaps(wrapper);//查询结果数
    maps.forEach(System.out::println);
}


@Test
void test5(){
    QueryWrapper<User> wrapper =new QueryWrapper<>();
    //子查询
    wrapper.insql("id","select id from user where id<3");
    List<Object> objects =userMapper.selectobjs(wrapper);
    objects.forEach(System.out::println);
}
    
@Test
void test6(){
    QueryWrapper<User> wrapper =new QueryWrapper<>();
    //通过id进行排序
    wrapper.orderByAsc("id");
    List<User> users =userMapper.selectList(wrapper);
    objects.forEach(System.out::println);
}

//姓王年龄大于等于25，按年龄降序，年龄相同按id升序排列
void test7(){
    QueryWrapper<User> wrapper =new QueryWrapper<>();
   wrapper.likeRoght("name","王").or().ge("age",25).ordeiByDesc("age").orderByAsc("id");
    List<User> users =userMapper.selectList(wrapper);
    objects.forEach(System.out::println);
}

//创建日期为2019年2月14日并且直属上级为姓王
void test8(){
    QueryWrapper<User> wrapper =new QueryWrapper<>();
    wrapper.apply("date_fromat(create_time,'%Y-%m-%d')='2019-02-14'").inSql("manager_id","select id from user where name like '王%'");
    List<User> users =userMapper.selectList(wrapper);
    objects.forEach(System.out::println);
}

//姓王并且（年龄小于40或者邮箱不为空）
void test9(){
    QueryWrapper<User> wrapper =new QueryWrapper<>();
    //lt小于，gt大于
    wrapper.likeRoght("name","王").and(wq->wa.lt("age",40).or().isNotNull("email"))
    List<User> users =userMapper.selectList(wrapper);
    objects.forEach(System.out::println);
}

//不列出所有字段
@Test
void test10(){
    QueryWrapper<User> wrapper =new QueryWrapper<>();
    
    wrapper.select("id","name").like("name","雨").lt("age",40);
    //不显示时间和id
    //wrapper.select(User.class,info->!info.getColumn().equals("create_time")&&!info.getColumn().equals("manager_id")).like("name","雨").lt("age",40);
    List<User> users =userMapper.selectList(wrapper);
    objects.forEach(System.out::println);
}

方式二：BaseMapper自定义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 {
    @Autowired
    private UserMapper userMapper;

    @Test
    public 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);
    }
}

方式三：XML自定义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-plus
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    username: root
    password: ITkaven@123
    url: jdbc:mysql://47.112.7.219:3306/test?characterEncoding=utf-8&useSSL=false

server:
  port: 8085

logging:
  level:
    root: warn
    com.kaven.mybatisplus.dao: trace
  pattern:
    console: '%p%m%n'

mybatis-plus:
  mapper-locations: classpath:mappers/*.xml

编写启动类

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperSqlTest {
    @Autowired
    private UserMapper userMapper;

    @Test
    public 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);
    }
}

三、Mybatis-plus常用的方法

参考实体

@Data
public class User {
	@TableId(type =IdType.AUTO)
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

1. 插入操作

@Test
    public void testInsert() {
        System.out.println(("----- selectAll method test ------"));
        User user = new User();
        user.setName=("shuishui");
        user.setAge(3);
        user.setEmail("12434141@qq.com");
        
        userMapper.insert(user);
    }

2. 更新操作

@Test
    public void testUpdate() {
        //sql自动动态配置
        User user = new User();
        user.setName=("shui");
        user.setId(3L);
        user.setAge("18");
        
        //注意：updateById的参数是一个对象
        userMapper.updateById(user);
    }

3. 查询操作

// 根据 ID 查询
T selectById(Serializable id);
// 根据 entity 条件，查询一条记录
T selectOne(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

// 查询（根据ID 批量查询）
List<T> selectBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);
// 根据 entity 条件，查询全部记录
List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
// 查询（根据 columnMap 条件）
List<T> selectByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);
// 根据 Wrapper 条件，查询全部记录
List<Map<String, Object>> selectMaps(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
// 根据 Wrapper 条件，查询全部记录。注意： 只返回第一个字段的值
List<Object> selectObjs(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

// 根据 entity 条件，查询全部记录（并翻页）
IPage<T> selectPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
// 根据 Wrapper 条件，查询全部记录（并翻页）
IPage<Map<String, Object>> selectMapsPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
// 根据 Wrapper 条件，查询总记录数
Integer selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

4. 删除操作

// 根据 entity 条件，删除记录
int delete(@Param(Constants.WRAPPER) Wrapper<T> wrapper);
// 删除（根据ID 批量删除）
int deleteBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);
// 根据 ID 删除
int deleteById(Serializable id);
// 根据 columnMap 条件，删除记录
int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);

四、分页查询（直接上案例）

1. 简单使用

Controller

@Slf4j
@RestController
@RequestMapping("/inter")
public class InterController {
	@Autowired
	private InterService interService;
 
	@GetMapping("/test")
	public IPage<Map> test() {
		Integer currentPage = 1; //当前页数：显示第一页数据
		Integer pageSize = 2;    //每页显示多少：每页显示2条数据
		Page<Map> page = new Page<Map>(currentPage, pageSize);
		IPage<Map> findList = interService.findlistpage(page);
		return findList;
	}
}

Service

@Slf4j
@Service
public class InterService {
	@Autowired
	private InterDao interDao;
 
	public IPage<Map> findlistpage(Page<Map> page) {
		return interDao.selectMapsPage(page, null);
	}
}

Dao

public interface InterDao extends BaseMapper<Permission> {
	IPage<Map> selectMapsPage(Page<Map> page, QueryWrapper<List<Map<String, Object>>> wrapper);
}

XML

<?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.xkcoding.rbac.security.dao.InterDao">
 
	<select id="selectMapsPage" resultType="java.util.Map" parameterType="java.util.Map">
		SELECT * FROM INTER_COLLECTION
	</select>
	
</mapper>

实体类

@Data
public class Inter {
    private String id;
 
    private String interName;
    
    private String mode;
 
    private String selectStatement;
 
    private String insertStatement;
    
    private String updateStatement;
 
    private String interEnable;
 
    private String interParam;
 
    private String interType;
    
    private String tableName;
}

运行结果

2. Page存放数据

五、注解总结

1. @TableName、@TableId、@TableField

@TableName("code_bas_district")
public class CodeBasDistrict {

    @TableId
    private String distCode;

    @TableField(exist = false)
    private List<CodeBasDistrict> children;

}

2. @Param

mapper中的方法：

public User selectUser(@Param("userName") String name,@Param("password") String pwd);

映射到xml中的标签

<select id="selectUser" resultMap="User">  
   select * from user  where user_name = #{userName} and user_password=#{password}  
</select>