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)
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
- 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();
}
}
}
|
No comments:
Post a Comment