How to mention the sqlserver database name in spring jpa entity?

Question

I have two entities for my spring boot project one is Employees

@Entity
@Table(name = "Employees ",schema = "dbo")
public class Employees {

which is in test1 database and another one is Payroll

 @Entity
    @Table(name = "Payroll ",schema = "dbo")
    public class Payroll {

which is in test2 database, In the application.properties files I have the

spring.datasource.url: jdbc:sqlserver://hostname.com;databaseName=test1

here jpa is looking both the table in test1 database since I have given that in spring.datasource.url

I need to know how to make jpa know the Payroll table is available in test2 database

In SQL SERVER Manangement studio we will do this by giving this SQL

select * from test2..Payroll

by this even if we are in test1 db we can access the table in test2 db

Ramin Safar Manesh · Accepted Answer · 2019-07-23 12:56:04Z

1

If you want to have two different databases, you should configure two completely different DataSources and the tell the Spring JPA to use different DataSources for each @Entity. I think the following link would be a great help:

Spring JPA - Multiple Databases

answered Jul 23, 2019 at 12:56

Ramin Safar Manesh

2772 silver badges10 bronze badges

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

Comments

Ibrahim · Accepted Answer · 2020-06-19 13:52:48Z

1

I solved this issue by creating a native query, stating my db name, schema name and table name in the query.

This will work like the JPA embedded methods, by returning a list of objects and you can call your getter methods on them to fetch the attributes returned by your repository.

Hope this helps someone.

 @Query(
        value = "select * from dbname.schema.table",
        nativeQuery = true)
    List<Employees> fetchAllEmployees();

      @Query(
        value = "select * from test2.schema.Payroll",
        nativeQuery = true)
    List<Payroll> fetchAllPayrollData();

answered Jun 19, 2020 at 13:52

Ibrahim

114 bronze badges

Collectives™ on Stack Overflow

How to mention the sqlserver database name in spring jpa entity?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related