The Data Class

After starting a new project in Android Studio, I added a SqlHelper class which extends the SQLiteOpenHelper.

Inheriting from this helper class requires that you implement two methods and a constructor:

public void onCreate(SQLiteDatabase db)
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)

And the required constructor:

 //required constructor
    //it is the constructor that actually creates the database
    public SqlHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }

The constructor calls the parent class constructor and passes it the database name and version. It is the parent class that actually creates the database.

The other two methods that the SQL Helper class is required to override are "onCreate" and "onUpgrade." The onCreate method initializes the database and sets up the table schema. We will look at this method in a minute. The onUpgrade method tells the program what do if the version of the database is changed. The default actions are to delete the current database and all its data and then recreate the tables.

  @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        android.util.Log.w("Constants", "Upgrading database will destroy all old data");
        db.execSQL("drop table if exists constants");
        onCreate(db);
    }

The next task is to define constants for the database, the tables and their columns. These constants are static and are used to give you access to the fields in you other activities. We are going to have a table for Vehicles and a table for gas mileage. The vehicle table will contain fields for an ID, Make, Model and Year. The Mileage table will have fields for Mileage, Vehicle_ID, Miles,Gallons, Cost, and MileageDate. There is no date data type in SQLite so it will be entered as text, though there are dateand time functions that we can use to parse it later. As in the diagram in the previous blog, the vehicle ID will be a foreign key in the Mileage table. SqLite wants key fields to be in the form of _ID. To prevent confusion, therefore, I use a different name for the foreign key than the primary key. I name it "Vehicle_ID." Here is the list of the constants:

//static database constants
    //database
    public final static String DATABASE_NAME="Mileage.db";
    public final static int DATABASE_VERSION=1;

    //tables
    public final static String VEHICLE_TABLE="Vehicle";
    public final static String MILEAGE_TABLE="Mileage";

    //column definitions for Vehicle
    public final static String VEHICLE_ID ="_ID";
    public final static String VEHICLE_MAKE="Make";
    public final static String VEHICLE_MODEL="Model";
    public final static String VEHICLE_YEAR="Year";

    //Column Definitions for Mileage
    public final static String MILEAGE_ID="_ID";
    public final static String VEHICLE_FK ="Vehicle_ID";
    public final static String MILEAGE_READING="Miles";
    public final static String TOTAL_GALLONS="Gallons";
    public final static String TOTAL_COST="COST";
    public final static String MILEAGE_DATE="MileageDate";

Now let's look at the onCreate method. We need to create two tables by concatenating SQL strings out of our constants. For elegance I separated them into two methods that return strings and called them in the onCreate method. Here are the methods that set up the create table strings:

   private String createVehicleTable(){
        String sql_Vehicle="Create table " +
                VEHICLE_TABLE +"(" +
                VEHICLE_ID + " integer primary key autoincrement, "  +
                VEHICLE_YEAR + " integer," +
                VEHICLE_MAKE + " text not null, "  +
                VEHICLE_MODEL + " text"  +
                ")";

        return sql_Vehicle;
    }

    private String createMileageTable(){
        String sql_Mileage="Create table " +
                MILEAGE_TABLE + " (" +
                MILEAGE_ID + " integer primary key autoincrement," +
                VEHICLE_FK + " integer, " +
                MILEAGE_READING + " real not null, " +
                TOTAL_GALLONS + " real not null," +
                TOTAL_COST + " real not null, "+
                MILEAGE_DATE + " text not null, " +
                "Foreign Key (" + VEHICLE_FK + ") references Vehicle(" + VEHICLE_ID + "))";


        return sql_Mileage;
    }

As I said above, these methods are then called in the onCreate method and the sql string passed to the database context's execSQL method.

   @Override
    public void onCreate(SQLiteDatabase db) {
        //call the methods that create the SQL for the tables
        String sql_Vehicle=createVehicleTable();
        String sql_Mileage=createMileageTable();

        //add the tables to the database
        db.execSQL(sql_Vehicle);
        db.execSQL(sql_Mileage);
    }

That is the complete helper class. The next tasks will be to set up the activities and use the database.