Multiples databases

[stackp...@gmail.com]

Hi,
I wanted to ask: is it possible in Ebean to have associations between entities that are in different databases?

Example:

default.url="jdbc:mysql://127.0.0.1/db_01?useSSL=true&serverTimezone=UTC"
general.url="jdbc:mysql://127.0.0.1/general_db?useSSL=true&serverTimezone=UTC"

ebean.default = ["models.*"]
ebean.general = ["generalModels.*"]

User only exist in default database:

@Entity
@DbName("default")
public class User extends Model {

@Id
@Column(length=11)
public Integer userId;

@ManyToMany(mappedBy="users")
public List<Country> countriesVisited = new ArrayList<>();

public static Finder<Integer, User> find = new Finder<>(User.class);

}

Country only exist in general database:

@Entity

@DbName("general")

public class Country extends Model {

@Id
@Column(length=11)
public Integer countryId;

@ManyToMany(mappedBy="countriesVisited")
public List<User> users = new ArrayList<>();

public static Finder<Integer, Country> find = new Finder<>(Country.class, "general");

}

Because I always receive an error:
Error with association to [class models.User] from [generalModels.Country.users]. Is class models.User registered?

[Rob Bygrave]

No, this is not supported.

[Jens]

While you can not make entity relations between databases you can still define the relation in terms of ids, e.g. Long.

Then you can load one entity of one database, read the ids and use them to fetch corresponding entities of the other database.

-- J.

[Rob Bygrave]

> In Mysql it is possible to have associations between different databases (schemas)

Databases and Schemas are very different things. I now get the impression you are talking about tables in the same database but in different schemas (which should work fine).

> But, as Rob said, in Ebean is not possible.

Careful. I was referring to Databases - NOT Schemas.

We absolutely should be able to have associations between tables that are in the same database but in different schemas. There should be no problem doing that (assuming the appropriate database permissions are granted) !!!

 

Cheers, Rob.

On Wed, 14 Jun 2023 at 13:07, stackp...@gmail.com <stackp...@gmail.com> wrote:

Thank you very much for your responses.

I'm doing some tests to verify.

In Mysql it is possible to have associations between different databases (schemas)

But, as Rob said, in Ebean is not possible.

For OneToOne we can use this to try to emulate the desired behavior:

default database:

@Entity
@DbName("default")
public class User extends Model {

@Id
@Column(length=11)
public Integer userId;

public String username;

@OneToOne(cascade= CascadeType.ALL)
public IdentityDocument identityDocument;

public static Finder<Integer, User> find = new Finder<>(User.class);

@Override
public void save() {
identityDocument.save();
DB.save(this);
}

public static User byId(Integer id) {
User user = find.query().select("username, identityDocument.identityDocumentId").where().eq("userId", id).findOne();
user.identityDocument = IdentityDocument.getById(user.identityDocument.identityDocumentId);
return user;
}

With this it is possible to do

User user = new User();
user.username = "te...@testing.es";

IdentityDocument idDoc = new IdentityDocument("Markus", "AABBCC123", "25/07/2025");

user.identityDocument = idDoc;

user.save();

With the rest of the associations (OneToMany, ManyToOne and ManyToMany), I believe that it is not possible to do this like this and the only solution may be the one provided by Jens (without foreign keys and storing only an identifier instead of an object).

I don't know if you think of anything better.

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/c369cb6f-5942-42e4-953f-01746b344ecan%40googlegroups.com.

[Rob Bygrave]

@DbName ... is to be used for different databases (not different schemas).

We use @Table(...) to correctly identify a schema name and table name.

That is, if these tables are in the same database but different schemas ... then specify there full name in @Table and do NOT use @DbName - @DbName is used for multiple different databases.