title: Mybatis-Plus
tag: 为简化开发而生
categories: 分类
comment: 是否允许评论(true or false)
description: 描述
top_img: https://z3.ax1x.com/2021/10/06/4xq2s1.png
cover: https://z3.ax1x.com/2021/10/06/4xq2s1.png
简介
Mybatis-Plus是一个Mybatis-Plus的增强工具,在Mybatis的基础上只做增强不做改变,为简化开发、提高效率而生。
特性
- 无侵入:只做增强不做改变,引入它不会对现有工程产生影响
- 损耗小:启动即会自动注入基本CRUD,性能基本无损耗,直接面向对象操作
- 强大的CRUD操作:内置通用Mapper,通用Service,仅仅通过少量配置即可实现单表大部分CRUD操作,更有强大的条件构造器,满足各类使用需求
- 支持Lambda形式调用:通过Lambda表达式,方便的编写各类查询条件,无需担心字段写错
- 支持主键自动生成:支持多达4种主键策略,可自由配置,完美解决主键问题
- 支持ActiveRecord模式:支持ActiveRecord形式调用,实体类只需继承Model类即可进行强大的CRUD操作
- 支持自定义全局通用操作:支持全局通用方法注入
- 内置代码生成器:采用代码或者Maven插件可快速生成Mappe、Model、Service、Condtroller层代码,支持模板引擎,更有超多自定义配置等您来使用
- 内置分页插件:基于Mybatis物理分页,开发者无需关系具体操作,配置好插件之后,写分页等同于普通List查询
- 分页插件支持多种数据库:支持MySQL、Oracle、DB2、H2、SQLite、Postgre、SQLServer等多种数据库
- 内置性能分析插件:可输出SQL语句以及执行时间,建议开发测试时启动改功能,能快速找出慢查询
框架结构
快速开始
使用springBoot创建工程
配置对应的xml文件
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
</dependencies>
在yml中配置对应的连接数据
Spring:
datasource:
username: root
password: root
url: jdbc:mysql://192.168.56.10:3306/user?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
driver-class-name: com.mysql.cj.jdbc.Driver
创建实体类user,实体类的字段要和数据库的字段相对应
package com.qian.mybatisplus.entity;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
/**
* 文件描述
*
* @Author: QJS
* @CreateDate: 2022/1/28 16:23
**/
@Data
@TableName("user")
public class User {
@TableId
private int id;
@TableField("name")
private String name;
private Integer age;
private String email;
}
创建Dao接口,注意需要继承BaseMapper<实体.class>
package com.qian.mybatisplus.dao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.qian.mybatisplus.entity.User;
/**
* 文件描述
*
* @Author: QJS
* @CreateDate: 2022/1/28 16:28
* 1.要实现BaseMapper
* 2.指定实体类
*
* BaseMapper时MP框架中的对象,定义17个操作方法
**/
public interface UserMapper extends BaseMapper<User> {
}
在SpringBoot的启动类上,加入@MapperScan(value=“指定接口的包名”)
package com.qian.mybatisplus;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* @MapperScan:扫描器,指定Mapper类所在的包
*/
@SpringBootApplication
@MapperScan(value = "com.qian.mybatisplus.dao")
public class MybatisplusApplication {
public static void main(String[] args) {
SpringApplication.run(MybatisplusApplication.class, args);
}
}
在test类中测试使用
@Test
public void testUserInsert(){
// 创建user对象
User user = new User();
user.setId(6);
user.setName("QJS");
user.setAge(20);
user.setEmail("QJS@163.com");
// 调用userMapper的方法,父接口BaseMapper中提供的方法
int rows = userMapper.insert(user);
System.out.println("insert 的结果:"+rows);
}
配置日志
yml中添加
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
重新运行会看到有日志中的sql语句
CRUD操作
insert操作
获取主键Id
// 添加数据后获取主键值
@Test
public void testInsertGetId(){
User user = new User();
user.setName("QJS1");
user.setAge(21);
user.setEmail("QJS1@163.com");
int rows = userMapper.insert(user);
System.out.println("insert user rows:"+rows);
// 获取主键id,添加数据库中的数据id
int id = user.getId();//主键字段对应的id方法
System.out.println("主键的id"+id);
}
输出
insert user rows:1
主键的id-439238654
更新数据
/**
* 更新操作update
*/
@Test
public void testUpdateUser(){
User user = new User();
user.setName("修改数据");
user.setAge(22);
user.setEmail("edt@163.com");
user.setId(3);
// 执行更新操作
// Preparing: UPDATE user SET name=?, age=?, email=? WHERE id=?
// 更新了所有非null属性值,条件where id = 主键值
int rows = userMapper.updateById(user);
System.out.println("update rows:"+rows);
}
注意:实体类属性,推荐使用包装类型,可以判断是否为null.
删除数据
根据主键进行删除:
/**
* 按主键删除一条数据
* 方法是deleteById()
* 参数:主键值
* 返回值:是删除的成功记录数
*/
@Test
public void testDeleteById(){
// Preparing: DELETE FROM user WHERE id=?
int rows = userMapper.deleteById(3);
System.out.println("deleteById:"+rows);
}
根据Map进行删除
/**
* 按条件删除数据,条件是封装到Map对象中
* 方法:deleteByMap(map对象)
* 返回值:删除成功的记录数
*/
@Test
public void testDeleteByMap(){
// 创建Map对象,保存条件值
HashMap<String, Object> map = new HashMap<>();
// put("表的字段名"+条件值)
map.put("name","QJS");
map.put("age",20);
// 调用删除方法
// DELETE FROM user WHERE name = ? AND age = ?
int rows = userMapper.deleteByMap(map);
System.out.println("deleteByMap rows:"+rows);
}
进行id的批量删除
/**
* 批量删除:使用多个主键值,删除数据
* 方法名称:deleteBatchIds()
* 参数:@Param("coll") Collection<?> idList
*/
@Test
public void deleteByBatchId(){
// List<Integer> ids = new ArrayList<>();
// ids.add(1);
// ids.add(2);
// ids.add(4);
List<Integer> ids = Stream.of(1, 2, 3).collect(Collectors.toList());
// DELETE FROM user WHERE id IN ( ? , ? , ? )
int i = userMapper.deleteBatchIds(ids);
System.out.println("deleteBatchIds:"+i);
}
查询操作(Select)
根据主键值查询
/**
* 实现查询selectById,根据主键值查询
* 参数:主键值
* 返回值: 实体对象
*/
@Test
public void testSelectById(){
// SELECT id,name,age,email FROM user WHERE id=?
User user = userMapper.selectById(5);
System.out.println("selectById:"+user);
// 使用对象之前,需要判断对象是否为空
if (user != null){
}
}
根据多个主键值进行查询
/**
* 实现批处理查询,根据多个主键值查询,获取到List
* 方法:selectBatchIds
* 参数: id的集合
* 返回值是一个list
*/
@Test
public void testSelectBatchid(){
List<Integer> ids = new ArrayList<>();
ids.add(6);
ids.add(7);
// SELECT id,name,age,email FROM user WHERE id IN ( ? , ? )
List<User> users = userMapper.selectBatchIds(ids);
System.out.println("size:"+users.size());
}
使用Map做条件查询
/**
* 使用Map做多条件查询
* 方法: selectMap()
* 参数: Map<String,Object>
* 返回值:List<T>
*/
@Test
public void testSelectMap(){
// 创建Map,封装查询条件
Map<String, Object> map = new HashMap<>();
// key是字段名,value:字段值 ,多个key ,是and连接
map.put("name","QJS");
map.put("age",24);
// 根据Map查询
// SELECT id,name,age,email FROM user WHERE name = ? AND age = ?
List<User> users = userMapper.selectByMap(map);
users.forEach(user -> {
System.out.println("selectNyMap:"+user);
});
}
ActiveRecord(AR)
简介
- ActiveRecord负责把自己持久化,在ActiveRecord中封装了对数据库的访问,通过对象自己实现CRUD,实现更好的操作数据库。
- ActiveRecord也封装了部分业务逻辑。可以作为业务对象使用。
相对于之前的Mapper,主要的区别在于:
- 之前需要注入mapper以及使用mapper中继承的类
- 现在不需要注入mapper,而是通过实体类继承extends Model,之后调用Model类中的方法进行crud
进行数据库的设置
创建表dept
CREATE TABLE `dept` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`mobile` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
`manager` int(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
创建对应的实体类
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* 使用AR,要求实体类继承MP中的Model
* Model中提供了对数据库的CRUD操作
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Dept extends Model {
// 定义属性,属性名和表的列名一样
@TableId(value = "id",type = IdType.AUTO)
private Integer id;
private String name;
private String mobile;
private Integer manager;
}
书写对应的Mapper文件
DeptMapper是不需要的,MP需要使用DeptMapper获取到数据库的表的信息,所以还是需要书写进行扫描
/**
* DeptMapper是不需要使用的,MP需要使用DeptMapper获取到数据库的表的信息
* 如果不定义DeptMapper,MP会报错,找不到表的定义信息
*/
public interface DeptMapper extends BaseMapper<Dept> {
}
插入操作
调用的是model中的insert方法
@Test
public void testARInsert() {
// 定义Dept的实体
Dept dept = new Dept();
dept.setName("研发部");
dept.setMobile("0101234567");
dept.setManager(1);
// 调用实体对象自己的方法,完成对象自身到数据库的添加操作
boolean flag = dept.insert();
System.out.println("ar insert result:"+flag);
}
更新操作
@Test
public void testARUpdate(){
// 定义实体Dept
Dept dept = new Dept();
dept.setId(1);
dept.setMobile("020123456");
dept.setName("测试部");
dept.setManager(2);
// 根据主键id更新记录
// Preparing: UPDATE dept SET name=?, mobile=?, manager=? WHERE id=?
//==> Parameters: 测试部(String), 020123456(String), 2(Integer), 1(Integer)
boolean result = dept.updateById();// 使用实体dept实体主键的值,作为where id = 1;
System.out.println("ar updateById result:"+result);
}
@Test
public void testARUpdate2(){
// 定义实体Dept
Dept dept = new Dept();
dept.setId(1);
dept.setMobile("020122222");
// 根据主键id更新记录
// Preparing: UPDATE dept SET name=?, mobile=?, manager=? WHERE id=?
//==> Parameters: 测试部(String), 020123456(String), 2(Integer), 1(Integer)
// null的属性值不做更新处理,在update中没有null的字段
boolean result = dept.updateById();// 使用实体dept实体主键的值,作为where id = 1;
System.out.println("ar updateById result:"+result);
}
删除操作
@Test
public void testARDeleteById(){
Dept dept = new Dept();
boolean result = dept.deleteById(1);
System.out.println("ar deleteById result:"+result);
}
查询操作
- 按实体得主键能查找出数据,返回对象
- 按实体的主键不能查出数据,是null,不报错
- 没有记录或者没有数据也是返回null
@Test
public void testARSelectById(){
Dept dept = new Dept();
//设置主键的值
// dept.setId(1);
//调用查询方法
//SELECT id,name,mobile,manager FROM dept WHERE id=?
Dept dept1 = dept.selectById();
System.out.println("ar selectById result:"+dept1);
}
@Test
public void testARSelectById2(){
Dept dept = new Dept();
Dept dept1 = dept.selectById(3);
System.out.println("dept1:"+dept1);
}
列和表
当实体类和数据库表不一致的情况下
分别为这三个属性
主键,TableName,TableId
主键的类型
idType的主键类型
public enum IdType {
AUTO(0),
NONE(1),
INPUT(2),
ASSIGN_ID(3),
ASSIGN_UUID(4);
private final int key;
private IdType(int key) {
this.key = key;
}
public int getKey() {
return this.key;
}
}
- 0.none没有主键
- 1.auto自动增长
- 2.input手工输入
指定表名
定义实体类,默认的表名和实体类同名
如果不一直,在实体类定义上面使用
@TableName(value="数据库表名")
主要区别在于
定义实体类的时候使用的类名
如果不符合数据库表名要加上这个注解
数据库表名为user_address
@TableName(value = "user_address")
public class Address {
}
指定列名
主要通过这个注解
@TableField(value="数据库属性名")
@TableField(value = "user_city")
private String city;
@TableField(value = "user_street")
private String street;
使用驼峰命名方式
- 列名使用custName
- 数据库使用cust_name
@Data
@ToString
@TableName(value = "customer")
public class Customer {
// 定义属性
@TableId(value = "id",type = IdType.AUTO)
private Integer id;
private String custName;
private int custAge;
private String custEmail;
}
自定义sql
创建数据库表
编写对应的实体类
package com.qian.mybatisplus.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
import lombok.ToString;
/**
* 文件描述
*
* @Author: QJS
* @CreateDate: 2022/1/29 22:23
**/
@Data
@ToString
public class Student {
// 定义属性
@TableId(value = "id",type = IdType.AUTO)
private Integer id;
private String name;
private Integer age;
private String email;
private Integer status;
}
编写Mapper文件
package com.qian.mybatisplus.dao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.qian.mybatisplus.entity.Student;
import java.util.List;
/**
* 文件描述
*
* @Author: QJS
* @CreateDate: 2022/1/29 22:26
**/
public interface StudentMapper extends BaseMapper<Student> {
// 自定义方法
public int insertStudent(Student student);
public Student selectStudentById(Integer id);
public List<Student> selectByName(String name);
}
方法的定义在resources文件下编写对应的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.qian.mybatisplus.dao.StudentMapper">
<insert id="insertStudent">
insert into student(name,age,email,status)values (#{name},#{age},#{email},#{status})
</insert>
<select id="selectStudentById" resultType="com.qian.mybatisplus.entity.Student">
select id ,name,age,email,status from student where id = #{studentId}
</select>
<select id="selectByName" resultType="com.qian.mybatisplus.entity.Student">
select id,name,age,email,status from student where name = #{name}
</select>
</mapper>
文件中的具体位置:
yml文件中添加mapper的扫描地址
mapper-locations: classpath*:Mapper/student/*Mapper.xml
- 名称空间要用mapper文件定义方法的全限定名称
<mapper namespace="com.qian.mybatisplus.dao.StudentMapper">
- crud中的方法id要用其类中定义的方法名,如insertStudent等方法
- resultType返回类型式实体类中的全限定名称
编写对应的测试类:
package com.qian.mybatisplus;
import com.qian.mybatisplus.dao.AddressMapper;
import com.qian.mybatisplus.dao.StudentMapper;
import com.qian.mybatisplus.entity.Address;
import com.qian.mybatisplus.entity.Student;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
/**
* 文件描述
*
* @Author: QJS
* @CreateDate: 2022/1/29 16:22
**/
@SuppressWarnings("all")
@SpringBootTest
public class StudentTest {
// 定义AddressMaper
@Autowired
private StudentMapper studentDao;
@Test
public void testInsertStudent(){
Student student = new Student();
student.setId(1);
student.setName("QJS");
student.setEmail("QJD@163.com");
student.setAge(20);
student.setStatus(1);
int rows = studentDao.insertStudent(student);
System.out.println("insertStudent rows:"+rows);
}
@Test
public void testSelectStudentById(){
Student student = studentDao.selectStudentById(1);
if (student != null){
// 其他的业务操作
}
System.out.println("student:"+student);
}
@Test
public void testSelectByName(){
List<Student> student = studentDao.selectByName("QJS");
student.forEach(stu -> System.out.println(stu));
}
}
查询和分页
IDEA ctrl+N查找当前的类,ctrl+H查看当前类的结构
条件有:
条件 | 描述 |
---|---|
allEq | 基于map相等 |
eq | 等于 |
ne | 不等于 |
gt | 大于 |
ge | 大于等于 |
---|---|
lt | 小于 |
le | 小于等于 |
between | 介值中间 |
notBetween | 不在这介值中间 |
like | like ‘%值%’ |
---|---|
not like | not like ‘%值%’ |
likeLeft | like ‘%值’ |
likeRight | like ‘值%’ |
isNull | 字段 is null |
isNotNull | 字段 is not null |
in | 字段 in(value1,value2,。。) |
---|---|
notIn | 字段 not in(value1,value2,。。) |
notInSql | 字段 not in (sql 语句) |
---|---|
groupBy | group by 字段 |
orderByAsc | 升序 order by字段,…ASC |
---|---|
orderByDesc | 降序 order by 字段,…DESC |
orderBy | 自定义字段排序 orderBy (true,true,“id”,“name”)—>order by id ASC,name ASC |
---|---|
having | 条件分组 |
or | or 语句,拼接+or 字段=值 |
---|---|
and | and 语句,拼接+and 字段=值 |
apply | 拼接sql |
---|---|
last | 在sql语句后拼接自定义条件 |
exists | 拼接exists(sql 语句) 例:exists(“select id from table where age=1”)—>exists(select id from table where age=1) |
---|---|
notExists | 拼接not exists (sql 语句) |
allEq
查询全部
@Test
public void testAllEq(){
QueryWrapper<Student> qw = new QueryWrapper<>();
// 组装条件
HashMap<String, Object> map = new HashMap<>();
// map<key,value>key列名,value:查询的值
map.put("name","QJS0");
map.put("age",20);
qw.allEq(map);
// 调用MP自己的查询方法
// Preparing: SELECT id,name,age,email,status FROM student WHERE (name = ? AND age = ?)
// Parameters: QJS0(String), 20(Integer)
List<Student> stu = studentDao.selectList(qw);
stu.forEach(student -> System.out.println(student));
}
如果有null的情况
- Map对象中有 key的value是null,使用的是
qw.allEq(param,true);
,结果:WHERE name = ? AND age IS NULL - Map对象中有 key的value是null,
qw.allEq(param,false);
结果:WHERE name = ?
结论:allEq(map,boolean)
- true:处理null值,Where条件加入字段is null
- false:忽略null,不作为where条件
@Test
public void testAllEq2(){
QueryWrapper<Student> qw = new QueryWrapper<>();
// 组装条件
HashMap<String, Object> map = new HashMap<>();
// map<key,value>key列名,value:查询的值
map.put("name","QJS0");
map.put("age",null);
qw.allEq(map,true);
// 调用MP自己的查询方法
List<Student> stu = studentDao.selectList(qw);
stu.forEach(student -> System.out.println(student));
}
符号判断
/**
* eq的使用
* eq("列名",值)
*/
@Test
public void testEq(){
QueryWrapper<Student> qw = new QueryWrapper<>();
// 组成条件
qw.eq("name","QJS2");
List<Student> students = studentDao.selectList(qw);
// SELECT id,name,age,email,status FROM student WHERE (name = ?)
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
/**
* ne使用
* ne表示不等于
*
* ne("列名",值)
*/
@Test
public void testNe(){
QueryWrapper<Student> qw = new QueryWrapper<>();
// 组成条件
qw.ne("name","QJS3");
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
/**
* gt 大于( > )
*/
@Test
public void testGt(){
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.gt("age",20);
// SELECT id,name,age,email,status FROM student WHERE (age > ?)
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
/**
* ge 大于等于( >= )
*/
@Test
public void testGe(){
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.ge("age",20);
// SELECT id,name,age,email,status FROM student WHERE (age >= ?)
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
/**
* lt 小于(<)
*/
@Test
public void testLt(){
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.lt("age",22);
// SELECT id,name,age,email,status FROM student WHERE (age < ?)
// where age < 20 and age > 20
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
/**
* le 小于等于(<=)
*/
@Test
public void testLe(){
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.le("age",22);
// SELECT id,name,age,email,status FROM student WHERE (age <= ?)
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
区间范围内
/**
* between(? and ?)
*/
@Test
public void testBetween(){
QueryWrapper<Student> qw = new QueryWrapper<>();
// between("列名",开始值,结束值)
qw.between("age",18,28);
// SELECT id,name,age,email,status FROM student WHERE (age BETWEEN ? AND ?)
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
/**
* notBetween(不在范围区间内)
*/
@Test
public void testBetween1(){
QueryWrapper<Student> qw = new QueryWrapper<>();
// between("列名",开始值,结束值)
qw.notBetween("age",20,21);
// SELECT id,name,age,email,status FROM student WHERE (age NOT BETWEEN ? AND ?)
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
匹配字段
/**
* like 匹配某个值
*/
@Test
public void testBetween2(){
QueryWrapper<Student> qw = new QueryWrapper<>();
//
qw.like("name",1);
// SELECT id,name,age,email,status FROM student WHERE (name LIKE ?)
// %1%
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
/**
* likeLeft "%值"
*/
@Test
public void testLikeLeft(){
QueryWrapper<Student> qw = new QueryWrapper<>();
//
qw.likeLeft("name",1);
// SELECT id,name,age,email,status FROM student WHERE (name LIKE ?)
// %1(String)
// Columns: id, name, age, email, status
//<== Row: 2, QJS1, 20, QJD@163.com, 1
//<== Row: 4, QJS1, 21, QJD@163.com, 1
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
/**
* likeRight "%值"
*/
@Test
public void testLikeRight(){
QueryWrapper<Student> qw = new QueryWrapper<>();
//
qw.likeRight("name","QJS");
// SELECT id,name,age,email,status FROM student WHERE (name LIKE ?)
// QJS%(String)
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
/**
* isNull,判断字段是 null
*/
@Test
public void testIsNull(){
QueryWrapper<Student> qw = new QueryWrapper<>();
// SELECT id,name,age,email,status FROM student WHERE (email IS NULL)
// WHERE email IS NULL
qw.isNull("email");
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
/**
* isNotNull,判断字段是 null
*/
@Test
public void testIsNull1(){
QueryWrapper<Student> qw = new QueryWrapper<>();
// Preparing: SELECT id,name,age,email,status FROM student WHERE (email IS NOT NULL)
qw.isNotNull("email");
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
in或子查询
/**
* in 值列表
*/
@Test
public void testIn(){
QueryWrapper<Student> qw = new QueryWrapper<>();
// Preparing: SELECT id,name,age,email,status FROM student WHERE (name IN (?,?,?))
// Parameters: QJS1(String), QJS2(String), QJS3(String)
qw.in("name","QJS1","QJS2","QJS3");
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
/**
* inSql()
*/
@Test
public void testInSQL(){
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.inSql("age","select age from student where id=1");
// SELECT id,name,age,email,status FROM student WHERE (age IN (select age from student where id=1))
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
/**
* NotinSql()
*/
@Test
public void testNotInSQL(){
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.notInSql("age","select age from student where id=1");
// SELECT id,name,age,email,status FROM student WHERE (age NOT IN (select age from student where id=1))
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
分组
/**
* GroupBy: 分组
*/
@Test
public void testGroupby(){
QueryWrapper<Student> qw = new QueryWrapper<>();
// Preparing: SELECT name,count(*) personNumbers FROM student GROUP BY name
qw.select("name,count(*) personNumbers");// select name,count(*) personNumbers
qw.groupBy("name");
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
升降序
/**
* orderbyAsc: 按字段升序
*/
@Test
public void testOrderByAsc(){
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.orderByAsc("name","age");
// Preparing: SELECT id,name,age,email,status FROM student ORDER BY name ASC,age ASC
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
/**
* orderbyDesc: 按字段降序
*/
@Test
public void testOrderByDesc(){
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.orderByDesc("name","age");
// Preparing: SELECT id,name,age,email,status FROM student ORDER BY name DESC,age DESC
List<Student> students = studentDao.selectList(qw);
students.forEach(stu -> System.out.println("查询eq:"+stu));
}
分页
/**
* 分页:
* 1.统计记录数,使用count(1)
* SELECT COUNT(1) FROM student WHERE age > ?
* 2.实现分页,在sql语句的末尾加入 limit 0,3
* SELECT id,name,age,email,status FROM student WHERE age > ? LIMIT 0,3
*/
@Test
public void testPage(){
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.gt("age",22);
IPage<Student> page = new Page<>();
//设置分页的数据
page.setCurrent(1);//第一页
page.setSize(3);// 每页的记录数
IPage<Student> result = studentDao.selectPage(page,qw);
//获取分页后的记录
List<Student> students = result.getRecords();
System.out.println("students.size()="+students.size());
//分页的信息
long pages = result.getPages();
System.out.println("页数:"+pages);
System.out.println("总记录数:"+result.getTotal());
System.out.println("当前页码:"+result.getCurrent());
System.out.println("每页的记录数:"+result.getSize());
}
MP代码生成器
这个有点像对应的mybatis逆向工程生成器
新版本和旧版本是有区别的
添加依赖包
<!-- 模板引擎 -->
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity-engine-core</artifactId>
<version>2.0</version>
</dependency>
创建一个生成类
public class AutoMapper {
public static void main(String[] args) {
//创建AutoGenerator ,MP中对象
AutoGenerator ag = new AutoGenerator();
//设置全局配置
GlobalConfig gc = new GlobalConfig();
//设置代码的生成位置, 磁盘的目录
String path = System.getProperty("user.dir");
gc.setOutputDir(path+"/src/main/java");
//设置生成的类的名称(命名规则)
gc.setMapperName("%sMapper");//所有的Dao类都是Mapper结尾的,例如DeptMapper
//设置Service接口的命名
gc.setServiceName("%sService");//DeptService
//设置Service实现类的名称
gc.setServiceImplName("%sServiceImpl");//DeptServiceImpl
//设置Controller类的命名
gc.setControllerName("%sController");//DeptController
//设置作者
gc.setAuthor("码农研究僧");
//设置主键id的配置
gc.setIdType(IdType.ID_WORKER);
ag.setGlobalConfig(gc);
//设置数据源DataSource
DataSourceConfig ds = new DataSourceConfig();
//驱动
ds.setDriverName("com.mysql.jdbc.Driver");
//设置url
ds.setUrl("jdbc:mysql://localhost:3306/springdb");
//设置数据库的用户名
ds.setUsername("root");
//设置密码
ds.setPassword("123456");
//把DataSourceConfig赋值给AutoGenerator
ag.setDataSource(ds);
//设置Package信息
PackageConfig pc = new PackageConfig();
//设置模块名称, 相当于包名, 在这个包的下面有 mapper, service, controller。
pc.setModuleName("order");
//设置父包名,order就在父包的下面生成
pc.setParent("com.wkcto"); //com.wkcto.order
ag.setPackageInfo(pc);
//设置策略
StrategyConfig sc = new StrategyConfig();
sc.setNaming(NamingStrategy.underline_to_camel);
//设置支持驼峰的命名规则
sc.setColumnNaming(NamingStrategy.underline_to_camel);
ag.setStrategy(sc);
//执行代码的生成
ag.execute();
}
}
点击运行生成对应的代码文件
执行对应的测试类
@SuppressWarnings("all")
@RunWith(SpringRunner.class)
@SpringBootTest
public class StudentMapperTest {
//注入生成的StudentMapper
@Autowired
StudentMapper studentMapper;
@Test
public void testInsertStudent(){
Student student = new Student();
student.setName("john");
student.setAge(28);
student.setEmail("john@yahu.com");
student.setStatus(2);
int rows = studentMapper.insert(student);
System.out.println("insert Student rows:"+rows);
}
@Test
public void testSelect(){
Student student = studentMapper.selectById(1);
System.out.println("testSelect:"+student);
}
@Test
public void testSelect1(){
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.gt("age",35);
//selectOne:查询结果只能是一条记录或没有没有记录,多条记录是报错的
Student student = studentMapper.selectOne(qw);
System.out.println("testSelect:"+student);
}
}
amingStrategy.underline_to_camel);
//设置支持驼峰的命名规则
sc.setColumnNaming(NamingStrategy.underline_to_camel);
ag.setStrategy(sc);
//执行代码的生成
ag.execute();
}
}
点击运行生成对应的代码文件
[[外链图片转存中...(img-FwXlIe4G-1644394120451)]](https://imgtu.com/i/Hi0ISI)
执行对应的测试类
```java
@SuppressWarnings("all")
@RunWith(SpringRunner.class)
@SpringBootTest
public class StudentMapperTest {
//注入生成的StudentMapper
@Autowired
StudentMapper studentMapper;
@Test
public void testInsertStudent(){
Student student = new Student();
student.setName("john");
student.setAge(28);
student.setEmail("john@yahu.com");
student.setStatus(2);
int rows = studentMapper.insert(student);
System.out.println("insert Student rows:"+rows);
}
@Test
public void testSelect(){
Student student = studentMapper.selectById(1);
System.out.println("testSelect:"+student);
}
@Test
public void testSelect1(){
QueryWrapper<Student> qw = new QueryWrapper<>();
qw.gt("age",35);
//selectOne:查询结果只能是一条记录或没有没有记录,多条记录是报错的
Student student = studentMapper.selectOne(qw);
System.out.println("testSelect:"+student);
}
}