In the last post I showed you how to create a database. We created a table inside it and inserted values. Although we could make sure that our database was successfully created, we had no way to confirm the same about our tables. In this post I’ll show you how to access the data from a database.
So create an activity and follow along. Complete Source Code is at the bottom
- Set the Content View of the activity. Do not put any elements in the layout.
- Switch over to the java file and write the below lines after the setContentView() method.
[java]
SQLiteDatabase db = openOrCreateDatabase("NeroDB", MODE_PRIVATE, null);
db.execSQL("CREATE TABLE IF NOT EXISTS NeroTable(LastName VARCHAR, FirstName VARCHAR, Age INT(3));");
db.execSQL("INSERT INTO NeroTable VALUES(‘NeroLast1’, ‘NeroFirst1’, 20);");
db.execSQL("INSERT INTO NeroTable VALUES(‘NeroLast2’, ‘NeroFirst2’, 21);");
db.execSQL("INSERT INTO NeroTable VALUES(‘NeroLast3’, ‘NeroFirst3’, 22);");
[/java] - Since, this post assumes that you have a fair idea of SQLite, you can change the name of the Database, table and the values inside the table to whatever you wish.
- Now, we will access the data from our database. Notice that we have not yet closed the database, this being the reason.
- Write the code below. I strongly recommend not to paste these lines, but to write them using the Auto-Complete feature of Eclipse editor. That way, in the suggestion, you can see the method signatures and it will help you understand what we are doing and also change a few values to your liking.
[java]
Cursor c = db.rawQuery("SELECT * FROM NeroTable;", null);
while(c.moveToNext()){
Log.v("NeroLog", c.getString(c.getColumnIndex("FirstName")));
}
[/java] - Now, we will drop the Table and delete the database. We are doing this because if we do not, every time we run the application, the three rows will be inserted into the table. You can choose to let it be and not drop the table and the database. In case you do want to, write the below lines.
[java]
db.execSQL("DROP TABLE NeroTable;");
this.deleteDatabase("NeroDB.db");
[/java] - Remember to close the database irrespective of whether or not you did the above step.
[java]
db.close();
[/java] - Now execute the application in an emulator.
- Once it runs successfully, switch over to the DDMS section. If you wrote the exact lines as I have you will find Logs with the tag NeroLog and the values as the first name column values in the database. This is how you make sure that your table has been created and the values have been inserted.
Understanding the Code
- Cursor :- Cursor is analogous to a pointer in most programming languages, It points to the beginning of the set of rows returned from a query.
- rawQuery(String SQL, String [] SelectionArgs) :- This method returns a Cursor and is similar to execSQL(). It provides a way to provide selection arguments to an SQL query. I will talk about selection arguments in a later post.
- moveToNext() :- Since a cursor points to the beginning of rows, this function provides a way to sequentially access each row.
- Log.v :- Verbose Log. Creates a Log with the provided tag and message in the DDMS LogCat.
- getColumnIndex :- This is a way to access the column by name of the row currently pointed to by Cursor.
- deleteDatabase() :- This method is used to delete the database.
COMPLETE SOURCE CODE
[java]
package com.nero.myfirstapp;
import android.media.MediaPlayer;
import android.os.Bundle;
import android.preference.PreferenceManager;
import android.app.Activity;
import android.app.AlertDialog;
import android.app.Dialog;
import android.app.ProgressDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
public class Main extends Activity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
SQLiteDatabase db = openOrCreateDatabase("NeroDB", MODE_PRIVATE, null);
db.execSQL("CREATE TABLE IF NOT EXISTS NeroTable(LastName VARCHAR, FirstName VARCHAR, Age INT(3));");
db.execSQL("INSERT INTO NeroTable VALUES(‘NeroLast1’, ‘NeroFirst1’, 20);");
db.execSQL("INSERT INTO NeroTable VALUES(‘NeroLast2’, ‘NeroFirst2’, 21);");
db.execSQL("INSERT INTO NeroTable VALUES(‘NeroLast3’, ‘NeroFirst3’, 22);");
Cursor c = db.rawQuery("SELECT * FROM NeroTable;", null);
while(c.moveToNext()){
Log.v("NeroLog", c.getString(c.getColumnIndex("FirstName")));
}
db.execSQL("DROP TABLE NeroTable;");
this.deleteDatabase("NeroDB.db");
db.close();
}
}
[/java]
