Android and Sqlite – Using Database – Part Two

Lesson II

SQLite database textview example

In this lesson we will cover some basic principles of the database usage for Android. If you have any project open in Eclipse, close it from Package Explorer. Right click on the open project and select Close Project. We will start fresh one. Let`s create new project (File -> New -> Project -> Android Project). Populate dialog with following data:

Project Name:  DbContactConsole

Build Target: Android 2.2

Application Name: DbContactConsole

Package Name: org.example.dbcontactconsole

Create Activity: DbContactConsole

Min SDK Version: 8

Open Package Explorer , expand the project src directory, expand the package and there is our class – DbContactConsole.java. Double click on it and open it. We will leave things here as they were, let`s focus for a minute on defining database. Right click on project and select New -> Class. This will start new class dialog.  Let`s call it DbCreate.java. This class will hold necessary data for database creation. It will also extend SQLiteOpenHelper, a helper class that will manage database creation and version management:

public class DbCreate extends SQLiteOpenHelper{

…

}

Compiler will tell you that he cannot resolve SQLiteOpenHelper. We can make import of this class by positioning cursor on this class and press CTRL+1. It will popup small dialog where we can select several options. Let`s import ‘;SQLiteOpenHelper’  android.database.sqlite. This will automatically make the import.  We can achieve same thing by right clicking on the problematic class, click on the Quick Fix (you can see that shortcut for this is CTRL+1).

Let`s create two constants (actually, it is static final variable, there are no constants in java), one for the database name and other for the database version:

private static final String DB_NAME = “contactTest.db”;

private static final int DB_VERSION = 1;

DB_NAME will hold name for database, and DB_VERSION marks current version. Also, we will create constructor:

public DbCreate(Context context) {

super(context, DB_NAME, null, DB_VERSION);

}

This will create helper object that will create, open and manage database.

We will have to create two methods, onCreate and onUpgrade. First time we start application and try to access database, SQLOpenHelper will check database existence. Since there is no database, it will call onCreate() method to create it. On the other side, if database already exists, it will call onUpgrade() method. We will delete table here, and call onCreate().

@Override

public void onCreate(SQLiteDatabase db) {

db.execSQL(“CREATE TABLE “ + DbConstants.TABLE_NAME + ” “ +

                  “(” + _ID + ” INTEGER PRIMARY KEY AUTOINCREMENT, “ +

                                DbConstants.NAME + ” TEXT NOT NULL,” +

                                DbConstants.PHONE + ” TEXT NOT NULL,” +

                                DbConstants.EMAIL + “);”);

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion,

int newVersion) {

            db.execSQL(“DROP TABLE IF EXISTS “ + DbConstants.TABLE_NAME);

            onCreate(db);

}

DbCreate class:

onCreate() method will run sql query for database table creation. We will define several fields. Our database will have four fields: _ID, NAME, PHONE and EMAIL. _ID is row identifier, and it will be autoincrement type. To use it, we will have to make following import:

import static android.provider.BaseColumns._ID;

It is just unique id for a row. Other three values will be defined latter in our constant class. onUpgrade() method we will simply drop table and create new one with name defined in TABLE_NAME constant (it will also be defined next). That`s it, this is everything we need here, we will now define our constant class. Actually, let`s stop here for a second and discuss constant class. Since java does not support constants we need the way to define it. This is the small application but there will be the time when you have many classes. It will be wise to store all constants into one (or more) classes. Actually this is the point I want to make. We could define class for constants or we can do it by defining the interface.

Here is interface for constants:

interface OlympicMedal {

  static final String GOLD = “Gold”;

  static final String SILVER = “Silver”;

  static final String BRONZE = “Bronze”;

}

Placing constants in interface was popular before, however, since interface should be used just for defining methods that should be implemented by the classes, many think that this is bad oo programming practice. By placing constants in interface, they will promote that constants to the public api for the class. On the other side, we can define constants in class. Since we don`t need to instantiate object to use them (class has final identifier), to use constant(s) we will have to first use name of the class:

Class_name.GOLD

This can be a nice thing because in large projects, where we have several constant classes, we can easily find which class holds which constant(s). Ok, let`s define our constant class. Create new class in the same package, name it DbConstants, and define following constants:

public final class DbConstants {

      public static final String TABLE_NAME = “contactTest”;

      public static final String NAME = “name”;

      public static final String PHONE = “phone”;

      public static final String EMAIL = “email”;

      /**

         The caller references the constants using Consts.EMPTY_STRING,

         and so on. Thus, the caller should be prevented from constructing objects of

         this class, by declaring this private constructor.

        */

      private DbConstants(){

            //this prevents even the native class from

          //calling this constructor as well :

          throw new AssertionError();

      }

}

DbConstants class:

That`s it. We reference each constant using: DbConstants.CONSTANT_NAME. Also, with this step we manage to correct errors on the DbCreate class, in onCreate() method.

Let`s focus now on our main class, DbContactConsole. Our class will extend Activity. Let`s define some constants and private variables for this class:

public class DbContactConsole extends Activity {

private static String[] FROM = { _ID, DbConstants.NAME,               DbConstants.PHONE, DbConstants.EMAIL, };

private DbCreate contacts;

      private static SQLiteDatabase db;

We define string array FROM so we can speed things up in the sql query latter. Also, since we will have to use DbCreate object several times, we will define it once and use it where needed. Also, define contacts object from our DbCreate class we defined earlier.

Also, we will modify onCreate() method for a little bit, add following lines:

…

contacts = new DbCreate(this);

try {

            addContact();

            Cursor cursor = getContacts();

            showContacts(cursor);

      } finally {

            contacts.close();

            db.close();

      }

First create new DbCreate() object, call addContact() method (will be defined latter) and call showContacts() method (also will be defined latter).These two methods will respectfully add new contact to database and show it to the screen. At the end of the method, close database. This is really straight forward, so let`s move on. We will now define methods for adding, retrieving and showing database content.

private void addContact() {

   // Insert a new record into the Events data source.

   // You would do something similar for delete and update.

   db = contacts.getWritableDatabase();

   ContentValues values = new ContentValues();

   values.put(DbConstants.NAME, “Richard”);

   values.put(DbConstants.PHONE, “12345″);

   values.put(DbConstants.EMAIL, “myemail@something.com”);

   db.insertOrThrow(DbConstants.TABLE_NAME, null, values);

}

addContact()method will add new contact to the database. We open database for writing, instantiate new ContentValues object, put some values it it and call the method to insert data into database. Since we have defined _ID as autoincrement, we don`t need to insert that value. Database will take care for that each time record is inserted. We use insertOrThrow method for record insertion, and thus, we are sure that if something happens, SQLException will be thrown. We could put whole block into try/catch like any other exception handler.

Next, we have to read database content, so we made getContact() call:

private Cursor getContacts() {

   db = contacts.getReadableDatabase();

   Cursor cursor = db.query(DbConstants.TABLE_NAME, FROM, null, null, null,

                  null, null);

   startManagingCursor(cursor);

   return cursor;

}

This time we just want to read data from database, so we require read handle. Next, we define Cursor object which expose result from database query. We query the database with FROM clause defined at the beginning of the class. Actually, we require whole record with all fields, we could make conditions here, but for now, we just want all records from database.

Last step is to show all records on the screen:

private void showContacts(Cursor cursor) {

   StringBuilder builder = new StringBuilder(

            “Saved Contacts:n”);

   while (cursor.moveToNext()) {

       long id = cursor.getLong(0);

       String name = cursor.getString(1);

       String phone = cursor.getString(2);

       String email = cursor.getString(3);

       builder.append(id).append(“: “);

       builder.append(name).append(“: “);

       builder.append(phone).append(“n”);

       builder.append(email).append(“n”);

   }

   // Display on the screen

   TextView text = (TextView) findViewById(R.id.text);

   text.setText(builder);

}

What we want here is to pack all data from record into one string and display it in the TextView object to the screen. Thus, we define StringBuilder, iterating and reading record by record from the cursor, and display data onto screen.

DbContactConsole class:

We will made small changes on resource files. Open res ->values ->strings.xml, and define one string that will be shown at the application screen:

Strings.xml:

Contacts

Just change application name tag. Also, let`s change main.xml resource file:

That`s it. We can now run our application, and if everything is ok, we should see the following:

I have inserted several values already. Each time we run the program, we will insert new value. Bad thing is, that not only we don`t control time of insertion, but each time we insert record, it carries same value as previous. We will deal with that latter. For now, we can see how our autoincrement value works. It increments by one each time we insert record. Before we end this lesson, I have to mention two things. We should open two more views in Eclipse, to make life easier. First, we need LogCat view, which can be very useful for error detection. So click on Window -> Show View -> Other… ->Android -> LogCat. LogCat is the place where we can track errors as they occurs. It`s a quick way to try to find what is going on, of course, we can debug application and trace code execution, but if we want a quick check, this will be the place to look at. It appears at the bottom of Eclipse IDE.  Also, open File Explorer. Window -> Show View -> Other… -> Android -> File Explorer . File Explorer is the place where we can find our database. Select File Explorer view, and start expand it: data->data->packagename (in our case org.example.dbcontactconsole)->databases->contactTest.db. If you select it, red line will became available on the far right side of the view. If you point cursor to it, it will show you that it will delete selection. So, if you want to delete database, select it and click on red line. Database is deleted. If we start application again, new database will be created and new record will be inserted into it, starting again from _ID value one.

Ok, this is the end of the lesson. Next, we will move to point where we take more control of the insertion process. So keep on.

Source code can be downloaded from: http://sites.google.com/site/thiswillworktoo - check the RESOURCES section of the website.

proceed to part III: http://computersight.com/software/android-and-sqlite-using-database-part-three

or back to part I: http://computersight.com/computers/android-and-sqlite-using-database/

also check my iPhone tutorial at http://computersight.com/computers/iphone-multiview-application-part-i/