= 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'':
<code xml>
<manifest...>
    <uses-permission android:name="android.permission.INTERNET"/>
    
    <application...>
    </application>
</manifest>
</code>

==== Database Contract ====
Create a class to hold the database strings:
File ''DbContract.java'':
<code 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";
    }
}
</code>

==== Database Helper ====
Create a class to hold the database strings:
File ''DbHelper.java'':
<code 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;
    }
}
</code>

==== Database Utils  ====
Create a class to hold the database strings:
File ''DbUtils.java'':
<code 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" };
        String selection = null;         // no filters
        String[] selectionArgs = null;   // no filters

        // 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
                selectionArgs,          // The values for the WHERE clause
                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" };
        String selection = null;         // no filters
        String[] selectionArgs = null;   // no filters

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


}
</code>

=== MainActivity ===
<code java>
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.acme.roboticsleague.TEAM_ID", (position+1)+"");
                showRubricActivity.putExtra("com.acme.roboticsleague.TEAM_INDEX", position);
                startActivity(showRubricActivity);
            }
        });
    }
}
</code>

activiy_main.xml Layout:
<code xml>
<?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>
</code>

=== ItemAdapter ===
<code java>
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;
    }
}
</code>

== References ==
See also:
  * [[swdev:java:Connecting to MYSQL Database]]
  * [[https://developer.android.com/training/data-storage/sqlite|Data Storage: SQLite]]
  * [[https://developer.android.com/training/data-storage/room|Data Storage: Room]]
  * [[https://dzone.com/articles/create-a-database-android-application-in-android-s|Create a database Android application]]