本机SQL查询的默认模式(spring-boot+hibernate+postgresql+postgis)

Question

我将spring引入到现有的应用程序中(hibernate已经存在)，并遇到了本机SQL查询的问题。

示例查询：

SELECT ST_MAKEPOINT(cast(longitude as float), cast(latitude as float)) FROM 
OUR_TABLE;

OUR_TABLE位于OUR_SCHEMA中。

当我们将数据库连接到OUR_SCHEMA:时

spring.datasource.url: jdbc:postgresql://host:port/db_name?currentSchema=OUR_SCHEMA

查询失败，因为找不到函数ST_MAKEPOINT-该函数位于模式PUBLIC中。

当我们在不指定模式的情况下连接到数据库时，ST_MAKEPOINT会被找到并正确运行，尽管需要将模式名称添加到查询中的表名称中。

当我们谈论成千上万这样的查询，并且所有的表都位于OUR_SCHEMA中时，是否有机会指定默认模式，以便PUBLIC模式中的函数仍然可见？

到目前为止，我已经尝试了以下springboot属性，但没有成功：

spring.jpa.properties.hibernate.default_schema: OUR_SCHEMA
spring.datasource.tomcat.initSQL: ALTER SESSION SET CURRENT_SCHEMA=OUR_SCHEMA
spring.datasource.initSQL: ALTER SESSION SET CURRENT_SCHEMA=OUR_SCHEMA

此外，它在切换到springboot配置之前就起了作用——在persistence.xml中指定hibernate.default-schema=OUR_schema就足够了。

堆栈：

弹簧靴：2.0.6

休眠：5.3.1.最终

postgresql:42.25

postgis：2.2.1

Answer 1

您可能正在寻找PostgreSQLsearch_path变量，该变量控制在解析数据库对象名称时检查哪些模式。该路径接受多个模式名称，这些名称按顺序进行检查。所以你可以使用以下

SET search_path=our_schema,public;

这将使PostgreSQL首先在our_schema中查找表(和函数！)，然后在public中查找。JDBC驱动程序的current_schema参数可能支持也可能不支持多个模式。

另一个选项是在our_schema模式中安装PostGIS扩展(提供make_point()功能)：

CREATE EXTENSION postgis SCHEMA our_schema;

这样，您只需要在搜索路径中有一个模式。

Answer 2

JDBC参数currentSchema明确允许指定几个用逗号分隔的模式：

jdbc:postgresql://postgres-cert:5432/db?currentSchema=my,public&connectTimeout=4&ApplicationName=my-app

发件人https://jdbc.postgresql.org/documentation/head/connect.html

currentSchema=字符串

指定要在搜索路径中设置的架构(或用逗号分隔的多个架构)。此架构将用于解析此连接上的语句中使用的不合格对象名。

请注意，您可能需要Postgres 9.6或更高版本来支持currentSchema。

PS可能更好的解决方案是为每个用户设置search_path：

ALTER USER myuser SET search_path TO mydb,pg_catalog;

Answer 3

如果您使用hibernate.default_schema，那么对于本机查询，您需要提供{h-schema}占位符，类似于

SELECT ST_MAKEPOINT(cast(longitude as float), cast(latitude as float)) FROM {h-schema}OUR_TABLE;