CODEWITHSUNDEEP
javamysqlhibernatejava-ee-7java-ee-8
Emmajiugo
Emmajiugo

Reputation: 213

Joining 2 tables with non-key columns in JPA, Hibernate

I am working with a legacy DB with tables skl_transactions and merchant_config. These two tables have a column where their values are equivalent to each other (e.g. skl_transactions.column_a === merchant_config.column.b . I am looking at a way to join both tables base on that common value instead of querying the DB twice.

See what I came up with but I keep having an error

(" => "org.hibernate.MappingException: Unable to find column with logical name: column_a in org.hibernate.mapping.Table(merchant_config) and its related supertables and secondary tables
    Caused by: org.hibernate.MappingException: Unable to find column with logical name: column_a in org.hibernate.mapping.Table(merchant_config) and its related supertables and secondary tables"}}

Below is my Entity class

@Entity
@Table(name = "skl_transactions")
public class Foo implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Basic(optional = false)
    protected Long id;

    @Column(name = "column_a")
    private String senderAddress;

    -----

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="column_b", referencedColumnName = "column_a")
    private Bar merchant;

    // getters and setters
}

@Entity
@Table(name = "merchant_config")
public class Bar implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Basic(optional = false)
    protected Long id;

    @Column(name = "column_b")
    private String merchantAddress;

    -----

    @OneToMany(mappedBy = "merchant_config", fetch = FetchType.LAZY)
    private List<Foo> transactions = new ArrayList<>();

    // getters and setters
}

NB: Both columns are not marked as primary or foreign keys.

What I am looking for is when I query a transaction from skl_transactions table, it will join the merchant that has that transaction from the merchant_config table.

Upvotes: 0

Views: 2354

Answers (1)

SSK
SSK

Reputation: 3766

You need to change your mappedBy to merchant

Foo

@Entity
@Table(name = "skl_transactions")
public class Foo implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Basic(optional = false)
    protected Long id;

    @Column(name = "column_a")
    private String senderAddress;

    -----

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="column_b", referencedColumnName = "column_a", insertable=false, updatable=false)
    private Bar merchant;

    // getters and setters
}

Bar

@Entity
@Table(name = "merchant_config")
public class Bar implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Basic(optional = false)
    protected Long id;

    @Column(name = "column_b")
    private String merchantAddress;

    -----

    @OneToMany(mappedBy = "merchant", fetch = FetchType.LAZY)
    private List<Foo> transactions = new ArrayList<>();

    // getters and setters
}

If above have any issue try changing name and referencedColumnName the other way round

in Foo
@ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="column_a", referencedColumnName = "column_b", insertable=false, updatable=false)
    private Bar merchant;

in Bar
@OneToMany(mappedBy = "merchant", fetch = FetchType.LAZY)
    private List<Foo> transactions = new ArrayList<>();

Upvotes: 1

Related Questions