JPA: many to one mapping from table in another database

Question

I have an existing table (let's call this Tickets), and I need to have a many-to-one relationship into a table in another database (let's call this Transaction). I know that it's impossible to have a many-to-one relationship in JPA, but what I am looking for is to connect these two tables without resorting to JPA. Also, merging these tables to one db is not an option (as per client request).

I'm at a loss here.

Welcome to SO. Can you show us some code about what you've tried so far? — Gray, Feb 17 '17 at 16:03

score 1 · Answer 1 · edited May 23 '17 at 12:17

Expanding on this answer (JPA using multiple database schemas) I think that you would need to do something like:

public class Schemas {
  public static final String S1D="S1D";
  public static final String S2D="S2D";
}

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "Tickets", schema=Schemas.S1D)
public class Tickets {

@ManyToOne
@JoinColumn(name = "transaction_id")
    private Transaction transaction;

}

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "Transaction", schema=Schemas.S2D)
public class Transaction {
...
}

Andres · Answer 2 · 2017-02-17T16:06:50.820

0

It's not impossible to have a ManyToOne relationship in JPA. Here you have an example:

https://en.wikibooks.org/wiki/Java_Persistence/ManyToOne

Concerning how to solve your particular problem using tables in two databases, you could use two different data sources, or a view on one of the databases, pointing to the table on the other one. The choice mainly depends on the nature of the databases you're trying to connect.

edited Feb 17 '17 at 16:06

answered Feb 17 '17 at 15:58

Andres

10,561
4
45
63

What particular technology would you use for a cross-database view? Considering there's no knowledge of actual databases being used. – Kayaman Feb 17 '17 at 16:01
It depends on the databases being used, but it's not an unusual solution. – Andres Feb 17 '17 at 16:02
And particularly easy to implement, if e.g. you're using two different instances of Oracle. – Andres Feb 17 '17 at 16:03
True, if you're using the same databases. If you don't require referential integrity. It's not a *great* solution. – Kayaman Feb 17 '17 at 16:09
Well...the question doesn't give much info, so I opened the spectrum of solutions. – Andres Feb 17 '17 at 16:12

score 0 · Answer 3 · edited Feb 09 '23 at 07:47

Here is an example of implementing a many-to-many parent-child relationship using JPA, with a Category table and a mapping table, as well as a save API and a fetch API by root ID:

@Entity

public class Category {

  @Id

  @GeneratedValue(strategy = GenerationType.IDENTITY)

  private Long id;



  private String name;



  @ManyToMany(cascade = CascadeType.ALL)

  @JoinTable(

      name = "category_mapping",

      joinColumns = @JoinColumn(name = "category_id"),

      inverseJoinColumns = @JoinColumn(name = "child_id")

  )

  private Set<Category> children;



  // getters and setters

}


@Entity

public class CategoryMapping {

  @Id

  @GeneratedValue(strategy = GenerationType.IDENTITY)

  private Long id;



  @ManyToOne

  @JoinColumn(name = "category_id")

  private Category category;



  @ManyToOne

  @JoinColumn(name = "child_id")

  private Category child;



  // getters and setters
}

[check here][1]

https://java-speed.blogspot.com/2023/02/jpa-implemntation-many-to-many-parent.html

JPA: many to one mapping from table in another database

3 Answers3