Stack Exchange
Persist Tables with parent-child relation between two different schema in Spring Data JPA
I am trying to do,
ChildTable child = new ChildTable();
clild.setParentTwoID(prentTwoID);
ParentOne parentOne = new ParentOne();
parentOne.getChildTableList().add(childTable);
parentReposetory.save(parentOne);

Where, ParentOne is in schema S1 and ParentTwo along with ChildTable in schema S2.
ParentOne.java
@Data
@EqualsAndHashCode(callSuper = false)
@Entity
@Table(name = "ParentOne", schema = "S1")
@JsonIdentityInfo(generator = ObjectIdGenerators.IntSequenceGenerator.class, scope = ParentOne.class)
public class ParentOne implements Serializable {

    private static final long serialVersionUID = 7502273069461829133L;

    @Id
    @Column(name = "ParentOneID", unique = true, nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer parentOneID;

    @OneToMany(targetEntity = ChildTable.class, fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "ParentOneID", nullable = false, insertable = false, updatable = false)
    private List<ChildTable> childTableList;
}

ParentTwo.java
@Data
@EqualsAndHashCode(callSuper = false)
@Entity
@Table(name = "ParentTwo", schema = "S2")
@JsonIdentityInfo(generator = ObjectIdGenerators.IntSequenceGenerator.class, scope = ParentTwo.class)
public class ParentTwo implements Serializable {

    private static final long serialVersionUID = 7502273069461829133L;

    @Id
    @Column(name = "ParentTwoID", unique = true, nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer parentTwoID;

    @OneToMany(targetEntity = ChildTable.class, fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "ParentTwoID", nullable = false, insertable = false, updatable = false)
    private List<ChildTable> childTableList;
}

ChildTable.java
@Data
@EqualsAndHashCode(callSuper = false)
@Entity
@Table(name = "ChildTable", schema = "S2")
@JsonIdentityInfo(generator = ObjectIdGenerators.IntSequenceGenerator.class, scope = ChildTable.class)
public class ChildTable implements Serializable {

    private static final long serialVersionUID = -6331600489988183852L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ChildTableID", unique = true, nullable = false)
    private Integer childTableID;

    @Column(name = "ParentOneID")
    private Integer parentOneID;

    @Column(name = "ParentTwoID", nullable = false)
    @NotNull(message = "mandatory_field")
    private Integer parentTwoID;

}

Here ParentTwo is already persisted and I have the Id of it. I am Going to Persist only ParentOne along with the ChildTable of different Schema. While I do this I get error saying that.

rg.springframework.dao.DataIntegrityViolationException: could not
  execute statement; SQL [n/a]; constraint [null]; nested exception is
  org.hibernate.exception.ConstraintViolationException: could not
  execute statement
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot
  insert the value NULL into column 'ParentOneID', table
  'PT_Sample.S2.ChildTable'; column does not allow nulls. INSERT fails.
SQL Error: 515, SQLState: 23000


The error says that the column ChildTable.ParentOneID has a NOT NULL constraint on it and that an INSERT is being issued with a null value for that column. Since you are not setting the value of that column in your code anywhere, the error is expected. What exactly is the question?

@manish As JPA will insert ParentOne table which will create a parentOneID and then Insert ChildTable with that generated parentOneID. But here for me its not happening.

When using JPA, the JPA provider needs to be allowed to manage the relationships between entity instances. In the given case, the relationships are being managed manually by keeping a track of various foreign key identifiers. Therefore, the JPA provider is unable to set the foreign key column values.
The basic problem seems to be in the understanding of how JPA (or any other ORM) works. Entities are not a column-by-column replica of database tables. They need to be thought of and modelled as objects without thinking of the underlying database. The mapping of entity attributes to database columns should be left to the JPA provider.

The following entity model will solve the problem (all unnecessary code removed for brevity):

ParentOne entity

@Entity
@Table(name="ParentOne", schema="S1")
public class ParentOne {
  @Id
  @Column(name="ParentOneID", unique=true, nullable=false)
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer id;

  @OneToMany(fetch=FetchType.LAZY, cascade=CascadeType.ALL, mappedBy="parentOne")
  private List<Child> children;

  public void addChild(final Child child) {
    if (children == null) {
      children = new ArrayList<>();
    }

    children.add(child);

    child.setParentOne(this);
  }
}

The following points are noteworthy:

The schema name (S1) has been declared along side the table name to which the entity instances must be persisted.
The relationship with the child entity is declared as a collection and annotated as @OneToMany.
Persistence operations (save, update, delete) are cascaded to the child entity instances (cascade = CascadeType.ALL). This ensures that a persistence operation on a parent instance automatically takes care of appropriate operations on any associated child instances.


ParentTwo entity

@Entity
@Table(name="ParentTwo", schema="S2")
public class ParentTwo {
  @Id
  @Column(name="ParentTwoID", unique=true, nullable=false)
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer id;

  @OneToMany(fetch=FetchType.LAZY, cascade=CascadeType.ALL, mappedBy="parentTwo")
  private List<Child> children;

  public void addChild(final Child child) {
    if (children == null) {
      children = new ArrayList<>();
    }

    children.add(child);

    child.setParentTwo(this);
  }
}


Child entity

@Entity
@Table(name="ChildTable", schema="S2")
public class Child {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name="ChildTableID", unique=true, nullable=false)
  private Integer id;

  @ManyToOne(cascade=CascadeType.PERSIST)
  @JoinColumn(name="ParentOneID", nullable=false)
  private ParentOne parentOne;

  @ManyToOne(cascade=CascadeType.PERSIST)
  @JoinColumn(name="ParentTwoID", nullable=false)
  private ParentTwo parentTwo;

  void setParentOne(final ParentOne parentOne) {
    this.parentOne = parentOne;
  }

  void setParentTwo(final ParentTwo parentTwo) {
    this.parentTwo = parentTwo;
  }
}

The following points are noteworthy:

The schema name (S2) is declared along side the table names.
The associations with the parent tables are declared as object instances, instead of raw columns.

After this, the following code will work fine:
Child child = new Child();
...

ParentOne parentOne = new ParentOne();
ParentTwo parentTwo = new ParentTwo();
...

parentOne.addChild(child);
parentTwo.addChild(child);

parentOneRepository.save(parentOne);


Ya Its working and I would like to know is it possible to persist all three tables together (i.e) clild.setParentTwoID(prentTwoID); I am not going to give this line of code will it work?

Yes, as long as operations have been configured to cascade to the appropriate related entities, a save can be initiated with any entity instance and will get cascaded to all others. See edited answer for details.

@manish hi! What if my url on the hibernate.cfg.xml is defined to a certain schema? https://stackoverflow.com/questions/56050784/hibernate-how-to-map-and-relate-a-class-from-another-schema

@manish In my case, for example, the schema S2 varies. But the Schema S1 would be common to every other Schema. So I cannot declare S2 to my Entities. I can only declare S1. And also, the S2 schema would be in a URL that comes from another source...

@KenobiShan, two DataSources will be required in this case. Entities mapped to S1 will need to be associated with an EntityManager that uses a regular DataSource, while entities not mapped to S1 will need to be associated with another EntityManager that uses a different DataSource that extends AbstractRoutingDataSource. The AbstractRoutingDataSource will have to indicate which schema to use, based on runtime information.
