Tuesday, February 26, 2008

SQLite Basics (Updated to 0.9-r1)

SDK : 0.9-r1

If this is your first encounter with SQLite, consider spending some time here.We'll do a simple activity to list some data stored in the database. There are some changes in the new SDK highlighted in blue.

SQLite.java :

public class SQLite extends ListActivity
public static SQLiteDatabase db = null;
String dbase = "myDB";
String dbTable = "my_table";
ArrayList<String> result = new ArrayList<String>();

public void onCreate(Bundle icicle)


db = openOrCreateDatabase(dbase, MODE_WORLD_READABLE,null);

} catch (FileNotFoundException e)
- createDatabase() and openDatabase() methods have been replaced by openOrCreateDatabase() method.

- db manages all the queries and SQL commands.

- Both createDatabase and openDatabase throw FileNotFoundException if the database could not be created/opened.

db.execSQL("CREATE TABLE IF NOT EXISTS "+dbTable+ " (Company TEXT, Model TEXT);");
db.execSQL("INSERT OR IGNORE INTO " + dbTable + " VALUES ('Motorola', 'Z12');");
db.execSQL("INSERT OR IGNORE INTO " + dbTable + " VALUES ('Nokia', 'N96');");
db.execSQL("INSERT OR IGNORE INTO " + dbTable + " VALUES ('HTC', 'Touch');");

- The execSQL method is used in non-query statements like DELETE, INSERT etc.

- In the INSERT statement, we input the value to be stored. However, in most conditions, the values will be stored in some variables. In such cases we use,db.execSQL("INSERT OR IGNORE INTO " + dbTable + " VALUES ('"+ company_value1 + "', '" + model_value1 +"');");

String[] values = {"Company", "Model"};
Cursor c = db.query(true, dbTable, values, null, null, null, null, null,null);
- An extra parameter has been added to the query() method. You can now limit the no. of rows to be queried.

- Cursor provides read/write access to the result from the query.

- values stores all the tables you want to query.

// Get the indices of the columns

int country = c.getColumnIndexOrThrow("Company");
int model = c.getColumnIndexOrThrow("Model");

// Check if any data was returned.
if (c != null)
if (c.first())
int i = 0;

// Loop through all results

do {

// Get values of the row the Cursor is pointing to.

String countryname = c.getString(country);

String modelname = c.getString(model);

// Add the value to an ArrayList<String> result

result.add("" + i + ". " + countryname+ " - " + modelname);

} while (c.next());

ArrayAdapter<String> fileList = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, result);


- getColumnIndex() has been replaced by getColumnIndexOrThrow() method.

- The results we store in the result list is added to the ArrayAdapter.

- ArrayAdapter manages the ListView which we use to display our data.

1 comment:

Anonymous said...

Thanks! This is much easier to understand than the sample notepad application in Android SDK.