CODEWITHSUNDEEP
javahibernatespring-bootspring-data-jpahibernate-entitymanager
agaonsindhe
agaonsindhe

Reputation: 594

Spring JPA: Providing Schema Name Dynamically

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?

Upvotes: 6

Views: 17904

Answers (3)

ScrappyDev
ScrappyDev

Reputation: 2778

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
}

Upvotes: 2

Sudha Chinnappa
Sudha Chinnappa

Reputation: 1223

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
}

Upvotes: 2

Lungu Daniel
Lungu Daniel

Reputation: 844

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

Upvotes: 5

Related Questions