ソフトウェアエンジニアの技術ブログ：Software engineer tech blog

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]

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

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

Create, Read, Update, Delete

In order to open and see the list of all the pets in the shelter taking read actions on the table.
Tapping on the entry in the CatalogActivity in order to see information about specific pet require Read.
From the EditorActivity, changing the weight of a specific pet requires taking Update actions on the table.
Using the option from the overflow menu that removes incorrectly entered pets requires Delete.
Selecting the “Insert Pet” option from the overflow menu to insert a list of default information requires taking Create actions on the table.

storage class, data types
NULL, INTEGER, REAL, TEXT, BLOB

No booleans, use INTEGER instead
Ex:
false = 0
true = 1

attribute
Name TEXT
Price INTEGER
Style INTEGER
In Stock INTEGER
Description TEXT

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

CREATE TABLE pets (_id INTEGER, 
	name TEXT,
	bread TEXT,
	gender INTEGER,
	weight INTEGER);