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 函数正常执行,并返回了预期结果。
原文地址:https://blog.csdn.net/weixin_47560078/article/details/143240379
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!