Multiple DataBases in Spring-Boot With mariaDB

Question

I need to know the simple steps to Access the Repository and Entity Classes from Multiple Databases. Example: I have User table in DB1 and Email in DB2 have to access from a single Service Request.

DB 1

spring.datasource.url = jdbc:mysql://localhost:3306/dbName1?useSSL=false
spring.datasource.username = user
spring.datasource.password = password
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.hibernate.ddl-auto = update

DB 2

spring.datasource.url = jdbc:mysql://localhost:3306/dbName2?useSSL=false
spring.datasource.username = user
spring.datasource.password = password
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

OTHER PROPERTIES

spring.jpa.properties.hibernate.jdbc.batch_size=20
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true
spring.servlet.multipart.max-file-size=100MB 
spring.servlet.multipart.max-request-size=100MB

Simon Martinelli · Accepted Answer · 2018-09-11 07:40:52Z

1

Solution 1:

You could create a user that has access to both databases and then use fully qualified table names when querying for the external table.

MySQL supports the dbname.tablename syntax to access tables outside the current database scope.

This requires that the currently connected user has the appropriate rights to read from the requested table in another physical db.

Solution 2:

You can configure two datasources as described here:

https://docs.spring.io/spring-boot/docs/current/reference/html/howto-data-access.html#howto-two-datasources

answered Sep 11, 2018 at 7:40

Simon Martinelli

36.6k6 gold badges62 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Shakeel Hussain Mir Over a year ago

i have user who can access both databases

Shakeel Hussain Mir Over a year ago

dbname.tablename creates new table with "_" underScore in a primaryDB db1

Simon Martinelli Over a year ago

Where do you specify dbname.tablename? This must go into the @Table annotation on your entity

Shakeel Hussain Mir Over a year ago

i have specified like this db2.tablename. but creates a new table in db1 like this bd2_tablename

Simon Martinelli Over a year ago

and what about @Table(name="tablename", catalog="db2")

|

Rick James · Accepted Answer · 2018-10-03 19:09:53Z

1

(Not a direct answer.)

If you will be using text other than plain English, consider these settings:

Hibernate XML:

<property name="hibernate.connection.CharSet">utf8mb4</property>
<property name="hibernate.connection.characterEncoding">UTF-8</property>
<property name="hibernate.connection.useUnicode">true</property>

Connection url:

db.url=jdbc:mysql://localhost:3306/db_name?useUnicode=true&character_set_server=utf8mb4

answered Oct 3, 2018 at 19:09

Rick James

144k15 gold badges144 silver badges255 bronze badges

Comments

Shakeel Hussain Mir · Accepted Answer · 2018-09-13 06:40:42Z

0

@Table(name="tablename", catalog="db2")

worked for me

answered Sep 13, 2018 at 6:40

Shakeel Hussain Mir

2862 silver badges20 bronze badges

Collectives™ on Stack Overflow

Multiple DataBases in Spring-Boot With mariaDB

DB 1

DB 2

OTHER PROPERTIES

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

DB 1

DB 2

OTHER PROPERTIES

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related