ANDROID: Basic SQLite
package com.example.android.waitlist.data;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.example.android.waitlist.data.WaitlistContract.*;
// COMPLETED (1) extend the SQLiteOpenHelper class
public class WaitlistDbHelper extends SQLiteOpenHelper {
// Static final String called DATABASE_NAME and set it to "waitlist.db"
// The database name
private static final String DATABASE_NAME = "waitlist.db";
// Static final int called DATABASE_VERSION and set it to 1
// If you change the database schema, you must increment the database version
private static final int DATABASE_VERSION = 1;
// Constructor that takes a context and calls the parent constructor
// Constructor
public WaitlistDbHelper(Context context) {
super(context, DATABASE_NAME, null /* Cursor Factory */ , DATABASE_VERSION);
}
// Override the onCreate method
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
// Create an String query called SQL_CREATE_WAITLIST_TABLE that will create the table
// Create a table to hold waitlist data
final String SQL_CREATE_WAITLIST_TABLE = "CREATE TABLE " + WaitlistEntry.TABLE_NAME + " (" +
WaitlistEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
WaitlistEntry.COLUMN_GUEST_NAME + " TEXT NOT NULL, " +
WaitlistEntry.COLUMN_PARTY_SIZE + " INTEGER NOT NULL, " +
WaitlistEntry.COLUMN_TIMESTAMP + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
"); ";
// Execute the query by calling execSQL on sqLiteDatabase and pass the string query SQL_CREATE_WAITLIST_TABLE
sqLiteDatabase.execSQL(SQL_CREATE_WAITLIST_TABLE);
}
// Override the onUpgrade method
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
// For now simply drop the table and create a new one. This means if you change the
// DATABASE_VERSION the table will be dropped.
// In a production app, this method might be modified to ALTER the table
// instead of dropping it, so that existing data is not deleted.
// Execute a drop table query, and then call onCreate to re-create it
sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + WaitlistEntry.TABLE_NAME);
onCreate(sqLiteDatabase);
}
}
package com.example.android.waitlist.data;
import android.provider.BaseColumns;
public class WaitlistContract {
// An inner class named WaitlistEntry class that implements the BaseColumns interface
public static final class WaitlistEntry implements BaseColumns {
// Static final members for the table name and each of the db columns
public static final String TABLE_NAME = "waitlist";
public static final String COLUMN_GUEST_NAME = "guestName";
public static final String COLUMN_PARTY_SIZE = "partySize";
public static final String COLUMN_TIMESTAMP = "timestamp";
}
}
package com.example.android.waitlist.data;
import android.content.ContentValues;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import java.util.ArrayList;
import java.util.List;
public class TestUtil {
public static void insertFakeData(SQLiteDatabase db){
if(db == null){
return;
}
//create a list of fake guests
List<ContentValues> list = new ArrayList<ContentValues>();
ContentValues cv = new ContentValues();
cv.put(WaitlistContract.WaitlistEntry.COLUMN_GUEST_NAME, "John");
cv.put(WaitlistContract.WaitlistEntry.COLUMN_PARTY_SIZE, 12);
list.add(cv);
cv = new ContentValues();
cv.put(WaitlistContract.WaitlistEntry.COLUMN_GUEST_NAME, "Tim");
cv.put(WaitlistContract.WaitlistEntry.COLUMN_PARTY_SIZE, 2);
list.add(cv);
cv = new ContentValues();
cv.put(WaitlistContract.WaitlistEntry.COLUMN_GUEST_NAME, "Jessica");
cv.put(WaitlistContract.WaitlistEntry.COLUMN_PARTY_SIZE, 99);
list.add(cv);
cv = new ContentValues();
cv.put(WaitlistContract.WaitlistEntry.COLUMN_GUEST_NAME, "Larry");
cv.put(WaitlistContract.WaitlistEntry.COLUMN_PARTY_SIZE, 1);
list.add(cv);
cv = new ContentValues();
cv.put(WaitlistContract.WaitlistEntry.COLUMN_GUEST_NAME, "Kim");
cv.put(WaitlistContract.WaitlistEntry.COLUMN_PARTY_SIZE, 45);
list.add(cv);
//insert all guests in one transaction
try
{
db.beginTransaction();
//clear the table first
db.delete (WaitlistContract.WaitlistEntry.TABLE_NAME,null,null);
//go through the list and add one by one
for(ContentValues c:list){
db.insert(WaitlistContract.WaitlistEntry.TABLE_NAME, null, c);
}
db.setTransactionSuccessful();
}
catch (SQLException e) {
//too bad :(
}
finally
{
db.endTransaction();
}
}
}
package com.example.android.waitlist;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;
import android.view.View;
import com.example.android.waitlist.data.TestUtil;
import com.example.android.waitlist.data.WaitlistContract;
import com.example.android.waitlist.data.WaitlistDbHelper;
public class MainActivity extends AppCompatActivity {
private GuestListAdapter mAdapter;
private SQLiteDatabase mDb;
//Create local EditText fields for mNewGuestNameEditText and mNewPartySizeEditText
private EditText mNewGuestNameEditText;
private EditText mNewPartySizeEditText;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
RecyclerView waitlistRecyclerView;
// Set local attributes to corresponding views
waitlistRecyclerView = (RecyclerView) this.findViewById(R.id.all_guests_list_view);
// Set the Edit texts to the corresponding views using findViewById
mNewGuestNameEditText = (EditText) this.findViewById(R.id.person_name_edit_text);
mNewPartySizeEditText = (EditText) this.findViewById(R.id.party_count_edit_text);
// Set layout for the RecyclerView, because it's a list we are using the linear layout
waitlistRecyclerView.setLayoutManager(new LinearLayoutManager(this));
// Create a DB helper (this will create the DB if run for the first time)
WaitlistDbHelper dbHelper = new WaitlistDbHelper(this);
// Keep a reference to the mDb until paused or killed. Get a writable database
// because you will be adding restaurant customers
mDb = dbHelper.getWritableDatabase();
//Fill the database with fake data
TestUtil.insertFakeData(mDb);
// Get all guest info from the database and save in a cursor
Cursor cursor = getAllGuests();
// COMPLETED (10) Pass the entire cursor to the adapter rather than just the count
// Create an adapter for that cursor to display the data
mAdapter = new GuestListAdapter(this, cursor);
// Link the adapter to the RecyclerView
waitlistRecyclerView.setAdapter(mAdapter);
// Create an item touch helper to handle swiping items off the list
new ItemTouchHelper(new ItemTouchHelper.SimpleCallback(0, ItemTouchHelper.LEFT | ItemTouchHelper.RIGHT) {
// COMPLETED (4) Override onMove and simply return false inside
@Override
public boolean onMove(RecyclerView recyclerView, RecyclerView.ViewHolder viewHolder, RecyclerView.ViewHolder target) {
//do nothing, we only care about swiping
return false;
}
// COMPLETED (5) Override onSwiped
@Override
public void onSwiped(RecyclerView.ViewHolder viewHolder, int swipeDir) {
// COMPLETED (8) Inside, get the viewHolder's itemView's tag and store in a long variable id
//get the id of the item being swiped
long id = (long) viewHolder.itemView.getTag();
// COMPLETED (9) call removeGuest and pass through that id
//remove from DB
removeGuest(id);
// COMPLETED (10) call swapCursor on mAdapter passing in getAllGuests() as the argument
//update the list
mAdapter.swapCursor(getAllGuests());
}
//COMPLETED (11) attach the ItemTouchHelper to the waitlistRecyclerView
}).attachToRecyclerView(waitlistRecyclerView);
}
/**
* This method is called when user clicks on the Add to waitlist button
*
* @param view The calling view (button)
*/
public void addToWaitlist(View view) {
// Check if any of the EditTexts are empty, return if so
if (mNewGuestNameEditText.getText().length() == 0 ||
mNewPartySizeEditText.getText().length() == 0) {
return;
}
// Create an integer to store the party size and initialize to 1
//default party size to 1
int partySize = 1;
// Use Integer.parseInt to parse mNewPartySizeEditText.getText to an integer
try {
//mNewPartyCountEditText inputType="number", so this should always work
partySize = Integer.parseInt(mNewPartySizeEditText.getText().toString());
} catch (NumberFormatException ex) {
// Make sure you surround the Integer.parseInt with a try catch and log any exception
Log.e(LOG_TAG, "Failed to parse party size text to number: " + ex.getMessage());
}
// call addNewGuest with the guest name and party size
// Add guest info to mDb
addNewGuest(mNewGuestNameEditText.getText().toString(), partySize);
// call mAdapter.swapCursor to update the cursor by passing in getAllGuests()
// Update the cursor in the adapter to trigger UI to display the new list
mAdapter.swapCursor(getAllGuests());
// To make the UI look nice, call .getText().clear() on both EditTexts, also call clearFocus() on mNewPartySizeEditText
//clear UI text fields
mNewPartySizeEditText.clearFocus();
mNewGuestNameEditText.getText().clear();
mNewPartySizeEditText.getText().clear();
}
/**
* Query the mDb and get all guests from the waitlist table
*
* @return Cursor containing the list of guests
*/
private Cursor getAllGuests() {
return mDb.query(
WaitlistContract.WaitlistEntry.TABLE_NAME,
null,
null,
null,
null,
null,
WaitlistContract.WaitlistEntry.COLUMN_TIMESTAMP
);
}
/**
* Adds a new guest to the mDb including the party count and the current timestamp
*
* @param name Guest's name
* @param partySize Number in party
* @return id of new record added
*/
private long addNewGuest(String name, int partySize) {
// Inside, create a ContentValues instance to pass the values onto the insert query
ContentValues cv = new ContentValues();
// COMPLETED (6) call put to insert the name value with the key COLUMN_GUEST_NAME
cv.put(WaitlistContract.WaitlistEntry.COLUMN_GUEST_NAME, name);
// COMPLETED (7) call put to insert the party size value with the key COLUMN_PARTY_SIZE
cv.put(WaitlistContract.WaitlistEntry.COLUMN_PARTY_SIZE, partySize);
// COMPLETED (8) call insert to run an insert query on TABLE_NAME with the ContentValues created
return mDb.insert(WaitlistContract.WaitlistEntry.TABLE_NAME, null, cv);
}
/**
* Removes the record with the specified id
*
* @param id the DB id to be removed
* @return True: if removed successfully, False: if failed
*/
private boolean removeGuest(long id) {
// COMPLETED (2) Inside, call mDb.delete to pass in the TABLE_NAME and the condition that WaitlistEntry._ID equals id
return mDb.delete(WaitlistContract.WaitlistEntry.TABLE_NAME, WaitlistContract.WaitlistEntry._ID + "=" + id, null) > 0;
}
}
package com.example.android.waitlist;
import android.content.Context;
import android.database.Cursor;
import android.support.v7.widget.RecyclerView;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.TextView;
import com.example.android.waitlist.data.WaitlistContract;
public class GuestListAdapter extends RecyclerView.Adapter<GuestListAdapter.GuestViewHolder> {
// Holds on to the cursor to display the waitlist
private Cursor mCursor;
private Context mContext;
/**
* Constructor using the context and the db cursor
* @param context the calling context/activity
* @param cursor the db cursor with waitlist data to display
*/
public GuestListAdapter(Context context, Cursor cursor) {
this.mContext = context;
this.mCursor = cursor;
}
@Override
public GuestViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
// Get the RecyclerView item layout
LayoutInflater inflater = LayoutInflater.from(mContext);
View view = inflater.inflate(R.layout.guest_list_item, parent, false);
return new GuestViewHolder(view);
}
@Override
public void onBindViewHolder(GuestViewHolder holder, int position) {
// Move the mCursor to the position of the item to be displayed
if (!mCursor.moveToPosition(position))
return; // bail if returned null
// Update the view holder with the information needed to display
String name = mCursor.getString(mCursor.getColumnIndex(WaitlistContract.WaitlistEntry.COLUMN_GUEST_NAME));
int partySize = mCursor.getInt(mCursor.getColumnIndex(WaitlistContract.WaitlistEntry.COLUMN_PARTY_SIZE));
// COMPLETED (6) Retrieve the id from the cursor and
long id = mCursor.getLong(mCursor.getColumnIndex(WaitlistContract.WaitlistEntry._ID));
// Display the guest name
holder.nameTextView.setText(name);
// Display the party count
holder.partySizeTextView.setText(String.valueOf(partySize));
// COMPLETED (7) Set the tag of the itemview in the holder to the id
holder.itemView.setTag(id);
}
@Override
public int getItemCount() {
// Update the getItemCount to return the getCount of the cursor
return mCursor.getCount();
}
/**
* Swaps the Cursor currently held in the adapter with a new one
* and triggers a UI refresh
*
* @param newCursor the new cursor that will replace the existing one
*/
public void swapCursor(Cursor newCursor) {
// COMPLETED (16) Inside, check if the current cursor is not null, and close it if so
// Always close the previous mCursor first
if (mCursor != null) mCursor.close();
// COMPLETED (17) Update the local mCursor to be equal to newCursor
mCursor = newCursor;
// COMPLETED (18) Check if the newCursor is not null, and call this.notifyDataSetChanged() if so
if (newCursor != null) {
// Force the RecyclerView to refresh
this.notifyDataSetChanged();
}
}
/**
* Inner class to hold the views needed to display a single item in the recycler-view
*/
class GuestViewHolder extends RecyclerView.ViewHolder {
// Will display the guest name
TextView nameTextView;
// Will display the party size number
TextView partySizeTextView;
/**
* Constructor for our ViewHolder. Within this constructor, we get a reference to our
* TextViews
*
* @param itemView The View that you inflated in
* {@link GuestListAdapter#onCreateViewHolder(ViewGroup, int)}
*/
public GuestViewHolder(View itemView) {
super(itemView);
nameTextView = (TextView) itemView.findViewById(R.id.name_text_view);
partySizeTextView = (TextView) itemView.findViewById(R.id.party_size_text_view);
}
}
}
Creating Contract Class
Creating the Database
Retrieving all records
Update the adapter
Add New
Remove
https://developer.android.com/reference/android/provider/BaseColumns.html
https://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html