0

So, I have a spring boot + hibernate application which has a main schema specified in the yml file (molecular) and also needs to fetch stuff from 3 others (vivax, malaria, chassis)

So, all schemas have access via the same user and are all accessible via port 3306, so my dev.yml file connection area looks like:

datasource:
        type: com.zaxxer.hikari.HikariDataSource
        url: jdbc:mysql://localhost:3306/molecular?useUnicode=true&characterEncoding=utf8
        username: universalUser
        password: universalPass

I've set EntityScan like:

@EntityScan(basePackages = {
    "org.wwarn.malaria.server.data",
    "org.wwarn.chassis.server.data",
    "org.wwarn.surveyorDM.domain.vivax",
    "org.wwarn.surveyorDM.domain"})

So it doesn't get scared when seeing 'alien' entities, so the app compiles and starts fine

However, when I try to do a default Repository operation on any of the packages (except org.wwarn.surveyorDM.domain) it won't build the HQL query properly, let's say I need to find all vivax.Category:

In the domain class I have specified the schema:

@Entity
  @Table(name = "Category", schema = "vivax")

It has its jpa repository:

public interface CategoryRepository extends JpaRepository<Category,Long> {

}

But the hql query will come out as:

Hibernate: select category0_.id as id1_59_, category0_.name as name2_59_, category0_.version as version3_59_ from Category category0_

When I need it to specify vivax.Category in the FROM clause

So, if I add the following native query to the repository:

@Query(value="select distinct * from vivax.Category", nativeQuery=true)
    List<Category> findAllCats();

It works like a charm

Any ideas out there as to how to force hibernate to add schema in FROM clause?

Steven
  • 1,236
  • 1
  • 13
  • 37

2 Answers2

1

You can try specifying the schema like below.

@Entity
@Table(name = "vivax.Category")
Ram
  • 1,743
  • 2
  • 18
  • 40
0

I noticed that you are using a MYSQL database,and in mysql there is no difference between schema and database. I suspect that is the reason that the schema property gets ignored when you are connecting to a MYSQL database. For example, this works fine on a POSTGRESQL database. I would try with the approach proposed by juserman10, and that is putting the db and the table name concatenated in the @Table annotation name property.

If that doesn't work, you can configure multiple data sources in your application, one for each database.

Community
  • 1
  • 1
Dimitar Spasovski
  • 2,023
  • 9
  • 29
  • 45