Active Record

Active Record Pattern

Active Record is a design pattern first proposed by Martin Fowler. According to Martin Fowler, an active record object represents a row in a database table. Contrary to J2EE's Value Object pattern which is an object having only data fields but no behavior, and to the popular DAO pattern which only has data access behavior but no data, the Active Record pattern describes a rich object which has both data fields and data access behavior.

Scooter Framework's ActiveRecord class is an implementation of Active Record pattern. All domain objects, if they want to take advantage of functionalities of ActiveRecord, must become a subclass of this class.

For example, a Post class which represents a blog post record in database, can be implemented as follows:

public class Post extends ActiveRecord {
}

Even though this class only has one code line (omitting import and package statement lines), it is already very powerful as it inherits many methods from its super class. It also has knowledge of posts table meta data information such as name and properties of all columns including primary key column(s).

By default, the Post class in singular form above maps to a table named posts in plural form in database. This can be changed by the use.plural.table.name property in the database.poperties file. There are some other properties in that file related to table naming conventions. For example, if names of all your tables start with a prefix of "CRM_", you simply use the global.table.naming.prefix property.

CRUD Made Easy

CRUD is an abbreviations of four common operations of data acess: create, read, update and delete. CRUD is easy with Scooter Framework's ActiveRecord.

Create

Create a record in database:

ActiveRecord post = Post.newRecord();
post.setData("title", "Happy Java Programming");
post.setData("body", "Java programming is fun.");
post.create();

The last line above is equivalent to the following sql statement:

    INSERT INTO posts (title, body) VALUES ('Happy Java Programming', 'Java programming is fun.');

It will create a new record in a posts table.

Read

For a single ActiveRecord instance, it can refresh itself with the latest data in database:

ActiveRecord post = ...;
post.reload();

The last line above is equivalent to the following sql statement, assuming 8 is the primary key value of the record:

SELECT * FROM posts WHERE id = 8;

More READ examples:

//read in all records
List records = Post.findAll();

//read in all records with a condition
List records = Post.findAll("id in (100, 101, 102)");

//read in all records by SQL query
List records = Post.findAllBySQL("SELECT * FROM posts");

//find the last record
ActiveRecord record = Post.findLast("title='Programming Java'")

Update

Update a record in database:

ActiveRecord post = Post.findFirst("title='Happy Java Programming'");
post.setData("title", "Scooter Rocks!");
post.update();

The last line above is equivalent to the following sql statement:

UPDATE posts SET title='Happy Java Programming',
                 body='Java programming is fun.'
WHERE id=8;

It will update the title of the record with id 8--assuming 8 is the value of the primary key, in the posts table.

The update() method will list all columns of the posts table in the update statement. Since we only edited the title column, we can use updateChanged():

ActiveRecord post = Post.findFirst("title='Happy Java Programming'");
post.setData("title", "Scooter Rocks!");
post.updateChanged();

The last line above is equivalent to the following sql statement which uses only those columns whose data are changed:

UPDATE posts SET title='Happy Java Programming'
WHERE id=8;

Scooter Framework's ActiveRecord is smart enough to track which fields are changed and only use those fields in the update sql statement.

Delete

Delte a record in database:

ActiveRecord post = Post.findFirst("title='Happy Java Programming'");
post.delete();

The last line above is equivalent to the following sql statement:

DELETE FROM posts WHERE id=8;

More convenience methods

Retrieve meta info

//all columns
List columns = Post.columns();

//all column names
List columnNames = Post.columnNames();

//primary key column names
List pkNames = Post.primaryKeyNames();

//readonly column names
List rNames = Post.readOnlyColumnNames();

//rowInfo
RowInfo rowInfo = Post.rowInfo();

//table name
String table = Post.tableName();

Calculation

//count all records
long result = Post.count();

//find the average value of a column
Object result = Post.average("salary");

//find the max value of a column
Object result = Post.maximum("salary");

//find the min value of a column
Object result = Post.minimum("salary");

//find the sum of a column
Object result = Post.sum("salary");

Dynamic Retrieval

You can simulate Ruby On Rails' dynamic finder by using ActiveRecord's findFirstBy(), findLastBy() and findAllBy() methods.

//The class
class Employee extends ActiveRecord {}

//retrieve the employee whose first name is John, last name is Doe
ActiveRecord john = Employee.findFirstBy("firstName_and_lastName", {"John", "Doe"});

//retrieve the employee whose first name is John, last name is Doe and age is 29.
ActiveRecord john = Employee.findFirstBy("firstName_and_lastName_and_age", {"John", "Doe", new Integer(29)});

//retrieve all employees who live in LA
List employees = Employee.findAllBy("city", {"LA"});

More on ActiveRecord

Specifying Table Name Directly

You can set the corresponding table name of an ActiveRecord class directly as follows:

public class Post extends ActiveRecord {
    public String getTableName() {
        return "entries";
    }
}

Using Non-database Fields

ActiveRecord implicitly pulls fields of a model from database. Therefore, you do not need to list database table fields in the class. However, what if you want to have some non-database fields in the model only? This kind of fields is called extraField in ActiveRecord. You can deal with them as follows:

public class User extends ActiveRecord {
    protected void declaresExtraFields() {
        //declare two extra fields
        setExtraFields("nickName, formerName");
    }
}

//Then you can use them as you use any other fields
ActiveRecord david = new User();
david.setData("nickName", "Dave");
System.out.println(david.getField("nickName"));

The values of this kind of fields are transient which means that they are lost once the reference to the object is gone.

Using Protected Fields

Protected fields are those table columns that you do not want users to change in an application. They can only be changed through database tools. For example, you may declare username and password as protected fields. Once they are entered in database, you would not allow users to change them.

public class User extends ActiveRecord {
    protected void declaresProtectedFields() {
        //declare two protected fields
        setProtectedFields("username, password");
    }
}

//Then operations like clearAndSetData() and setData() will have no effects on
//the protected fields
ActiveRecord david = new User();
david.setData("username", "IJustChangedYou");
System.out.println(david.getField("username"));//should still print out unchanged database record.