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

Using SQLite Database Model to Store, Query and Manipulate Your Data

Retrieving Data from the Database

SQLite Database Model provides a variety of convenience methods for querying your database and inflating the results into an instance of the appropriate class or collections thereof.

The methods methods provided for querying the database as standard are:

Lets use the List<T> getAll(String whereClause, Object... whereArgs) method to query our Cars database for a list of cars with a mileage > 50000. The first step is to get the ObjectModel for the Car class by calling getObjectModel(Car.class) on a CarDatabaseModel instance. The Car ObjectModel exposes all the methods listed above with the generic type set to Car.

    public static void selectDemo() {
        CarDatabaseModel carDatabaseModel = null;
        try {
            //Get a DatabaseModel instance         
            carDatabaseModel = new CarDatabaseModel();

            // Use the DatabaseModel to get the ObjectModel for the Car class
            List<Car> cars = carDatabaseModel.getObjectModel(Car.class)
                    //Call get all cars with a milege > 50000
                    .getAll("mileage > ?", 50000);
           
            //Display the results
            for (Car car : cars)
                System.out.println(car);

        } catch (ClassNotFoundException | NoSuchFieldException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (carDatabaseModel != null) {
                //Disconnect the DatabaseModel's connection to the SQLite database
                carDatabaseModel.disconnect();
                //Make the DatabaseModel eligible for garbage collection
                carDatabaseModel = null;
            }
        }
    }

Notice the ? in the whereClause parameter, "mileage > ?". Any question mark characters are replaced with the string value of the objects supplied in the whereArgs parameter sequentially.

One could also hard code the value in the whereClause parameter:

List<Car> cars = carDatabaseModel.getObjectModel(Car.class).getAll("mileage > 50000");

The output of either of the methods above is:

Car [registration=BM52ZVGP, make=Volkswagen, model=Polo, mileage=60149, manufacturedDate=Sat Jun 05 00:00:00 SAST 2010, registered=false]
Car [registration=PZ56XTGP, make=Ford, model=Ranger, mileage=162276, manufacturedDate=Tue Jul 26 00:00:00 SAST 2011, registered=true]

If however, we’re just looking for the Car with the lowest mileage that is greater than 50000, we could query the DatabaseModel using the getFirst() method with a sort on mileage:

    Car carWithTheLowestMileageAbove50k = carDatabaseModel.getObjectModel(Car.class)
            .getFirst("mileage > ?",
                      new Object[]{
                        50000
                      },
                      "mileage");
   
    System.out.println(carWithTheLowestMileageAbove50k);

The output of which would be:

Car [registration=BM52ZVGP, make=Volkswagen, model=Polo, mileage=60149, manufacturedDate=Sat Jun 05 00:00:00 SAST 2010, registered=false]

The orderBy parameter can be a comma separated list of fields on which the results of the database query should be ordered. the orderBy parameter may include the "DESC" keyword.

Since we created foreign key relationships between the Wheel & Engine classes and the Car class we know that when we retrieve a Car from the database using SQLite Database Model, the returned Car’s engine reference and wheels collection will be filled for us automatically.

    public static void selectDemo() {
        CarDatabaseModel carDatabaseModel = null;
        try {

            carDatabaseModel = new CarDatabaseModel();

            ObjectModel<WheelNut, ?, ?> wheelNutModel = carDatabaseModel
                    .getObjectModel(WheelNut.class);

            // Use the DatabaseModel to get the ObjectModel for the Car class
            List<Car> cars = carDatabaseModel.getObjectModel(Car.class)
                    //Call get all cars with a milege > 50000
                    .getAll("mileage > ?", 50000);
           
            //Display the results          
            for (Car car : cars) {
                System.out.println(car);

                // The Volkswagen's Engine was not created in the
                // CarDataBaseModel.onInsertDefaultValues() method.
                System.out.println(car.getEngine());

                for (Wheel wheel : car.getWheels()) {
                    System.out.println(wheel);

                    /*
                     * The WheelNut Class was not created with a reference to
                     * it's wheel and the wheel has no reference to it's wheel
                     * nuts. Therefore we need to use the WheelNutModel to
                     * get the wheel nuts.
                     *
                     * If you would like the wheel nuts to be retrieved, inserted,
                     * updated & deleted along with their wheels. Add a
                     * Collection<WheelNut> property to the Wheel class and a
                     * Wheel property to the WheelNut class. Then include the
                     * reference variables in WheelNut.wheelId's ForeignKey
                     * attribute:
                     *
                     * @ForeignKey(table="Wheel", column="wheelId",
                     * parentReference="wheelNuts", childReference="wheel")
                     * private int wheelId;
                     *
                     * Only the Ford Ranger's Wheels were seeded with WheelNuts
                     */

                    for (WheelNut wheelNut : wheelNutModel.getAll(
                            "wheelId = ?", wheel.getWheelId()))
                        System.out.println(wheelNut);
                }
                System.out.println();
            }

        } catch (ClassNotFoundException | NoSuchFieldException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (carDatabaseModel != null) {
                //Disconnect the DatabaseModel's connection to the SQLite database
                carDatabaseModel.disconnect();
                //Make the DatabaseModel eligible for garbage collection
                carDatabaseModel = null;
            }
        }
    }

The WheelNut class was purposely created with a foreign key reference to Wheel, but without the optional @ForeignKey parameter, parentReference set. This means that we need to manually retrieve a Wheel’s WheelNuts by getting the WheelNut ObjectModel from the DatabaseModel and querying it for any WheelNuts with the same wheelId as the Wheel.

The output of the selectDemo() method above is as follows:

Car [registration=BM52ZVGP, make=Volkswagen, model=Polo, mileage=60149, manufacturedDate=Sat Jun 05 00:00:00 SAST 2010, registered=false]
null
Wheel [wheelId=1, size=18, car=Volkswagen Polo (BM52ZVGP)]
Wheel [wheelId=2, size=18, car=Volkswagen Polo (BM52ZVGP)]
Wheel [wheelId=3, size=18, car=Volkswagen Polo (BM52ZVGP)]
Wheel [wheelId=4, size=18, car=Volkswagen Polo (BM52ZVGP)]

Car [registration=PZ56XTGP, make=Ford, model=Ranger, mileage=162276, manufacturedDate=Tue Jul 26 00:00:00 SAST 2011, registered=true]
Engine [carRegistration=PZ56XTGP, engineCapacity=3.2, torque=470.0, power=147.0, fuel=PETROL]
Wheel [wheelId=9, size=18, car=Ford Ranger (PZ56XTGP)]
WheelNut [wheelNutId=1, wheelId=9, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=2, wheelId=9, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is depleted]]
WheelNut [wheelNutId=3, wheelId=9, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=4, wheelId=9, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is depleted]]
WheelNut [wheelNutId=5, wheelId=9, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
Wheel [wheelId=10, size=18, car=Ford Ranger (PZ56XTGP)]
WheelNut [wheelNutId=6, wheelId=10, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=7, wheelId=10, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is depleted]]
WheelNut [wheelNutId=8, wheelId=10, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=9, wheelId=10, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is depleted]]
WheelNut [wheelNutId=10, wheelId=10, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
Wheel [wheelId=11, size=18, car=Ford Ranger (PZ56XTGP)]
WheelNut [wheelNutId=11, wheelId=11, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=12, wheelId=11, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is depleted]]
WheelNut [wheelNutId=13, wheelId=11, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=14, wheelId=11, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is depleted]]
WheelNut [wheelNutId=15, wheelId=11, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
Wheel [wheelId=12, size=18, car=Ford Ranger (PZ56XTGP)]
WheelNut [wheelNutId=16, wheelId=12, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=17, wheelId=12, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is depleted]]
WheelNut [wheelNutId=18, wheelId=12, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=19, wheelId=12, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is depleted]]
WheelNut [wheelNutId=20, wheelId=12, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]

Inserting a Record into the Database

the ObjectModel methods related to inserting objects into the database are:

  • long insert(T t)
  • long[] insertAll(Collection<T> collection)
  • T insertAndReturnUpdated(T t)
  • List<T> insertAllAndReturnUpdated(Collection<T> ts)

The long insert(T t) & long[] insertAll(Collection<T> collection) methods will insert an object or a collection of objects into the database respectively. The T insertAndReturnUpdated(T t) & List<T> insertAllAndReturnUpdated(Collection<T> ts) methods also insert the object(s) but return them again after performing a fresh select on the database to pick up any modifications made to the data by SQLite as a result of any triggers or an automatically incremented primary key.

To demonstrate how to insert objects into a SQLite database using SQLite Database Model, let’s create a new car, this time a Honda Jazz, and insert it into the database along with its wheels and wheel nuts.

    public static void insertDemo() {
        CarDatabaseModel carDatabaseModel = null;
        try {
            carDatabaseModel = new CarDatabaseModel();

            Car honda = new Car();
            honda.setMake("Honda");
            honda.setModel("Jazz");
            honda.setRegistered(true);
            honda.setRegistration("RG23BTGP");
            honda.setMileage(55673);
            honda.setManufacturedDate(Date.valueOf("2013-12-03"));

            honda.setWheels(new ArrayList<Wheel>(4));
            for (int w = 0; w < 4; w++) {
                Wheel wheel = new Wheel();
                wheel.setSize(16);
                honda.getWheels().add(wheel);
            }

            ...

Above we’ve created the new Honda record and added 4 wheels to it’s wheels collection. We know that inserting the Honda into the database will automatically insert its wheels also as we created a foreign key reference to Car in the Wheel class on the Wheel.carRegistration property and specified Car.wheels as the relationship parent (Car)’s reference to the relationship child (Wheel). We don’t even need to set each wheel’s car registration to the same registration as the Honda, this will be done for us internally.

Next, let’s insert the Honda into the database using the Car ObjectModel’s insertAndReturnUpdated(T t) method. Using insertAndReturnUpdated(T t) means that after the Honda (including it’s wheels) is inserted, it will be updated by a fresh select on the database. This is useful as we’re going to need each wheel’s wheelId that will be assigned by SQLite when the wheels are inserted, as the wheelId field is an auto-incrementing primary key.

            ...

            honda = carDatabaseModel.getObjectModel(Car.class)
                    .insertAndReturnUpdated(honda);

            ...

Next, lets create new wheel nuts per wheel. As we chose not to create a Collection<WheelNut> wheelNuts property in the Wheel class and link it as the parentReference in the @ForeignKey annotation on WheelNut.wheelId, we need to set each WheelNut’s wheelId and insert them manually.

            ...

            ObjectModel<WheelNut, ?, ?> wheelNutModel = carDatabaseModel.getObjectModel(WheelNut.class);
           
            for (Wheel wheel : honda.getWheels()) {
                for(int i = 0; i < 5; i++){
                    WheelNut wheelNut = new WheelNut();
                    wheelNut.setWheelId(wheel.getWheelId());
                    wheelNut.setThingaMaJigger(new ThingaMaJigger(Status.CHARGED));
                    wheelNutModel.insert(wheelNut);
                }
            }

            ...

Now that we have inserted the Honda, it’s wheels and their wheel nuts into the database, lets disconnect the database and then go back and make sure that the Honda has been inserted.

            ...

            /*
             * For the sake of this demonstration let's close our database
             * connection and dispose our models to prove that the records were
             * persisted.
             */

            carDatabaseModel.disconnect();
            carDatabaseModel = null;
            wheelNutModel = null;
            System.gc();

            /*
             * Now lets get the Honda the database and display the results.
             * Getting the Honda will automatically get it's wheels due to the
             * relationship we created between the two but we'll need to get
             * each wheel's wheel nuts manually
             */

            carDatabaseModel = new CarDatabaseModel();
            honda = carDatabaseModel.getObjectModel(Car.class).getFirst("registration = ?", "RG23BTGP");
            System.out.println(honda);
           
            wheelNutModel = carDatabaseModel.getObjectModel(WheelNut.class);
            for (Wheel wheel : honda.getWheels()) {
                System.out.println(wheel);
                for (WheelNut wheelNut : wheelNutModel.getAll("wheelId = ?", wheel.getWheelId())) {
                    System.out.println(wheelNut);
                }
            }

        } catch (ClassNotFoundException | NoSuchFieldException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (carDatabaseModel != null) {
                carDatabaseModel.disconnect();
                carDatabaseModel = null;
            }
        }
    }

The output of the above insertDemo() method is as follows:

Car [registration=RG23BTGP, make=Honda, model=Jazz, mileage=55673, manufacturedDate=Tue Dec 03 00:00:00 SAST 2013, registered=true]
Wheel [wheelId=17, size=16, car=Honda Jazz (RG23BTGP)]
WheelNut [wheelNutId=25, wheelId=17, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=26, wheelId=17, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=27, wheelId=17, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=28, wheelId=17, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=29, wheelId=17, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
Wheel [wheelId=18, size=16, car=Honda Jazz (RG23BTGP)]
WheelNut [wheelNutId=30, wheelId=18, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=31, wheelId=18, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=32, wheelId=18, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=33, wheelId=18, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=34, wheelId=18, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
Wheel [wheelId=19, size=16, car=Honda Jazz (RG23BTGP)]
WheelNut [wheelNutId=35, wheelId=19, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=36, wheelId=19, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=37, wheelId=19, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=38, wheelId=19, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=39, wheelId=19, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
Wheel [wheelId=20, size=16, car=Honda Jazz (RG23BTGP)]
WheelNut [wheelNutId=40, wheelId=20, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=41, wheelId=20, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=42, wheelId=20, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=43, wheelId=20, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]
WheelNut [wheelNutId=44, wheelId=20, thingaMaJigger= ThingaMaJigger [The WheelNut's ThingaMaJigger is charged]]

Updating Records in the Database

The ObjectModel convenience methods for updating Objects in the database are as follows.

  • int insertOrUpdate(T t)
  • int insertOrUpdate(T t, String whereClause)
  • int insertOrUpdate(T t, String whereClause, Object... whereArgs)
  • insertOrUpdateAll(Collection<T> collection)
  • int update(T t)
  • int update(T t, String whereClause, Object... whereArgs)

When we seeded the database with some default records, the Volkswagen Polo was created without a corresponding record in the Engine table. To demonstrate how we can update records and related child objects using SQLite Database Model, let’s give it one!

The first step is to retrieve the Volkswagen Polo from the database.

    public static void updateDemo() {
        CarDatabaseModel carDatabaseModel = null;
        try {
            carDatabaseModel = new CarDatabaseModel();
            ObjectModel<Car, ?, ?> carModel = carDatabaseModel.getObjectModel(Car.class);

            Car polo = carModel.getFirst("make = ? AND model = ? ", "Volkswagen", "Polo");

            ...

Now that we have the Polo, let’s update it’s mileage and give it an engine.

            ...

            /*
             * Let's update the Polo's mileage
             */

            int mileage = polo.getMileage() + 10000;
            polo.setMileage(mileage);
           
            /*
             * The Polo was initially created without a corresponding record in
             * the Engine table. Let's give it one!
             */

            Engine engine = new Engine();
            engine.setEngineCapacity(1.2F);
            engine.setFuel(Fuel.DIESEL);
            engine.setPower(55F);
            engine.setTorque(180F);
            polo.setEngine(engine);

            /*
             * All that we have to do to update the Polo is pass it to the
             * ObjectModel's update() method
             */

            carModel.update(polo);

            ...

All that we have to do to update the Polo is pass as a parameter to the update() method of the car ObjectModel.

With the Polo updated, lets disconnect the database and then double check that our changes have been saved.

            ...

            /*
             * For the sake of this demonstration let's close our database
             * connection and dispose our models to prove that the records were
             * persisted.
             */

            carDatabaseModel.disconnect();
            carDatabaseModel = null;
            carModel = null;
            System.gc();

            /*
             * Now lets get the Polo from the database and display the results.
             * Getting the Polo will automatically get it's Engine due to the
             * relationship we created between the Car and Engine Models
             */

            carDatabaseModel = new CarDatabaseModel();
            carModel = carDatabaseModel.getObjectModel(Car.class);

            polo = carModel.getFirst("make = ? AND model = ?", "Volkswagen", "Polo");

            System.out.println(polo);
            System.out.println(polo.getEngine());

        } catch (ClassNotFoundException | NoSuchFieldException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (carDatabaseModel != null) {
                carDatabaseModel.disconnect();
                carDatabaseModel = null;
            }
        }
    }

The output of the updateDemo() method is as follows:

Car [registration=BM52ZVGP, make=Volkswagen, model=Polo, mileage=70149, manufacturedDate=Sat Jun 05 00:00:00 SAST 2010, registered=false]
Engine [carRegistration=BM52ZVGP, engineCapacity=1.2, torque=180.0, power=55.0, fuel=DIESEL]

Deleting Records From the Database

The SQLite Database Model convenience methods available for deleting records from a database are:

  • int delete(T t)
  • int deleteAll(Collection<T> collection)
  • int deleteAll()
  • int deleteAll(String whereClause)
  • int deleteAll(String whereClause, Object... whereArgs)

To demonstrate how we can use SQLite Database Model to delete records from our database let’s delete the Honda Jazz, it’s wheels and wheel nuts that we added earlier.

    public static void deleteDemo() {
        CarDatabaseModel carDatabaseModel = null;
        try {
            carDatabaseModel = new CarDatabaseModel();
            ObjectModel<Car, ?, ?> carModel = carDatabaseModel.getObjectModel(Car.class);

            Car honda = carModel.getFirst("make = ? ", "Honda");
            if (honda != null) {
               
                /*
                 * Deleting the honda will automatically delete it's wheels, however
                 * since we opted not to create a reference to a wheel's wheel nuts
                 * in the wheel class we need to delete each wheel's wheel nuts
                 * manually before we can delete them
                 */

                for (Wheel wheel : honda.getWheels()) {
                    carDatabaseModel.getObjectModel(WheelNut.class).deleteAll("wheelId = ?", wheel.getWheelId());
                }
           
                carModel.delete(honda);
            }
            ...

To delete the Honda we’ll need to delete it’s wheel nuts and wheels first due to the foreign key constraints between the three. SQLite Database Model can cascade delete operations recursively provided that the parentReference attributes in the @ForeignKey relationships have been set. We have not included the parentReference for the Wheel/WheelNuts relationship to demonstrate how we can perform these CRUD operations manually.

Once we have manually deleted the WheelNuts we can simply delete the Honda. Deleting the Honda will delete it’s wheels as we did set the parentReference attribute in the @ForeignKey attribute that created the Car/Wheel relationship.

            ...

            /*
             * Now that we have deleted the Honda, let's output all the
             * remaining cars in the Car table
             */

            for (Car car : carModel.getAll()) {
                System.out.println(car);
            }

        } catch (ClassNotFoundException | NoSuchFieldException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (carDatabaseModel != null) {
                carDatabaseModel.disconnect();
                carDatabaseModel = null;
            }
        }
    }

The output of the deleteDemo() method is as follows:

Car [registration=BM52ZVGP, make=Volkswagen, model=Polo, mileage=70149, manufacturedDate=Sat Jun 05 00:00:00 SAST 2010, registered=false]
Car [registration=FS13TWGP, make=Mazda, model=3, mileage=48526, manufacturedDate=Sat Mar 17 00:00:00 SAST 2012, registered=true]
Car [registration=PZ56XTGP, make=Ford, model=Ranger, mileage=162276, manufacturedDate=Tue Jul 26 00:00:00 SAST 2011, registered=true]


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