How to specify table schema in spring-data-jdbc

Question

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?

Anton Bondarenko · Answer 1 · 2021-10-20T21:53:47.287

3

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")

edited Oct 20 '21 at 21:53

answered Oct 20 '21 at 21:48

Anton Bondarenko

501
4
7

This worked for me. Also keep in mind that the schema and table name are case sensitive with the quotes. – shj Mar 10 '22 at 01:48

score 2 · Answer 2 · answered Nov 02 '20 at 08:46

2

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

answered Nov 02 '20 at 08:46

Jens Schauder

77,657
34
181
348

1

This solution works for me only if entity class does not annotated with "@Table" annotation. If class it does, my custom NamingStrategy does not call. Can you explain me how to set schema with namingstrategy and "@Table" annotation, please? – MadMax Dec 23 '20 at 10:43

score 0 · Answer 3 · answered Feb 24 '21 at 16:18

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

Myat Min · Answer 4 · 2021-03-23T05:49:55.063

0

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

edited Mar 23 '21 at 05:49

answered Mar 23 '21 at 05:16

Myat Min

1
1

It seems that this solution does not work if tables in different schemas – MadMax Mar 24 '21 at 18:30
@MadMax You have to add `@Table("another_schema.table_name")` annotation for the specific `Entity` which has different schema. For those `Entity` which has different table name but same schema then no need to explicitly specify the schema again in the `@Table` annotation but just with the table name only, like this `@Table("different_table_name")`. – Myat Min Mar 30 '21 at 15:09
Actualy i have tried this possibility but with no luck. It seams that jdbc or someone else perceives it as table name "another_schema.table_name" (like single undivided table name). I think so because i got exception from undelying db driver 'Relation "another_schema.table_name" does not exist'. But this releation does exists, of course. – MadMax Apr 01 '21 at 04:09
@MadMax That seems odd, on my end it's working and I used Postgres and Spring Data JDBC 2.1.5 with Spring Boot 2.4.3. – Myat Min Apr 01 '21 at 06:11
thx, i will try to use this versions of jdbc. May be it will work. – MadMax Apr 01 '21 at 09:07

score 0 · Answer 5 · answered Dec 04 '21 at 10:58

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.

score 0 · Answer 6 · answered Feb 02 '22 at 15:48

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'

How to specify table schema in spring-data-jdbc

6 Answers6