SQLite database

Print Friendly

This article describes how to create a project using an SQLite database. Most online tutorials show how to input one value. Here I will create a database of users with three values: first name, last name and age. This is then displayed in a ListView. This article includes adding and deleting users, using a Cursor, dynamically loading data to a ListView and using an AlertDialog to input user information.

The source code can be downloaded HERE

Fist, we define the main.xml file to be used:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
	android:layout_width="fill_parent" android:layout_height="fill_parent"
	android:orientation="vertical">
	<LinearLayout android:id="@+id/group"
		android:layout_width="wrap_content" android:layout_height="wrap_content">
		<Button android:id="@+id/add" android:layout_width="wrap_content"
			android:layout_height="wrap_content" android:text="Add New" />
		<Button android:id="@+id/delete" android:layout_width="wrap_content"
			android:layout_height="wrap_content" android:text="Delete First" />
	</LinearLayout>
	<ListView android:id="@android:id/list" android:layout_width="fill_parent"
		android:layout_height="wrap_content" />
</LinearLayout>

For the alert dialog, we need to create another XML file called newuser.xml in order to insert new users into our database:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
	android:layout_width="fill_parent" android:layout_height="fill_parent"
	android:orientation="vertical">
	<LinearLayout android:layout_width="wrap_content"
		android:layout_height="wrap_content" android:orientation="horizontal">
		<TextView android:layout_width="wrap_content"
			android:layout_height="wrap_content" android:text="First Name:" />
		<EditText android:id="@+id/first_name" android:layout_width="wrap_content"
			android:layout_height="wrap_content" />
	</LinearLayout>
	<LinearLayout android:layout_width="wrap_content"
		android:layout_height="wrap_content" android:orientation="horizontal">
		<TextView android:layout_width="wrap_content"
			android:layout_height="wrap_content" android:text="Last Name:" />
		<EditText android:id="@+id/last_name" android:layout_width="wrap_content"
			android:layout_height="wrap_content" />
	</LinearLayout>
	<LinearLayout android:layout_width="wrap_content"
		android:layout_height="wrap_content" android:orientation="horizontal">
		<TextView android:layout_width="wrap_content"
			android:layout_height="wrap_content" android:text="Age:" />
		<EditText android:id="@+id/age_age" android:layout_width="wrap_content"
			android:layout_height="wrap_content" />
	</LinearLayout>
</LinearLayout>

Create MYSQLiteHelper.java

Now lets create a class MYSQLiteHelper that extends SQLiteOpenHelper and paste the code below:

public class MySQLiteHelper extends SQLiteOpenHelper {

 public static final String TABLE_USERS = "users";
 public static final String COLUMN_ID = "_id";
 public static final String COLUMN_FIRST_NAME = "firstname";
 public static final String COLUMN_LAST_NAME = "lastname";
 public static final String COLUMN_AGE = "age";

 private static final String DATABASE_NAME = "users.db";
 private static final int DATABASE_VERSION = 1;

 // Database creation sql statement
 private static final String DATABASE_CREATE = "create table "
 + TABLE_USERS + "( " + COLUMN_ID
 + " integer primary key autoincrement, " + COLUMN_FIRST_NAME
 + " text not null," + COLUMN_LAST_NAME + " text not null," +
 COLUMN_AGE + " text not null);";

 public MySQLiteHelper(Context context) {
 super(context, DATABASE_NAME, null, DATABASE_VERSION);
 }

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

 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 db.execSQL("DROP TABLE IF EXISTS "+TABLE_USERS);
 onCreate(db);

 }

}

The static final Strings represent the values of the database. Clearly you can see that my database’s name is users.db, I will create a table called “users”, with three columns, “firstname”, “lastname” and “age”.

IMPORTANT — COLUMN_ID = “_id” NEEDS TO BE INCLUDED FOR ANDROID DATABASES TO WORK PROPERLY.

For those that do not know mysql syntax, the String DATABASE_CREATE is just creating a TABLE (should name it TABLE_CREATE?). It is creating the structure of the database for adding values. Most importantly take close attention on how the columns are added:

String DATABASE_CREATE = "create table "
			+ TABLE_USERS + "( " + COLUMN_ID
			+ " integer primary key autoincrement, " + COLUMN_FIRST_NAME
			+ " text not null," + COLUMN_LAST_NAME + " text not null," +
			COLUMN_AGE + " text not null);";

SQLite syntax:

create table users(_id integer primary key autoincrement, firstname text not null, lastname text not null, age text not null);


If you want to create more tables or columns, write it out first in SQLite syntax then turn it to a string, for example:

create table workers(_id integer primary key autoincrement, firstname text not null, lastname text not null, age text not null, work_id text not null,
	work_extension);

In java, this would be:

public static final String TABLE_WORKERS = "workers"
public static final String COLUMN_ID = "_id";
public static final String COLUMN_FIRST_NAME = "firstname";
public static final String COLUMN_LAST_NAME = "lastname";
public static final String COLUMN_AGE = "age";
public static final String COLUMN_WORK_ID = "work_id";
public static final String COLUMN_WORK_EXTENSION = "work_extension";

String DATABASE_CREATE = "create table "
			+ TABLE_WORKERS + "( " + COLUMN_ID
			+ " integer primary key autoincrement, " + COLUMN_FIRST_NAME
			+ " text not null," + COLUMN_LAST_NAME + " text not null,"
			+ COLUMN_AGE + " text not null," + COLUMN_WORK_ID + "text not null,"
			+ COLUMN_WORK_EXTENSION + "text not null);";

I thought people might be confused as to how to create more columns, so there you go!

The methods that follow are just to create, update the database.

Create Users.java

Now create another class Users.java:

public class Users {

	private long mId;
	private String mFirstName;
	private String mLastName;
	private String mAge;

	public long getmId() {
		return mId;
	}
	public void setmId(long mId) {
		this.mId = mId;
	}
	public String getmFirstName() {
		return mFirstName;
	}
	public void setmFirstName(String mFirstName) {
		this.mFirstName = mFirstName;
	}
	public String getmLastName() {
		return mLastName;
	}
	public void setmLastName(String mLastName) {
		this.mLastName = mLastName;
	}
	public String getmAge() {
		return mAge;
	}
	public void setmAge(String mAge) {
		this.mAge = mAge;
	}

	@Override
	public String toString(){
		return mFirstName+" "+mLastName+";"+mAge;

	}

}

What this class will do is keep user information for first,last name and age that will be added to both the database and the Adapter of the ListView, thus the get and set methods. The last method is very important, public String toString() is what the ListView’s Adapter will use to display the information in the ListView.

Create DataSource.java

Create another class DataSource.java and paste the below:

public class DataSource {

	private SQLiteDatabase mSQLiteDatabase;
	private MySQLiteHelper mSQLiteHelper;

	private String[] mAllColumns = {MySQLiteHelper.COLUMN_ID,
			MySQLiteHelper.COLUMN_FIRST_NAME,
			MySQLiteHelper.COLUMN_LAST_NAME,
			MySQLiteHelper.COLUMN_AGE};

	public DataSource (Context context){
		mSQLiteHelper = new MySQLiteHelper(context);
	}

	public void open() throws SQLiteException {
		mSQLiteDatabase = mSQLiteHelper.getWritableDatabase();
	}

	public void close() {
		mSQLiteHelper.close();
	}

	public void addUser(String firstname, String lastname, String age){
		ContentValues values = new ContentValues();
		values.put(MySQLiteHelper.COLUMN_FIRST_NAME, firstname);
		values.put(MySQLiteHelper.COLUMN_LAST_NAME, lastname);
		values.put(MySQLiteHelper.COLUMN_AGE, age);
		mSQLiteDatabase.insert(MySQLiteHelper.TABLE_USERS, null, values);
	}

	public void deleteUser(Users user){
		long id = user.getmId();
		mSQLiteDatabase.delete(MySQLiteHelper.TABLE_USERS,
				MySQLiteHelper.COLUMN_ID+ " = " + id, null);
	}

This class creates a SQLiteDatabase object and MySQLiteHelper object to manage the database. It will open the database (or create it if it is the first time) when we call the method open().

It also adds and removes users. It will add user by passing user information, which will come from our AlertDialog (explained later) or deletes the user by passing a Users object (from the class created above with getters and setters).

	public List getAllUsers(){

		List  users = new ArrayList();

		Cursor cursor = mSQLiteDatabase.query(MySQLiteHelper.TABLE_USERS,
				mAllColumns, null, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()){
			Users user = cursorToUser(cursor);
			users.add(user);
			cursor.moveToNext();
		}
		cursor.close();
		return users;
	}

	private Users cursorToUser(Cursor cursor) {
		Users user = new Users();
		user.setmId(cursor.getLong(0));
		user.setmFirstName(cursor.getString(1));
		user.setmLastName(cursor.getString(2));
		user.setmAge(cursor.getString(3));
		return user;
	}

}

Lastly we have a method to get a list of Users by using a cursor. When I query a database, the results are put into a Cursor. I use this cursor to extract the information (In this case the information is Users objects) and I use cursorToUser to transform the information to Users so that my ListView’s Adapter can add it to display.

Create TestDatabaseActivity.java

Finally, we add the last class, TestDatabaseActivity and extend ListActivity.

public class TestDatabaseActivity extends ListActivity {

	private DataSource mDataSource;
	private Button mAddButton;
	private Button mDeleteButton;
	private Context mContext;
	private ArrayAdapter mAdapter;

	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.main);
		mContext = this;
		mAddButton = (Button) findViewById(R.id.add);
		mDeleteButton = (Button) findViewById(R.id.delete);
		mDeleteButton.setClickable(true);
		mDeleteButton.setOnClickListener(new View.OnClickListener() {

			@Override
			public void onClick(View v) {
				deleteFirst();
			}
			private void deleteFirst() {
				Users user = null;
				if (getListAdapter().getCount() > 0){
					user = (Users) getListAdapter().getItem(0);
					mDataSource.deleteUser(user);
					mAdapter.remove(user);
					mAdapter.notifyDataSetChanged();
				}
			}
		});
		mAddButton.setClickable(true);
		mAddButton.setOnClickListener(new View.OnClickListener() {
			@Override
			public void onClick(View v) {
				inputUserInformation();
			}
		});

Simple enough I create button listeners to my add and delete buttons. For mAddButton I create a method inputUserInformation(). For mDeleteButton I create a method deleteFirst(). I am only deleting first element with this button. The deleteFirst() method simply look for the first Users element in my ArrayAdapter and remove it from both the database (by using DataSource.deleteUser(Users)) and from the ArrayAdapter list. We must call the notifyDataSetChanged() method so our ListView is updated.

		mDataSource = new DataSource(this);
		mDataSource.open();

		mAdapter = new ArrayAdapter(
				this,android.R.layout.simple_list_item_1,
				mDataSource.getAllUsers());
				setListAdapter(mAdapter);
	}

	@Override
	protected void onResume() {
		mDataSource.open();
		super.onResume();
	}

	@Override
	protected void onPause() {
		mDataSource.close();
		super.onPause();
	}

Here I am just creating a DataSource object and passing it the activity context and opening the database. Also, I am binding the ArrayAdapter to the ListView and making sure that I close the database when onPause() is called and opening it again when onResume() is called. Now finally, the method to add to the database:

	private void inputUserInformation() {
		LayoutInflater factory = LayoutInflater.from(this);
		final View textEntryView = factory.inflate(R.layout.newuser, null);

		AlertDialog.Builder alert = new AlertDialog.Builder(this);

		alert.setTitle("New User Information");
		alert.setMessage("Enter your information below");
		alert.setView(textEntryView);

		final EditText input1 = (EditText) textEntryView.findViewById(R.id.first_name);
		final EditText input2 = (EditText) textEntryView.findViewById(R.id.last_name);
		final EditText input3 = (EditText) textEntryView.findViewById(R.id.age_age);

		alert.setPositiveButton("New Account", new DialogInterface.OnClickListener() {
			public void onClick(DialogInterface dialog, int whichButton) {

				String fName = input1.getText().toString();
				String lName = input2.getText().toString();
				String age = input3.getText().toString();

				if ((fName.length() <=0) || (lName.length()<=0) || (age.length()<=0)){					           
                                    Toast toast = Toast.makeText(mContext,
							"Please fill in all data and try again", Toast.LENGTH_SHORT);
					toast.show();
				}
				else{
					Users user = new Users();
					user.setmAge(age);
					user.setmFirstName(fName);
					user.setmLastName(lName);
					mDataSource.addUser(fName, lName, age);
					mAdapter.add(user);
					mAdapter.notifyDataSetChanged();
				}
			}
		});
		alert.setNegativeButton("Cancel", new DialogInterface.OnClickListener() {
			public void onClick(DialogInterface dialog, int whichButton) {
			}
		});
		alert.show();
	}

Here I use the newuser.xml file to create an AlertDialog where I can input user information just like I showed at the beginning of the tutorial. On positive button click I check that the data is not empty and if it is not, then I create a Users object, populate it with the first, last name and age and then pass it to DataSource.add(Users) and ArrayAdapter.add(Users), then I call notifyDataSetChanged() to update the ListView.

NOTE THAT ArrayAdapter IN TURN CALLS Users’s toString() method to display the data in the format I gave it:

@Override
	public String toString(){
		return mFirstName+" "+mLastName+";"+mAge;

	}

And that is all. Happy coding!

Comments are closed.