目录
queryWrapper构造器selectMaps指定字段查询
queryWrapper构造器selectObjs获取第一列数据查询
集成
创建本地数据库
创建本地数据库，并创建用户表，假如我创建了一个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); 查询单条数据（需保证只要一条）
1099
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
