SpringBoot + Mybatis + 达梦数据库 + PageHelper 实现分页

        随着国产化呼声越来越高，目前很多项目都需要进行国产化相关改造。除了中间件和组件的改造外，数据库的改造也是其中重要一环，网上很多相关适配文档都是千篇一律，存在各种问题或者需要而外引入其他的包，导致更改成本太大。

        下面来简单说说改动量比较小的一种方案，亲测确实可行，废话不多说，直接上代码：

1.达梦驱动程序安装，这里采用mafen本地化仓库结合maven指令安装到本地：

        达梦驱动(可以去官网下载对应版本)：DmJdbcDriver18.jar

        本地化打包指令：

mvn install:install-file -DgroupId=com.dm -DartifactId=DmJdbcDriver -Dversion=1.8.0 -Dpackaging=jar -Dfile=F:\DmJdbcDriver18.jar

        打包好后，存在本地仓库目录下：(根据个人配置本地仓库不同，而在不同路径下)

2..pom.xml 引入相关关键基础包：

        <dependency>
			<groupId>tk.mybatis</groupId>
			<artifactId>mapper-spring-boot-starter</artifactId>
			<version>2.1.5</version>
			<exclusions>
	            <exclusion>
	                <groupId>ch.qos.logback</groupId>
	                <artifactId>logback-classic</artifactId>
	            </exclusion>
	            <exclusion>
	                <groupId>com.fasterxml.jackson.core</groupId>
		    		<artifactId>jackson-databind</artifactId>
	            </exclusion>
	            <exclusion>
	                <groupId>com.fasterxml.jackson.core</groupId>
		    		<artifactId>jackson-core</artifactId>
	            </exclusion>
	  	 	</exclusions>
		</dependency>
		<dependency>
		    <groupId>org.mybatis.spring.boot</groupId>
		    <artifactId>mybatis-spring-boot-starter</artifactId>
		    <version>1.3.1</version>
		    <exclusions>
	            <exclusion>
	                <groupId>ch.qos.logback</groupId>
	                <artifactId>logback-classic</artifactId>
	            </exclusion>
	            <exclusion>
	                <groupId>com.fasterxml.jackson.core</groupId>
		    		<artifactId>jackson-databind</artifactId>
	            </exclusion>
	            <exclusion>
	                <groupId>com.fasterxml.jackson.core</groupId>
		    		<artifactId>jackson-core</artifactId>
	            </exclusion>
	  	 	</exclusions>
		</dependency>
        
        <dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper-spring-boot-starter</artifactId>
			<version>1.3.0</version>
		</dependency>
        
        <dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.4.6</version>
		</dependency>

		<!-- 数据库连接池 -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.10</version>
		</dependency>

		<dependency>
			<groupId>com.dm</groupId>
			<artifactId>DmJdbcDriver</artifactId>
			<version>1.8.0</version>
		</dependency>

3.application.yml 关键配置，其他配置根据个人项目决定：

spring:
  jackson:
    time-zone: GMT+8
    date-format: yyyy-MM-dd HH:mm:ss
  http:
    encoding:
      force: true
      charset: UTF-8
      enabled: true
  datasource:
    driverClassName: dm.jdbc.driver.DmDriver
    url: jdbc:dm://127.0.0.1:5236/TEST?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
    username: TESTUSER
    password: TEST@user168

logging:
  level:
    com.*: debug
    com.zaxxer.hikari.pool.HikariPool: info


mybatis:
  mapper-locations: classpath:/mapper/*.xml
  type-aliases-package: com.test.entity
  configuration:
    map-underscore-to-camel-case: true

#达梦分页PageHelper配置,配置不生效，不清楚具体原因
pagehelper:
  dialect: com.github.pagehelper.dialect.helper.OracleDialect
  reasonable: true
  helper-dialect: dm

4.数据库连接工厂配置:

package com.test.config;

import java.util.Properties;

import javax.sql.DataSource;

import org.apache.ibatis.plugin.Interceptor;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import com.github.pagehelper.PageInterceptor;

/**
 * 达梦数据库配置
 * 如果不加，分页不生效
 * @author    created by test
 * @date  2024年6月21日--上午10:07:45
 * @description
 * @action
 */
@Configuration
@MapperScan("com.test.mapper")
public class DMConfig {
	
	@Value("${mybatis.mapper-locations:/mapper/*.xml}")
	private String mapperLocation;
	
	@Value("${pagehelper.helper-dialect:dm}")
	private String helperDialect;
	
	@Bean
    public SqlSessionFactoryBean sqlSessionFactory(DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        //如果为多数据源，则添加以下配置
//        sessionFactory.setDataSource(multipleDataSource(db1(),db2()));
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(mapperLocation));
        // Configure PageHelper
        PageInterceptor pageInterceptor = new PageInterceptor();
        Properties properties = new Properties();
        //配置数据库dialect，pagehelper需要指定
        properties.setProperty("helperDialect", helperDialect); // Set the dialect
        pageInterceptor.setProperties(properties);

        sessionFactory.setPlugins(new Interceptor[]{pageInterceptor});
        return sessionFactory;
    }
	
}

接下来为业务代码：

1.controller：

@RestController
@RequestMapping(value = "/group")
public class controller {
    @Autowired
    private VideoGroupAuthService videoGroupAuthService;

    /**
     * 分组---查分组列表
     * @param videoAuthGroupQueryDto
     * @return
     */
    @RequestMapping(value="/getVideoAuthGroupInfo",method = RequestMethod.POST)
    public ResponseJson<?> getVideoAuthGroupInfo(@RequestBody VideoAuthGroupQueryDto videoAuthGroupQueryDto) {
        return ResponseJson.now(videoGroupAuthService.getVideoAuthGroupInfo(videoAuthGroupQueryDto));
    }
}

2.业务接口类：

import java.util.List;

import com.github.pagehelper.PageInfo;

public interface VideoGroupAuthService {
    
    public PageInfo<VideoAuthGroupInfoModel> getVideoAuthGroupInfo(VideoAuthGroupQueryDto videoAuthGroupQueryDto);
}

3.业务接口实现类：

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;

@Service
public class VideoGroupAuthServiceImpl implements VideoGroupAuthService {
    @Autowired
    DevVideoAuthGroupMapper devVideoAuthGroupMapper;

    @Override
    public PageInfo<VideoAuthGroupInfoModel> getVideoAuthGroupInfo(VideoAuthGroupQueryDto videoAuthGroupQueryDto) {

        PageHelper.startPage(videoAuthGroupQueryDto.getPageNumber(), videoAuthGroupQueryDto.getPageSize());
        return new PageInfo<>(devVideoAuthGroupMapper.getVideoAuthGroupInfo(videoAuthGroupQueryDto));
    }
}

4.数据库mapper：

public interface DevVideoAuthGroupMapper extends Mapper<DevVideoAuthGroup>, MySqlMapper<DevVideoAuthGroup> {

    List<VideoAuthGroupInfoModel> getVideoAuthGroupInfo(VideoAuthGroupQueryDto videoAuthGroupQueryDto);
}

5.数据库语句：

    <select id="getVideoAuthGroupInfo" resultType="com.test.model.VideoAuthGroupInfoModel">
        SELECT 
        id,
        .....
        FROM
        t_test
        WHERE 1=1
        <if test="test!= null and test!= ''">
            AND test like '%'||#{test}||'%'
        </if>
        
        ORDER BY time DESC
    </select>

到这就完成达梦数据库分页适配配置，操作结果如下：

请求参数：

{"pageNumber":1,"pageSize":1,"test":"test"}

后台数据库执行记录，已经实现分页：

Preparing: SELECT count(0) FROM test WHERE 1 = 1 AND test = ? 
Parameters: 1(String)
Total: 1
Preparing: SELECT * FROM test WHERE 1=1 AND test = ? ORDER BY time DESC ) TMP_PAGE) WHERE PAGEHELPER_ROW_ID <= ? AND PAGEHELPER_ROW_ID > ? 
Parameters: 1(String)
Total: 1

前端返回：

到此，完成springboot + DM + mybatis 分页适配改造

记录信息，以供来者避坑