[FIXED] Strange validation conflict in Spring JPA TableGenerator

Issue

I have a legacy database with composite primary key in table project. (BaseEntity contains common properties for lastModifiedDate and lastModifiedBy)

@Entity
@IdClass(ProjectPk.class)
public class Project extends BaseEntity {

    @Id
    @GeneratedValue(strategy=GenerationType.TABLE, generator="nextProjectId")
    @TableGenerator(
            name="nextProjectId",
            table="projectId",
            pkColumnName = "proj_Id",
            pkColumnValue="proj_id"
    )
    private Long  projId;

    @Id
    private int version;

  //other properties, getters and setters omitted for clarity
}

PK class

public class ProjectPk implements java.io.Serializable {
    private int  projId;
    private int  version;

  //both constructoirs, equals, hashcode, getters and setters omitted for clarity
}

• I have flyway migration files to simulate production database.

drop table if exists project;
CREATE TABLE project
(
    proj_id             bigint,
    version             int,
    -- other columns omitted for clarity
    PRIMARY KEY (`proj_id`, `version`)
) ENGINE=InnoDB;

drop table if exists project_id;
CREATE TABLE project_id
(
    proj_id             bigint
) ENGINE=InnoDB;

• flyway creates tables as ordered in migration file

Table: project_id
Columns:
proj_id bigint
...


Table: project
Columns:
proj_id bigint PK 
version int PK
...

during maven build I'm getting validation error

• Schema-validation: wrong column type encountered in column [proj_id] in table [project_id]; found [bigint (Types#BIGINT)], but expecting [varchar(255) (Types#VARCHAR)]

What I did wrong to make hibernate expect [varchar(255) (Types#VARCHAR)]?

This is SpringBoot project 2.6.6 with MySql database

Solution

I see the following problems with your code:

1. Type mismatch between Project.projId (Long type) and ProjectPk.projId (int type).
2. You use wrong table structure for the project_id table.

You can see a working example below.

Assuming that you have the following tables:

CREATE TABLE test_project
(
    proj_id   bigint,
    version   int,
    title     VARCHAR(50),
    
    PRIMARY KEY (proj_id, version)
);

create table table_identifier (
    table_name varchar(255) not null,
    product_id bigint,
    primary key (table_name)
);
insert into table_identifier values ('test_project', 20);

and the following mapping:

@Entity
@Table(name = "test_project")
@IdClass(ProjectPk.class)
public class Project {

    @Id
    @GeneratedValue(strategy = GenerationType.TABLE, generator = "nextProjectId")
    @TableGenerator(
        name="nextProjectId",
        table="table_identifier",
        pkColumnName = "table_name",
        valueColumnName="product_id",
        allocationSize = 5
    )
    @Column(name = "proj_id")
    private Long  projId;

    @Id
    private int version;

    // other fields, getters, setters ...
}

you will be able to persist the entity like below:

Project project = new Project();
project.setVersion(1);
// ...
entityManager.persist(project);

Answered By - SternK
Answer Checked By - Gilberto Lyons (JavaFixing Admin)