Not an entity error for classes in different databases

Question

I work on a spring boot project which works on different DBs. Today I tried to fetch some filtered records via Specification. Filtering is based on a different entity on another DB with no mapping or relation. I got a Not an entity error while fetching records. I thought the reason was based on the databases, so I tried the same way on two classes in the same DB and it worked.

My code is like that:

class 1:

@Data
@Entity
@Table(name="s")
public class S {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id")
    Integer id;

    @Column(name="s_id")
    Integer sId;

    @Column(name="c_id")
    Integer cId;
}

class 2:

@Data
@Entity
@Table(name = "c")
public class C {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Column(name = "name")
    private String name;
}

Specificatin class:

public class SSpecification {

    public static Specification<S> isNameLike(String nameQuery) {
        return (root, query, builder) -> {

            query.distinct(true);

            Root<C> cRoot = query.from(Co.class);

            List<Predicate> predicateList = new ArrayList<>();
            predicateList.add(builder.equal(root.get("cId"), cRoot .get("id")));
            predicateList.add(builder.like(cRoot .get("name"), "%" + nameQuery + "%"));

            return builder.and(predicateList.toArray(Predicate[]::new));
        };
    }

    public static Specification<S> isMember(Boolean isMember) {
        return (root, query, criteriaBuilder) -> criteriaBuilder.equal(root.get("isMember"), isMember);
    }
}

Do you have any idea to solve this problem?

Ps, I thought chained Tx management could be a solution but I was wrong.

Answer 1

You can't do queries across databases with JPA except when your database presents them as if they are in the same database. You can do that for example with Oracle database links. I expect the other big commercial databases to have similar features.

Without such a database feature you can only query data from one database at a time.