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/dbnameusername: postgrespassword: #################driver-class-name: org.postgresql.Drivertype: 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 $$
DECLAREmax_record type_my_setting ;
BEGIN-- Initialize max_record with the first element of the arraymax_record := arr[1];-- Loop through the array to find the record with the maximum item valueFOR i IN 2 .. array_length(arr, 1) LOOPIF arr[i].item > max_record.item THENmax_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>> {@Overridepublic 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);}@Overridepublic List<MySettingsDTO> getNullableResult(ResultSet rs, String columnName) throws SQLException {return toList(rs.getArray(columnName));}@Overridepublic List<MySettingsDTO> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {return toList(rs.getArray(columnIndex));}@Overridepublic 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 函数正常执行，并返回了预期结果。