学校网站建设实训,wordpress搭建公司网站,网站开发图片压缩,建设部网站社保联网“哲学家们只是用不同的方式解释世界#xff0c;而问题在于改变世界。” ——卡尔马克思 (Karl Marx) 解读#xff1a;马克思强调了实践的重要性#xff0c;主张哲学不仅要理解世界#xff0c;更要致力于改造世界。 本文我们引入 Mybatis Plus 作为 ORM #xff0c;并且使… “哲学家们只是用不同的方式解释世界而问题在于改变世界。” ——卡尔·马克思 (Karl Marx) 解读马克思强调了实践的重要性主张哲学不仅要理解世界更要致力于改造世界。 本文我们引入 Mybatis Plus 作为 ORM 并且使用 PgSQL 作为数据库实现一个自定义复合类型数组参数的自定义函数。
一、POM 依赖
!-- https://mvnrepository.com/artifact/org.postgresql/postgresql --
dependencygroupIdorg.postgresql/groupIdartifactIdpostgresql/artifactIdversion42.6.0/version
/dependency!-- https://mvnrepository.com/artifact/com.alibaba/druid --
dependencygroupIdcom.alibaba/groupIdartifactIddruid/artifactIdversion1.2.8/version
/dependency!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter --
dependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-boot-starter/artifactIdversion3.5.3.1/version
/dependency!-- https://mvnrepository.com/artifact/org.projectlombok/lombok --
dependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdversion1.18.30/versionscopeprovided/scope
/dependency!-- web --
dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-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 类它有一个成员 ListMySettingsDTO 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 ListMySettingsDTO 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 BaseTypeHandlerListMySettingsDTO {Overridepublic void setNonNullParameter(PreparedStatement ps, int i, ListMySettingsDTO 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 ListMySettingsDTO getNullableResult(ResultSet rs, String columnName) throws SQLException {return toList(rs.getArray(columnName));}Overridepublic ListMySettingsDTO getNullableResult(ResultSet rs, int columnIndex) throws SQLException {return toList(rs.getArray(columnIndex));}Overridepublic ListMySettingsDTO getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {return toList(cs.getArray(columnIndex));}private ListMySettingsDTO toList(Array pgArray) throws SQLException {if (pgArray null) {return null;}Object[] array (Object[]) pgArray.getArray();ListMySettingsDTO 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 ListHashMapString, Object dynamicSqlQuery(Param(dto) TestDTO dto);}
select iddynamicSqlQuery resultTypejava.util.HashMap statementTypeCALLABLEselect * FROM dynamic_sql_query(#{dto.mySettingsDTOList,jdbcTypeARRAY,typeHandlercom.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 函数正常执行并返回了预期结果。
