一.springboot整合mybatis分页插件代码实现
- application.properties配置信息
#spring.datasource.username=root
#spring.datasource.password=y@888888
#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#spring.datasource.hikari.jdbc-url==jdbc:mysql://127.0.0.1:3306/mybatis-test?characterEncoding=utf-8&serverTimezone=GMT&useSSL=false
spring.redis.password=lvnao888888
spring.redis.database=6
spring.redis.host=127.0.0.1
spring.redis.port=6379
#datasource config
spring.datasource.hikari.jdbc-url=jdbc:mysql://127.0.0.1:3306/mybatis-test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.datasource.hikari.username=root
spring.datasource.hikari.password=y@888888
spring.datasource.hikari.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.hikari.minimum-idle=3
spring.datasource.hikari.maximum-pool-size=15
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.connection-test-query=SELECT 1
spring.datasource.hikari.pool-name=HikariCP
mybatis.config-location=classpath:mybatis-config.xml
mybatis.type-aliases-package=
mybatis.mapper-locations=classpath:mappers/*.xml
#mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
logging.config=classpath:logback-spring-${spring.profiles.active:local}.xml
logging.file.path=${user.home}/logs/${spring.application.name}
spring.application.name=springboot-mybatis
spring.profiles.active=local
- 日志文件配置信息
<?xml version="1.0" encoding="UTF-8"?>
<configuration debug="false">
<springProperty name="APP_NAME" scope="context" source="spring.application.name" />
<!-- 控制台输出 -->
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
<!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度%msg：日志消息，%n是换行符 -->
<pattern>%d{HH:mm:ss.SSS}[%thread] %-5level %logger{36}: %msg%n</pattern>
</encoder>
</appender>
<!-- 按照每天生成日志文件 -->
<appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
<file>${LOG_PATH}/${APP_NAME}.log</file>
<rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
<!--日志文件输出的文件名 -->
<fileNamePattern>${LOG_PATH}/${APP_NAME}-%d{yyyyMMdd}-%i.log.gz</fileNamePattern>
<!--日志文件保留天数 -->
<maxHistory>30</maxHistory>
<!--日志文件最大的大小 -->
<maxFileSize>30MB</maxFileSize>
</rollingPolicy>
<encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
<!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度%msg：日志消息，%n是换行符 -->
<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} : %msg%n</pattern>
</encoder>
</appender>
<!-- HTTP请求 -->
<logger name="org.apache.http" level="info" />
<!-- eureka client -->
<logger name="com.netflix.discovery" level="info" />
<!-- jetty -->
<logger name="org.eclipse.jetty" level="info" />
<!-- sql 打印 -->
<logger name="com.wanglian.letter.mapper" level="debug" />
<!--myibatis log configure -->
<logger name="com.apache.ibatis" level="INFO" />
<logger name="org.mybatis" level="INFO" />
<logger name="java.sql.Connection" level="INFO" />
<logger name="java.sql.Statement" level="INFO" />
<logger name="java.sql.PreparedStatement" level="INFO" />
<!-- spring log configure -->
<logger name="org.springframework.beans" level="info"></logger>
<logger name="org.springframework.jdbc" level="INFO"></logger>
<logger name="org.springframework" level="INFO"></logger>
<!-- spring cloud -->
<logger name="com.netflix.loadbalancer" level="info"></logger>
<logger name="com.codingapi.tx.netty" level="info"></logger>
<logger name="com.codingapi.tm.netty.service.impl" level="warn"></logger>
<!-- wanglian 日志打印 -->
<logger name="com.wanglian" level="debug"></logger>
<root level="debug">
<appender-ref ref="STDOUT" />
</root>
</configuration>
- mybatis-config.xml配置信息，此处需要注意标签的顺序不能改变
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- MyBatis 配置 -->
<settings>
<setting name="cacheEnabled" value="true" />
<setting name="lazyLoadingEnabled" value="false" />
<setting name="multipleResultSetsEnabled" value="" />
<setting name="useColumnLabel" value="true" />
<setting name="useGeneratedKeys" value="true" />
<setting name="autoMappingBehavior" value="FULL" />
<setting name="defaultExecutorType" value="REUSE" />
<setting name="defaultStatementTimeout" value="25" />
<setting name="safeRowBoundsEnabled" value="false" />
<setting name="localCacheScope" value="SESSION" />
<setting name="jdbcTypeForNull" value="OTHER" />
<setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString" />
<setting name="logImpl" value="SLF4J" />
<!-- 开启驼峰，开启后，只要数据库字段和对象属性名字母相同，无论中间加多少下划线都可以识别 -->
<setting name="mapUnderscoreToCamelCase" value="true" />
</settings>
<typeHandlers>
</typeHandlers>
<!-- 配置分页插件信息 -->
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="helperDialect" value="mysql"/>
</plugin>
</plugins>
</configuration>
- MybatisConfig 配置信息
package com.spring.demo.config;
import java.util.List;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.ibatis.mapping.DatabaseIdProvider;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.scripting.LanguageDriver;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.TypeHandler;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
import org.mybatis.spring.boot.autoconfigure.MybatisAutoConfiguration;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.ObjectProvider;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.ResourceLoader;
import com.github.pagehelper.PageHelper;
@Configuration
@MapperScan(value= {"com.spring.demo"}, sqlSessionTemplateRef="sessionTemplate")
@EnableConfigurationProperties(MybatisProperties.class)
public class MybatisConfig extends MybatisAutoConfiguration{
public MybatisConfig(MybatisProperties properties, ObjectProvider<Interceptor[]> interceptorsProvider,
ObjectProvider<TypeHandler[]> typeHandlersProvider,
ObjectProvider<LanguageDriver[]> languageDriversProvider, ResourceLoader resourceLoader,
ObjectProvider<DatabaseIdProvider> databaseIdProvider,
ObjectProvider<List<ConfigurationCustomizer>> configurationCustomizersProvider) {
super(properties, interceptorsProvider, typeHandlersProvider, languageDriversProvider, resourceLoader,
databaseIdProvider, configurationCustomizersProvider);
}
@Bean(name = "dataSource")
@ConfigurationProperties(prefix = "spring.datasource.hikari")
@Primary
public DataSource createDataSouce() {
return org.springframework.boot.jdbc.DataSourceBuilder.create().build();
}
@Bean(name = "sqlSessionFactory")
@Override
public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
return super.sqlSessionFactory(dataSource);
}
@Bean(name = "sessionTemplate")
@Primary
public SqlSessionTemplate createSqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
/**
* 分页插件配置
* @return
*/
@Bean
public PageHelper pageHelper() {
PageHelper pageHelper = new PageHelper();
Properties properties = new Properties();
properties.setProperty("offsetAsPageNum", "true");
properties.setProperty("rowBoundsWithCount", "true");
properties.setProperty("reasonable", "true");
pageHelper.setProperties(properties);
return pageHelper;
}
}
- UserMapper接口和实体类
UserMapper类
@Mapper
//@CacheNamespace(implementation = MybatisRedisCache.class )
public interface UserMapper {
@Select("select * from user where id = #{id}")
User findById(Integer id);
User findById1(Integer id);
@Select("select * from user")
Page<User> findAll();
}
User实体类
public class User implements Serializable{
private static final long serialVersionUID = 1L;
private Integer id;
private String userName;
private String realName;
private Byte sex;
private String mobile;
private String email;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getRealName() {
return realName;
}
public void setRealName(String realName) {
this.realName = realName;
}
public Byte getSex() {
return sex;
}
public void setSex(Byte sex) {
this.sex = sex;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "User [id=" + id + ", userName=" + userName + ", realName=" + realName + ", sex=" + sex + ", mobile="
+ mobile + ", email=" + email + "]";
}
}
- UserController类，进行测试的
@Slf4j
@RestController
public class UserController {
@Autowired
private UserMapper userMapper;
@PostMapping("/findById")
public User findById(Integer id) {
return userMapper.findById(id);
}
/**
* 测试分页查询
*/
@PostMapping("/findAll")
public User findById() {
log.info("分页查询");
PageHelper.startPage(2, 1);
Page<User> users = userMapper.findAll();
for (User user : users) {
System.out.println(user.toString());
}
return users.get(0);
}
}
- 测试结果与总结：在mapper中我们的fingAll接口是查询所有，并没有limit参数，而且只有一个sql语句，但是通过分页插件后查询了两个sql语句，说明分页插件通过拦截Executor的query方法，在执行原来的sql之前先生成了一个新的sql用于查询数量，然后又对原来的sql进行了修改，加上了limit参数信息最后把结果封装到page进行返回。
扫一扫
377
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
