CODEWITHSUNDEEP
hibernatejpaspring-boot
Steven
Steven

Reputation: 1256

Hibernate not specifying schema on generated HQL query

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?

Upvotes: 0

Views: 1417

Answers (2)

Dimitar Spasovski
Dimitar Spasovski

Reputation: 2132

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.

Upvotes: 0

Ram
Ram

Reputation: 1803

You can try specifying the schema like below.

@Entity
@Table(name = "vivax.Category")

Upvotes: 1

Related Questions