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?

Looks like HibernateInterceptor is one option. More info on https://stackoverflow.com/questions/25283767/how-to-use-spring-managed-hibernate-interceptors-in-spring-boot/25293683#25293683 — Kumar V, May 12 '20 at 22:25

score 3 · Accepted Answer · answered May 12 '20 at 20:21

3

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

answered May 12 '20 at 20:21

Lungu Daniel

816
2
8
15

score 0 · Answer 2 · answered Sep 10 '21 at 14:13

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
}

CustomDatabaseIdentifierNamingStrategy - Created database namespace [logicalName=${property.schema.name}, physicalName=my_schema]} , still hitting ERROR: relation "config_details" does not exist — Anup Singh, Jan 16 '22 at 08:26
Also tried this: https://stackoverflow.com/questions/65660340/spring-boot-multiple-data-source-with-springphysicalnamingstrategy/65665172#65665172 facing same error. — Anup Singh, Jan 16 '22 at 08:55

Spring JPA: Providing Schema Name Dynamically

2 Answers2

Linked