Spring Data JPA - Unidirectional Join with Constant Values in PK-CodePudding

I am working with Spring Data JPA/Hibernate and have two tables, one for orders and another one for customers. Each has a composite primary key as this is given by the DB model. The customer table is a general one that is used in multiple contexts and therefore has two columns "module" & "funtion" in the primary key. Now I want to join the customer in the orders entity with constant values for "module" and "function", which seems not to work with @JoinFormula..

orders entity:

@Getter
@Setter
@Entity
@Table(name = "customorder")
@IdClass(OrderId.class)
public class Order {

   @Id
   @Column("COMPANYNUMBER")
   private Integer companyNumber;

   @Id
   @Column("CUSTOMERNUMBER")
   private Integer customerNumber;

   @Id
   @Column("ORDERNUMBER")
   private Integer orderNumber; 

   ...

   @ManyToOne(fetch = FetchType.LAZY)
   @JoinColumnsOrFormulas({
        @JoinColumnOrFormula(column = @JoinColumn(name = "COMPANYNUMBER", referencedColumnName = "COMPANYNUMBER", insertable = false , updatable = false)),
        @JoinColumnOrFormula(column = @JoinColumn(name = "CUSTOMERNUMBER", referencedColumnName = "CUSTOMERNUMBER", insertable = false , updatable = false)),
        @JoinColumnOrFormula(formula = @JoinFormula(value = "'MODULE_FOO'", referencedColumnName = "MODULE")),
        @JoinColumnOrFormula(formula = @JoinFormula(value = "'FUNCTION_BAR'", referencedColumnName = "FUNCTION"))
    })
    private Customer customer; 
}

Id class of orders entity:

public class OrderId implements Serializable {

    private Integer companyNumber;

    private Integer customerNumber;
    
    private Integer orderNumber;
    
    
    public OrderId() {
    }


    public OrderId(Integer companyNumber, Integer customerNumber, Integer orderNumber) {
        super();
        this.companyNumber = companyNumber;
        this.customerNumber = customerNumber;
        this.orderNumber = orderNumber;
    }   
}

Customer entity:

@Getter
@Setter
@Entity
@Table(name = "customer")
@IdClass(CustomerId.class)
public class Customer {

   @Id
   @Column("COMPANYNUMBER")
   private Integer companyNumber;

   @Id
   @Column("CUSTOMERNUMBER")
   private Integer customerNumber; 

   @Id
   @Column("MODULE")
   private String module;    

   @Id
   @Column("FUNCTION")
   private String function;    

   ...
}

Id class of customer entity:

public class CustomerId implements Serializable {

    private Integer companyNumber;

    private Integer customerNumber;

    private String module;

    private String function;
    
    
    public CustomerId() {
    }


    public CustomerId(Integer companyNumber, Integer customerNumber, String module, String function) {
        super();
        this.companyNumber = companyNumber;
        this.customerNumber = customerNumber;
        this.module = module;
        this.function = function;
    }   
}

Beside of that I read the order via a custom native query in the corresponding Repository:

public interface OrderDao extends JpaRepository<Order, OrderId> {

   @Query(value = "SELECT COMPANYNUMBER, CUSTOMERNUMBER, ORDERNUMBER, ... FROM customorder WHERE COMPANYNUMBER = :companyNumber", nativeQuery = true)
   List<Order> findAllByCompanyNumber(@Param(value = "companyNumber") Integer companyNumber)
}

When I try to load the data via the repository method, I receive an excpetion that the sql could not be executed as an undefined column name was detected. In detail the constant columns module and function can not be resolved..

Any ideas, how to set up this constellation correctly?

Thanks for any help and ideas!

CodePudding user response：

I am not familiar with @JoinColumnOrFormula, but I implemented your entities and I noticed that your reference column name here should be 'MODULE' not 'MODUL'. Also you have a table named 'order' which is a reserved word in SQL - 'ORDER BY'

@JoinColumnOrFormula(formula = @JoinFormula(value = "'MODUL_FOO'", referencedColumnName = "MODUL"))

CodePudding user response：

I have found a quite simple solution meanwhile, where I have changed the @JoinColumns and the Query in the OrderDao:

inside orders entity:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumns({
    @JoinColumn(name = "COMPANYNUMBER", referencedColumnName = "COMPANYNUMBER", insertable = false , updatable = false),
    @JoinColumn(name = "CUSTOMERNUMBER", referencedColumnName = "CUSTOMERNUMBER", insertable = false , updatable = false),
    @JoinColumn(name = "MODULE", referencedColumnName = "MODULE", insertable = false , updatable = false),
    @JoinColumn(name = "FUNCTION", referencedColumnName = "FUNCTION", insertable = false , updatable = false)
})
private Customer customer;

inside order dao:

@Query(value = "SELECT COMPANYNUMBER, CUSTOMERNUMBER, ORDERNUMBER, ..., 'MODULE_FOO' AS MODULE, 'FUNCTION_BAR' AS FUNCTION FROM customorder WHERE COMPANYNUMBER = :companyNumber", nativeQuery = true)
   List<Order> findAllByCompanyNumber(@Param(value = "companyNumber") Integer companyNumber)

Maybe this will help someone in the future..