mybatis-plus基础查询语句_mybatis-plus findall代码

目录

集成

创建本地数据库

初始化项目

创建spring-boot项目，在pom文件添加所需依赖

配置yml文件

为mapper接口创建全局代理对象

 在pojo目录下创建User映射类 

定义公共接口，并继承BaseMapper

测试

常用mybatis-plus增删改查方法

mybatis-plus（select查询）

selectList查询全部数据

 selectBatchIds根据集合查询数据

selectByMap根据指定条件查询

queryWrapper构造器基础查询

 queryWrapper构造器nested嵌套查询

queryWrapper构造器inSql关联查询

 queryWrapper构造器selectMaps指定字段查询

 queryWrapper构造器过滤null条件查询

queryWrapper构造器groupBy分组查询

queryWrapper构造器selectObjs获取第一列数据查询

insert插入数据

updateById更新数据

 delete删除数据

 queryWrapper构造器其他查询

---

集成

依赖包地址https://mp.baomidou.com/

创建本地数据库

创建本地数据库，并创建用户表，假如我创建了一个user表，id设置为自增的主键

初始化项目

创建spring-boot项目，在pom文件添加所需依赖

<project
	xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.jt.vip</groupId>
	<artifactId>spring_boot_mybatis</artifactId>
	<version>0.0.1-SNAPSHOT</version>
 
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.4.6</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
 
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
 
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
 
		<!--为属性赋值,添加依赖 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
 
		<!--添加热部署插件 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
		</dependency>
 
		<!--引入插件lombok 自动的set/get/构造方法插件 -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
		</dependency>
 
		<!--引入数据库驱动 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
 
		<!--springBoot数据库连接 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.4.3.1</version>
		</dependency>
		
 
	</dependencies>
 
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

---

配置yml文件

server:
  port: 80
  servlet:
    context-path: /
spring:
  datasource:
    #高版本配置如下 com.mysql.cj.jdbc.Driver
    #低版本配置如下com.mysql.jdbc.Driver
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://IP地址:3306/你的数据库名?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
    username: root
    password: 123456
 
mybatis-plus:
  #定义别名包  resultType的映射可以省略包路径,这里修改为你的User文件的包路径
  type-aliases-package: com.example.demo.pojo
  #加载mapper的映射文件
  mapper-locations: classpath:/mybatis/mappers/*.xml
#日志     替换成自己mapper包路径
logging:
  level:
    '[com.example.demo.mapper]': debug

---

为mapper接口创建全局代理对象

@SpringBootApplication
@MapperScan("com.example.demo.mapper")
public class Application {
 
	public static void main(String[] args) {
		SpringApplication.run(Application.class, args);
	}
 
}

说明，@MapperScan("com.example.demo.mapper")，在启动类中，动态的为mapper的接口创建代理对象，免去之后所有mapper类中的@Mapper注解

---

 在pojo目录下创建User映射类 

@Data
@Accessors(chain = true)
@NoArgsConstructor
@AllArgsConstructor
@TableName("user")  //将对象和表进行关联
public class User {
	@TableId(type = IdType.AUTO)
	private Integer id;
	@TableField("name")
	private String name;
	@TableField("sex")
	private String sex;
	@TableField("age")
	private Integer age; 
}

这里对注解做一个说明：

@TableName("user")	将User对象和user表进行关联
@TableId(type = IdType.AUTO)	标识这个字段为主键，这里我设置为自增
@TableField("")	关联字段映射

 如果User对象中的字段和表字段一致，注解可以省略不写，如图

---

定义公共接口，并继承BaseMapper

public interface UserMapper extends BaseMapper<User>{
    //这里手动添加一个自己的测试方法，查询所有数据
	List<User>findAll();
}

说明，BaseMapper需要一个泛型类型，这里是User类型，BaseMapper中包含我们所需的增删改查方法

---

测试

@SpringBootTest
class ApplicationTests {
	
	@Autowired
	private UserMapper userMapper;
 
	@Test
	void contextLoads() {
		List<User>list=userMapper.findAll();
		System.out.println(list);
	}
 
}

常用mybatis-plus增删改查方法

mybatis-plus（select查询）

---

selectList查询全部数据

	@Test
	void test1() {
		List<User>list=userMapper.selectList(null);
		System.out.println(list);
	}

---

 selectBatchIds根据集合查询数据

	@Test
	void test2() {
		List<Integer>idList=new ArrayList<Integer>();
		idList.add(1);
		idList.add(2);
		List<User>list=userMapper.selectBatchIds(idList);
		System.out.println(list);
	}

相当于  SELECT id,name,sex,age FROM user WHERE id IN ( ? , ? ) 

---

selectByMap根据指定条件查询

    @Test
	void test3() {
		Map<String, Object>columnMap=new HashMap<String, Object>();
		columnMap.put("name", "张三");
		columnMap.put("age", 4000);
		List<User>list=userMapper.selectByMap(columnMap);
		System.out.println(list);
	}

 相当于  SELECT id,name,sex,age FROM user WHERE name = ? AND age = ?

 

---

queryWrapper构造器基础查询

	//条件构造器内容查询
	@Test
	void test4() {
		QueryWrapper<User>queryWrapper=new QueryWrapper<User>();
		queryWrapper.like("name", "三");
		queryWrapper.eq("sex", "男");
		List<User>list=userMapper.selectList(queryWrapper);
		System.out.println(list);
	}

 相当于  SELECT id,name,sex,age FROM user WHERE (name LIKE ? AND sex = ?)

---

 queryWrapper构造器nested嵌套查询

 例如 我想查询（age<5000 或者 性别是男）并且姓张的人

	@Test
	void test6() {
		QueryWrapper<User>queryWrapper=new QueryWrapper<User>();
		queryWrapper
		     .nested(i ->i.lt("age", 5000).or().eq("sex", "男"))
		     .likeRight("name", "张");
		List<User>list=userMapper.selectList(queryWrapper);
		System.out.println(list);
	}

i ->i.lt("age", 5000)是lambda表达式

 相当于   SELECT id,name,sex,age FROM user WHERE ((age < ? OR sex = ?) AND name LIKE ?)

---

queryWrapper构造器inSql关联查询

例如 我想查询 age<5000并且和李四是相同性别的数据

	@Test
	void test5() {
		QueryWrapper<User>queryWrapper=new QueryWrapper<User>();
		queryWrapper.lt("age", 5000);
		queryWrapper.inSql("sex", "select sex from user where name='李四'");
		List<User>list=userMapper.selectList(queryWrapper);
		System.out.println(list);
	}

相当于  SELECT id,name,sex,age FROM user WHERE (age < ? AND sex IN (select sex from user where name='李四')) 

---

 queryWrapper构造器selectMaps指定字段查询

	@Test
	void test10() {
		QueryWrapper<User>queryWrapper=new QueryWrapper<User>();
		queryWrapper.select("name","age").eq("name", "张三");
		List<Map<String, Object>>list=userMapper.selectMaps(queryWrapper);
		System.out.println(list);
	}

 相当于  SELECT name,age FROM user WHERE (name = ?)

---

 queryWrapper构造器过滤null条件查询

  如下三种

	@Test
	void test7() {
		String name="张三";
		QueryWrapper<User>queryWrapper=new QueryWrapper<User>();
		queryWrapper.eq(!StringUtils.isEmpty(name),"name",name);
		List<User>list=userMapper.selectList(queryWrapper);
		System.out.println(list);
	}
	
	@Test
	void test8() {
		User user=new User();
		user.setAge(3000);
		//如果对象中的某个属性不为空，则拼接user。使用对象作为条件，只能使用等号运算符
		QueryWrapper<User>queryWrapper=new QueryWrapper<User>(user);
		List<User>list=userMapper.selectList(queryWrapper);
		System.out.println(list);
	}
	
	@Test
	void test9() {
		Map<String, Object>columnMap=new HashMap<String, Object>();
		columnMap.put("name", "张三");
		columnMap.put("age", null);
		QueryWrapper<User>queryWrapper=new QueryWrapper<User>();
		queryWrapper.allEq(columnMap,true);//true无论是否为空，都拼接该条件，如果为false，不拼接
		List<User>list=userMapper.selectList(queryWrapper);
		System.out.println(list);
	}

---

queryWrapper构造器groupBy分组查询

比如我想查询性别男的最大和最小年龄，性别女的最大和最小年龄

	@Test
	void test11() {
		QueryWrapper<User>queryWrapper=new QueryWrapper<User>();
		queryWrapper.select("max(age) max_age","min(age) min_age")
		.groupBy("sex")
		.having("sex in({0},{1})", "男","女"); //这里的0，1就是为后面的男女占位，防止注入攻击
		List<Map<String, Object>>list=userMapper.selectMaps(queryWrapper);
		System.out.println(list);
	}

相当于  SELECT max(age) max_age,min(age) min_age FROM user GROUP BY sex HAVING sex in(?,?)

---

queryWrapper构造器selectObjs获取第一列数据查询

	@Test
	void test12() {
		QueryWrapper<User>queryWrapper=new QueryWrapper<User>();
		queryWrapper.gt("age", 2000);
		List<Object>list=userMapper.selectObjs(queryWrapper);
		System.out.println(list);
	}

相当于  SELECT id,name,sex,age FROM user WHERE (age > ?)

---

insert插入数据

	@Test
	void test14() {
		User user=new User();
		user.setName("围裙妈妈");
		user.setAge(50);
		user.setSex("女");
		int x=userMapper.insert(user);
		System.out.println(x);
	}

相当于  INSERT INTO user ( name, sex, age ) VALUES ( ?, ?, ? ) 

---

updateById更新数据

	@Test
	void test15() {
		User user=new User();
		user.setId(100).setName("围裙妈妈").setAge(50).setSex("女");
		int x=userMapper.updateById(user);
		System.out.println(x);
	}

 或者根据条件更新

	@Test
	void test16() {
		User user=new User();
		user.setName("小头爸爸").setAge(60).setSex("女");
		QueryWrapper<User>updateWrapper=new QueryWrapper<User>();
		updateWrapper.eq("name", "大头儿子");
		int x=userMapper.update(user, updateWrapper);
		System.out.println(x);
	}

---

 delete删除数据

	@Test
	void test17() {
		int x=userMapper.deleteById(1000);
		System.out.println(x);
	}

或者批量删除

	@Test
	void test18() {
		List<Integer>list=new ArrayList<Integer>();
		list.add(1100);
		list.add(1200);
		int x=userMapper.deleteBatchIds(list);
		System.out.println(x);
	}

 或者根据条件删除

	@Test
	void test19() {
		QueryWrapper<User>queryWrapper=new QueryWrapper<User>();
		queryWrapper.eq("age", 100000);
		int x=userMapper.delete(queryWrapper);
		System.out.println(x);
	}

或者 根据条件删除

	@Test
	void test20() {
		Map<String, Object>columnMap=new HashMap<String, Object>();
		columnMap.put("name", "花花");
		int x=userMapper.deleteByMap(columnMap);
		System.out.println(x);
	}

相当于  DELETE FROM user WHERE name = ? 

---

 queryWrapper构造器其他查询

queryWrapper.like("name", "三");	模糊查询   %三%
queryWrapper.likeLeft(column, val);	左模糊
queryWrapper.likeRight(column, val);	右模糊
queryWrapper.in("id", 1,2,3,4,5);	查询指定的几个
queryWrapper.select("name","","","");	查询指定字段
queryWrapper.eq("sex", "男");	sex为男的条件
queryWrapper.gt(column, val);	大于
queryWrapper.lt(column, val);	小于
queryWrapper.ge(column, val);	大于等于
queryWrapper.le(column, val);	小于等于
queryWrapper.between(column, val1, val2);	两者之间
queryWrapper.orderByDesc(column, columns);	降序
queryWrapper.orderByAsc(columns)	升序
selectCount(queryWrapper)	根据条件查询总数
selectOne(queryWrapper);	查询单条数据（需保证只要一条）