CODEWITHSUNDEEP
spring-data-jdbc
MadMax
MadMax

Reputation: 153

How to specify table schema in spring-data-jdbc

In my project, I use spring-data-jdbc version 2.0.4.RELEASE and I'm stuck with the problem of how to specify a schema for a table. The entity class looks like this

@Data
@Table(value = "alpha.op_organization")
public class OrganizationEntity {
   @Id
   private Long id;
   @Column(name="name")
   private String name;
}

If I try to save entity with jdbc repository method save like this

OrganizationEntity organization = new OrganizationEntity();
organization.setName("OrgName1");
organizationRepository.save(organisation)

I have got exception "Releation alpha.op_organization does not exist. Corresponding sql query is

INSERT INTO "alpha.op_organization" ("name") VALUES (?)

I. e. it turns out that we are trying to insert an entry in the table "alpha.op_organization" and not in the table op_organization in the schema alpha.

I tred to customize NamingStrategy to return schema alpha but with no luck.

@Configuration
public class AppConfig extends AbstractJdbcConfiguration {
   @Bean
   public NamingStrategy namingStrategy() {
      return new NamingStrategy() {

         @Override
         public String getSchema() {
            return "alpha";
         }
      };
   }
}

Is there any way to specify the table schema in spring-data-jdbc?

Upvotes: 6

Views: 7261

Answers (6)

Artem Yemelin
Artem Yemelin

Reputation: 66

One can override schema directly in DataSource:

@Configuration
public class DatabaseConfig {
    @Autowired
    public void configureDataSource(HikariDataSource dataSource) {
        dataSource.setSchema("SCHEMA_NAME");
    }
}

or simply specify in application.yml

spring:
  datasource:
    hikari:
      schema: 'SCHEMA_NAME'

Upvotes: 1

mipo256
mipo256

Reputation: 3140

Sorry for being kind of late, but I think I can help other developers here. As @MadMax mentioned, specifying NamingStrategy#getSchema() does not really work with @Table. In fact, there is bug, and it is reported in spring-data-jdbc, that actually tells that specifying @Table#value is not working together with NamingStrategy.

The good news is that there is a Pull Request opened in spring-data-jdbc in order to solve this issue. The changes, introduced by this Pull Request will add schema parameter to @Table annotation, which will allow you to explicitly specify the schema for each table, if you prefer to do so. Also this PR will eliminate this bug.

What can you do for now : well, if you are unfortunate enough and you need to use @Table#value in conjunction with NamingStrategy#getSchema() - it wont work. If you really need to specify the schema, you need to get rid of @Table annotation for now. If you will do so, the schema from NamingStrategy will apply properly.

Upvotes: 0

Anton Bondarenko
Anton Bondarenko

Reputation: 641

if you are using

import org.springframework.data.relational.core.mapping.Table;

Spring trying build query

INSERT INTO "alpha.op_organization" ("name") VALUES (?)

but we need

INSERT INTO "alpha"."op_organization" ("name") VALUES (?)

try:

@Table(value = "alpha\".\"op_organization")

Upvotes: 3

Myat Min
Myat Min

Reputation: 1

Assign currentSchema parameter in the JDBC connection url.

Example: jdbc:postgresql://server/catalog?currentSchema=schema

This gave me more flexible in terms of different table name but no need to describe the same schema name again in the @Table annotation.

Postgres JDBC connections documentation

Upvotes: 0

Андрей Палкин
Андрей Палкин

Reputation: 71

I use postgresql + HikariDataSource. So in my case:

 @Bean
 public DataSource dataSource() {

        HikariConfig hikariConfig = new HikariConfig();
...
        if(schema != null && !schema.isEmpty()){
            hikariConfig.setConnectionInitSql("SET SEARCH_PATH TO " + schema);
        }
        
        HikariDataSource dataSource = new HikariDataSource(hikariConfig);
        return dataSource;
  }

And entity annotated like this: @Table("user") correct mapped into service.user

Upvotes: 0

Jens Schauder
Jens Schauder

Reputation: 81862

Currently the way to do this is to define a NamingStrategy. Example taken from this integration test:

@Bean
NamingStrategy namingStrategy() {
    return new NamingStrategy() {
        @Override
        public String getSchema() {
            return "other";
        }
    };
}

Upvotes: 3

Related Questions