spring-data-jpaschemashared-primary-key

Using schema in a primary key sharing situation


I'm having problems with a bidirectional one-to-one mapping that share the same Id when I try to use a schema. When I don't use the schema attribute in the @Table annotation the program runs normally and creates the SERVICES(id, description) and USERS(name, password, application_id) tables just as I would like. But when I use the schema annotation it shows some errors and doesn't create the tables. Does anyone know how to use schema in this situation?

ServiceJpa class:

import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;

@Getter
@Setter
@Entity
@Table(schema = "company", name = "SERVICES")
public class ServiceJpa {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String description;
    
    @OneToOne(cascade = CascadeType.ALL)
    @PrimaryKeyJoinColumn
    private UserJpa user;
    
}

UserJpa class:

import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;

@Getter
@Setter
@Entity
@Table(schema = "company", name = "USERS")
public class UserJpa {
    
    @Id
    private Long id;
    
    private String name;
    
    private String password;
    
    @OneToOne(mappedBy = "user")
    @MapsId
    private ServiceJpa application;
    
}

I'm using H2 database. application.properties file:

# datasource
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:banco
spring.datasource.username=sa
spring.datasource.password=

# jpa
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

# h2
spring.h2.console.enabled=true
spring.h2.console.path=/h2

Part of error log:

2023-05-20 11:30:29.011  WARN 2508 --- [           main] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL "create table company.services (id bigint generated by default as identity, description varchar(255), primary key (id))" via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table company.services (id bigint generated by default as identity, description varchar(255), primary key (id))" via JDBC Statement

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Schema "COMPANY" not found; SQL statement:
create table company.services (id bigint generated by default as identity, description varchar(255), primary key (id)) [90079-214]

I just tried not using the schema annotation and it works, but I want to know how do I use it


Solution

  • I just had to set the INIT in the datasource url property setting in application.properties file:

    # datasource
    spring.datasource.driverClassName=org.h2.Driver
    spring.datasource.url=jdbc:h2:mem:banco;INIT=CREATE SCHEMA IF NOT EXISTS company;
    spring.datasource.username=sa
    spring.datasource.password=