CODEWITHSUNDEEP
postgresqlhibernatespring-bootpostgis
Tirlipirli
Tirlipirli

Reputation: 113

Default schema for native SQL queries (spring-boot + hibernate + postgresql + postgis)

I am introducing spring to the existing application (hibernate has already been there) and encountered a problem with native SQL queries.

A sample query:

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

OUR_TABLE is in OUR_SCHEMA.

When we connect to the db to OUR_SCHEMA:

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

the query fails because function ST_MAKEPOINT is not found - the function is located in schema: PUBLIC.

When we connect to the db without specifying the schema, ST_MAKEPOINT is found and runs correctly, though schema name needs to be added to the table name in the query.

As we are talking about thousands of such queries and all the tables are located in OUR_SCHEMA, is there a chance to anyhow specify the default schema, so still functions from PUBLIC schema were visible?

So far, I have tried the following springboot properties - with no success: 

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

Also, it worked before switching to springboot config - specifying hibernate.default-schema = OUR_SCHEMA in persistence.xml was enough.

Stack:

spring-boot: 2.0.6

hibernate: 5.3.1.Final

postgresql: 42.2.5

postgis: 2.2.1

Upvotes: 2

Views: 5281

Answers (3)

gavenkoa
gavenkoa

Reputation: 48723

JDBC param currentSchema explicitly allows specifying several schemas separating them by commas:

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

From https://jdbc.postgresql.org/documentation/head/connect.html

currentSchema = String

Specify the schema (or several schema separated by commas) to be set in the search-path. This schema will be used to resolve unqualified object names used in statements over this connection.

Note you probably need Postgres 9.6 or better for currentSchema support.

PS Probably better solution is to set search_path per user:

ALTER USER myuser SET search_path TO mydb,pg_catalog;

Upvotes: 4

Vladimir Gurevich
Vladimir Gurevich

Reputation: 171

if you use hibernate.default_schema, then for native queries, you need to provide the {h-schema} placeholder, something like that 

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

Upvotes: 2

Shay Rojansky
Shay Rojansky

Reputation: 16672

You're probably looking for the PostgreSQL search_path variable, which controls which schemas are checked when trying to resolve database object names. The path accepts several schema names, which are checked in order. So you can use the following

SET search_path=our_schema,public;

This will make PostgreSQL look for your tables (and functions!) first in our_schema, and then in public. Your JDBC driver may or may not support multiple schemas in its current_schema parameter.

Another option is to install the PostGIS extension (which provides the make_point() function) in the our_schema schema:

CREATE EXTENSION postgis SCHEMA our_schema;

This way you only have to have one schema in your search path.

Upvotes: 3

Related Questions