Spring boot connect with multiple schemas in mysql

Question

I am not able to configure on how to connect to multiple schemas with spring boot. Until now with spring 4 and XML configuration I was able to only put the DB URL like: jdbc:mysql://180.179.57.114:3306/?zeroDateTimeBehavior=convertToNull and in the entity class specify the schema to use and thus able to connect to multiple schemas.

However with Spring Boot I am not able to achieve the same. If in application.properties I only specify the JDBC URL without schema it gives error:

No database selected

even though I have specified the schema name in entity class. Please suggest how can I achieve the same in Spring Boot? Thanks.

score 12 · Answer 1 · answered Feb 28 '18 at 20:37

The reason why it says "No database selected" is because you put the forward slash after the port number. This should work...

jdbc:mysql://180.179.57.114:3306?zeroDateTimeBehavior=convertToNull

I spent a lot of time on getting Hibernate to work with one MySQL instance and multiple schemas.

I ended up specifying my connection as:

jdbc:mysql://localhost:3306/schema1?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull

And my entities as:

@Entity    
@Table(name="table1", schema="schema1", catalog="schema1")
public class Table1 {
   @Id
   private int id;
}

@Entity
@Table(name="table2", schema="schema2", catalog="schema2")
public class Table2 {
   @Id
   private int id;
}

Apparently JDBC considers a MySQL schema as a catalog. I tried the above without specifying schema and it worked, however for integration tests I am using HSQL so I left the schema in the @Table definition.

Hopefully this helps someone.

But, how to handle if diff schema has diff username, password? — Satish Patro, Sep 04 '19 at 06:59
tell me how to configure and use 2nd schema in `application.properties` file if you know — mannedear, May 20 '22 at 07:31

akshaya pandey · Answer 2 · 2017-11-07T06:51:11.857

You can configure multiple datasource in the application.properties file and use approapriate datasource in your entity classes. Since, the sample example is already in the below mentioned link, I am directly pointing to the same. Please refer to the link below and check if it solves your problem:

https://www.ccampo.me/java/spring/2016/02/13/multi-datasource-spring-boot.html

Basically what it specifies is that you can configure multiple datasources and specify the desired one in your Entity

#first db
spring.datasource.url = [url]
spring.datasource.username = [username]
spring.datasource.password = [password]
spring.datasource.driverClassName = oracle.jdbc.OracleDriver

#second db ...
spring.secondDatasource.url = [url]
spring.secondDatasource.username = [username]
spring.secondDatasource.password = [password]
spring.secondDatasource.driverClassName = oracle.jdbc.OracleDriver

I didn't have to do anything as such with spring 4.0 and xml configuration must be a way to do same in spring boot as well , looking for that solution. — Jeets, Nov 07 '17 at 06:00

KayV · Answer 3 · 2017-11-07T07:11:27.177

Here is the code which you can use:

public static SessionFactory buildSessionFactory(DatabaseData dbData) {
    SessionFactory sessionFactory = null;
    try {

        if (dbData != null) {

            DriverManagerDataSource dataSource = new DriverManagerDataSource();
            dataSource.setDriverClassName(dbData.getDataSourceDriver());
            dataSource.setUrl("jdbc:mysql://" + dbData.getDatabaseIP() + ":" + dbData.getDatabasePort() + "/" + dbData.getDatabaseSchema()
                    + "?autoReconnect=true&useSSL=false");
            dataSource.setUsername(dbData.getDatabaseUserName());
            dataSource.setPassword(dbData.getDatabasePassword());

            LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();
            sessionFactoryBean.setDataSource(dataSource);

            Properties hibernateProperties = new Properties();

            hibernateProperties.put("hibernate.show_sql", false);

            sessionFactoryBean.setHibernateProperties(hibernateProperties);
            sessionFactoryBean.setPackagesToScan("com.***.***.entity");
            sessionFactoryBean.afterPropertiesSet();
            return sessionFactoryBean.getObject();
        }
    } catch (Exception ex) {
        logger.error("Initial SessionFactory creation failed.", ex);
        ex.printStackTrace();
        throw new ExceptionInInitializerError(ex);
    }
    return sessionFactory;
}

where , the DatabaseData class specifies as follows:

@Data
public class DatabaseData {

    private String databaseIP;
    private String databasePort;
    private String databaseName;
    private String databaseSchema;
    private String databaseUserName;
    private String databasePassword;
    private String dataSourceDriver;
    private int timeout;
}

And for each schema in your database, you can create an object of this class either by reading the properties from the properties file or by any other program. I hope this will solve your problem.

this is of some interest to me. If I get you correctly, I can connect to say 500 databases without having 500 connection parameters in my properties file? — jaletechs, Jul 31 '19 at 06:07

Spring boot connect with multiple schemas in mysql

3 Answers3

Linked