SQLite Basics

From ModMyGPhone Wiki

SDK : M5-rc14

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.

public class SQLite extends ListActivity
{
	public static SQLiteDatabase db = null;
	String dbase = "myDB";
	String dbTable = "my_table";
	ArrayList<String> result = new ArrayList<String>();
	@Override
    
    public void onCreate(Bundle icicle) 
    {
        super.onCreate(icicle);
        setContentView(R.layout.main);
        
         try {
        	
        	createDatabase(dbase, 1, MODE_WORLD_READABLE,null);
		
                db = openDatabase(dbase, null);
			
			
	      } catch (FileNotFoundException e) 
	      {
			e.printStackTrace();
	      }

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

• 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.getColumnIndex("Company"); 
int model = c.getColumnIndex("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);

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

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

Source :

XML :

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout   xmlns:android="http://schemas.android.com/apk/res/android"
                     android:orientation="vertical"
                     android:layout_width="fill_parent"
                     android:layout_height="fill_parent">

<ListView   android:id="@android:id/list"
                android:layout_width="fill_parent"
                android:layout_height="fill_parent"/>

</LinearLayout>

Java :

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

    @Override
    public void onCreate(Bundle icicle) 
    {
        super.onCreate(icicle);
        setContentView(R.layout.main);
        
        try {
        	
        	createDatabase(dbase, 1, MODE_WORLD_READABLE,null);
			db = openDatabase(dbase, null);
			
			
	     } catch (FileNotFoundException e) 
	     {
			e.printStackTrace();
	      }
		
	     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');");
					 
	     String[] values = {"Company", "Model"};
	     Cursor c = db.query(true, dbTable, values, null, null, null, null, null);
				
	     int country = c.getColumnIndex("Company"); 
	     int model = c.getColumnIndex("Model"); 
		
	     if (c != null) 
	     { 
			
		if (c.first()) 
		{ 
			int i = 0; 
				
			do { 
				i++; 
					
				String countryname = c.getString(country); 
				String modelname = c.getString(model); 
					
			        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);
    }
}