Category: Android / Java / SQLite

public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs,
		String sortOrder){
	SQLiteDatabase database = mDbHelper.getReadableDatabase();

	Cursor cursor;

	int match = sUriMatcher.match(uri);
	switch(match){
		case PETS:
			// 
			break;
		case PET_ID:
			selection = PetEntry._ID + "=?";
			selectionArgs = new String[] { String.valueOf(ContentUris.parseId(uri))};
			cursor = database.query(PetEntry.TABLE_NAME, projection, selection, selectionArgs,
				null, null, sortOrder);
			break;
		default:
			throw new IllegalArgumentException("Cannot query unknown URI " + uri);
	}
	return cursor;
}

public class PetProvider extends ContentProvider {

	@Override
	public Uri insert(Uri uri, ContentValues contentValues){
		final int match = sUriMatcher.match(uri);
		switch (match){
			case PETS:
				return insertPet(uri, contentValues);
			default:
				throw new IllegalArgumentException("Insertion is not supported for " + uri);
		}
	}

	private Uri insertPet(Uri uri, ContentValues values){
		return ContentUris.withAppendedId(uri, id):
	}
}

private Uri insertPet(Uri uri, ContentValues values){
	String name = values.getAsString(PetEntry.COLUMN_PET_NAME);
	if (name == null){
		throw new IllegalArgumentException("Pet requires a name");
	}

	SQLiteDatabase database = mDbHelper.getWritableDatabse();

	long id = database.insert(PetEntry.TABLE_NAME, null, values);
	if (id == -1){
		Log.e(LOG_TAG, "Failed to insert row for " + uri);
		return null;
	}

	return ContentUris.withAppendedId(uri, id);
}

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

<provider
	android:name=".data.PetProvider"
	android:authorities="com.example.android.pets"
	android:exported="false" />

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
}

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

Cursor c = db.query(PetEntry.TABLE_NAME,projection,
		selection, selectionArgs,
		null, null, null);

while (cursor.moveToNext()){
	int currentID = cursor.getInt(idColumnIndex);
	String currentName = cursor.getString(nameColumnIndex);
	displayView.append(("\n" + currentId + " - " +
		currentName));
}

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

SELECT name, breed FROM pets;
SELECT * FROM pets;
SELECT * FROM pets WHERE _id = 1;

String selection = PetEntry._ID = "=?";
String[] selectionArgs = {"1"};

In Java;

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

cusor.moveToPosition(3);
int nameColumnIndex =
	cusor.getColumnIndex(PetEntry.COLUMN_PET_NAME);
String name = cursor.getString(nameColumnIndex);

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

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

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

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

-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)

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

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

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

Contract Class

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 );"

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";
	}
}

_ID
COLUMN_PET_NAME
COLUMN_PET_BREED
COLUMN_PET_GENDER
COLUMN_PET_WEIGHT

GENDER_UNKNOWN
GENDER_MALE
GENDER_FEMALE

DELETE FROM DELETE FROM pets WHERE id = ;

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

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

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;

PRIMARY KEY, AUTO INCREMENT, NOT NULL, DEFAULT

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

CREATE TABLE headphones (_id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT, 
	price INTEGER,
	style INTEGER, 
	in_stock INTEGER, 
	description TEXT);

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]