ActiveJDBC with multi-tenant DB structure

Question

I have a multi-tenant DB architecture. Is there any way to use one active model for multiple identical databases?

A little clarification:

I have a Foo exdends Model class that works with a foo_table. That foo_table is identical in schemaA, schemaB, schameC etc. Can I use the same Foo class to do operations on each of these tables from different schemas?

Answer 1

If you have databases with identical schemas, you can totally use the same model across multiple databases. In fact, it is typical to do this when people do ETL from one database to another.

A typical program looks like this:

Base.open(/*connection params*/);
List<Person> johns = Person.where("first_name = ?", "John");
Base.close();

• The Base.open() opens a connection and attaches it to the current thread.
• Next line Person.where(..) finds a connection on the thread, and uses it to select records.
• Base.close() finds a connection on the current thread and closes it.

Also, the ActiveJDBC models when first used in the JVM, will expect a connection on the current thread, and will pull metadata from the current schema. This will ensure that:

• Tables are related to models (http://javalite.io/english_inflections)
• Models know their attributes (columns)
• Models discover relationships (many to many, one to many, etc.)

This means that at any given time, a model assumes that it is connected to a database with the same structure that was used to get the metadata.

Next, this is probably what you want:

Base.open(/*connection params for DB1*/);
List<Person> johns = Person.where("first_name = ?", "John");
Base.close();

Base.open(/*connection params for DB2*/);
for(Person person:johns){
    person.set("first_name", "Bill").setId(null).saveIt();
}
Base.close();

Essentially, you will read data from one database, but will save it to another.

The call to person.setId(null) is needed to ensure the framework generates insert, and not update, see http://javalite.io/surrogate_primary_keys.