mybatis+PageHelper 多数据库分页
1.JDBCTemplate基本与jdbc连接数据库的代码差不多，要支持多数据库，最关键的是分页的语句。解决思路：编写一个统一处理sql的方法，接收业务SQL，根据不同的数据库类型，返回最终处理好的分页SQL。@Repositorypublic class BaseDao {/*** 处理业务SQL为可分页SQL** @param sql 业务sql* @param start 开始索引 = (页
·
底层分页SQL：
示例 start=10，end=20 查第11-20的数据
public String searchByPage(String sql, int start, int end, List<Object> valueList) {
String ret = "";
String dbType = ConfigUtil.getInstance().getDbType();
switch (dbType) {
case "oracle":
ret = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (" + sql + ") A WHERE ROWNUM <= ? ) TEMP WHERE RN > ?";
valueList.add(end);
valueList.add(start);
break;
case "dm":
ret = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (" + sql + ") A WHERE ROWNUM <= ? ) TEMP WHERE RN > ?";
valueList.add(end);
valueList.add(start);
break;
case "db2":
ret = "SELECT * FROM (SELECT TEMP.*, ROW_NUMBER() OVER() AS NUM FROM (" + sql + ") TEMP ) WHERE NUM <= ? AND NUM > ?";
valueList.add(end);
valueList.add(start);
break;
case "sqlserver":
sql = sql.replaceFirst("select", "");
sql = sql.substring(0,sql.lastIndexOf("order by"));
String str = sql.substring(0,sql.indexOf(","));
ret = "SELECT * FROM (SELECT row_number() OVER (ORDER BY " + str + " DESC ) AS row," + sql + " ) tt WHERE tt.row BETWEEN ? AND ?";
byte bytes[] = {(byte) 0xC2,(byte) 0xA0};
String UTFSpace;
try {
UTFSpace = new String(bytes,"utf-8");
ret = ret.replaceAll(UTFSpace, " ");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
valueList.add(start+1);
valueList.add(end);
break;
case "mysql":
ret = sql + " LIMIT ?, ?";
valueList.add(start);
valueList.add(end - start);
break;
// GBase 8s 中分页支持两种
// 1.使用SKIP m FIRST/LIMIT n
// 2.TOP m,n
case "gbase8s":
ret = sql + " skip " + start + " first " + (end - start);
break;
case "gbase8t":
ret = sql + " LIMIT ?, ?";
valueList.add(start);
valueList.add(end - start);
break;
// 人大金仓，神通分页支持两种：A为查询条数，B为查询起点
// 1. SELECT * FROM TABLE1 LIMIT A ;
// 2. SELECT * FROM TABLE1 LIMIT A OFFSET B;
default:
ret = "not support db type, is " + dbType;
break;
}
return ret;
}
分页
1.JDBCTemplate
基本与jdbc连接数据库的代码差不多，要支持多数据库，最关键的是分页的语句。
解决思路：编写一个统一处理sql的方法，接收业务SQL，根据不同的数据库类型，返回最终处理好的分页SQL。
@Repository
public class BaseDao {
public String searchByPage(String sql, int start, int end, List<Object> valueList) {
// 如上
}
}
2. mybatis之mapper动态代理
参考文档：
MyBatis-16MyBatis动态SQL之【支持多种数据库】
mybatis多数据库兼容改造
oracle,mysql都可以用concat()函数进行连接，oracle的concat()只能传入两个参数，而mysql的可以传入多个参数，sqlserver则不能用concat()函数 ，只能用+来进行字符串的连接。
-
解决方式一：同一个mapper.xml，根据属性databaseId区分不同数据库
缺点：同样的sql要写很多份，文件异常庞大臃肿。不建议
升级：拆成多个xml。不建议 -
解决方式二：根据不同的数据库，选用不同的mapper.xml
mapper映射采用在mybatis的配置文件中指定路径进行批量映射。
实现方式：通过配置文件读取到数据库类型，统一转换大小写，作为文件夹名称，每个数据库都有一个对应的存放mapper.xml的文件夹。
缺点：这种办法导致mapper.xml好几份，差别不大，很浪费，不建议使用。
升级：使用插件 -
解决方式三：使用分页插件
-
- PageHelper
原理：通过拦截器，在SQL真正执行前拦截下来，重新改造（eg:改为分页SQL），再继续执行改造好的SQL。
- PageHelper
参考文章
- https://www.cnblogs.com/vandusty/archive/2020/05/12/12872978.html
- https://www.csdn.net/gather_2f/MtjaIgwsNDA5MDMtYmxvZwO0O0OO0O0O.html
- https://blog.csdn.net/houysx/article/details/80229991
Mybatis
1. Mybatis
MyBatis分页提供的是逻辑分页，每次将所有数据查询出来，存储到内存中，然后根据请求的页码，进行逐页返回。
2. Mybatis-Plus
官方介绍：https://baomidou.com/guide
集成mybatisplus后，简单的CRUD就不用写了，如果没有特别的sql，就可以不用mapper的xml文件的。
<!--mybatis-plus自动的维护了mybatis以及mybatis-spring的依赖，
在springboot中这三者不能同时的出现，避免版本的冲突，表示：跳进过这个坑-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.1.1</version>
</dependency>
2.1 dao
- dao层接口（UserMapper）继承BaseMapper接口
继承BaseMapper接口之后，就可以使用mybatisPlus封装的一系列增删改查方法。
UserMapper.java
package com.example.mapper.auto;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.model.auto.User;
import java.util.List;
/**
* <p>
* Mapper 接口
* </p>
*
* @author astupidcoder
* @since 2020-05-13
*/
public interface UserMapper extends BaseMapper<User> {
public List<User> findAllUser();
}
测试：
@RunWith(SpringRunner.class)
@SpringBootTest
public class SampleTest {
@Autowired
private UserMapper userMapper;
@Test
public void testSelect() {
System.out.println(("----- selectAll method test ------"));
// UserMapper 中的 selectList() 方法的参数为 MP 内置的条件封装器 Wrapper，所以不填写就是无任何条件
List<User> userList = userMapper.selectList(null);
Assert.assertEquals(5, userList.size());
userList.forEach(System.out::println);
}
}
2.2 Service接口
- service接口（IUserService）继承IService接口
package com.example.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.model.auto.User;
import java.util.List;
/**
* <p>
* 服务类
* </p>
*
* @author astupidcoder
* @since 2020-05-13
*/
public interface IUserService extends IService<User> {
public List<User> findAllUser();
}
2.3 Service实现类
- service实现类（UserServiceImpl）继承ServiceImpl，并实现相应的service接口（IUserService）
package com.example.service.impl;
import com.example.model.auto.User;
import com.example.mapper.auto.UserMapper;
import com.example.service.IUserService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* <p>
* 服务实现类
* </p>
*
* @author astupidcoder
* @since 2020-05-13
*/
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
@Autowired
private UserMapper userMapper;
@Override
public List<User> findAllUser() {
return userMapper.findAllUser();
}
}
2.4 实体类
实体类用到的注解：
- 表名注解@TableName（value = …）
- 字段注解@TableField（value = …）
// 当数据库名与实体类名不一致或不符合驼峰命名时，需要在此注解指定表名
@TableName(value = "sys_user")
public class sysUser {
// 用来解决数据库中的字段和实体类的字段不匹配问题
@TableField(value = "create_time")
private Integet createTime;
// 用来解决实体类中有的属性但是数据表中没有的字段
@TableField(exist = false) // 默认为true
private Integet createTimeCn;
}
2.5 自定义sql
MP自带的条件构造器虽然很强大，有时候也避免不了写稍微复杂一点业务的sql，自定义的sql当然是写在XML文件中的啦，那么首先来定义xml文件的位置，在yml配置文件如下：
mybatis-plus:
# 如果是放在src/main/java目录下 classpath:/com/*/*/mapper/*Mapper.xml
# 如果是放在resource目录 classpath:/mapper/**.xml
mapper-locations: classpath:/mapper/**.xml
使用注解实现
/**
* @Auther: IT贱男
* @Date: 2019/6/10 14:40
* @Description: User对象持久层
*/
public interface UserMapper extends BaseMapper<User> {
/**
*
* 如果自定义的方法还希望能够使用MP提供的Wrapper条件构造器，则需要如下写法
*
* @param userWrapper
* @return
*/
@Select("SELECT * FROM user ${ew.customSqlSegment}")
List<User> selectByMyWrapper(@Param(Constants.WRAPPER) Wrapper<User> userWrapper);
/**
* 和Mybatis使用方法一致
* @param name
* @return
*/
@Select("SELECT * FROM user where name = #{name}")
List<User> selectByName(@Param("name") String name);
}
使用xml实现
/**
* @Auther: IT贱男
* @Date: 2019/6/10 14:40
* @Description: User对象持久层
*/
public interface UserMapper extends BaseMapper<User> {
/**
* 和Mybatis使用方法一致
* @param name
* @return
*/
List<User> selectByName(@Param("name") String name);
}
在xml文件中，namespace对应的是user模块的dao，id对应dao下面UserMapper接口的函数名，resultType定义返回的类型。
<?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.example.demo.mapper.UserMapper">
<select id="selectByName" resultType="com.example.demo.model.User">
SELECT * FROM user where name = #{name}
</select>
</mapper>
2.6 MyBatisPlus物理分页
官方介绍：https://baomidou.com/guide/page.html
3. 公共字段自动写入
例如创建时间、更新时间等字段。
package cn.com.infosec.netseal.webserver.config.mybatisPlus;
import cn.com.infosec.netseal.common.entity.po.BasePo;
import cn.com.infosec.netseal.common.util.DateUtil;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;
/**
* @Description mybatisPlus 公共字段自动写入
* @Date 2020/11/17 11:21
*/
@Component
public class CommonFieldAutoSetHandler implements MetaObjectHandler {
@Override
public void insertFill(MetaObject metaObject) {
if (metaObject.getOriginalObject() instanceof BasePo) {
long currentTime = DateUtil.getCurrentTime();
this.setFieldValByName("createTime", currentTime, metaObject);
this.setFieldValByName("updateTime", currentTime, metaObject);
}
}
@Override
public void updateFill(MetaObject metaObject) {
if (metaObject.getOriginalObject() instanceof BasePo) {
long currentTime = DateUtil.getCurrentTime();
this.setFieldValByName("updateTime", currentTime, metaObject);
}
}
}
4. 禁止全表更新或删除
package cn.com.infosec.netseal.webserver.config.mybatisPlus;
import com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.SqlExplainInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@EnableTransactionManagement(proxyTargetClass = true)
@Configuration
public class MybatisPlusConfig {
/**
* 乐观锁插件
*/
@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor() {
return new OptimisticLockerInterceptor();
}
/**
* 如果是对全表的删除或更新操作，就会终止该操作
*/
@Bean
public SqlExplainInterceptor sqlExplainInterceptor() {
return new SqlExplainInterceptor();
}
}
SpringBoot 整合PageHelper
maven依赖
<!-- pagehelper 分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.3.0</version>
<exclusions>
<exclusion>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
</exclusion>
</exclusions>
</dependency>
依赖版本分析
| pagehelper-spring-boot-starter | pagehelper | 支持的数据库及其方言 | |
|---|---|---|---|
| 版本 | 1.3.0 | 5.2.0 | mysql(mysql) oracle(oracle) sqlserver(sqlserver或sqlserver2012) db2(db2) 达梦(dm) 南大通用Gbase(infomix) 人大金仓KingBase(无方言，暂用postgresql) 等常用数据库及其他 |
| 版本 | 1.3.1 | 5.2.1 | 优化项： postgresql(postgresql) 神通(oscar) 优化项： 瀚高(higngo) 虚谷(xugu) |
通过源码对比发现，和实践证明：人大金仓和神通两个库，都可以用mysql的分页方式
| 数据库 | 实现类 | 分页方式 | |
|---|---|---|---|
| 5.2.0 | 人大金仓 | HsqldbDialect | SELECT * FROM TABLE1 LIMIT A OFFSET B; |
| 5.2.1 | 人大金仓 | PostgreSqlDialect | SELECT * FROM TABLE1 LIMIT A ; SELECT * FROM TABLE1 LIMIT A OFFSET B; |
| 5.2.0 | 神通 | MySqlDialect | SELECT * FROM TABLE1 LIMIT B,A; |
| 5.2.1 | 神通 | PostgreSqlDialect | SELECT * FROM TABLE1 LIMIT A ; SELECT * FROM TABLE1 LIMIT A OFFSET B; |
常见配置
PageHelper插件，不配置helperDialect时，默认从数据库连接里自动找方言，建议配置上helperDialect，避免自动找会出什么异常
#pageHelper 方言
# 不配置时，默认从数据库连接里自动找方言，配置值为上图里的方言，eg：mysql、oracle、db2、infomix、sqlserver、dm、oscar等。
helperDialect=oracle
#启用合理化，如果pageNum<1会查询第一页，如果pageNum>pages会查询最后一页
reasonable=true
#为了支持startPage(Object params)方法，增加了该参数来配置参数映射，用于从对象中根据属性名取值， 可以配置pageNum,pageSize,count,pageSizeZero,reasonable，
#不配置映射的用默认值， 默认值为pageNum=pageNum;pageSize=pageSize;count=countSql;reasonable=reasonable;pageSizeZero=pageSizeZero
params=count=countSql
#支持通过 Mapper 接口参数来传递分页参数，默认值false，分页插件会从查询方法的参数值中，自动根据上面 params 配置的字段中取值，查找到合适的值时就会自动分页
#在代码中直接调用：List<Country> list = countryMapper.selectByPageNumSize(user, 1, 10);
supportMethodsArguments=true
#如果 pageSize=0 就会查询出全部的结果（相当于没有执行分页查询）
pageSizeZero=true
自定义实现分页
PageHelper的分页逻辑是写在了AbstractHelperDialect类中，而不同的数据库分页实现是通过继承实现抽象方法的方式来实现的。
PageHelper的分页方言，默认是从数据库连接url地址里取双冒号之间的值，例如jdbc:kingbase://10.20.61.86:54321/NETSEAL，方言即kingbase。会遍历已注册的方言，挨个来匹配url，若匹配不到就会报错。
若遇到PageHelper不支持的数据库，可以自己实现（基本都支持了）。
- 在启动类排除PageHelperAutoConfiguration的加载，自定义同名Bean，实现addPageInterceptor方法；
创建PageInterceptor拦截器，指定数据库方言，添加进SqlSessionFactory拦截器链； - 自定义数据库方言类，继承AbstractHelperDialect，在PageAutoDialect中注册该类，并重写processPageParameter、getPageSql；
@PostConstruct：
Java中该注解的说明：@PostConstruct该注解被用来修饰一个非静态的void（）方法。被@PostConstruct修饰的方法会在服务器加载Servlet的时候运行，并且只会被服务器执行一次。PostConstruct在构造函数之后执行，init（）方法之前执行。
https://blog.csdn.net/qq360694660/article/details/82877222
启动类排除默认PageHelper启动类
@SpringBootApplication(exclude = { PageHelperAutoConfiguration.class})
public class WebServerApplication extends SpringBootServletInitializer {
public static void main(String[] args) {
SpringApplication.run(WebServerApplication.class, args);
System.out.println("服务已启动");
}
}
自定义同名Bean，添加拦截器
package cn.com.infosec.netseal.webserver.config.datasource;
import cn.com.infosec.netseal.common.config.ConfigUtil;
import cn.com.infosec.netseal.common.exceptions.ToLogException;
import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.boot.autoconfigure.MybatisAutoConfiguration;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Configuration;
import javax.annotation.PostConstruct;
import java.util.List;
import java.util.Properties;
/**
* @Description 自定义分页插件自动装配策略,使用自定义配置文件
* @Author lhx
*/
@Configuration
@AutoConfigureAfter(MybatisAutoConfiguration.class)
public class PageHelperAutoConfiguration {
@Autowired
private List<SqlSessionFactory> sqlSessionFactoryList;
@PostConstruct
public void addPageInterceptor() {
//pageHelper的sql分页拦截器
PageInterceptor interceptor = new PageInterceptor();
//读取自定义的配置文件
try {
Properties sqlDialect = ConfigUtil.getInstance().getSqlDialect();
// System.out.println(sqlDialect);
interceptor.setProperties(sqlDialect);
} catch (Exception e) {
throw new ToLogException("读取配置发生错误", e);
}
//将分页拦截器添加到mybatis的sqlsession
for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) {
sqlSessionFactory.getConfiguration().addInterceptor(interceptor);
}
}
}
自定义分页类示例
package cn.com.infosec.netseal.webserver.config.datasource;
import com.github.pagehelper.Page;
import com.github.pagehelper.dialect.AbstractHelperDialect;
import com.github.pagehelper.page.PageAutoDialect;
import com.github.pagehelper.util.MetaObjectUtil;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* @Description 示例人大金仓的分页，此处按mysql的写法
*
*/
@Component
public class KingBaseDialect extends AbstractHelperDialect {
public KingBaseDialect() {
// alias 不能瞎起名字，要写连接url里双冒号之间的值，例如jdbc:kingbase://10.20.61.86:54321/NETSEAL，alias即kingbase
PageAutoDialect.registerDialectAlias("kingbase", KingBaseDialect.class);
}
public Object processPageParameter(MappedStatement ms, Map<String, Object> paramMap, Page page, BoundSql boundSql, CacheKey pageKey) {
paramMap.put("First_PageHelper", page.getStartRow());
paramMap.put("Second_PageHelper", page.getPageSize());
pageKey.update(page.getStartRow());
pageKey.update(page.getPageSize());
if (boundSql.getParameterMappings() != null) {
List<ParameterMapping> newParameterMappings = new ArrayList(boundSql.getParameterMappings());
if (page.getStartRow() == 0L) {
newParameterMappings.add((new ParameterMapping.Builder(ms.getConfiguration(), "Second_PageHelper", Integer.TYPE)).build());
} else {
newParameterMappings.add((new ParameterMapping.Builder(ms.getConfiguration(), "First_PageHelper", Long.TYPE)).build());
newParameterMappings.add((new ParameterMapping.Builder(ms.getConfiguration(), "Second_PageHelper", Integer.TYPE)).build());
}
MetaObject metaObject = MetaObjectUtil.forObject(boundSql);
metaObject.setValue("parameterMappings", newParameterMappings);
}
return paramMap;
}
public String getPageSql(String sql, Page page, CacheKey pageKey) {
StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
sqlBuilder.append(sql);
if (page.getStartRow() == 0L) {
sqlBuilder.append("\n LIMIT ? ");
} else {
sqlBuilder.append("\n LIMIT ?, ? ");
}
return sqlBuilder.toString();
}
}
人大金仓精简1：
直接使用mysql，不再定义分页语句
@Component
public class KingBaseDialect extends AbstractHelperDialect {
public KingBaseDialect() {
// alias 不能瞎起名字，要写连接url里双冒号之间的值，例如jdbc:kingbase://10.20.61.86:54321/NETSEAL，alias即kingbase
PageAutoDialect.registerDialectAlias("kingbase", MySqlDialect.class);
}
}
人大金仓精简2：
在PageHelperAutoConfiguration 类的addPageInterceptor方法里直接注册：
@PostConstruct
public void addPageInterceptor() {
PageAutoDialect.registerDialectAlias("kingbase", MySqlDialect.class);
}
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
1
0- 0
已为社区贡献1条内容
所有评论(0)