最近web端比较热门的框架就是SpringBoot和Mybatis-Plus,这里简单总结集成用法。
Mybatis-Plus是一个很强大而且方便的ORM框架,而且对SpringBoot也进行了适配,用起来简易,官方网站也有中文文档。
引入Jar
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.2</version>
</dependency>
Mybatis-Plus对SpringBoot进行了适配,所以可如此引入。下面的Jar可用于SpringMVC或者单独使用
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>3.0.2</version>
</dependency>
全局配置
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=true
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
mybatis-plus.type-aliases-package=com.plf.springbootmybatisplus.entity
#AUTO 数据库自增ID;ID_WORKER 全局唯一ID,内容为空自动填充(默认配置);
#INPUT 用户输入ID;UUID 全局唯一ID,内容为空自动填充
mybatis-plus.global-config.db-config.id-type=AUTO
mybatis-plus.global-config.db-config.table-underline=true
实体类
import java.util.Date;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
@TableName
public class Log {
@TableId
private Integer id;
@TableField
private String content;
@TableField
private Date createtime;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
}
Mybatis-Plus配置类
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
@Configuration
@MapperScan("com.plf.springbootmybatisplus.mapper*")
public class MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
}
Mapper类
import org.springframework.stereotype.Repository;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.plf.springbootmybatisplus.entity.Log;
@Repository
public interface LogMapper extends BaseMapper<Log>{
}
上述接口继承BaseMapper<Log>
,即可使用通用方法
接口方法
import com.baomidou.mybatisplus.extension.service.IService;
import com.plf.springbootmybatisplus.entity.Log;
public interface LogService extends IService<Log>{
public void insertLog(Log log);
}
接口实现类
@Service
public class LogServiceImpl extends ServiceImpl<BaseMapper<Log>,Log>
implements LogService {
@Override
public void insertLog(Log log) {
baseMapper.insert(log);
}
}
控制器类
import java.util.Date;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.plf.springbootmybatisplus.entity.Log;
import com.plf.springbootmybatisplus.service.impl.LogServiceImpl;
@RestController
@RequestMapping("/log")
public class LogController {
@Autowired
private LogServiceImpl logServiceImpl;
//日志插入
@RequestMapping("/add")
public String addLog(Log log){
log.setCreatetime(new Date());
logServiceImpl.insertLog(log);
return "success";
}
}
调用接口
http://localhost:8080/log/add?content=test
逻辑删除
配置逻辑值
# 已删除表示1
mybatis-plus.global-config.db-config.logic-delete-value=1
# 未删除表示0
mybatis-plus.global-config.db-config.logic-not-delete-value=0
注入LogicSqlInjector
@Bean
public ISqlInjector sqlInjector(){
return new LogicSqlInjector();
}
实体类标注逻辑删除字段
@TableField
@TableLogic
private Integer delstatus;
测试
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
@RunWith(SpringRunner.class)
@SpringBootTest
public class LogServiceTest {
@Autowired
private LogService logService;
@Test
public void delLog(){
logService.deleteLog(5);
}
}
结果
后台执行如下语句
UPDATE log SET delstatus=1 WHERE id=? AND delstatus=0
分页查询
注入分页插件
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
编写接口方法
public Page<Log> getPageLog(Page<Log> page, String strattime);
实现接口方法
@Override
public Page<Log> getPageLog(Page<Log> page, String starttime) {
return (Page<Log>) baseMapper.selectPage(page,
new QueryWrapper<Log>().select("content,createtime,user_id").gt("createtime",starttime));
}
测试
@Test
public void ListPage(){
Page<Log> page=logService.getPageLog(new Page<Log>(1,2), "2018-10-09");
System.out.println("total:"+page.getTotal());
List<Log> list = page.getRecords();
for (Log log : list) {
System.out.println(log);
}
}
结果
后台SQL语句
Preparing: SELECT COUNT(1) FROM log WHERE delstatus = 0 AND createtime > ?
Parameters: 2018-10-09(String)
Preparing: SELECT content,createtime,user_id FROM log WHERE delstatus=0 AND createtime > ? LIMIT 0,2
Parameters: 2018-10-09(String)
Total: 2
结果
total:3
Log [id=null, userId=1, content=test,
createtime=Wed Oct 10 21:03:17 CST 2018, delstatus=null]
Log [id=null, userId=1, content=test2,
createtime=Thu Oct 11 20:51:47 CST 2018, delstatus=null]
自定义联表分页查询
新建DTO类用于接受联表字段
import java.util.Date;
public class LogDto{
private String content;
private Date createtime;
private String username;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
@Override
public String toString() {
return "LogDto [content=" + content + ", createtime=" + createtime + ", username=" + username + "]";
}
}
在LogMapper中编写SQL语句
@Select("select l.content,u.username,l.createtime from log l"
+ " left join user u on l.user_id=u.id"
+ " where u.username=#{username}")
public List<LogDto> getLogAndName(Page<LogDto> page,@Param("username")String username);
编写接口方法
public Page<LogDto> getLogAndName(Page<LogDto> page, String username);
实现接口方法
@Override
public Page<LogDto> getLogAndName(Page<LogDto> page, String username) {
List<LogDto> records = baseMapper.getLogAndName(page,username);
page.setRecords(records);
return page;
}
测试
@Test
public void ListPageAndName(){
Page<LogDto> page=logService.getLogAndName(new Page<LogDto>(1,2), "小白");
List<LogDto> list = page.getRecords();
for (LogDto logDto : list) {
System.out.println(logDto);
}
}
结果
后台SQL语句
Preparing: SELECT COUNT(1) FROM log l LEFT JOIN user u ON l.user_id = u.id WHERE u.username = ?
Parameters: 小白(String)
Preparing: select l.content,u.username,l.createtime from log l left join user u on l.user_id=u.id where u.username=? LIMIT 0,2
小白(String)
Total: 2
结果
LogDto [content=测试1, createtime=Sun Oct 07 21:01:37 CST 2018, username=小红]
LogDto [content=test, createtime=Wed Oct 10 21:03:17 CST 2018, username=小红]
条件构造器
从官网搬运过来的,方便自己查看。
查询方式 | 说明 |
---|---|
setSqlSelect | 设置 SELECT 查询字段 |
where | WHERE 语句,拼接 + WHERE 条件 |
and | AND 语句,拼接 + AND 字段=值 |
andNew | AND 语句,拼接 + AND (字段=值) |
or | OR 语句,拼接 + OR 字段=值 |
orNew | OR 语句,拼接 + OR (字段=值) |
eq | 等于= |
allEq | 基于 map 内容等于= |
ne | 不等于<> |
gt | 大于> |
ge | 大于等于>= |
lt | 小于< |
le | 小于等于<= |
like | 模糊查询 LIKE |
notLike | 模糊查询 NOT LIKE |
in | IN 查询 |
notIn | NOT IN 查询 |
isNull | NULL 值查询 |
isNotNull | IS NOT NULL |
groupBy | 分组 GROUP BY |
having | HAVING 关键词 |
orderBy | 排序 ORDER BY |
orderAsc | ASC 排序 ORDER BY |
orderDesc | DESC 排序 ORDER BY |
exists | EXISTS 条件语句 |
notExists | NOT EXISTS 条件语句 |
between | BETWEEN 条件语句 |
notBetween | NOT BETWEEN 条件语句 |
addFilter | 自由拼接 SQL |
last | 拼接在最后,例如:last(“LIMIT 1”) |