Month: May 2017

Content Provider

UI Code is directory interacting with database

CatalogActivity ->
EditorActivity -> Content Provider -> PetDbHelper (Pet with ID 1 has weight 7)

Manage access to a structured set of data
-good abstraction layer between data source & UI code (can add data validation, can modify how data is stored UI code is unaffeted)
-work well with other Android framework classes

UI code
Catalog Activity, Content Resolver, Pet Provider, PetDbHelper SQLiteDatabase

Contacts provider
content://com.android.contacts/contacts
Calendar provider
content://com.android.calendar/events
User Dictionary provider
content://user_dictionary/words

Scheme, Content Authority, Type of data

content authority

1
2
3
4
<provider
    android:name=".data.PetProvider"
    android:authorities="com.example.android.pets"
    android:exported="false" />
1
2
3
4
5
6
int match = sUriMatcher.match(uri);
if (match == CONTACTS){
    // Act on the contacts table
} else if (match == CONTACTS_ID){
    // Act on a single contact in contacts table
}
1
2
3
4
5
6
7
8
9
10
11
12
public class PetProvider extends ContentProvider {
 
    private static final int PETS = 100;
    private static final int PET_ID = 101;
 
    private static final UriMatcher sUriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
    static {
        sUriMatcher.addURI(PetContract.CONTENT_AUTHORITY, PetContract.PATH_PETS,PETS);
        sUriMatcher.addURI(PetContract.CONTENT_AUTHORITY,PetContract.PATH_PETS + ""/, PETS_ID);
    }
    ...
}

Read values from Cursor

Read

1
2
3
Cursor c = db.query(PetEntry.TABLE_NAME,projection,
        selection, selectionArgs,
        null, null, null);
1
2
3
4
5
6
while (cursor.moveToNext()){
    int currentID = cursor.getInt(idColumnIndex);
    String currentName = cursor.getString(nameColumnIndex);
    displayView.append(("\n" + currentId + " - " +
        currentName));
}

Insert Dummy pet from menu item

1. Get pet attributes from user input fields
2. Insert new pet with those attributes into the database

1
2
3
4
5
6
SELECT name, breed FROM pets;
SELECT * FROM pets;
SELECT * FROM pets WHERE _id = 1;
 
String selection = PetEntry._ID = "=?";
String[] selectionArgs = {"1"};

In Java;

1
2
3
4
5
6
7
8
9
10
String[] projection = { PetEntry.COLUMN_PET_BREED,
    PetEntry.COLUMN_PET_WEIGHT };
String selection = PetEntry.COLUMN_PET_GENDER + "=?";
String selectionArgs = new String[] { PetEntry.GENDER_FEMALE };
 
Cursor c = db.query(PetEntry.TABLE_NAME, projection,
    selection, selectionArgs,
    null, null, null);
 
SELECT breed, weight FROM pets WHERE gender = 1

Handling a cursor object
-moveToFirst: moves the cursor to the first row that’s in the result set
-moveToLast: moves the cursor to the last row that’s in the result set
-moveToPosition(int position): moves the cursor to a specific position; for example, saying “moveToPosition(3)” would move it to the fourth row.

abstract float, abstract int, abstract long, abstract short, abstract String, abstract int

1
2
3
4
cusor.moveToPosition(3);
int nameColumnIndex =
    cusor.getColumnIndex(PetEntry.COLUMN_PET_NAME);
String name = cursor.getString(nameColumnIndex);
1
2
3
4
5
6
7
8
cusor.moveToFirst();
cusor.moveToNext();
cusor.moveToNext();
 
int nameColumnIndex = cusor.getColumnIndex(PetEntry.COLUMN_PET_NAME);
int weightColumnIndex = cursor.getColumnIndex(PetEntry.COLUMN_PET_WEIGHT);
 
int result = cursor.getInt(weightColumnIndex);

Getting a database connection

UI -> I want a readable database
EditorActivity, CatalogActivity

SQLiteDatabase db = mDbHelper.getReadableDatabase();

PetContract

PetDbHelper

PetDbHelper mDbHelper = new PetDbHelper(this);

Is there a database? no -> Make a database using onCreate() code

SQLiteDatabase

Create Update Delete
->SQLiteDatabase db = mDbHelper.getWritableDatabase();

Select
->SQLiteDatabase db = mDbHelper.getWritableDatabase();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
INSERT INTO <table name>(
    <column_name_1>,
    <column_name_2>,
    ...)
VALUES (
    <values_1>,
    <values_2>
    ...)
 
INSERT INTO pets (
    _id,
    name,
    breed,
    weight)
VALUES (
    1,
    "Tommy",
    1,
    4);

Inserting Data: Content Values
Key – Value, COLUMN_PET_NAME – “Garfield”, COLUMN_PET_BREED – “Tabby”, COLUMN PET GENDER – GENDER_MALE, COLUMN_PET_WEIGHT – 14

1
2
3
4
5
ContentValues values = new ContentValues();
values.put(PetEntry.COLUMN_PET_NAME, "Garfield");
values.put(PetEntry.COLUMN_PET_BREED, "Tabby");
values.put(PetEntry.COLUMN_PET_GENDER, PetEntry.GENDER_MALE);
values.put(PetEntry.COLUMN_PET_WEIGHT, 14);

SQLiteOpenHelper

-Create a class that extends from SQLiteOpenHelper
-Create constants for database name and database version
-Create a constructor
-Implement the onCreate() method – this method is for when the database is first created
-Implement onUpgrade() – this method is for when the database schema of the database changes
(ex:adding a different column)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public class FeedReaderDbHelper extends SQLiteOpenHelper {
 
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "FeedReader.db";
 
    public FeedReaderDbHelper(Context context){
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    public void onCreate(SQLiteDatabase db){
        db.execSQL(SQL_CREATE_ENTRIES);
    }
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
        db.execSQL(SQL_DELETE_ENTRIES);
        onCreate db;
    }
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion){
        onUpgrade db oldVersion, newVersion;
    }
}

SQLite Database

https://www.tutorialspoint.com/android/android_sqlite_database.htm

1
CREATE TABLE <table_name> (<column_name_1><column_name_2>...);

Contract Class

1
2
3
4
String SQL_CREATE_ENTRIES = "CREATE TABLE" + FeedEntry.TABLE_NAME + "(" +
    FeedEntry._ID + "INTEGER PRIMARY KEY," +
    FeedEntry.COLUMN_NAME_ENTRY_ID + "TEXT ," +
    FeedEntry.COLUMN_NAME_TITLE + "TEXT );"
1
2
3
4
5
6
7
8
9
10
public final class FeedReaderContract {
    public FeedReaderContract(){}
 
    public static abstract class FeedEntry implements BaseColumns {
        public static final String TABLE_NAME = "entry";
        public static final String COLUMN_NAME_ENTRY_ID = "entryid";
        public static final String COLUMN_NAME_TITLE = "title";
        public static final String COLUMN_NAME_SUBTITLE = "subtitle";
    }
}
1
2
3
4
5
6
7
8
9
_ID
COLUMN_PET_NAME
COLUMN_PET_BREED
COLUMN_PET_GENDER
COLUMN_PET_WEIGHT
 
GENDER_UNKNOWN
GENDER_MALE
GENDER_FEMALE

DELETE SQL COMMAND

DELETE FROM DELETE FROM pets WHERE id = ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
package com.example.android.pets;
 
import android.content.Intent;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.v7.app.AppCompactActivity;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
 
public class CatalogActivity extends AppCompactActivity {
 
    @Override
    protected void onCreate(Budle savedInstanceState){
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_catalog);
 
        // Setup FAB to open EditorActivity
        FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
        fab.setOnClickListener(new View.OnClickListener(){
            @Override
            public void onClick(View view){
                Intent intent = new Intent(CatalogActivity.this, EditorActivity.class);
                startActivity(intent);
            }
        });
    }
 
    @Override
    public boolean onCreateOptionsMenu(Menu menu){
        // Inflate the menu options from the  res/menu/menu_catalog.xml file.
        // This adds menu items to the app bar.
        getMenuInflater().inflate(R.menu.menu_catalog, menu);
        return true;
    }
 
    @Override
    public boolean onOptionsItemSelected(MenuItem item){
        // User clicked on a menu option in the app bar overflow menu
        switch (item.getItemId()){
            case R.id.action_insert_dummy_data:
 
                return true;
            case R.id.action_delete_all_entries:
 
                return true;
        }
        return super.onOptionsItemSelected(item);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
package com.example.android.pets;
 
import android.os.Bundle;
import android.support.v4.app.NavUtils;
import android.support.v7.app.AppCompactActivity;
import android.text.TextUtils;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.EditText;
import android.widget.Spinner;
 
public class EditorActivity extends AppCompactActivity {
    private EditText mNameEditText;
 
    private EditText mBreedEditText;
 
    private EditText mWeightEditText;
 
    private Spinner mGenderSpinner;
 
    private int mGender = 0;
 
    @Override
    protected void onCreate(Bundle savedInstanceState){
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_editor);
 
        mNameEditText = (EditText) findViewById(R.id.edit_pet_name);
        mBreedEditText = (EditText) findViewById(R.id.edit_pet_breed);
        mWeightEditText = (EditText) findViewById(R.id.edit_pet_weight);
        mGenderSpinner = (Spinner) findById(R.id.spinner_gender);
 
        setupSpinner();
    }
 
}  
 
private void setupSpinner(){
 
    ArrayAdapter genderSpinnerAdapter = ArrayAdapter.createFromResource(this,
        R.array.array_gender_options, android.R.layout.simple_spinner_item);
 
    genderSpinnerAdapter.setDropDownViewResource(android.R.layout.simple_dropdown_item_1line);
 
    mGenderSpinner.setAdapter(genderSpinnerAdapter);
 
    mGenderSpinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener(){
        @Override
        public void onItemSelected(AdapterView<?> parent, View view, int position, long id){
            String selection = (String) parent.getItemAtPosition(position);
            if (!TextUtils.isEmpty(selection)){
                if (selection.equals(getString(R.string.gender_male))){
                    mGender = 1;
                } else if (selection.equals(getSTring(R.string.gender_female))){
                    mGender = 2;
                } else {
                    mGender = 0;
                }
            }
        }
 
        @Override
        public void onNothingSelected(AdapterView<?> parent){
            mGender = 0;
        }
    });
}
 
    @Override
    public boolean onCreateOptionsMenu(Menu menu){
        getMenuInflater().inflate(R.menu.menu_editor, menu);
        return true;
    }
 
    @Override
    public boolean onOptionItemSelected(MenuItem item){
        switch (item.getItemId()){
            case R.id.action_save:
                return true;
            case R.id.action_delete:
                return true;
            case android.R.id.home:
                NavUtils.navigateUpFromSameTask(this);
                return true;
        }
        return super.onOptionsItemSelected(item);
    }

UPDATE

sqlite>   CREATE TABLE pets (
   ...>        _id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...>        name TEXT NOT NULL,
   ...>        breed TEXT,
   ...>        gender INTEGER NOT NULL,
   ...>        weight INTEGER NOT NULL DEFAULT 0);
sqlite>   INSERT INTO pets ( name, breed, gender, weight) VALUES ( "Tommy", "Pomeranian", 1, 4);
sqlite>   INSERT INTO pets (name, breed, gender, weight) VALUES ("Garfield", "Tabby", 1, 14);
sqlite>   INSERT INTO pets (name, breed, gender, weight) VALUES ("Binx", "Bombay", 1, 6);
sqlite>   INSERT INTO pets (name, breed, gender, weight)  VALUES ( "Lady", "Cocker Spaniel", 2, 14);
sqlite>   INSERT INTO pets (name, breed, gender, weight) VALUES ("Duke", "Unknown", 1, 70);
sqlite>   INSERT INTO pets (name, breed, gender, weight) VALUES ("Cat", "Tabby", 0, 7);
sqlite>   INSERT INTO pets (name, breed, gender, weight) VALUES ("Baxter", "Border Terrier", 1, 8);
sqlite>   INSERT INTO pets (name, gender, weight) VALUES ("Arlene", 2, 5);
sqlite> UPDATE pets SET weight = 20 WHERE _id == 2;
sqlite> SELECT _id, name, weight FROM pets;
_id         name        weight
----------  ----------  ----------
1           Tommy       4
2           Garfield    20
3           Binx        6
4           Lady        14
5           Duke        70
6           Cat         7
7           Baxter      8
8           Arlene      5
sqlite> UPDATE pets SET breed = "Pomeranian Terrier" WHERE id==1;
Error: no such column: id
sqlite> UPDATE pets SET weight = 0;

TABLE CONSTRAINTS

PRIMARY KEY, AUTO INCREMENT, NOT NULL, DEFAULT

1
2
CREATE TABLE headphones (_id INTEGER, name TEXT, price INTEGER,
    style INTEGER, in_stock INTEGER, description TEXT);

PRIMARY KEY: Ensure uniqueness. There can only be one primary key per table
AUTO INCREMENT: Automatically calculates new integer when row is added. Useful for IDs

1
2
3
4
5
6
CREATE TABLE headphones (_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    price INTEGER,
    style INTEGER,
    in_stock INTEGER,
    description TEXT);
1
2
3
4
5
6
CREATE TABLE headphones (_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    price INTEGER,
    style INTEGER,
    in_stock INTEGER,
    description TEXT);

DEFAULT – When inserting a new row, if no value is defined, the default value given will be used

[slq]
CREATE TABLE headphones (_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
price INTEGER,
style INTEGER,
in_stock INTEGER NOT NULL DEFAULT 0,
description TEXT);
[/sql]

SQL COMMANDS FOR APP

SELECT FROM ;
->
SELECT * FROM pets;
* indicates “all rows and all columns”
“pets” specifies we want data from the pets table

1
2
INSERT INTO pets (_id, name, bread, gender, weight)
    VALUES(1, "TOMMY", "Pomeranian", 1, 4);
sqlite> .tables
pets   pets2
sqlite> SELECT * FROM pets;
sqlite> INSERT INTO pets (_id, name, breed, gender, weight)
   ...>         VALUES(1, "TOMMY", "Pomeranian", 1, 4);
Error: table pets has no column named breed
sqlite> INSERT INTO pets (_id, name, bread, gender, weight)
   ...>         VALUES(1, "TOMMY", "Pomeranian", 1, 4);
sqlite> INSERT INTO pets (_id, name, bread, gender, weight)
   ...>         VALUES(2, "Garfield", "Tabby", 1, 8);
sqlite> SELECT * FROM pets;
1|TOMMY|Pomeranian|1|4
2|Garfield|Tabby|1|8