The database contract is a class where we define the name of our database and the name for all the tables and columns as constants.

It serves two purposes: firstly, it is a good way to have an idea of the structure of the database at first sight.

To create a database package and the DatabaseContract.java class, use the following code:

public class DatabaseContract {
  
  public static final String DB_NAME = "mastering_android_app.db";
  
  
  public abstract class JobOfferTable {
    
    public static final String TABLE_NAME = "job_offer_table";
    
    public static final String TITLE = "title";
    public static final String DESC = "description";
    public static final String TYPE = "type";
    public static final String SALARY = "salary";
    public static final String LOCATION = "location";
    public static final String COMPANY_ID = "company_id";
  }
  
  public abstract class CompanyTable {
    
    public static final String TABLE_NAME = "company_table";
    
    public static final String NAME = "name";
    public static final String IMAGE_LINK = "image_link";
  }
}

Secondly, using a reference to the constant avoids mistakes and allows us to make only one change in the value of a constant and propagate this over our entire app.

For instance, while creating this table in the database, we need to use the SQL sentence, CREATE TABLE "name"…; what we will do is use the name of the table from the contract with CREATE TABLE DatabaseContract.CompanyTable.TABLE_NAME….

The database contract is just the first step. It doesn't create a database; it's just a file that we use as a schema. To create the database, we need the help of SQLiteOpenHelper.

The open helper is a class that manages the creation and updating of the database. Updating is an important aspect that we need to keep in mind. Consider that we upload an app to Play Store, and after some time, we want to change the structure of the database. For instance, we want to add a column to a table without losing the data that the users of previous versions have stored in the old schema. Uploading a new version to Play Store, which deletes the previous information when the user updates our app, is not good for user experience at all.

To know when a database needs to be updated, we have a static integer with the database version that we have to manually increase if we alter the database, as follows:

/**
* DATABASE VERSION
*/
private static final int DATABASE_VERSION = 1;

We need to create a DatabaseOpenHelper class that extends SQLiteOpenHelper. While extending this class, we are asked to implement two methods:

@Override
public void onCreate(SQLiteDatabase db) {
  //Create database here
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  //Update database here
}

SQLiteOpenHelper will automatically call onCreate when we create an object of this class. However, it will only call this if the database is not created before and only once. In the same way, it will call onUpgrade when we increase the database version. That's why we need to send the params with the database name and the current version when we create an object of this class:

public DBOpenHelper(Context context){
  super(context, DatabaseContract.DB_NAME, null, DATABASE_VERSION);
}

Let's start with the creation of the database; the onCreate method needs to execute a SQL sentence on the database to create the table:

db.execSQL(CREATE_JOB_OFFER_TABLE);
db.execSQL(CREATE_COMPANY_TABLE);

We will define these sentences in static variables, as follows:

/**
* SQL CREATE TABLE JOB OFFER sentence
*/
private static final String CREATE_JOB_OFFER_TABLE = "CREATE TABLE "
+ DatabaseContract.JobOfferTable.TABLE_NAME + " ("
+ DatabaseContract.JobOfferTable.TITLE + TEXT_TYPE + COMMA
+ DatabaseContract.JobOfferTable.DESC + TEXT_TYPE + COMMA
+ DatabaseContract.JobOfferTable.TYPE + TEXT_TYPE + COMMA
+ DatabaseContract.JobOfferTable.SALARY + TEXT_TYPE + COMMA
+ DatabaseContract.JobOfferTable.LOCATION + TEXT_TYPE + COMMA
+ DatabaseContract.JobOfferTable.COMPANY_ID + INTEGER_TYPE + " )";

By default, Android creates a column_id column, which is unique and autoincremental in every row; therefore, we don't need to create a column ID in the companies table.

As you can see, we also have the commas and types in the variable to avoid mistakes. It's very common to miss a comma or make a mistake when writing the sentence directly, and it's very time consuming to find the error:

/**
* TABLE STRINGS
*/
private static final String TEXT_TYPE = " TEXT";
private static final String INTEGER_TYPE = " INTEGER";
private static final String COMMA = ", ";

We've seen how to create our tables, now we have to manage the update. In this case, we will simply drop the previous information and create the database again because there is no important information in the table. Once the app is opened after the update, it will download the job offers again and populate the new database:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  db.execSQL(DROP_JOB_OFFER_TABLE);
  db.execSQL(DROP_COMPANY_TABLE);
  onCreate(db);
}

/**
* SQL DELETE TABLE SENTENCES
*/
public static final String DROP_JOB_OFFER_TABLE = "DROP TABLE IF EXISTS "+ DatabaseContract.JobOfferTable.TABLE_NAME;
public static final String DROP_COMAPNY_TABLE = "DROP TABLE IF EXISTS "+ DatabaseContract.CompanyTable.TABLE_NAME;

Our complete version of the class will appear as the following:

public class DBOpenHelper extends SQLiteOpenHelper {
  
  
  private static final int DATABASE_VERSION = 1;
  
  /**
  * TABLE STRINGS
  */
  private static final String TEXT_TYPE = " TEXT";
  private static final String INTEGER_TYPE = " INTEGER";
  private static final String COMMA = ", ";
  
  /**
  * SQL CREATE TABLE sentences
  */
  private static final String CREATE_JOB_OFFER_TABLE = "CREATE TABLE "
  + DatabaseContract.JobOfferTable.TABLE_NAME + " ("
  + DatabaseContract.JobOfferTable.TITLE + TEXT_TYPE + COMMA
  + DatabaseContract.JobOfferTable.DESC + TEXT_TYPE + COMMA
  + DatabaseContract.JobOfferTable.TYPE + TEXT_TYPE +
  
  COMMA       + DatabaseContract.JobOfferTable.SALARY + TEXT_TYPE +
  
  COMMA       + DatabaseContract.JobOfferTable.LOCATION + TEXT_TYPE +
  
  COMMA + DatabaseContract.JobOfferTable.COMPANY_ID +
  
  INTEGER_TYPE + " )";
  
  private static final String CREATE_COMPANY_TABLE = "CREATE TABLE "
  + DatabaseContract.CompanyTable.TABLE_NAME + " ("
  + DatabaseContract.CompanyTable.NAME + TEXT_TYPE + COMMA
  + DatabaseContract.CompanyTable.IMAGE_LINK + TEXT_TYPE +  " )";
  
  
  /**
  * SQL DELETE TABLE SENTENCES
  */
  public static final String DROP_JOB_OFFER_TABLE = "DROP TABLE IF EXISTS "+ DatabaseContract.JobOfferTable.TABLE_NAME;
  public static final String DROP_COMPANY_TABLE = "DROP TABLE IF EXISTS "+ DatabaseContract.CompanyTable.TABLE_NAME;
  
  public DBOpenHelper(Context context){
    super(context, DatabaseContract.DB_NAME, null, DATABASE_VERSION);
  }
  
  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_JOB_OFFER_TABLE);
    db.execSQL(CREATE_COMPANY_TABLE);
  }
  
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL(DROP_COMPANY_TABLE);
    db.execSQL(DROP_JOB_OFFER_TABLE);
    onCreate(db);
  }
}

Database Access Object, commonly known as DAO, is an object that manages all access to the database from the app. Conceptually, it's a class in the middle of the database and our app:

It's a pattern usually used in J2EE (Java 2 Enterprise Edition) on the server side. In this, the implementation of the database can be changed and added an extra layer of independency to, thus allowing the change in database implementation without changing any data in the app. Even if we do not change the implementation of the database in Android, (it will always be a SQLite database retrieved through SQLiteOpenHelper), it still makes sense to use this pattern. From a structural point of view, we will have all our database access operations in the same place. Also, using a DAO as a singleton with synchronized methods prevents issues such as trying to open the database from two different places at the same time, which can be locked if we are writing. Of course, the possibility to retrieve this singleton from anywhere in the app makes access to the database really easy as well.

In the next section, we'll take a look at how to create a content provider, which is an element that can replace our DAO object; however, content providers are tedious to implement if what we want is to just store and read data from the database. Let's continue with MasteringAndroidApp, creating a class called MasteringAndroidDAO, which will store the job offers and companies and show the information from the database in order to have an offline-working app.

This class will be a singleton with two public synchronized methods: one to store job offers (in the job offer table and the company table) and another to read them. Even if we split the information into two tables, while reading we will merge it again so that we can keep displaying the job offers with our current adapter without making major changes. Through this, you will learn how to join two tables in a query.

If a method is synchronized, we guarantee that it can't be executed from two places at the same time. Therefore, use the following code:

public class MasteringAndroidDAO {
  
  /**
  * Singleton pattern
  */
  private static MasteringAndroidDAO sInstane = null;
  
  /**
  * Get an instance of the Database Access Object
  *
  * @return instance
  */
  public static MasteringAndroidDAO getInstance(){
    if (sInstane == null){
      sInstane = new MasteringAndroidDAO();
    }
    return sInstane;
  }
  
  public synchronized boolean storeOffers(Context context, List<JobOffer> offers){
    //Store offers
  }
  
  
  public synchronized List<JobOffer> getOffersFromDB(Context context){
    //Get offers
  }
  
}

We will start with the storeOffers() method. The first thing that we need to do is open the database with DatabaseOpenHelper, and after this we need to start a transaction in the database. We will store a list of items, so it doesn't make sense to perform a transaction for each item. It's much more efficient if we open a transaction, perform all the insert operations that we need, and end the transaction after this, committing all the changes in a batch:

try {
  SQLiteDatabase db = newDBOpenHelper(context).getWritableDatabase();
  
  db.beginTransaction();
  //insert single job offer
  db.setTransactionSuccessful();
  db.endTransaction();
  db.close();
} catch ( Exception e){
  Log.d("MasteringAndroidDAO",e.toString());
  return false;
}

If we only had to insert data in a single table, we would only need to create a ContentValue object—a key-value object built based on the columns that we want to store—and call db.insert(contentValue). However, our example is a little bit more complicated. To store a job offer, we need to know the company ID, and to obtain this ID, we need to ask our database if the company is already stored on it. If it's not, we need to store it and know which ID was assigned to it because, as we mentioned before, the ID is automatically generated and increased.

To find out if the company is already on the table, we need to perform a query searching all the rows to see if any row matches the name of the company that we are searching. There are two ways of performing a query: query() and rawQuery().

Cursor cursorCompany = db.query(DatabaseContract.CompanyTable.TABLE_NAME,
  new String[]{"rowid"},
  DatabaseContract.CompanyTable.NAME +" LIKE ?",
  new String[]{offer.getCompany()},
  null,null,null);

String queryString = "SELECT rowid FROM company_table WHERE name LIKE '?'"; 
Cursor c = sqLiteDatabase.rawQuery(queryString, whereArgs);

Cursor c = query….
while (c.moveToNext()) {
  String currentName = c.getString(c.getColumnIndex("column_name"));
}

public int findCompanyId(SQLiteDatabase db, JobOffer offer){
  Cursor cursorCompany = db.query(DatabaseContract.CompanyTable.TABLE_NAME,
  new String[]{"rowid"},
  DatabaseContract.CompanyTable.NAME +" LIKE ?",
  new String[]{offer.getCompany()},
  null,null,null);
  
  int id = -1;
  
  if (cursorCompany.moveToNext()){
    id = cursorCompany.getInt(0);
  }
  return id;
}

db.insertWithOnConflict(DATABASE_TABLE, null, initialValues, SQLiteDatabase.CONFLICT_IGNORE);

public boolean storeOffers(Context context, List<JobOffer> offers){
  
  try {
    SQLiteDatabase db = new DBOpenHelper(context).getWritableDatabase();
    
    db.beginTransaction();
    
    for (JobOffer offer : offers){
      
      ContentValues cv_company = new ContentValues();
      cv_company.put(DatabaseContract.CompanyTable.NAME, offer.getCompany());
      cv_company.put(DatabaseContract.CompanyTable.IMAGE_LINK,offer.getImageLink());
      
      int id = findCompanyId(db,offer);
      
      if (id < 0) {
        id = (int) db.insert(DatabaseContract.CompanyTable.TABLE_NAME,null,cv_company);
      }
      
      ContentValues cv = new ContentValues();
      cv.put(DatabaseContract.JobOfferTable.TITLE,offer.getTitle());
      cv.put(DatabaseContract.JobOfferTable.DESC,offer.getDescription());
      cv.put(DatabaseContract.JobOfferTable.TYPE, offer.getType());
      cv.put(DatabaseContract.JobOfferTable.DESC, offer.getDescription());
      cv.put(DatabaseContract.JobOfferTable.SALARY,offer.getSalary());
      cv.put(DatabaseContract.JobOfferTable.LOCATION,offer.getLocation());
      cv.put(DatabaseContract.JobOfferTable.COMPANY_ID,id);
      
      
      db.insert(DatabaseContract.JobOfferTable.TABLE_NAME,null,cv);
    }
    
    db.setTransactionSuccessful();
    db.endTransaction();
    
    db.close();
    
  } catch ( Exception e){
    Log.d("MasteringAndroidDAO", e.toString());
    return false;
  }
  
  return true;
}

public List<JobOffer> getOffersFromDB(Context context){
  
  SQLiteDatabase db = new DBOpenHelper(context).getWritableDatabase();
  
  String join = DatabaseContract.JobOfferTable.TABLE_NAME + " JOIN " +
  DatabaseContract.CompanyTable.TABLE_NAME + " ON " +
  DatabaseContract.JobOfferTable.TABLE_NAME+"."+DatabaseContract.JobOfferTable.COMPANY_ID
  +" = " + DatabaseContract.CompanyTable.TABLE_NAME+".rowid";
  
  Cursor cursor = db.query(join,null,null,null,null,null,null);
  
  List<JobOffer> jobOfferList = new ArrayList<>();
  
  while (cursor.moveToNext()) {
    //Create job offer from cursor and add it
    //to the list
  }
  
  cursor.close();
  db.close();
  
  return jobOfferList;
}

while (cursor.moveToNext()) {
  
  JobOffer offer = new JobOffer();
  offer.setTitle(cursor.getString(cursor.getColumnIndex(DatabaseContract.JobOfferTable.TABLE_NAME)));
  offer.setDescription(cursor.getString(cursor.getColumnIndex(DatabaseContract.JobOfferTable.DESC)));
  offer.setType(cursor.getString(cursor.getColumnIndex(DatabaseContract.JobOfferTable.TYPE)));
  offer.setSalary(cursor.getString(cursor.getColumnIndex(DatabaseContract.JobOfferTable.SALARY)));
  offer.setLocation(cursor.getString(cursor.getColumnIndex(DatabaseContract.JobOfferTable.LOCATION)));
  offer.setCompany(cursor.getString(cursor.getColumnIndex(DatabaseContract.CompanyTable.NAME)));
  offer.setImageLink(cursor.getString(cursor.getColumnIndex(DatabaseContract.CompanyTable.IMAGE_LINK)));
  
  jobOfferList.add(offer);
}

/**
* Remove all offers and companies
*/
public void clearDB(Context context)
{
  SQLiteDatabase db = new DBOpenHelper(context).getWritableDatabase();
  // db.delete(String tableName, String whereClause, String[] whereArgs);
  // If whereClause is null, it will delete all rows.
  db.delete(DatabaseContract.JobOfferTable.TABLE_NAME, null, null);
  db.delete(DatabaseContract.CompanyTable.TABLE_NAME, null, null);
}

public boolean isOnline() {
  ConnectivityManager cm =
  (ConnectivityManager) getActivity().getSystemService(Context.CONNECTIVITY_SERVICE);
  NetworkInfo netInfo = cm.getActiveNetworkInfo();
  return netInfo != null && netInfo.isConnectedOrConnecting();
}

@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container,
Bundle savedInstanceState) {
  // Inflate the layout for this fragment
  View view = inflater.inflate(R.layout.fragment_list, container, false);
  
  mRecyclerView = (RecyclerView) view.findViewById(R.id.my_recycler_view);
  
  // use this setting to improve performance if you know that changes
  // in content do not change the layout size of the RecyclerView
  mRecyclerView.setHasFixedSize(true);
  
  // use a linear layout manager
  mRecyclerView.setLayoutManager(new LinearLayoutManager(getActivity()));
  
  //Retrieve the list of offers
  
  if (isOnline()){
    retrieveJobOffers();
  } else {
    showOffersFromDB();
  }
  
  return view;
}


public void retrieveJobOffers(){
  ParseQuery<JobOffer> query = ParseQuery.getQuery("JobOffer");
  query.findInBackground(new FindCallback<JobOffer>() {
    
    @Override
    public void done(List<JobOffer> jobOffersList, ParseException e) {
      MasteringAndroidDAO.getInstance().clearDB(getActivity());
      MasteringAndroidDAO.getInstance().storeOffers(getActivity(), jobOffersList);
      mListItems = MasteringAndroidDAO.getInstance().getOffersFromDB(getActivity());
      JobOffersAdapter adapter = new JobOffersAdapter(mListItems);
      mRecyclerView.setAdapter(adapter);
    }
    
  });
}

public void showOffersFromDB(){
  mListItems = MasteringAndroidDAO.getInstance().getOffersFromDB(getActivity());
  JobOffersAdapter adapter = new JobOffersAdapter(mListItems);
  mRecyclerView.setAdapter(adapter);
}