CODEWITHSUNDEEP
javamysqlhibernatejpaspring-boot
mmesnjak
mmesnjak

Reputation: 179

JPA many-to-one with constant values in referenced table

I'm doing Spring Boot project and use spring-boot-jpa (Hibernate implementation). I'm having trouble configuring following relation between entities.

Let's assume I need many-to-one (and reversly one-to-many) relation between two tables (MySQL in this example, table1 logically stores description for codes in various other tables) :

CREATE TABLE `table1` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `ref_table` VARCHAR(50) NOT NULL,
   `ref_column` VARCHAR(50) NOT NULL,
   `code` VARCHAR(10) NOT NULL,
   `description` VARCHAR(100) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE INDEX `u_composite1` (`ref_table` ASC, `ref_column` ASC, `code` ASC));

CREATE TABLE `table2` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `field1` VARCHAR(100) NULL,
   `code` VARCHAR(10) NOT NULL,
   PRIMARY KEY (`id`));

The way I join these two tables in SQL is like this:

SELECT t2.*, t1.description 
FROM table2 t2 
JOIN table1 t1 
  ON    ( t1.ref_table = 'table2'
      AND t1.ref_column = 'code' 
      AND t1.code = t2.code);

So, I created entities like this (minus the getters an setters):

@Entity
@Table(name = "table1")
public class Table1  implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(unique = true, nullable = false)
    private int id;

    @Column(nullable = false, length = 10)
    private String code;

    @Column(length = 100)
    private String description;

    @Column(name = "ref_column", nullable = false, length = 50)
    private String refColumn;

    @Column(name = "ref_table", nullable = false, length = 50)
    private String refTable;

    @OneToMany(mappedBy = "table1")
    private List<Table2> table2;
}

@Entity
@Table(name = "table2")
public class Table2  implements Serializable {
    private static final long serialVersionUID = 1L;

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(unique = true, nullable = false)
   private int id;

   @Column(nullable = false, length = 45)
   private String field1;

   @ManyToOne(fetch=FetchType.LAZY)
   @Column(name = "code")
   @JoinColumns({
      @JoinColumn(name = "code", referencedColumnName = "code", nullable = false, updatable = false),
      @JoinColumn(name = "'table2'", referencedColumnName = "ref_table", nullable = false, updatable = false),
      @JoinColumn(name = "'code'", referencedColumnName = "ref_column", nullable = false, updatable = false)
   })
   private Table1 table1;

}

But it doesn't work. :(

Can this kind of relation even be defined in JPA? If so, please, how?

Upvotes: 1

Views: 4660

Answers (1)

Allenaz
Allenaz

Reputation: 1109

Pertaining to the "join with constant values" problem I managed to make it work using the @Where Hibernate annotation:

  • How to replace a @JoinColumn with a hardcoded value?

    @Entity
@Table(name = "a")
public class A {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public long id;

    @OneToMany
    @JoinColumn(name = "id", referencedColumnName = "id")
    @Where(clause = "blah = 'CONSTANT_VALUE'")
    public Set<B> b;

    protected A() {}
}

@Entity
@Table(name = "b")
public class B {
    @Id
    @Column(nullable = false)
    public Long id;

    @Column(nullable = false)
    public String blah;

    protected B() {}
}

Upvotes: 1

Related Questions