Add prefix to table names in liquibase using Java Spring JPA

Question

I'm searching for a way to automatically add a prefix to my table names when liquibase executes my xml files. For example, if this is my initial-changelog.xml:

 <changeSet id="initial_changeset" author="ME">
    <createTable tableName="EMP">
        <column name="ID" type="UUID">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="REFERENCE" type="VARCHAR(250)">
            <constraints nullable="false"/>
        </column>
    </createTable>

    <createIndex tableName="EMP" indexName="IDX_EMP_REFERENCE" unique="true">
        <column name="ESS_REFERENCE"/>
    </createIndex>

    <createTable tableName="COMPANY_CAR">
        <column name="ID" type="UUID">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="START_DATE" type="DATE">
            <constraints nullable="false"/>
        </column>
        <column name="END_DATE" type="DATE">
            <constraints nullable="false"/>
        </column>
    </createTable>
</changeSet>

I would like to have my tables generated with a specific prefix, for example:

TBL_EMP
TBL_COMPANY_CAR

Can I specify a prefix in the XML? Or is there an other way to force it?

Also the tableName indexes should have the prefixes too...

I'm using liquibase with a MYSQL database using Java Spring JPA

TRY 1: I have tried this already but this doesn't overwrite the liquibase xml config:

hibernate.physical_naming_strategy=be.everesst.employee.adapter.jpa.config.CustomHibernateNamingStrategy

With this in my application.properties file

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.context.annotation.Configuration;

    @Configuration
    public class CustomHibernateNamingStrategy extends PhysicalNamingStrategy {
    
    public static final String TABLE_NAME_PREFIX = "EMPLOYEE_";
    
    @Override
    public Identifier toPhysicalTableName(final Identifier identifier, final JdbcEnvironment jdbcEnvironment) {
        return prefix(identifier);
    }

    private Identifier prefix(Identifier identifier) {
        return Identifier.toIdentifier(TABLE_NAME_PREFIX + identifier.getText());
    }
    
 }

TRY 2: I have tried this alsobut this doesn't overwrite the liquibase xml config aswell:

spring.jpa.hibernate.naming.physical-strategy=be.everesst.employee.adapter.jpa.config.PrefixPhysicalNamingStrategy

With this in my application.properties file

import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;

public class PrefixPhysicalNamingStrategy extends PhysicalNamingStrategyStandardImpl {

    public static final String TABLE_NAME_PREFIX = "EMPLOYEE_";

    @Override
    public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
        Identifier newIdentifier = new Identifier(TABLE_NAME_PREFIX + name.getText(), name.isQuoted());
        return super.toPhysicalTableName(newIdentifier, context);
    }

FOUND A SOLUTION of Liquibase itself: using the modifySql statement

(I removed all the custom hibernate naming strategies!)

I created 2 properties:

<property name="table.prefix" value="TBL_"/>
<property name="schema.name" value="PUBLIC"/>

Then added the following statements:

    <modifySql>
        <regExpReplace replace="CREATE\ TABLE\ ${schema.name}.([\w]*)\ (.*)" with="CREATE TABLE ${schema.name}.${table.prefix}$1 $2"/>
    </modifySql>

    <modifySql>
        <regExpReplace replace="CREATE\ UNIQUE\ INDEX\ ${schema.name}.([\w]*)\ ON\ PUBLIC.([\w]*)\((.*)\)" with="CREATE UNIQUE INDEX ${schema.name}.$1 ON ${schema.name}.${table.prefix}$2($3)"/>
    </modifySql>

    <modifySql>
        <regExpReplace replace="CREATE\ INDEX\ ${schema.name}.([\w]*)\ ON\ ${schema.name}.([\w]*)\((.*)\)" with="CREATE INDEX ${schema.name}.$1 ON ${schema.name}.${table.prefix}$2($3)"/>
    </modifySql>

why don't you just name them with the prefix in the changelog? — aBnormaLz, Jul 27 '20 at 14:51
We are working on a large scale applications with multiple micro services and multiple contexts, so we don't want to manually rename the prefix if needed...the count of tables will get very big... — Thomas Verhoeven, Jul 27 '20 at 14:59
well as I see there are only create table changes in the sets, so you don't need to rename any tables. Also you are using liquibase so you don't need to do it manually. Why do you need the prefix? Will multiple microservice connect to the same database? If yes then you shouldn't https://stackoverflow.com/questions/43612866/microservices-with-shared-database-using-multiple-orms — aBnormaLz, Jul 27 '20 at 15:03
But this is just a simple example, it's not actual source code...We want our tables to have the prefix of the microservice, because it is possible that a microservices will create a table with the same name, without prefixes there is no structure for us...I'm not really asking for wether I need prefixes or not, I'm asking if you know a solution for doing so — Thomas Verhoeven, Jul 27 '20 at 15:06
Every microservices creates their own database with their own tables, but they can just have the same name, so no we don't have a shared database...we work event driven so we have tables which contains projection of other microservices — Thomas Verhoeven, Jul 27 '20 at 15:07
Does this answer your question? [Liquibase table name prefix](https://stackoverflow.com/questions/29099775/liquibase-table-name-prefix) — aBnormaLz, Jul 27 '20 at 15:10
This solution doesn't work...I have tried this, question is not answered as solved so I guess it didn't work — Thomas Verhoeven, Jul 27 '20 at 15:12
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/218700/discussion-between-abnormalz-and-thomas-verhoeven). — aBnormaLz, Jul 27 '20 at 15:16
Micro-services should be using separate databases, or otherwise at minimum a schema per service. — Mark Rotteveel, Jul 27 '20 at 18:09

Viktor Reinok · Answer 1 · 2020-07-27T15:23:27.860

-1

Create a configuration file: CustomHibernateNamingStrategy implemnts org.hibernate.boot.model.naming.PhysicalNamingStrategy

Add it to hibernate config with following property: hibernate.physical_naming_strategy

In the configuration file overwrite following method:

  @Override
  public Identifier toPhysicalTableName(final Identifier identifier, final JdbcEnvironment jdbcEnvironment) {
    return prefix(identifier);
  }

  private Identifier prefix(final Identifier identifier) {
    return Identifier.toIdentifier("TBL_" + identifier.getText());
  }

  @Override
  public Identifier toPhysicalCatalogName(final Identifier identifier, final JdbcEnvironment jdbcEnvironment) {
    return identifier;
  }

 
  @Override
  public Identifier toPhysicalColumnName(final Identifier identifier, final JdbcEnvironment jdbcEnvironment) {
    return identifier;
  }
 
  @Override
  public Identifier toPhysicalSchemaName(final Identifier identifier, final JdbcEnvironment jdbcEnvironment) {
    return identifier;
  }

I would recommend to overwrite all the methods of the interface to achieve consistent result.

edited Jul 27 '20 at 15:23

answered Jul 27 '20 at 15:04

Viktor Reinok

113
13

Is this using liquibase? – aBnormaLz Jul 27 '20 at 15:08
1

Your first method has some weird characters in it...Can you fix this? – Thomas Verhoeven Jul 27 '20 at 15:09
Yes, Using Liquibase. And the method name is fixed. – Viktor Reinok Jul 27 '20 at 15:13
Example src is enhanced. – Viktor Reinok Jul 27 '20 at 15:23
This doesn't overwrite the liquibase table naming unfortunately – Thomas Verhoeven Jul 27 '20 at 15:30
@ViktorReinok tried your changes still no prefix shown... – Thomas Verhoeven Jul 27 '20 at 15:33
Have You added the hibernate configuration? This only modifies the table is the DB by ORM. Please elaborate on "liquibase table naming", so I am able to understand what are You trying to achieve. – Viktor Reinok Jul 27 '20 at 15:34
I have added the hibernate configuration line to the application.properties file in my spring application...if I put a break point on the methodes they are reached but still overwritten bij the liquibase config (xml file) – Thomas Verhoeven Jul 27 '20 at 15:48

Add prefix to table names in liquibase using Java Spring JPA

1 Answers1