创建java模块,导入依赖
<!--springboot父工程-->
<parent>
<artifactId>spring-boot-starter-parent</artifactId>
<groupId>org.springframework.boot</groupId>
<version>2.3.12.RELEASE</version>
</parent>
<dependencies>
<!--数据库驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.45</version>
</dependency>
<!--连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.3</version>
</dependency>
<!--持久层框架-mybatisPlus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.1</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--web环境依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--配置提示-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
</dependency>
<!--测试依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
</dependencies>
复制代码
添加配置
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: root
type: com.alibaba.druid.pool.DruidDataSource
druid:
#初始化连接池的的连接数据量
initial-size: 5
#连接池最小连接数
min-idle: 5
#获取连接等待超时时间 (毫秒)
max-wait: 6000
#最大连接数
max-active: 20
# 检测连接是否有效。建议配置为true,不影响性能,并且保证安全性,默认false
test-while-idle: true
# 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能 默认true
test-on-borrow: false
# 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。默认false
test-on-return: false
mybatis-plus:
#别名包路径
type-aliases-package: com.eponine.domain
#mapper文件路径,默认位置classpath:/mapper/**/*.xml
#xml用于扩展自定义sql
mapper-locations: classpath:/mapper/**/*.xml
configuration:
#开启驼峰命名规则,默认开启
map-underscore-to-camel-case: true
#日志
logging:
level:
com.eponine.mapper: debug
复制代码
启动类
@MapperScan("com.eponine.mapper") //mapper接口扫描
@SpringBootApplication //标注为springboot启动类
public class MybatisPlusApplication {
public static void main(String[] args) {
SpringApplication.run(MybatisPlusApplication.class,args);
}
}
复制代码
ORM映射
实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
//把实体类和表绑定关系,同名可以不写
@TableName("tb_user")
public class User {
//指定id属性和主键生成方式(AUTO-自增)
@TableId(type = IdType.AUTO)
private Long id;
//属性和表字段绑定关系
@TableField("username")
private String username;//同名或符合驼峰命名规则可以省略
private String password;
private String nickName;
private Integer age;
private String email;
}
复制代码
UserMapper接口
//继承BaseMapper类,BaseMapper里封装了通用的CRUD方法
public interface UserMapper extends BaseMapper<User> {
}
复制代码
UserService
//继承Mybatis-plus提供的基础接口,IService接口,封装了service需要的基础的CRUD方法
public interface UserService extends IService<User> {
}
复制代码
//继承service的基础实现类,实现UserService接口
//ServiceImpl: myabtis-plus提供,封装了通用CURD,包含mapper接口注入
@Service
@Transactional
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}
复制代码
UserController
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
UserService userService;
@GetMapping("/{id}")
public void SelectById(@PathVariable Long id){
User user = userService.getById(id);
System.out.println(user);
}
@GetMapping("/list")
public void SelectUser(){
List<User> list = userService.list();
for (User user : list) {
System.out.println(user);
}
}
}
复制代码
Service CRUD接口
mybatis-plus 在IService接口中封装了大量的基础CRUD方法
@RunWith(SpringRunner.class)
@SpringBootTest
public class MyTest {
@Autowired
UserService userService;
@Test
public void TestMP(){
//User user = new User(10l, "dover", null, null, null, null);
//userService.save(user);
//保存或修改(修改时空值不会覆盖旧值)
//userService.saveOrUpdate(user);
//删除
//userService.removeById(11l);
List<Long> ids = new ArrayList<>();
ids.add(1l);
ids.add(3l);
ids.add(4l);
//批量查询
List<User> list = userService.listByIds(ids);
for (User user : list) {
System.out.println(user);
}
//批量删除
//userService.removeByIds(ids);
}
}
复制代码
Mapper CRUD接口
如果CURD需要添加条件,可以使用 Wrapper` 的4个实现类
-
UpdateWrapper
封装修改条件
-
QueryWrapper
封装查询、删除条件
-
LambdaQueryWrapper
用法基本和 QueryWrapper 一致,区别在于获取 column 的方式不一样
-
LambdaUpdaeWrapper
用法基本和 UpdateWrapper 一致,区别在于获取 column 的方式不一样
条件查询
官网提供的
QueryWrapper
对象,用于生成 sql 的 where 条件
查询方法 | 说明 |
---|---|
eq (equals) | 等于= |
ne (not equals) | 不等与<> |
gt (greater than) | 大于> |
ge (greater equals) | 大于等于>= |
lt (less than) | 小于< |
le (less equals) | 小于等于<= |
like | 模糊查询 LIKE |
notLike | 模糊查询 NOT LIKE |
in | IN 查询 |
notIn | NOT IN 查询 |
isNull | NULL 值查询 |
isNotNull | IS NOT NULL |
@Test
public void TestWrapper() {
User user = new User();
user.setId(3l);
user.setAge(22);
user.setNickName("张");
/*
-- 查询 id > 3 并且 年龄 <= 22 或者 昵称以 张开头的用户信息,以ID倒序排序
SELECT *
FROM tb_user
WHERE
(id > 3 AND age <= 22)
OR nick_name LIKE "张%"
ORDER BY id DESC
*/
QueryWrapper<User> wrapper = new QueryWrapper<>();
if (user.getId()!=null){
wrapper.gt("id",user.getId());
}
if (user.getAge()!=null){
wrapper.le("age",user.getAge());
}
wrapper.or();
if (user.getNickName()!=null){
wrapper.likeRight("nick_name",user.getNickName());
}
wrapper.orderByDesc("id");
List<User> list = userMapper.selectList(wrapper);
for (User thisUser : list) {
System.out.println(thisUser);
}
}
//优化1: 使用重载方法添加查询条件
@Test
public void TestBetter1() {
User user = new User();
user.setId(3l);
user.setAge(22);
user.setNickName("张");
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.gt(user.getId() != null, "id", user.getId());
wrapper.le(user.getAge() != null, "age", user.getAge());
wrapper.or();
wrapper.likeRight(user.getNickName() != null, "nick_name", user.getNickName());
wrapper.orderByDesc("id");
List<User> list = userMapper.selectList(wrapper);
for (User thisUser : list) {
System.out.println(thisUser);
}
}
//优化2: 使用链式调用
@Test
public void TestBetter2() {
User user = new User();
user.setId(3l);
user.setAge(22);
user.setNickName("张");
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.gt(user.getId() != null, "id", user.getId())
.le(user.getAge() != null, "age", user.getAge())
.or()
.likeRight(user.getNickName() != null, "nick_name", user.getNickName())
.orderByDesc("id");
List<User> list = userMapper.selectList(wrapper);
for (User thisUser : list) {
System.out.println(thisUser);
}
}
//优化3: 使用lambda表达式动态获取字段名
@Test
public void TestBetter3() {
User user = new User();
user.setId(3l);
user.setAge(22);
user.setNickName("张");
//lambdaQueryWrapper可以从实体类的 `@TableField` 上统一获取列名称
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.gt(user.getId() != null, User::getId, user.getId())
.le(user.getAge() != null, User::getAge, user.getAge())
.or()
.likeRight(user.getNickName() != null, User::getNickName, user.getNickName())
.orderByDesc(User::getId);
List<User> list = userMapper.selectList(wrapper);
for (User thisUser : list) {
System.out.println(thisUser);
}
}
复制代码
自定义SQL
注解方式
// 根据年龄分组,并统计每组人数
@Select("select age,count(*)num from tb_user group by age")
List<Map> orderByAge();
复制代码
xml配置方式
//继承BaseMapper接口,BaseMapper里封装了通用的CRUD方法
public interface UserMapper extends BaseMapper<User> {
// 根据年龄分组,并统计每组人数
@Select("select age,count(*)num from tb_user group by age")
List<Map> orderByAge();
User selectById(Long id);
}
复制代码
<?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.eponine.mapper.UserMapper">
<select id="selectbyId" resultType="user">
select * from tb_user where id=#{value}
</select>
</mapper>
复制代码
分页查询
配置分页拦截器
@Configuration
public class MybatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
复制代码
编写分页代码
@Test
public void TestPage(){
//开启分页查询
Page<User> page = new Page<>(1, 3);
//条件
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(User::getPassword,"123456");
Page<User> userPage = userMapper.selectPage(page, wrapper);
//分页查询结果
System.out.println("查询总记录数:"+ page.getTotal());
System.out.println("查询数据列表:"+ page.getRecords());
System.out.println("总页数:"+ page.getPages());
System.out.println("当前数:"+ page.getCurrent());
System.out.println("是否有下一页:"+ page.hasNext());
System.out.println("是否有上一页:"+ page.hasPrevious());
}
链接:https://juejin.cn/post/7032661933337608228