[FIXED] Spring JPA: Error when trying to create the foreign key constraints

Issue

I am trying to map some columns of a big database with JPA, so i can fetch some data from it. This database has composite primary keys, and some of these are also foreign keys. Im fairly new to JPA mapping, so i need some help. Here's the error i get:

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table tbpedidoentrega add constraint FKiwjj63py270eqhfb1olp08oox foreign key (fk_cliente) references tbcadastro" via JDBC Statement

and also:

ERROR: number of referencing and referenced columns for foreign key disagree

It would seem that JPA is not specifying the columns that it needs to reference in the end of the command (the correct command would be alter table tbpedidoentrega add constraint FKiwjj63py270eqhfb1olp08oox foreign key (fk_cliente) references tbcadastro (codigo)), specifying just the table. But why?

Here's my code: The Client class

package com.agilsistemas.construtordepedidos.model;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Entity
@Getter
@Setter
@NoArgsConstructor
@Table(name = "tbcadastro")
public class ClienteModel implements Serializable {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "codigo")
    int idCliente;
    
    @Column(name = "razao")
    String razaoSocial;

    @Column(name = "logradouro")
    String rua;
    
    @Column(name = "numero")
    String numero;

    @Column(name = "bairro")
    String bairro;

    @Column(name = "complemento")
    String complemento;

    @Column(name = "cidade")
    String cidade;

    @Column(name = "fixo")
    String telefoneFixo;

    @Column(name = "celular")
    String celular;

    @Column(name = "cliente")
    String cliente;

}

The Order class:

package com.agilsistemas.construtordepedidos.model;

import java.io.Serializable;
import java.time.LocalDate;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Entity
@Getter
@Setter
@NoArgsConstructor
@Table(name = "tbpedidoentrega")
public class PedidoModel implements Serializable {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id_pedido", nullable = false)
    @SequenceGenerator(name = "sqpedido")
    int idPedido;

    @Column(name = "data_pedido", nullable = false)
    LocalDate dataPedido;

    @Column(name = "hora_pedido", nullable = false)
    LocalDate horaPedido;

    @ManyToOne
    @JoinColumn(name = "fk_funcionario")
    FuncionarioModel fkFuncionario;

    @ManyToOne
    @JoinColumn(name = "fk_cliente")
    ClienteModel fkCliente;

    @OneToMany(mappedBy = "pedido")
    List<ItemPedidoModel> itensPedido;

}

I was expecting this to create the FKs and start the backend. I think the issue lies in the end of the SQL command generated by hibernate (It was supposed to be references tbcadastro (codigo)), but i dont know why it is generating like this.

Solution

After some medling and some research, I was able to solve the problem. The problem was that the table Cliente had a composite primary key, so i needed to implement a special class to specify the composite key:

package com.agilsistemas.construtordepedidos.model;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Embeddable;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;

import lombok.AllArgsConstructor;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

@Embeddable
@Getter
@Setter
@EqualsAndHashCode
@AllArgsConstructor
public class IdClienteModel implements Serializable {
    
    @Column(name = "codigo")
    private int idCliente;

    @ManyToOne
    @JoinColumn(name = "empresa")
    private EmpresaModel idEmpresa;

}

Then i implemented the ClienteModel class like so:

package com.agilsistemas.construtordepedidos.model;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.Table;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Entity
@Getter
@Setter
@NoArgsConstructor
@Table(name = "tbcadastro")
public class ClienteModel implements Serializable {
    
    @EmbeddedId
    private IdClienteModel idCliente; //using the object as the ID
    
    @Column(name = "razao")
    String razaoSocial;

    @Column(name = "logradouro")
    String rua;
    
    @Column(name = "numero")
    String numero;

    @Column(name = "bairro")
    String bairro;

    @Column(name = "complemento")
    String complemento;

    @Column(name = "cidade")
    String cidade;

    @Column(name = "fixo")
    String telefoneFixo;

    @Column(name = "celular")
    String celular;

    @Column(name = "cliente")
    String cliente;

}

And now, in the Pedido (Order) class, i can get the OneToOne relation like this:

@OneToOne
    @JoinColumns({
            @JoinColumn(name = "fk_cliente", referencedColumnName = "codigo", insertable = false, updatable = false),
            @JoinColumn(name = "fk_empresa", referencedColumnName = "empresa", insertable = false, updatable = false) })
    ClienteModel fkCliente;

I did this to all the other entities that had composite PKs also, and now the application starts propperly. I hope this awnser can help someone else.

Answered By - Siri-Piell
Answer Checked By - Katrina (JavaFixing Volunteer)