Spring JPA: Providing Schema Name Dynamically

Question

I am building a CRUD application where I am using spring data jpa to work with Database. I don't want to give schema name in my entity class, like mentioned below.

 @Entity
 @Table(name = "PROPERTY",schema = "ABC")
 public class PropertyDTO extends BaseDTO{
     //all the properties and getter,setters
 }

When I will fetch any results using Repository, if I don't provide the schema name in My Entity class, it will throw an error, saying invalid object name PROPERTY, is there any other way where I can mention schema name and Framework will append the schema name while firing query?

Deification

Looks like HibernateInterceptor is one option. More info on #25284267 – Jacktar

Answer 1

If you need to change the schema name at the runtime, I recommend to use Hibernate multi-tenancy approach. You could find more details here and here

Cemetery

Answer 2

You can also use physical naming strategy like the example here

application.properties

spring.jpa.hibernate.naming.physical-strategy=com.example.persistencee.CustomDatabaseIdentifierNamingStrategy
property.schema.name=${PROPERTY_SCHEMA_NAME:abc}

CustomDatabaseIdentifierNamingStrategy

package com.example.persistence;

import lombok.extern.slf4j.Slf4j;
import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;
import org.springframework.beans.BeansException;
import org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;

import java.io.Serializable;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Component
@Slf4j
public class CustomDatabaseIdentifierNamingStrategy extends SpringPhysicalNamingStrategy implements ApplicationContextAware {

    private final Pattern VALUE_PATTERN = Pattern.compile("^\\$\\{([\\w.]+)}$");
    private Environment environment;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        environment = applicationContext.getBean(Environment.class);
    }

    @Override
    public Identifier toPhysicalSchemaName(Identifier name, JdbcEnvironment jdbcEnvironment) {
        return apply(name, jdbcEnvironment);
    }

    private Identifier apply(Identifier name, JdbcEnvironment jdbcEnvironment) {
        if (name == null) {
            return null;
        }

        String logicalText = name.getText();
        String physicalText = getPhysicalText(logicalText);
        if (physicalText != null) {
            log.info("Created database namespace [logicalName={}, physicalName={}]", logicalText, physicalText);
            return getIdentifier(physicalText, name.isQuoted(), jdbcEnvironment);
        }
        return null;
    }

    private String getPhysicalText(String logicalText) {
        String physicalText = null;
        Matcher matcher = VALUE_PATTERN.matcher(logicalText);
        if (matcher.matches()) {
            String propertyKey = matcher.group(1);
            physicalText = environment.getProperty(propertyKey);
            if (physicalText == null) {
                log.error("Environment property not found for key {}", propertyKey);
            }
        } else {
            log.error("Property key {} is not in pattern {}", logicalText, VALUE_PATTERN);
        }
        return physicalText;
    }
}

PropertyDTO

@Entity
@Table(name = "PROPERTY", schema = "${property.schema.name}")
public class PropertyDTO extends BaseDTO {
    // all the properties and getter, setters
}

Stutzman

CustomDatabaseIdentifierNamingStrategy - Created database namespace [logicalName=${property.schema.name}, physicalName=my_schema]} , still hitting ERROR: relation "config_details" does not exist – Puttee

Also tried this: #65660840 facing same error. – Puttee

There's a typo in the regex and the \w is redundant since you includs all characters using the . wildcard: "^\\$\\{([\\w.]+)}$" -> "^\\$\\{(.+)\\}$" – Karinakarine

SpringPhysicalNamingStrategy was deprecated & removed in favor of CamelCaseToUnderscoresNamingStrategy as of Spring Boot 3 – Karinakarine

Answer 3

Updated & simplified version of @sudha-chinnappa's answer. Props to her for the original answer.

As of Spring Boot 3, the SpringPhysicalNamingStrategy was removed in favor of CamelCaseToUnderscoresNamingStrategy.
There's also no need for the regex since you can take advantage of the Environment resolvePlaceholders method.
And finally, you can Autowire the Environment bean if you are using Spring Boot.

All together this significantly reduces the code needed.

Here is the updated code:

Application Context:

spring.jpa.hibernate.naming.physical-strategy=com.example.persistencee.CustomSchemaNamingStrategy
property.schema.name=${PROPERTY_SCHEMA_NAME:abc}

Updated Naming Strategy:

import org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy;
import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;

import lombok.extern.log4j.Log4j2;

@Component
@Log4j2
public class CustomSchemaNamingStrategy extends CamelCaseToUnderscoresNamingStrategy {

    @Autowired
    private Environment environment;

    @Override
    public Identifier toPhysicalSchemaName(Identifier name, JdbcEnvironment    jdbcEnvironment) {
        if (name == null) {
            return null;
        }
        return super.toPhysicalSchemaName(new Identifier(environment.resolvePlaceholders(name.getText()), name.isQuoted()), jdbcEnvironment);
    }
}

Entity:

@Entity
@Table(name = "PROPERTY", schema = "${property.schema.name}")
public class PropertyDTO extends BaseDTO {
    // all the properties and getter, setters
}

Karinakarine