依赖
注意:如果引入了mybatis-plus就不要再引入mybatis-plus-boot-starter,而且mybatis-puls包是自己开发的,非官方的,建议引用mybatis-plus-boot-starter。注意spring和mysql包的版本关系。
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>2.3.0.RELEASE</version>
<scope>import</scope>
<type>pom</type>
</dependency>
</dependencies>
</dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.0</version>
</dependency>
</dependencies>
如果Mapper接口加了@Mapper注释, 启动类不需要加@MapperScan
application.yml
server:
port: 8081
spring:
application:
name: employ-manager
#mysql
datasource:
url: jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&serverTimezone = Asia/Shanghai
username: root
password: 123456
#mybatis-plus
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper-locations: classpath*:mybatis/*.xml
type-aliases-package: com.jane.pojo
pojo
package com.jane.pojo;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
/**
* @author jane
* @date 2022/7/10
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("tbl_employee")
public class Employee implements Serializable {
@TableField("id")
private Integer id;
@TableField("last_name")
private String lastName;
@TableField("email")
private String email;
@TableField("gender")
private String gender;
@TableField("age")
private Integer age;
}
mapper
@Mapper
public interface EmployeeMapper extends BaseMapper<Employee> {
List<Employee> selectByNameAndGender(String name,String gender);
}
为什么在测试时,注入的mapper为Null
需要引入@RunWith(SpringRunner.class)
@SpringBootTest
@RunWith(SpringRunner.class)
@Slf4j
public class EmployeeTest {
@Resource
private FilmMapper filmMapper;
@Test
public void test2(){
List<Film> films = filmMapper.selectList(null);
log.info("结果[{}]",films);
}
}
分页
注意要mybatis-plus3.4.0版本才可以用新版的分页工具
config
@Configuration
public class MybatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// 添加分页插件
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
page输入当前页和每页记录数
@Test
public void pageEmployTest(){
Page<Employee> page = new Page<Employee>(1,5);
Page<Employee> employeePage = employeeMapper.selectPage(page, null);
log.info("分页结果:[{}]",employeePage.getRecords());
log.info("当前页:{},总页数:{},记录数,{}",employeePage.getCurrent(),
employeePage.getPages(),employeePage.getTotal());
}
旧版本的拦截器:
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
// 设置请求的页面大于最大页后操作, true调回到首页,false 继续请求 默认false
// paginationInterceptor.setOverflow(false);
// 设置最大单页限制数量,默认 500 条,-1 不受限制
// paginationInterceptor.setLimit(500);
// 开启 count 的 join 优化,只针对部分 left join
paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
return paginationInterceptor;
}
性能分析插件
3.2.0版本以下可以用PerformanceInterceptor,以上的建议用第三方扩展插件 执行 SQL 分析打印
com.mysql.jdbc.Driver 是mybatis-connection-java 5 及 5 以下的
com.mysql.cj.jdbc.Driver是mybatis-connection-Java 6 及以上的
这里以第三方插件为例
依赖:
<!--sql 分析打印-->
<dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.8.2</version>
</dependency>
application.yml修改
注意url处添加了p6spy和driver-class-name也是p6spy
#mysql
datasource:
url: jdbc:p6spy:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&serverTimezone = Asia/Shanghai
username: root
password: 123456
driver-class-name: com.p6spy.engine.spy.P6SpyDriver
添加文件spy.properties
按自己需要修改driverlist
module.log=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台,解开注释就行了
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 指定输出文件位置
logfile=sql.log
# 使用日志系统记录 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,batch,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动可多个
driverlist=com.mysql.cj.jdbc.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2
控制台会打印
Consume Time:19 ms 2022-07-10 10:56:41
Execute SQL:SELECT COUNT(1) FROM tbl_employee
联表查询
1、创建VO类,把两个表中你需要的字段提取出来
@Data
public class EmployeeVo {
private String name;
private String lastName;
}
2、在mapper中,用#{}作为条件,进行筛选
@Mapper
public interface EmployeeMapper extends BaseMapper<Employee> {
@Select("select a.name,b.last_name\n" +
"from film a,tbl_employee b where a.id=#{id} and a.id=b.id")
List<EmployeeVo> list(Integer id);
}
常用条件:
eq:等于 =
ne:不等于 <>
gt:大于 >
ge:大于等于 >=
lt:小于 <
le:小于等于 <=
between:BETWEEN 值1 AND 值2
notBetween:NOT BETWEEN 值1 AND 值2
like:LIKE '%值%'
notLike:NOT LIKE '%值%'
likeLeft:LIKE '%值'
likeRight:LIKE '值%'
isNull:字段 IS NULL
isNotNull: 字段 IS NULL
in:字段 IN
notIn:字段 NOT IN
inSql:字段 IN ( sql语句 )
notInSql:字段 NOT IN ( sql语句 )
groupBy:分组:GROUP BY 字段, ...
orderByAsc:排序:ORDER BY 字段, ... ASC
orderByDesc 排序:ORDER BY 字段, ... DESC
orderBy:排序:ORDER BY 字段, ...
having:HAVING ( sql语句 )
.stream().forEach(x -> x.setColumn(StrUtil.toUnderlineCase(x.getColumn())));
list.forEach()
可以实现List的iterator()和Iterator的next(),这样就可以改变遍历的顺序了,而list.stream().forEach()则不行。list.stream().forEach()适用于非list
为了避免不必要的麻烦,在单纯只想迭代集合的场景下,应该直接使用forEach。
stream()是Collection接口的一个方法,但是Map接口并没有继承Collection,所以Map没有map.stream().forEach()
BeanUtil.copyProperties(a, assetVo);复制类