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)
 {
    super.onCreate(icicle);
    setContentView(R.layout.main);

    try
    {

        db = openOrCreateDatabase(dbase, MODE_WORLD_READABLE,null);

    } catch (FileNotFoundException e)
    {
        e.printStackTrace();
    }

- 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 {
       i++;

       // 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);

setListAdapter(fileList);

- 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.

Download Source