Getting Started With SQLite Database Model Part 1 – Defining Your Object Models

Defining your Object Models

Before we get started with SQLite Database Model, we need to define the objects that we need to store in the database. Any Java object can be used by simply decorating it’s properties with annotations to control the way in which those fields are stored in corresponding the database table.

Primary Keys

In the Car class below, the @PrimaryKey(autoIncrement=false) annotation has been used to designate the String registration; property as it’s primary key. Primary key fields cannot be stored with null values and must be unique. If the field is numeric, the optional autoIncrement parameter can be set to true, in which case database will automatically assign a sequential numeric value to new objects inserted into the database. This primary key will also be used in mapping objects to their corresponding database records during update and delete operations.

If you do not have access to the Object’s source, the primary keys etc can be set by creating a custom ObjectModel<MyCompiledType> for the Object as I’ll demonstrate a little later on.

public class Car {

    @PrimaryKey
    private String registration;
    private String make;
    private String model;
    private int mileage;
    private Date manufacturedDate;
    private boolean registered;

    /*
     * The wheels collection will automatically be filled by SQLite Database
     * Model when the car is fetched from the database as it has been defined as
     * the parent reference in the foreign key relationship in the Wheel class
     */

    private Collection<Wheel> wheels;

    /*
     * The Engine object will automatically be initialized by SQLite Database
     * Model when the car is fetched from the database as it has been defined as
     * the parent reference in the foreign key relationship in the Engine class
     */

    private Engine engine;

    /*
     * Getters, setters & toString() omitted...
     */


}

Each car will have information about it’s Wheels stored in a separate table, related by a foreign key reference to the Car table’s primary key column, registration.

Configured in this way, the table created in SQLite will look like this:

Car

registration make model mileage manufacturedDate registered (boolean)
BM52ZVGP Volkswagen Polo 60149 1275688800000 0

As Engine is not a supported type, SQLite Database Model will simply ignore the field when creating the columns for the Car table. To instruct SQLite Database Model to ignore supported fields, decorate the field with the @Transient annotation.

public class Wheel {

    @PrimaryKey(autoIncrement = true)
    private int wheelId;

    /*
     * By decorating the size property with the @Nullable annotation with the
     * value set to false, we are instructing SQLite Database Model to create
     * the Wheel table with a non-null value constraint on the size column
     */

    @Nullable(value = false)
    private int size;

    /*
     * Specifying the childReference as the car Field means that this object's
     * car property will automatically be initialized when this wheel is
     * retrieved from the database. Specifying the parentReference as "wheels"
     * means that when the parent (Car) object is retrieved from the database,
     * SQLite Database Model will search for a property in the Car object called
     * wheels that is of the type Collection<Wheel> and fill it with all the
     * wheels in the Wheel table with the same registration as the car.
     */

    @ForeignKey(table = "Car", column = "registration", childReference = "car", parentReference = "wheels")
    private String carRegistration;

    private Car car;

    /*
     * Getters, setters and toString() omitted...
     */

}

The Wheel table will be constructed by SQLite Database Model as follows:

Wheel

wheelId size carRegistration
1 14 BM52ZVGP 2 14 BM52ZVGP 3 14 BM52ZVGP 4 14 BM52ZVGP

The Wheel object has a numeric wheelId property that we’ll use as it’s primary key. Setting the @PrimaryKey annotation’s autoIncrement option to true means that SQLite will automatically assign a sequential numeric value for the primary key when the object is first inserted into the database.

Foreign Keys & Relationships

In order to create the relationship between a car and it’s wheels, the Wheel class has a String carRegistration; property that will contain the registration of the car to which the wheel belongs. In order to create a foreign key, we must reference the foreign table’s primary key.

The @ForeignKey annotation has two required parameters, table (by default this is the name of the parent object) & column (by default columns are created with exactly the same name as the Field they represent).

The optional parameter childReference can be set to the name of a property in the child (Wheel) object that is of the same type as the parent (Car) object. This field will automatically be filled with a reference to the parent object instance when the child object is retrieved from the database.

The parentReference can optionally be set to the name of a property in the parent (Car) object that is of the same type as the child (Wheel) object in the case of a one-to-one relationship or a collection of the child type (Collection) in the case of a to-many relationship. This field will automatically be filled with a reference(s) to the child object(s) when the object is retrieved from the database.

One-to-one Relationsips

The relationship between a car and it’s wheels is one-to-many, one Car has many Wheels. Let’s create another table to contain each car’s Engine. Each car can have only one engine and each engine must belong to only one car.

public class public class Engine {

    // SQLite Database Model can map any enum to a column in a database table by
    // default
    enum Fuel {
        PETROL, DIESEL
    }

    @PrimaryKey
    @ForeignKey(table = "car", column = "registration", parentReference = "engine")
    private String carRegistration;
    private float engineCapacity;
    private float torque;
    private float power;
    private Fuel fuel;

    /*
     * Getters, setters and toString() omitted...
     */

}

The engine table will be constructed by SQLite Database Model as follows:

Engine

carRegistration engineCapacity torque power fuel
PZ56XTGP 3.2 470.0 147.0 PETROL

To create a one-to-one relationship, the primary key of the child object (Engine.carRegistration) must contain the same value as the primary key of the parent (Car.registration). The two fields are then linked by a placing a @ForeignKey annotation on the Engine’s carRegistration primary key, pointing to the car’s registration property.

This time round we have only given the parent (Car) a reference to the child (Engine) object, so that if we retrieve the car from the database it’s engine will be retrieved as well. Furthermore if we delete the car it’s engine will automatically be deleted and if we modify the engine’s properties and call an update on the car, the modifications to the engine will also be persisted.

Mapping Custom/Unsupported Types to Database Table Columns

Lastly, to demonstrate how we can map custom field types to database columns lets create the WheelNut class with a complex type property ThingaMaJigger. By default SQLite Database Model will ignore the WheelNut’s thingaMaJigger property as it is not a supported type. To map the thingaMaJigger property we’ll need to customize the WheelNut’s ObjectModel which we will cover later in this tutorial.

public class WheelNut {

    @PrimaryKey(autoIncrement = true)
    private int wheelNutId;

    @ForeignKey(table = "Wheel", column = "wheelId")
    private int wheelId;

    /**
     * A complex type to demonstrate how custom objects can be mapped to database table columns by an ObjectModel
     */

    public static class ThingaMaJigger {

        enum Status{
            CHARGED,
            DEPLETED;
           
            public String toString() {
                return this.name().toLowerCase();
            };
        }
        private Status status;
       
        public ThingaMaJigger(Status status){
            this.status = status;
        }
       
        public Status getStatus() {
            return status;
        }

        public void setStatus(Status status) {
            this.status = status;
        }

        @Override
        public String toString() {
            return " ThingaMaJigger [The WheelNut's ThingaMaJigger is " + status.toString() + "]";
        }

    }

    private ThingaMaJigger thingaMaJigger;

    /*
     * Getters, setters and toString() omitted...
     */

}

The WheelNut’s @ForeignKey annotation, creating the relationship between a Wheel and it’s WheelNuts does not specify a parentReference or a childReference.

The parentReference and childReference have been omitted so that we can explore how to insert, update & delete related child objects manually later in this tutorial.

One we have created our custom Object model to map the WheelNut’s thingaMaJigger, a WheelNut with a charged thingaMaJigger would be created in the database as follows:

WheelNut

wheelNutId wheelId thingaMaJigger
1 9 2


Getting Started With SQLite Database Model Part 1 – Defining Your Object Models

Getting Started With SQLite Database Model Part 2 – Creating the Database Model

Getting Started With SQLite Database Model Part 3 – Using SQLite Database Model to Store, Query and Manipulate Your Data