How to dynamically change schema in spring boot Entities?

Ajith Deivam

I am using spring boot(1.5.9 version) and MySQL,hibernate .In MySQL i am having two or more different schema ,Table1 present in schema1 and Table2 present in schema2,in my spring boot application.properties i have connected mysql schema1 database,i need to store the values in schema2.table2 in same port(3356).how to dynamically change the schema in Entity.

application.properties

spring.datasource.url=jdbc:mysql://localhost:3356/schema1
spring.datasource.username=root
spring.datasource.password=12345
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Entity:

@Entity
@Table(name = "table2", schema = "schema2")
public class Company implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "CompanyID")
    private Integer id;

    @Column(name = "CompanyName")
    private String companyname;
}

Error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'schema1.table2' doesn't exist
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_151]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_151]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_151]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_151]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998) ~[mysql-connector-java-5.1.44.jar:5.1.44]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_151]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_151]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_151]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_151]
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc-8.5.23.jar:na]
    at com.sun.proxy.$Proxy106.executeUpdate(Unknown Source) ~[na:na]

Alejandro

You need to define 2 different configuration:

spring.schema1.datasource.url=jdbc:mysql://localhost:3356/schema1
spring.schema1.datasource.username=root
spring.schema1.datasource.password=12345
spring.schema1.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.schema2.datasource.url=jdbc:mysql://localhost:3356/schema2
spring.schema2.datasource.username=root
spring.schema2.datasource.password=12345
spring.schema2.datasource.driver-class-name=com.mysql.jdbc.Driver

And after that, you need to make the config for each datasource:

package yorPackage;

import javax.persistence.EntityManagerFactory;
import javax.persistence.PersistenceContext;
import javax.sql.DataSource;

import org.hibernate.jpa.HibernatePersistenceProvider;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableAutoConfiguration
@EntityScan(basePackages = "yorPackage.entity")
@EnableJpaRepositories(transactionManagerRef = "schema1TransactionManager", entityManagerFactoryRef = "schema1EntityManagerFactory", basePackages = "yorPackage.dao")
public class Schema1DbConfig {

    @Bean(name = "schema1DataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.schema1.datasource")
    public DataSource schema1DataSource() {
        return DataSourceBuilder.create().build();
    }

    @PersistenceContext(unitName = "schema1")
    @Bean(name = "schema1EntityManagerFactory")
    @Primary
    public LocalContainerEntityManagerFactoryBean schema1EntityManagerFactory() {
        LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
        emf.setDataSource(schema1DataSource());
        emf.setPersistenceUnitName("schema1");
        emf.setPersistenceProvider(new HibernatePersistenceProvider());
        emf.setPackagesToScan("yorPackage");
        return emf;
    }

    @Bean(name = "schema1TransactionManager")
    @Primary
    public JpaTransactionManager schema1TransactionManager(@Qualifier("schema1EntityManagerFactory") final EntityManagerFactory emf) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(emf);
        return transactionManager;
     }
}

Collected from the Internet

Please contact [email protected] to delete if infringement.