This is an old revision of the document!


Database: SQLite

DB Connector

When using the built-in android.database.sqlite library, there is not need to add an additional database connector.

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();
    }
 
 
}

MainActivity

public class MainActivity extends AppCompatActivity {
 
    // Const
    private static final String TAG = "ROBOTICS_LEAGUE_APP";
 
    // Properties
    ListView lstTeams;
    String[] arrTeams;
    List<String> teamNames;
 
    @Override
    protected void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
 
        // Connect to resources
        Resources res = getResources();
        lstTeams = (ListView) findViewById(R.id.lstTeams);
        //arrTeams = res.getStringArray(R.array.teams);  // get data from resource stings
 
        try {
            DbUtils dbutil = new DbUtils();
            dbutil.getConnection(this);
            teamNames = dbutil.findTeamNamesAll();  // get data from database
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        // Using custom ItemAdapter with custom layout
        TeamItemAdapter itemAdapter = new TeamItemAdapter(
                this,    // context
                //arrTeams     // data
                teamNames     // data
        );
        lstTeams.setAdapter(itemAdapter);
 
        lstTeams.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                Log.d(TAG, "Loading rubric for Team " + (position+1));
                Toast.makeText(getApplicationContext(), "Loading rubric for Team " + (position+1), Toast.LENGTH_SHORT).show();
 
                Intent showRubricActivity = new Intent(getApplicationContext(), RobotRubricActivity.class);
                showRubricActivity.putExtra("com.voirtech.roboticsleague.TEAM_ID", (position+1)+"");
                showRubricActivity.putExtra("com.voirtech.roboticsleague.TEAM_INDEX", position);
                startActivity(showRubricActivity);
            }
        });
    }
}

activiy_main.xml Layout:

<?xml version="1.0" encoding="utf-8"?>
<android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">
 
    <ListView
        android:id="@+id/lstTeams"
        android:layout_width="match_parent"
        android:layout_height="495dp"
        android:layout_marginEnd="8dp"
        android:layout_marginStart="8dp"
        android:layout_marginTop="8dp"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />
</android.support.constraint.ConstraintLayout>

ItemAdapter

package com.acme.myapp;
 
import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;
 
import java.util.List;
 
public class TeamItemAdapter extends BaseAdapter {
    LayoutInflater mInflater;
 
    //String[] arrTeams;
    List<String> arrTeams;
 
    //public TeamItemAdapter(Context cx, String[] teams)
    public TeamItemAdapter(Context cx, List<String> teams)
    {
        arrTeams = teams;
 
        mInflater = (LayoutInflater) cx.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
    }
 
    @Override
    //public int getCount()
    //{
    //    return arrTeams.length;
    //}
    public int getCount()
    {
        return arrTeams.size();
    }
 
    @Override
    //public Object getItem(int idx)
    //{
    //    return arrTeams[idx];
    //}
    public Object getItem(int idx)
    {
        return arrTeams.get(idx);
    }
 
    @Override
    public long getItemId(int idx)
    {
        return idx;
    }
 
    @Override
    public View getView(int idx, View view, ViewGroup viewGroup)
    {
        // Use layout inflater.  Use listview_detail_team.xml layout
 
        View vw = mInflater.inflate(R.layout.listview_detail_team, null);
        TextView lblItemNumber = (TextView) vw.findViewById(R.id.lblItemNumber);
        TextView lblTeamName = (TextView) vw.findViewById(R.id.lblTeamName);
 
        String strItemNumber = (idx+1) + "";
        //String strTeam = arrTeams[idx];
        String strTeam = arrTeams.get(idx);
 
        lblItemNumber.setText(strItemNumber);
        lblTeamName.setText(strTeam);
 
        return vw;
    }
}
References