Accessing multiple databases

Scooter provides three ways to allow developers to use multiple databases in the same application. In fact, the Data Browser tool is a good example of accessing multiple databases.

ActiveRecord for multiple databases

ActiveRecord has the below constructor that allows developers to construct a record instance for a specific database.

public ActiveRecord(String connectionName, String tableName);

The connectionName here is one of the connection names defined in database.properties file. tableName should be the name of the table which this ActiveRecord instance represents.

ActiveRecord post = new ActiveRecord("blog_development", "posts");
post.setData("content", "Good morning");
post.save();//create a new post to blog_development database

ActiveRecord owner = new ActiveRecord("petclinic_development", "owners");
owner.setData("name", "John Doe");
owner.save();//save the new owner to petclinic_development database

When using a model class, it is better to override the getConnectionName() method to specify the database for the model. For example: 

//The post record comes from blog_mysql database.
public class Post extends ActiveRecord {
    public String getConnectionName() {
        return "blog_mysql";
    }
}

//The user record comes from user_oracle database.
public class User extends ActiveRecord {
    public void registerRelations() {
        hasMany("posts", "cascade:delete");
    }

    public String getConnectionName() {
        return "user_oracle";
    }
}

//Retrieve all posts from MySQL for a user in Oracle:
User user = User.findById(1001);
List posts = user.allAssociated("posts").getRecords();

SqlServiceClient for multiple databases

SqlServiceClient provides many convenient static methods for accessing records in a database. Some of the methods are ready for using with multiple database connections.

All those methods in SqlServiceClient class that accept a Map inputs parameter can be used to access multiuple databases.

Map inputs = new HashMap();

//This line specifies the database to be used:
inputs.put(DataProcessor.input_key_database_connection_name, "petclinic_development");
inputs.put("id", new Integer(101));
String sql = "SELECT * FROM owners WHERE id = ?id";

//Retrieve record with id 101:
Object owner101 = SqlServiceClient.retrieveObjectBySQL(sql, inputs);

In this example, petclinic_development is a database connection defined in the database.properties file.

Dynamic database accessing

The above two methods require that database connection information must be already defined in the database.properties file. What if this information is only available at run time? No problem, Scooter can handle that throught DatabaseConnectionContext object. This means developers can construct a DatabaseConnectionContext instance that contains database connection properties like those defined in the database.properties file.

Properties properties = new Properties();
properties.setProperty("driverClassName", "com.mysql.jdbc.Driver");
properties.setProperty("url", "jdbc:mysql://localhost/blog_development");
properties.setProperty("username", "root");
properties.setProperty("password", "");
DatabaseConnectionContext dcc = new JdbcConnectionContext(properties);

Map inputs = new HashMap();
//This line specifies the database to be used:
inputs.put(DataProcessor.input_key_database_connection_context, dcc);
inputs.put("id", new Integer(101));
String sql = "SELECT * FROM owners WHERE id = ?id";

//Retrieve record with id 101:
Object owner101 = SqlServiceClient.retrieveObjectBySQL(sql, inputs);

Developers can also create an instance of DataSourceConnectionContext object if the database connection is represented by a datasource. For properties allowed in either JdbcConnectionContext or DataSourceConnectionContext, please refer to the content of the database.properties file or here.