SQLite Basics

From ModMyGPhone Wiki

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.

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.

• openOrCreateDatabase 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

_______________