Introduction
I am working on a project and I confronted a weird use case that maybe ActiveJDBC is not meant for, I pledge for patience because many things in this project are not in my control:
I have 10 to 15 small/medium databases (~30 tables each, 40000 records max) and most of them share a "core" schema of 15 Tables, but at the same time they have some specific Tables unique for each database, they are all maintained by legacy systems which I don't have access to.
Goal
We(me and some conrades) will need to centralize the data in a kind of "convolunted data warehouse". Unfortunately for higher reasons, I can not use any technologies other than ActiveJDBC and everything other than that needs to be write by us (I know that this could be handled better with MongoDB and/or Liquibase)
We already handled the connection between the Databases, and the project itself is going well for most part. The part of the program that handles the core schema that all the databases share is already "working", but We are having trouble with their unique Tables.
I get all table names from the databases from a query that is made at runtime (not my choice either). We need to keep the number of classes at minimum preferably
Finally my question
Can I create a Generic/Dynamic Model or something similar, that can hold Data from a query at runtime? something like:
Model a = Base.findall("select * from ?", TableName)
or
Model a = Model.fromTable(Tablename)
I know it is weird to use a Model that way, but that would simplify our lives so much if we could get Table Data that way.
We will use this kind of Model mostly to get "raw" data from the Tables and satisfy interfaces, so no need to worry about each table relationship right now.
Thanks in Advance
@Edit: Thank you Igor for this awesome tool!
We kinda did it(almost)! Thank you. We were using a similar approach for the "core schema", but you kinda gave us some light.
As I commented:
We need to use the results of Base.findall() like it were a Model, would it be possible?
You are in for a treat. I'm sensing from your reference to ActiveJDBC that this is not your choice, but you will be surprised at its flexibility. Let me dispell a couple of things first: MongoDB is not a relation database, and Liquibase is a DB migration system. JavaLite provides a much simpler DB-Migrator.
Now, to the meat of the answer. As you might already know, an ActiveJDBC is really a Map on steroids. This means you can do this:
Person p = new Person();
p.fromMap(aMap);
See, the method Model#fromMap(Map) reads attributes from a map as long as they correspond to names of this models' attributes and overwrites its values by values from the map.
Lets write some code:
For instance, there is a table called PEOPLE in first database, and USERS in "other" database such that:
create table USERS( first_name VARCHAR(56));
create table PEOPLE( firstname VARCHAR(56));
As you can see, the "first name" columns exist in both databases/tables, but have different names
So, lets write code to read from USERS and save to PEOPLE:
// define model
public class Person extends Model {}
...
Base.open(...) // open default database
DB otherDB = new DB("other_database");
otherDB.open(...); // open other database
// read users from "other" database
List<Map> users = db.findAll("select first_name \"firstname\" from users");
// save people into default database
for(Map user: users){
Person p = new Person();
p.fromMap(user);
p.saveIt;
}
Base.close();
otherDb.close();
I hope this solves your problem!