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.

Answer 1

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.