The Chewy Explorer: One Day in Android SQLite

Plan and Estimate Time Required

Preparation and download ; require about 10 mins
Installation 15mins-30mins

(depends on internet download speed).

Android and SQLite Programming 60mins

What do we need?

Hardware and OS
Computer OS : Mac OS X 10.8.5
Phone H/W : Nexus 4
Phone OS : Android 4.4.4 (KitKat)

Tools and others
IDE : Eclipse 4.3 for J2EE
Java : JRE 6 and JDK 6
SQLite : version 3.8.11.1.
Xcode Command Line Tool : from Xcode 5.1

Optional :
Chrome : Version 44.0.2403.155 (64-bit)

SQLite Download and Installation

Reference: Download click me ; Install click me

Download sqlite-autoconf-*.tar.gz from source code section.
Open app "Finder", double click the gz file, a folder named "sqlite-autoconf-3081101" will be created.
Open app "Terminal", do the following

$cd sqlite-autoconf-3081101
$./configure --prefix=/usr/local
$make
$make install

(or $sudo make install)

$sqlite3

The last line will load sqlite, means it is successfully installed.
Enter ".help" anytime to show the sqlite commands.
Besides, News folders are created in /usr/local/, "bin", "include", "lib", "share"

Troubleshoot

Error: configure: error: no acceptable C compiler found in $PATH

Cause: run SQLite installation command "./configure --prefix=/usr/local/"

Solution: Download the "Command Line Tools" from Xcode (open Preference > Download)

Where is my data

If you run SQLite command at Mac Terminal app, you will see the data that CRUD by Android application. (C-Create, R-Read, U-Update, D-Delete)

The Android write data to

/data/data/{your java package}/databases/{your database name}.db

e.g.

/data/data/com.chewy.unitconverter/databases/HistoryReader.db

Android SQLite Programming

Save History

Remove History

In this Blog Post

Insert a record in a table at DB
Read the record count from a table at DB
Remove all the record from a table at DB

Android UI, Activity

The code in this example is an extension from previous blog One Day in Android Programming.

modify lo_convert_me.xml

add Button "button_save".
add Button "button_clear".
add TextView "text_message".

modify ConverterActivity.java

rename method from "addListenerOnButton" to "addListenerOnButtons".
modify method "addListenerOnButtons".

Create java class, named "ConverterHistory", extends "java.lang.Object".
Create java class, named "HistoryReaderDBHelper", extends "android.database.sqlite.SQLiteOpenHelper", select checkbox "Constructors from Superclass".
Modify ConverterHistory.java

abstract class ConverterEntry implements android.provider.BaseColumns
add method addInformationIntoDB.
add method getNumberOfRecordsFromDB.
add method removeAllRecordsFromDB.

Modify HistoryReaderDBHelper.java

add constructor with one input parameter, "Context"
modify method onCreate.
modify method onUpgrade.
add method onDowngrade.

Sample Code

ConverterActivity.java
package com.chewy.unitconverter;

import java.util.Calendar;
import android.app.Activity;
import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;

public class ConverterActivity extends Activity {

private static final int CASE_INCH_CM = 0;
private static final int CASE_FAH_CEL = 1;
private int selectedItemInt = 0;

private TextView tvConvertFrom;
private TextView tvConvertTo;
private TextView tvMessage;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.lo_convert_me);

Bundle bundle = this.getIntent().getExtras();
String thingPassedOntoMe = bundle.getString ("passMeOn");
selectedItemInt = bundle.getInt("selectedItem");

TextView tvConvertType = (TextView)findViewById(R.id.text_convert_type);
tvConvertType.setText(thingPassedOntoMe);

tvConvertFrom = (TextView)findViewById(R.id.edit_convert_from);
tvConvertFrom.setText("");
tvConvertTo = (TextView)findViewById(R.id.text_convert_to);
tvConvertTo.setText("0.00");

tvMessage = (TextView)findViewById(R.id.text_message);
tvMessage.setText("--");

addListenerOnButtons();

}

private void addListenerOnButtons() {
Button convertMeButton = (Button) findViewById(R.id.button_convert);
convertMeButton.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
// Perform action on click
float result =0f;
switch (selectedItemInt) {
case CASE_INCH_CM:
result = Converter.convertInchToCm(Float.valueOf(tvConvertFrom.getText().toString()));
break;
case CASE_FAH_CEL:
result = Converter.convertFehrenheitToCelsius(Float.valueOf(tvConvertFrom.getText().toString()));
break;
} // end switch
tvConvertTo.setText(String.valueOf(result));
} // end onclick
}); // end convertButton

Button saveMeButton = (Button) findViewById(R.id.button_save);
saveMeButton.setOnClickListener(new View.OnClickListener() {

@Override
public void onClick(View v) {
float result =0f;
String unitFrom="", unitTo="";
switch (selectedItemInt) {
case CASE_INCH_CM:
result = Converter.convertInchToCm(Float.valueOf(tvConvertFrom.getText().toString()));
unitFrom = "INCH";
unitTo = "CM";
break;
case CASE_FAH_CEL:
result = Converter.convertFehrenheitToCelsius(Float.valueOf(tvConvertFrom.getText().toString()));
unitFrom = "FAH";
unitTo = "CEL";
break;
} // end switch
tvConvertTo.setText(String.valueOf(result));

ConverterHistory saveHist = new ConverterHistory();
saveHist.addInformationIntoDB(v, unitFrom, unitTo,
String.valueOf( tvConvertFrom.getText()),
String.valueOf( tvConvertTo.getText()) );

// display the number of records saved in db
int recordSaved = saveHist.getNumberOfRecordsFromDB(v);
tvMessage.setText("Records saved is " + String.valueOf(recordSaved));

} // end onClick
}); // end savebutton

Button clearMeButton = (Button) findViewById(R.id.button_clear);
clearMeButton.setOnClickListener(new View.OnClickListener() {

@Override
public void onClick(View v) {

ConverterHistory clearHist = new ConverterHistory();
int recordRemoved = clearHist.removeAllRecordsFromDB(v);

tvMessage.setText("Records removed is " + String.valueOf(recordRemoved));

} // end onClick
}); // end clearbutton

}
}

ConverterHistory.java

package com.chewy.unitconverter;

import java.util.Calendar;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.provider.BaseColumns;
import android.view.View;

public class ConverterHistory {

public ConverterHistory() {
}

public static abstract class ConverterEntry implements BaseColumns {
public static final String TABLE_ENTRY = "entry";
public static final String COLUMN_ENTRY_ID = "entryid"; //use timestamp mmddyyhhmmss as entryid
public static final String COLUMN_UNIT_FROM = "unitfrom";
public static final String COLUMN_VAL_FROM = "valfrom";
public static final String COLUMN_UNIT_TO = "unitto";
public static final String COLUMN_VAL_TO = "valto";

public static final String COMMA_SEP = ",";
public static final String TEXT_TYPE = " TEXT ";
public static final String COLUMN_NAME_NULLABLE = " NULLABLE ";

public static final String SQL_CREATE_ENTRIES =
new StringBuffer()
.append("CREATE TABLE ").append(TABLE_ENTRY).append(" ( ")
.append(ConverterEntry.COLUMN_ENTRY_ID)
.append(" INTEGER PRIMARY KEY").append(COMMA_SEP)
.append(ConverterEntry.COLUMN_UNIT_FROM).append(TEXT_TYPE).append(COMMA_SEP)
.append(ConverterEntry.COLUMN_UNIT_TO).append(TEXT_TYPE).append(COMMA_SEP)
.append(ConverterEntry.COLUMN_VAL_FROM).append(TEXT_TYPE).append(COMMA_SEP)
.append(ConverterEntry.COLUMN_VAL_TO).append(TEXT_TYPE)
.append(" ) ")
.toString();

public static final String SQL_DELETE_ENTRIES =
"DROP TABLE IF EXISTS " + ConverterEntry.TABLE_ENTRY;

}

public void addInformationIntoDB (
View v, String unitFrom, String unitTo, String valFrom, String valTo) {

HistoryReaderDBHelper mDbHelper = new HistoryReaderDBHelper(v.getContext());
// Gets the data repository in write mode
SQLiteDatabase db = mDbHelper.getWritableDatabase();

// for debug in realtime
String dbpath = (v.getContext().getDatabasePath(HistoryReaderDBHelper.DATABASE_NAME)).getAbsolutePath();

// Create a new map of values, where column names are the keys
ContentValues values = new ContentValues();

Calendar now = Calendar.getInstance();
String id = new StringBuffer()
.append( String.valueOf( now.get(Calendar.YEAR) ) )
.append( String.valueOf( now.get(Calendar.MONTH) ) )
.append( String.valueOf( now.get(Calendar.DATE) ) )
.append( String.valueOf( now.get(Calendar.HOUR_OF_DAY) ) )
.append( String.valueOf( now.get(Calendar.MINUTE) ) )
.append( String.valueOf( now.get(Calendar.SECOND) ) )
.toString() ;

values.put(ConverterHistory.ConverterEntry.COLUMN_ENTRY_ID, id);
values.put(ConverterHistory.ConverterEntry.COLUMN_UNIT_FROM, unitFrom);
values.put(ConverterHistory.ConverterEntry.COLUMN_UNIT_TO, unitTo);
values.put(ConverterHistory.ConverterEntry.COLUMN_VAL_FROM, valFrom);
values.put(ConverterHistory.ConverterEntry.COLUMN_VAL_TO, valTo);

// Insert the new row, returning the primary key value of the new row
long newRowId;
newRowId = db.insert(
ConverterHistory.ConverterEntry.TABLE_ENTRY,
ConverterHistory.ConverterEntry.COLUMN_NAME_NULLABLE,
values);
db.close();
}

public int getNumberOfRecordsFromDB(View v) {
HistoryReaderDBHelper mDbHelper = new HistoryReaderDBHelper(v.getContext());
// Gets the data repository in read mode
SQLiteDatabase db = mDbHelper.getReadableDatabase();
int records=0;

String countQuery = new StringBuffer()
.append("SELECT entryid FROM ")
.append(ConverterEntry.TABLE_ENTRY)
.toString() ;
Cursor cursor = db.rawQuery(countQuery, null);
records = cursor.getCount();
cursor.close();
db.close();
return records;
}
public int removeAllRecordsFromDB(View v) {
HistoryReaderDBHelper mDbHelper = new HistoryReaderDBHelper(v.getContext());
SQLiteDatabase db = mDbHelper.getWritableDatabase();

int records=0;

// delete all records from the entry table
records = db.delete(ConverterEntry.TABLE_ENTRY, null, null);
db.close();
return records;
}

}

HistoryReaderDBHelper

package com.chewy.unitconverter;

import java.sql.SQLException;

import com.chewy.unitconverter.ConverterHistory.ConverterEntry;

import android.content.Context;

import android.database.DatabaseErrorHandler;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

import android.database.sqlite.SQLiteOpenHelper;

/**

* A helper class to manage database creation and version management.

* @author gndev

*

*/

public class HistoryReaderDBHelper extends SQLiteOpenHelper {

public static final int DATABASE_VERSION = 1;

public static final String DATABASE_NAME = "HistoryReader.db";

public HistoryReaderDBHelper(Context context) {

super(context, DATABASE_NAME, null, DATABASE_VERSION);

}

public HistoryReaderDBHelper(Context context, String name,

CursorFactory factory, int version) {

super(context, name, factory, version);

}

public HistoryReaderDBHelper(Context context, String name,

CursorFactory factory, int version,

DatabaseErrorHandler errorHandler) {

super(context, name, factory, version, errorHandler);

}

@Override

public void onCreate(SQLiteDatabase db) {

db.beginTransaction();

try {

db.execSQL(ConverterEntry.SQL_CREATE_ENTRIES);

} finally {

db.endTransaction();

}

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

// discard data and restart

db.beginTransaction();

try {

db.execSQL(ConverterEntry.SQL_DELETE_ENTRIES);

onCreate(db);

}finally {

db.endTransaction();

}

}

public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {

db.beginTransaction();

try {

onUpgrade(db, oldVersion, newVersion);

} finally {

db.endTransaction();

}

}

}

Wednesday, September 9, 2015

One Day in Android SQLite