Android app using database sqlLite

I created a cute little test app today to try out a few database concepts on Android. Thankfully it's really easy and there are some great tutorials out there. Anyway some of the concepts are really useful so I thought I'd jot down my findings.

First I created my new Android project ignore the MainActivity for now. I create a pretty standard Muppet.java class. I shan't patronise you with the code, int ID, String name and getters and setters for each. Then I create a new class called MySqlLiteHelper.java this is what will do the db interaction for us.

MySqlLiteHelper.java:

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MySqlLiteHelper extends SQLiteOpenHelper {

 private static final String DATABASE_NAME  = "muppets.db";
 private static final int DATABASE_VERSION = 1;
 private static final String TABLE_NAME  = "theusers";

 // Database creation sql statement
 private static final String DATABASE_CREATE  = "create table " + TABLE_NAME + " (userid integer primary key autoincrement, muppetname text not null);";
 // DO NOT DO THIS IN YOUR CODE:
 private static final String DATABASE_ADD_USER  = "insert into "  + TABLE_NAME + " (muppetname) values (";
  
 
 public MySqlLiteHelper(Context context) {
  super(context, DATABASE_NAME, null, DATABASE_VERSION);
 }


 @Override
 public void onCreate(SQLiteDatabase database) {
  database.execSQL(DATABASE_CREATE);
 }
 

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  Log.w(MySqlLiteHelper.class.getName(), "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
  db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
  onCreate(db);
 }
 
 
 public void addNewuser(SQLiteDatabase database, String name){
  // DO NOT DO INSERTS LIKE THIS IN YOUR CODE. FIND OUT WHY I THE NEXT ARTICLE.
  String insertString = DATABASE_ADD_USER + "'" + name + "');";
  database.execSQL(insertString);
 }
 
 
 public List getAllUsers(SQLiteDatabase database){
  List muppets = new ArrayList();
  String[] columns = {"userid","muppetname"};

  Cursor cursor = database.query(TABLE_NAME, columns, null, null, null, null, null);

  cursor.moveToLast();
  while (!cursor.isBeforeFirst()) {
   Muppet muppet = cursorToMuppet(cursor);
   muppets.add(muppet);
   cursor.moveToPrevious();
  }
  // Make sure to close the cursor
  cursor.close();
  return muppets;
 }
 
 
 private Muppet cursorToMuppet(Cursor cursor) {
  Muppet muppet = new Muppet();
  muppet.setMuppetId(cursor.getInt(0));
  muppet.setMuppetName(cursor.getString(1));
  return muppet;
 }
 
} 

• MySqlLiteHelper - This function is the constructor, on initialization it will create the DB.
• onCreate - This function created the database table by executing the static string at the top.
• onUpgrade - I think this function is pretty clever, if you change the db version number, it'll self upgrade! Amazeballs!
• addNewuser - This is an ugly way to insert a user, I'll change this later.
• getAllUsers - Here we're creating a cursor and querying the db for all the users. Then we're reversing through the cursor and putting the user object into a list. I opted to reverse the list so new Muppets show up at the top. Although it's quite easy to traverse it normally and have new users at the bottom.

One quick thing that's important to mention is we're using a function in Muppet.java to force the fragment to show the muppet name, instead of the object id.

@Override
public String toString() {
 return name;
}

Our next step is to create the layout file. As an additional step I've included a fragment. This lets us compartmentalise our layout neatly. If you want to just dump a listView on the layout, you could also do that quite easily.

 <Button
  android:id="@+id/btnKermit"
  android:layout_width="wrap_content"
  android:layout_height="wrap_content"
  android:layout_alignParentLeft="true"
  android:text="Insert Kermit" />

 <Button
  android:id="@+id/btnFuzzy"
  android:layout_width="wrap_content"
  android:layout_height="wrap_content"
  android:layout_alignParentRight="true"
  android:text="Insert Fuzzy" />

 <Button
  android:id="@+id/btnRefresh"
  android:layout_width="wrap_content"
  android:layout_height="wrap_content"
  android:layout_below="@+id/btnKermit"
  android:layout_centerHorizontal="true"
  android:layout_marginTop="19dp"
  android:text="Refresh" />
 
 <fragment
  android:id="@+id/fragment1"
  android:name="com.example.testdata.ShowFragment"
  android:layout_width="wrap_content"
  android:layout_height="wrap_content"
  android:layout_alignParentBottom="true"
  android:layout_below="@+id/btnRefresh"
  android:layout_centerHorizontal="true" />

Notice that this fragment is pointing directly to our ShowFragment.java class which we'll create now:

import java.util.List;

import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.app.ListFragment;

public class ShowFragment extends ListFragment {

 private SQLiteDatabase database;
 private MySqlLiteHelper dbHelper;
 
 @Override
 public void onActivityCreated(Bundle savedInstanceState) {
  super.onActivityCreated(savedInstanceState);
  
  dbHelper = new MySqlLiteHelper(getActivity());
  database = dbHelper.getWritableDatabase();
  
  showAll();
 }
 
 public void showAll(){
  List values   = dbHelper.getAllUsers(database);
  final ArrayAdapter adapter = new ArrayAdapter(getActivity(), android.R.layout.simple_list_item_1, values);
  
  setListAdapter(adapter);
 }

 @Override
 public void onListItemClick(ListView l, View v, int position, long id) {
  // Do something with the data
 }
}

This should be pretty straightforward. OnActivityCreated we get the database instance, then call showAll. showAll() calls getAllUsers and puts the results in a List. This List is then converted to an adapter and we use setListAdapter to show the results in the fragment.

Bingo!
The main activity doesn't do anything special, it has a couple of buttons for adding new muppets, basic listeners which call

dbHelper.addNewuser(database, "Kermit");

Oh and there's a refresh button which updates the fragment:

 public void refresh(){
  //get fragment to refresh
  ShowFragment viewer = (ShowFragment) getFragmentManager().findFragmentById(R.id.fragment1);
     viewer.showAll();
 }

You're done! Super easy, hope it helps.