How do I skip certain @Entity classes from being created as a table in h2 (in memory) database in Spring Boot?

Question

I am trying to build a Spring Boot application that uses 2 data sources. My primary database for now is the in-memory database (just for testing purposes) with tables that are populated with the help of the sql file that I have created. The other database(oracledb) has tables that are already populated.

What am I trying to achieve? I am trying to pull data from oracledb, process it and populate tables in h2 database.

What is the problem I am facing? I only want the entities for the h2 db be created as tables in the in-mem database (I want to be able to specify h2 which entities to scan and create tables, not all the classes that have @Entity annotation). All of the classes that have @Entity are being created as tables in my in-mem database and that is what I am trying to avoid.

What have I tried? I created two separate packages for the entities belonging to h2 and oracledb and using @EntityScan I only scanned the package that had entities for h2. This worked, however, I need the other entities for oracledb to be scanned so I am able to use them.

Code Snippet of what I have:

My applications.properties file

#spring.jpa.hibernate.ddl-auto=none
spring.jpa.generate-ddl=true

spring.h2.console.enabled=true
spring.h2.console.path=/h2


# first data source
spring.datasource.url=jdbc:h2:mem:mydb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

spring.application.name=Health Monitor


## 2nd data source
spring.production-datasource.url= jdbc:oracle://localhost:3306/db2
spring.production-datasource.driverClassName=oracle.jdbc.driver.OracleDriver
spring.production-datasource.username=abcd
spring.production-datasource.password=xyz123

DemoApplication.java

package com.automation.demo;

import java.sql.SQLException;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.Bean;

@SpringBootApplication
//@EntityScan( basePackages = {"domain"} )
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }


    @Bean(initMethod = "start", destroyMethod = "stop")
    public org.h2.tools.Server inMemoryH2DatabaseaServer() throws SQLException {
        return org.h2.tools.Server.createTcpServer(
          "-tcp", "-tcpAllowOthers", "-tcpPort", "9090");
    }



}

ConfigureDB.java

package com.automation.demo.configurations;

import javax.sql.DataSource;

import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

import com.zaxxer.hikari.HikariDataSource;

@Configuration
public class ConfigureDB {

    @Primary
    @Bean
    @ConfigurationProperties(prefix="spring.datasource")
    public DataSourceProperties devDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.configuration")
    public DataSource devDataSource() {
        return devDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();
    }

    @Bean
    @ConfigurationProperties("spring.production-datasource")
    public DataSourceProperties productionDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("spring.production-datasource.configuration")
    public DataSource productionDataSource() {

        return productionDataSourceProperties().initializeDataSourceBuilder()
                .type(HikariDataSource.class).build();

    }

}

Employee.java (for the h2 database)

package com.automation.demo.dev.entities;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="test")
public class Employee {

    // define fields
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="ID")
    private int id;

    @Column(name="FirstName")
    private String firstName;

    @Column(name="LastName")
    private String lastName;

    @Column(name="Email")
    private String email;


    // define constructors

    public Employee() {

    }

    public Employee(String firstName, String lastName, String email) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
    }

    // define getter/setter

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    // define tostring

    @Override
    public String toString() {
        return "Employee [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", email=" + email + "]";
    }

}

CustomerInfo.java (this is the entity representing a table in oracledb, I want to prevent a table representing this entity to be created in the h2 database)

package com.automation.demo.production.entities;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="customer")
public class CustomerInfo {

    // define fields
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="ID")
    private int id;

    @Column(name="FirstName")
    private String firstName;

    @Column(name="LastName")
    private String lastName;

    @Column(name="Email")
    private String email;


    // define constructors

    public CustomerInfo() {

    }

    public CustomerInfo(String firstName, String lastName, String email) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
    }

    // define getter/setter

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    // define tostring

    @Override
    public String toString() {
        return "Customer [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", email=" + email + "]";
    }

}

Please let me know if I have missed anything. Also, if there are any other mistakes I have made but are not related to my current problem please mention those as well.

Thanks.

Answer 1

1. Implement SchemaFilterProvider and SchemaFilter interfaces.
2. In SchemaFilter implementation, add coonditional check which table or on what condition you don't have to create table.
3. Define application.yml/ properties to point to the SchemaFilterProvider e.g schema_filter_provider: com.XXX.XXX.XXX.config.MySchemaFilterProvider

public class MySchemaFilterProvider implements SchemaFilterProvider {

@Override
public SchemaFilter getCreateFilter() {
    return MySchemaFilter.INSTANCE;
}

@Override
public SchemaFilter getDropFilter() {
    return MySchemaFilter.INSTANCE;
}

@Override
public SchemaFilter getMigrateFilter() {
    return MySchemaFilter.INSTANCE;
}

@Override
public SchemaFilter getValidateFilter() {
    return MySchemaFilter.INSTANCE;
}

}

public class MySchemaFilter implements SchemaFilter {

public static final MySchemaFilter INSTANCE = new MySchemaFilter();

@Override
public boolean includeNamespace(Namespace namespace) {
    return true;
}

@Override
public boolean includeTable(Table table) {
    if (table.getName().equals("XXXXXXXX")) {
        return false;
    }
    return true;
}

@Override
public boolean includeSequence(Sequence sequence) {
    return true;
}

}

Answer 2

Note - With Springboot the property is: spring.jpa.properties.hibernate.hbm2ddl.schema_filter_provider=xxx

Answer 3

Hibernate has a way to do this with SchemaFilterProvider.

SchemaFilterProvider

public class MySchemaFilterProvider implements SchemaFilterProvider {

    @Override
    public SchemaFilter getCreateFilter() {
        return MySchemaFilter.INSTANCE;
    }

    @Override
    public SchemaFilter getDropFilter() {
        return MySchemaFilter.INSTANCE;
    }

    @Override
    public SchemaFilter getMigrateFilter() {
        return MySchemaFilter.INSTANCE;
    }

    @Override
    public SchemaFilter getValidateFilter() {
        return MySchemaFilter.INSTANCE;
    }
}

SchemaFilter

public class MySchemaFilter implements SchemaFilter {

    public static final MySchemaFilter INSTANCE = new MySchemaFilter();

    @Override
    public boolean includeNamespace(Namespace namespace) {
        return true;
    }

    @Override
    public boolean includeTable(Table table) {
        if (table.getName().equals("customer")) {
            return false;
        }

        return true;
    }

    @Override
    public boolean includeSequence(Sequence sequence) {
        return true;
    }
}

Spring property configuration

spring.jpa.properties.hibernate.hbm2ddl.schema_filter_provider=MySchemaFilterProvider