How to specify table schema in spring-data-jdbc

3
votes

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?

3
spring-data-jdbc

3 Answers

1
votes

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";
        }
    };
}
0
votes

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

0
votes

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