4

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.

Ivan Aracki
  • 4,861
  • 11
  • 59
  • 73
Jeets
  • 3,189
  • 8
  • 34
  • 50

3 Answers3

16

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.

Holzberg
  • 171
  • 1
  • 4
  • 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 – Suresh May 20 '22 at 07:31
  • Solved my problem. İt looks like spring boot needs default schema for the first connection, then defining other tables schema manually solves the problem – tsadigov Jun 22 '22 at 11:00
  • if both the schemas are present in same DB instance then it should be 100% acceptable answer, I tried it on my side it's working perfectly... – Ashish Choudhary Aug 14 '22 at 04:25
  • @SatishPatro - if user name, passwords are different then define 2 urls for DB and separate username, pads for both in .properties file – Ashish Choudhary Aug 14 '22 at 04:26
  • @AshishChoudhary yes, best practice is to create 2 different DB properties of each & their own DB configuration – Satish Patro Aug 15 '22 at 16:51
2

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.

KayV
  • 12,987
  • 11
  • 98
  • 148
  • 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
1

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
akshaya pandey
  • 997
  • 6
  • 16