Spring Boot multiple data source with SpringPhysicalNamingStrategy

Question

Require Spring boot application with multiple data source configured. Multiple data source configuration is working with separate datasource, entityManager and transactionManager.

Also hibernate naming configuration is working with single data source with below configuration. spring.jpa.hibernate.naming.physical-strategy=org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy

The above configuration can be provided with JPA properties to entityManager. But not able to add dynamic table name (from application.properties) in SpringPhysicalNamingStrategy.

Answer 1

I have created a custom physical naming strategy. It can change names with environment variables. Also, it can change column names too. Name identifier will be decided by the database catalog name. You can change selection criteria with using jdbcEnvironment. If you put any criteria option text will be the property value.

Application Properties

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

spring.jpa.show-sql=true

spring.jpa.hibernate.naming.physical-strategy=com.example.demo.DynamicPhysicalNamingStrategy

# dynamic
user.table.name=TESTDB:TestUser,TESTDB2:TestUser2
user.column.name=username

User Entity

@Table(name = "${user.table.name}")
@Entity
@Data
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

    @Column(name = "${user.column.name}")
    private String name;

    private String email;
}

DynamicPhysicalNamingStrategy

package com.example.demo;

import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.boot.model.naming.PhysicalNamingStrategy;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;
import org.springframework.util.Assert;

import java.util.Locale;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * It is copied from {@link org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl}
 */
@Component
public class DynamicPhysicalNamingStrategy implements PhysicalNamingStrategy, ApplicationContextAware {
    private final Pattern VALUE_PATTERN = Pattern.compile("^\\$\\{([\\w.]+)}$");
    private Environment environment;

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

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

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

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

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

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

        // Custom Implementation Start
        String text = name.getText();
        Matcher matcher = VALUE_PATTERN.matcher(text);
        if (matcher.matches()) {
            String propertyKey = matcher.group(1);
            text = environment.getProperty(propertyKey);
            Assert.notNull(text, "Property is not found '" + propertyKey + "'");

            // extract catalog selection part
            // Example:
            // Current Catalog: TESTDB
            // Property: TESTDB:TestUser, TESTDB2:TestUser
            // Text will be TestUser
            Pattern catalogPattern = Pattern.compile(jdbcEnvironment.getCurrentCatalog().getText() + ":([^,]+)");
            Matcher catalogMatcher = catalogPattern.matcher(text);
            if (catalogMatcher.find()) {
                text = catalogMatcher.group(1);
            }

            // Caution: You can remove below return function, if so text will be transformed with spring advice
            return getIdentifier(text, name.isQuoted(), jdbcEnvironment);
        }
        // Custom Implementation End


        StringBuilder builder = new StringBuilder(text.replace('.', '_'));
        for (int i = 1; i < builder.length() - 1; i++) {
            if (isUnderscoreRequired(builder.charAt(i - 1), builder.charAt(i), builder.charAt(i + 1))) {
                builder.insert(i++, '_');
            }
        }
        return getIdentifier(builder.toString(), name.isQuoted(), jdbcEnvironment);
    }

    /**
     * Get an identifier for the specified details. By default this method will return an
     * identifier with the name adapted based on the result of
     * {@link #isCaseInsensitive(JdbcEnvironment)}
     *
     * @param name            the name of the identifier
     * @param quoted          if the identifier is quoted
     * @param jdbcEnvironment the JDBC environment
     * @return an identifier instance
     */
    protected Identifier getIdentifier(String name, boolean quoted, JdbcEnvironment jdbcEnvironment) {
        if (isCaseInsensitive(jdbcEnvironment)) {
            name = name.toLowerCase(Locale.ROOT);
        }
        return new Identifier(name, quoted);
    }

    /**
     * Specify whether the database is case sensitive.
     *
     * @param jdbcEnvironment the JDBC environment which can be used to determine case
     * @return true if the database is case insensitive sensitivity
     */
    protected boolean isCaseInsensitive(JdbcEnvironment jdbcEnvironment) {
        return true;
    }

    private boolean isUnderscoreRequired(char before, char current, char after) {
        return Character.isLowerCase(before) && Character.isUpperCase(current) && Character.isLowerCase(after);
    }

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

Test

package com.example.demo;

import com.example.demo.entity.User;
import com.example.demo.repository.UserRepository;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class DemoApplicationTests {
    @Autowired
    private UserRepository userRepository;

    @Test
    void contextLoads() {
        User user = new User();
        user.setName("test");
        user.setEmail("[email protected]");
        userRepository.save(user);

        userRepository.findAll();
    }

}

Hibernate: call next value for hibernate_sequence
Hibernate: insert into testuser (email, username, id) values (?, ?, ?)
Hibernate: select user0_.id as id1_0_, user0_.email as email2_0_, user0_.username as username3_0_ from testuser user0_