解决瀚高数据库(PostgreSQL)中com.highgo.jdbc.util.PSQLException: ERROR: function XXX does not exist

1. 前言

在使用PostgreSQL数据库的函数时，遇到“function XXX does not exist”的错误提示，通常意味着数据库无法识别这样的函数调用。这通常是因为当前库无法调用public库中的函数导致。下面是一篇解决此问题的详细指南。

2. 场景模拟

2.1 创建数据表

DROP TABLE IF EXISTS "test"."t_point";
CREATE TABLE "test"."t_point" (
  "int_id" int8,
  "longitude" varchar(255) COLLATE "pg_catalog"."default",
  "latitude" varchar(255) COLLATE "pg_catalog"."default"
)
;

INSERT INTO "test"."t_point" VALUES (1,  '120.80826', '115.07296');
INSERT INTO "test"."t_point" VALUES (2,  '10.12345', '10.56789');
INSERT INTO "test"."t_point" VALUES (3,  '785.45680', '456.75465');

2.2 编写代码

1. 配置pom.xml

瀚高依赖：

<!--highgo-->
<dependency>
    <groupId>com.highgo</groupId>
    <artifactId>HgdbJdbc</artifactId>
    <version>6.2.2</version>
</dependency>

或 配置PostgreSQL依赖：

<!--postgresql-->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

2. 配置application.yaml文件

如果是瀚高依赖则配置文件为：

spring:
  datasource:
    dynamic:
      primary: db1
      datasource:
        db1:
          driver-class-name: com.highgo.jdbc.Driver
          url: jdbc:highgo://localhost:5866/highgo?currentSchema=test&stringtype=unspecified
          username: postgres
          password: postgres

如果是PostgreSQL依赖则配置为：

spring:
  datasource:
    dynamic:
      primary: db1
      datasource:
        db1:
          driver-class-name: org.postgresql.Driver
          url: jdbc:postgresql://localhost:5866/highgo?currentSchema=test&stringtype=unspecified
          username: postgres
          password: postgres

 3. 编写Controller层

@RestController
@RequiredArgsConstructor
public class PositionController {
    private final PositionService positionService;

    @GetMapping("/getAllPosition")
    public List<Position> getAllPosition() {
        return positionService.getAllPosition();
    }
}

4. 编写Service层

public interface PositionService extends IService<Position> {
    List<Position> getAllPosition();
}

@Service
@RequiredArgsConstructor
public class PositionServiceImpl extends ServiceImpl<PositionMapper, Position> implements PositionService {
    private final PositionMapper positionMapper;

    @Override
    public List<Position> getAllPosition() {
        return positionMapper.getAllPosition();
    }
}

5. 编写Mapper层

@Mapper
public interface PositionMapper extends BaseMapper<Position> {
    List<Position> getAllPosition();
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zjp.demo.mapper.PositionMapper">
    <select id="getAllPosition" resultType="com.zjp.demo.pojo.Position">
        SELECT
            *
        FROM
            t_fl_fire_rescue_station
        WHERE
            longitude IS NOT NULL
          AND latitude IS NOT NULL
        ORDER BY
            round(
                    st_distancesphere (
                            geometry ( POINT ( 0:: DOUBLE, 0:: DOUBLE ) ),
                            geometry ( POINT ( longitude :: DOUBLE, latitude :: DOUBLE ) )
                    ),
                    2
            );
    </select>
</mapper>

2.3 测试代码

1. 启动项目，浏览器访问http://localhost:8080/getAllPosition，界面报错为500。

2. 查看后端日志，日志报错为：com.highgo.jdbc.util.PSQLException: ERROR: function geometry(point) does not exist

 3. 测试SQL语句，SQL语句正常。

 3. 错误分析

错误信息明确指出没有与给定名称和参数类型相匹配的函数。这意味着尝试调用的函数在当前数据库上下文中不可用。

4. 解决方案

在pom.xml里currentSchema中额外添加public库，以上述案例问题为例，其解决方式为：

如果是瀚高依赖则配置文件为：

spring:
  datasource:
    dynamic:
      primary: db1
      datasource:
        db1:
          driver-class-name: com.highgo.jdbc.Driver
          url: jdbc:highgo://localhost:5866/highgo,public?currentSchema=test&stringtype=unspecified
          username: postgres
          password: postgres

 如果是PostgreSQL依赖则配置为：

spring:
  datasource:
    dynamic:
      primary: db1
      datasource:
        db1:
          driver-class-name: org.postgresql.Driver
          url: jdbc:postgresql://localhost:5866/highgo,public?currentSchema=test&stringtype=unspecified
          username: postgres
          password: postgres