swdev:android:database_sqlite [Audina Documentation]

This is an old revision of the document!

Database: SQLite

DB Connector

Download the appropriate Java database connector:

Download JDBC Connector library.
Extract JAR file to some folder.
Add JAR file as module: app, right-click then New > Module, then select 'Import .JAR/.AAR Package', and find JAR file.
JAR package should be now listed under the project Gradle Scripts: build.gradle (sqlite-jdbc-3.20.0).
If there is an error including the JAR package, try the following:
- In settings.gradle (Project Settings), add references to the sqlite-jdbc:
```
include ':app'
include ':sqlite-jdbc-3.20.0'
```
Sync project (File > Sync Project with Gradle Files).
Add sqlite-jdbc module as dependency to app module:
- Right-click on the app module folder, and select “Open Module Settings” (F4).
- In the top left navigation pane of the “Project Structure” window, go to category “Modules” and select tab “Dependencies”.
- Click on the green plus (+), and select 'Module Dependencies'. Select the sqlite-jdbc module.
- Click OK, and close all the opened windows. A synchronization process should take place once more.
If there is an error including the JAR package, try the following:
- In build.gradle (Module:app), add references to compile sqlite-jdbc under dependencies:
```
dependencies {
  //...
  compile project(':sqlite-jdbc-3.20.0')
}
```
If succesfull, under the app module folder there should be a sqlite-jdbc module folder.
Include the classes required in your code.

Manifest

Add permission to access the Internet. Go to app > manifests > AndroidManifest.xml:

<manifest...>
    <uses-permission android:name="android.permission.INTERNET"/>
 
    <application...>
    </application>
</manifest>

Database Contract

Create a class to hold the database strings: File DbContract.java:

package com.acme.myapp;
 
import android.provider.BaseColumns;
 
public final class DbContract
{
    // To prevent someone from accidentally instantiating the contract class,
    // make the constructor private.
    private DbContract() {}
 
    /* Inner class that defines the table contents */
    public class TeamEntry implements BaseColumns {
        public static final String TABLE_NAME = "entry";
        public static final String COL_NAME = "name";
        public static final String COL_NUMBER = "number";
        public static final String COL_RANKING = "ranking";
    }
}

Database Helper

Create a class to hold the database strings: File DbHelper.java:

package com.acme.myapp;
 
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
 
public class DbHelper extends SQLiteOpenHelper
{
    //-----------------------------------
    // If you change the database schema, you must increment the database version.
    //-----------------------------------
    // Properties
    public static final String DATABASE_NAME = "database.db";
    public static final int DATABASE_VERSION = 1;
 
    private static final String SQL_CREATE_ENTRIES =
        "CREATE TABLE " + DbContract.TeamEntry.TABLE_NAME + " (" +
                DbContract.TeamEntry._ID + " INTEGER PRIMARY KEY," +
                DbContract.TeamEntry.COL_NAME + " TEXT," +
                DbContract.TeamEntry.COL_NUMBER + " TEXT," +
                DbContract.TeamEntry.COL_RANKING + " TEXT)";
 
    private static final String SQL_DELETE_ENTRIES =
        "DROP TABLE IF EXISTS " + DbContract.TeamEntry.TABLE_NAME;
 
 
    //-----------------------------------
    // Methods
    //-----------------------------------
    public DbHelper(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        Boolean createDatabase = false;
        if(createDatabase) {
            SQLiteDatabase db = this.getWritableDatabase();
        }
    }
 
    public void onCreate(SQLiteDatabase db)
    {
        db.execSQL(SQL_CREATE_ENTRIES);
 
        // Add content to table
        for(int i=1; i <= 20; i++) {
            ContentValues values = new ContentValues();
            values.put(DbContract.TeamEntry.COL_NAME, "Team " + i);
            values.put(DbContract.TeamEntry.COL_NUMBER, i + "");
 
            // Insert the new row, returning the primary key value of the new row
            db.insert(DbContract.TeamEntry.TABLE_NAME, null, values);
        }
    }
 
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        // This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        db.execSQL(SQL_DELETE_ENTRIES);
        onCreate(db);
    }
 
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        onUpgrade(db, oldVersion, newVersion);
    }
 
    public String getDbVersionNumber()
    {
        String query = "select sqlite_version() AS sqlite_version";
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(":memory:", null);
        Cursor cursor = db.rawQuery(query, null);
        String sqliteVersion = "";
        if (cursor.moveToNext()) {
            sqliteVersion = cursor.getString(0);
        }
        return sqliteVersion;
    }
}

Database Utils

Create a class to hold the database strings: File DbUtils.java:

package com.acme.myapp;
 
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.provider.BaseColumns;
 
import java.util.ArrayList;
import java.util.List;
 
public class DbUtils
{
    private DbHelper dbHelper;
    private SQLiteDatabase db;
 
    public DbHelper getConnection(Context cx)
    {
        dbHelper = new DbHelper(cx);
        return dbHelper;
    }
 
    public long addTeamRecord()
    {
        // Gets the data repository in write mode
        SQLiteDatabase db = dbHelper.getWritableDatabase();
 
        // Create a new map of values, where column names are the keys
        ContentValues values = new ContentValues();
        values.put(DbContract.TeamEntry.COL_NAME, "Team A");
        values.put(DbContract.TeamEntry.COL_NUMBER, "1234");
 
        // Insert the new row, returning the primary key value of the new row
        long newRowId = db.insert(DbContract.TeamEntry.TABLE_NAME, null, values);
 
        return newRowId;
    }
 
    public List readTeamRecords()
    {
        db = dbHelper.getReadableDatabase();
 
        // Define a projection that specifies which columns from the database
        // you will actually use after this query.
        String[] projection = {
                BaseColumns._ID,
                DbContract.TeamEntry.COL_NAME,
                DbContract.TeamEntry.COL_NUMBER,
                DbContract.TeamEntry.COL_RANKING
        };
 
        // Filter results WHERE "title" = 'My Team'
        String selection = DbContract.TeamEntry.COL_NAME + " = ?";
        String[] selectionArgs = { "My Team" };
 
        // How you want the results sorted in the resulting Cursor
        String sortOrder =  DbContract.TeamEntry.COL_NUMBER + " DESC";
 
        Cursor cursor = db.query(
                DbContract. TeamEntry.TABLE_NAME,   // The table to query
                projection,             // The array of columns to return (pass null to get all)
                selection,              // The columns for the WHERE clause
                selectionArgs,          // The values for the WHERE clause
                null,                   // don't group the rows
                null,                   // don't filter by row groups
                sortOrder               // The sort order
        );
 
        List itemIds = new ArrayList<>();
        while(cursor.moveToNext()) {
            long itemId = cursor.getLong( cursor.getColumnIndexOrThrow(DbContract.TeamEntry._ID) );
            itemIds.add(itemId);
        }
        cursor.close();
 
        return itemIds;
    }
 
    public Map<String,String> findTeamRecordsAll()
    {
        db = dbHelper.getReadableDatabase();
 
        // Define a projection that specifies which columns from the database
        // you will actually use after this query.
        String[] projection = {
                BaseColumns._ID,
                DbContract.TeamEntry.COL_NAME,
                DbContract.TeamEntry.COL_NUMBER,
                DbContract.TeamEntry.COL_RANKING
        };
 
        // Filter results WHERE "title" = 'My Team'
        //String selection = DbContract.TeamEntry.COL_NAME + " = ?";
        //String[] selectionArgs = { "My Team" };
 
        // How you want the results sorted in the resulting Cursor
        //String sortOrder =  DbContract.TeamEntry.COL_NUMBER + " ASC";
        String sortOrder =  DbContract.TeamEntry.COL_NAME + " ASC";
 
        Cursor cursor = db.query(
                DbContract.TeamEntry.TABLE_NAME,   // The table to query
                projection,             // The array of columns to return (pass null to get all)
                //selection,              // The columns for the WHERE clause
                null,
                //selectionArgs,          // The values for the WHERE clause
                null,
                null,                   // don't group the rows
                null,                   // don't filter by row groups
                sortOrder               // The sort order
        );
 
        Map<String,String> mapTeam = new LinkedHashMap<String,String>();
        while(cursor.moveToNext()) {
            String name   = cursor.getString( cursor.getColumnIndexOrThrow(DbContract.TeamEntry.COL_NAME) );
            String number = cursor.getString( cursor.getColumnIndexOrThrow(DbContract.TeamEntry.COL_NUMBER) );
            mapTeam.put(name,number);
        }
        cursor.close();
 
        return mapTeam;
    }
 
    public List<String> findTeamNamesAll()
    {
        try {
            db = dbHelper.getReadableDatabase();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
 
        // Define a projection that specifies which columns from the database
        // you will actually use after this query.
        String[] projection = {
                BaseColumns._ID,
                DbContract.TeamEntry.COL_NAME,
                DbContract.TeamEntry.COL_NUMBER,
                DbContract.TeamEntry.COL_RANKING
        };
 
        // Filter results WHERE "title" = 'My Team'
        //String selection = DbContract.TeamEntry.COL_NAME + " = ?";
        //String[] selectionArgs = { "My Team" };
 
        // How you want the results sorted in the resulting Cursor
        //String sortOrder =  DbContract.TeamEntry.COL_NUMBER + " ASC";
        String sortOrder =  DbContract.TeamEntry.COL_NAME + " ASC";
 
        Cursor cursor = db.query(
                DbContract.TeamEntry.TABLE_NAME,   // The table to query
                projection,             // The array of columns to return (pass null to get all)
                //selection,              // The columns for the WHERE clause
                null,
                //selectionArgs,          // The values for the WHERE clause
                null,
                null,                   // don't group the rows
                null,                   // don't filter by row groups
                sortOrder               // The sort order
        );
 
        List<String> arrNames = new ArrayList<String>();
        while(cursor.moveToNext()) {
            String name = cursor.getString( cursor.getColumnIndexOrThrow(DbContract.TeamEntry.COL_NAME) );
            arrNames.add(name);
        }
        cursor.close();
 
        return arrNames;
    }
 
    public void deleteTeamRecord()
    {
        // Define selection filter
        String selection = DbContract.TeamEntry.COL_NAME + " LIKE ?";
        String[] selectionArgs = { "MyTeam" };
 
        // Issue SQL statement.
        int deletedRows = db.delete(DbContract.TeamEntry.TABLE_NAME, selection, selectionArgs);
    }
 
    public int updateTeamRecords()
    {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
 
        // New value for one column
        String name = "MyNewTeam";
        ContentValues values = new ContentValues();
        values.put(DbContract.TeamEntry.COL_NAME, name);
 
        // Which row to update, based on the title
        String selection = DbContract.TeamEntry.COL_NAME + " LIKE ?";
        String[] selectionArgs = { "MyOldTeam" };
 
        int count = db.update(
                DbContract.TeamEntry.TABLE_NAME,
                values,
                selection,
                selectionArgs
        );
 
        return count;
    }
 
    public void destroy()
    {
        dbHelper.close();
    }
 
 
}