Mybatis Plus 集成 PgSQL 指南

“哲学家们只是用不同的方式解释世界，而问题在于改变世界。”

——卡尔·马克思 (Karl Marx)

解读：马克思强调了实践的重要性，主张哲学不仅要理解世界，更要致力于改造世界。

本文我们引入 Mybatis Plus 作为 ORM ，并且使用 PgSQL 作为数据库，实现一个自定义复合类型数组参数的自定义函数。

一、POM 依赖

<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.6.0</version>
</dependency>

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>

<!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.3.1</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.30</version>
    <scope>provided</scope>
</dependency>

<!-- web -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

二、配置 YML

spring:
  datasource:
    # 数据源基本配置
    url: jdbc:postgresql://127.0.0.1:5432/dbname
    username: postgres
    password: #################
    driver-class-name: org.postgresql.Driver
    type: com.alibaba.druid.pool.DruidDataSource

三、配置 Mybatis

package com.example.pgsqldemo.config;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Configuration;

/**
 * @version: V1.0
 * @author: 余衫马
 * @description: mybatis 配置
 * @data: 2024-10-25 14:25
 **/
@Configuration
@MapperScan("com.example.pgsqldemo.dao")
public class MybatisConfig {
}

四、实体类封装

模拟复合类型传参，创建 MySettingsDTO 类，

package com.example.pgsqldemo.dto;

import lombok.Data;

/**
 * @version: V1.0
 * @author: 余衫马
 * @description: 自定设置类 DTO
 * @data: 2024-10-25 14:42
 **/
@Data
public class MySettingsDTO {

    /**
     * 配置项
     */
    private String item;

    /**
     * 配置值
     */
    private String content;

}

创建 TestDTO 类，它有一个成员 List<MySettingsDTO> mySettingsDTOList

package com.example.pgsqldemo.dto;

import lombok.Data;

import java.util.List;

/**
 * @version: V1.0
 * @author: 余衫马
 * @description: 测试 DTO
 * @data: 2024-10-25 14:37
 **/
@Data
public class TestDTO {

    private List<MySettingsDTO> mySettingsDTOList;

}

五、数据库操作

创建复合类型，

CREATE TYPE type_my_setting AS (
    item text,
    content text
);

创建函数 dynamic_sql_query ，输出 item 字母顺序上最大的一行记录

CREATE OR REPLACE FUNCTION dynamic_sql_query (arr type_my_setting [])
RETURNS type_ptl_setting  AS $$
DECLARE
    max_record type_my_setting ;
BEGIN
    -- Initialize max_record with the first element of the array
    max_record := arr[1];

    -- Loop through the array to find the record with the maximum item value
    FOR i IN 2 .. array_length(arr, 1) LOOP
        IF arr[i].item > max_record.item THEN
            max_record := arr[i];
        END IF;
    END LOOP;

    RETURN max_record;
END;
$$ LANGUAGE plpgsql;

函数测试，

SELECT dynamic_sql_query (ARRAY[
    ROW('apple', 'content1')::type_my_setting ,
    ROW('banana', 'content2')::type_my_setting ,
    ROW('cherry', 'content3')::type_my_setting 
]);

六、自定义handler

处理复合类型数组

package com.example.pgsqldemo.handler;

import com.example.pgsqldemo.dto.MySettingsDTO;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.postgresql.util.PGobject;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * @version: V1.0
 * @author: 余衫马
 * @description: MySettings复合类型数组处理器
 * @data: 2024-10-25 17:07
 **/
public class MySettingsArrayTypeHandler extends BaseTypeHandler<List<MySettingsDTO>> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, List<MySettingsDTO> parameter, JdbcType jdbcType) throws SQLException {

        Connection conn = ps.getConnection();
        PGobject[] pgObjects = new PGobject[parameter.size()];

        // 每个对象都是 type_my_setting 复合类型
        for (int j = 0; j < parameter.size(); j++) {
            MySettingsDTO mySettingsDTO = parameter.get(j);
            PGobject pgObject = new PGobject();
            pgObject.setType("type_my_setting");
            pgObject.setValue(String.format("(%s,%s)", mySettingsDTO.getItem(), mySettingsDTO.getContent()));
            pgObjects[j] = pgObject;
        }
        // pgsql 复合数组类型 type_my_setting[]
        Array array = conn.createArrayOf("type_my_setting", pgObjects);
        ps.setArray(i, array);

    }

    @Override
    public List<MySettingsDTO> getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return toList(rs.getArray(columnName));
    }

    @Override
    public List<MySettingsDTO> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return toList(rs.getArray(columnIndex));
    }

    @Override
    public List<MySettingsDTO> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return toList(cs.getArray(columnIndex));
    }

    private List<MySettingsDTO> toList(Array pgArray) throws SQLException {
        if (pgArray == null) {
            return null;
        }
        Object[] array = (Object[]) pgArray.getArray();
        List<MySettingsDTO> list = new ArrayList<>();
        for (Object obj : array) {
            PGobject pgObject = (PGobject) obj;
            String[] values = Objects.requireNonNull(pgObject.getValue()).replace("(", "").replace(")", "").split(",");
            MySettingsDTO mySettingsDTO = new MySettingsDTO(values[0], values[1]);
            list.add(mySettingsDTO);
        }
        return list;
    }
}

七、Mapper 与 XML 编写

在 DAO 层新建一个动态查询方法 dynamicSqlQuery ，

package com.example.pgsqldemo.dao;

import com.example.pgsqldemo.dto.TestDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.HashMap;
import java.util.List;

/**
 * @version: V1.0
 * @author: 余衫马
 * @description: 测试 DAO
 * @data: 2024-10-25 14:26
 **/
@Mapper
public interface TestDao {

    public List<HashMap<String, Object>> dynamicSqlQuery(@Param("dto") TestDTO dto);

}

<select id="dynamicSqlQuery" resultType="java.util.HashMap" statementType="CALLABLE">
    select * FROM dynamic_sql_query(#{dto.mySettingsDTOList,jdbcType=ARRAY,typeHandler=com.example.pgsqldemo.handler.MySettingsArrayTypeHandler});
</select>

八、Postman测试

POST localhost:8080/api/dynamicSqlQuery

# 请求报文
{
    "mySettingsDTOList": [
        {
            "item": "AAAAA",
            "content": "BBBBB"
        },
        {
            "item": "123",
            "content": "456"
        },
        {
            "item": "ABC",
            "content": "FFFFFF"
        }
    ]
}

# 响应报文
[
    {
        "item": "ABC",
        "content": "FFFFFF"
    }
]

可以看到，传复合类型的数组参数可以被 SQL 函数正常执行，并返回了预期结果。