Active Record CRUD Made Easy

CRUD methods

CRUD is an abbreviation 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 above code snippet 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 posts = Post.findAll();

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

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

//find the record with its id, assuming id is a column in the posts table
ActiveRecord post100 = Post.findById(100);

//find the record with its primary key regardless the primary-key column name (id or postid or abc)
ActiveRecord post100 = Post.findByPK("100");

//find the record with its primary key assuming comments table has a composite primary key
ActiveRecord comment12OfPost100 = Comment.findByPK("100-12");

Update

Update a record in database:

ActiveRecord post = Post.where("title='Happy Java Programming'").getRecord();
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.where("title='Happy Java Programming'").getRecord();
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

Delete a record in database:

ActiveRecord post100 = Post.deleteById(100);

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

DELETE FROM posts WHERE id=8;

Delete a record in database gracefully:

ActiveRecord post100 = Post.findById(100);
post.delete();

Unlike using deleteById, the delete() method here may cascade the deletion to its associated comments if cascade is specified in the relation between post and comment models.

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

DELETE FROM posts WHERE id=8;
DELETE FROM comments WHERE post_id=8;

More convenience methods

Retrieve meta info

//all columns
List<ColumnInfo> columns = Post.columns();

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

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

//readonly column names
List<String> 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";
    }
}

Enforcing a specific database

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

public class Post extends ActiveRecord {
    public String getConnectionName() {
        return "blog_development";
    }
}

All posts records are now associated with the database connection named blog_development which is defined in the WEB-INF/config/database.properties file.

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.