Home » Android » core » database » Android Database Example

About Chryssa Aliferi

Chryssa Aliferi
Chryssa is a Computer Science graduate from Athens University of Economic and Business. During her studies, Chryssa carried out a great variety of projects ranging from networking to software engineering. She is very keen on front end development especially on mobile technologies and web applications. She has worked as a junior Software Engineer in the telecommunications area and currently works as an Android Developer.

Android Database Example

In Android programming, storing data into a database is not a common practice, as we have other and most suitable methods to store our data, such as the SharedPreferences way.

However, we do have a very strong tool, that can help our Android Application communicate with a database, and this is SQLite. SQLite is an Open Source Database for structured data in relational databases. It is embedded in Android, to you don’t have to do anything special to set up or administer an SQLite server.

This example shows how to work with an SQLite database and how to make the basic actions. We will do this by following the DataModel and DataHandling structure. We will use a data access object (DAO) to manage the handling of the database connection and for accessing and modifying the data. By dealing with the database entries as Data Objects, it will be easier for us to present the sql queries results on our user interface.

Let’s start! For our example will use the following tools in a Windows 64-bit or an OS X platform:

  • JDK 1.7
  • Eclipse 4.2 Juno
  • Android SDK 4.4.2

Let’s take a closer look:

1. Create a New Android Application Project

Tip
You may skip project creation and jump directly to the beginning of the example below.

Open Eclipse IDE and go to File → New → Project → Android Application Project.

Figure 2. Create a new Android project

Figure 1. Create a new Android project

Specify the name of the application, the project and the package and then click Next.

Figure 2. Create a new Android project name

Figure 2. Create a new Android project name

In the next window, the “Create Activity” option should be checked. The new created activity will be the main activity of your project. Then press Next button.

Create Activity

Figure 3. Configure the project

In “Configure Launcher Icon” window you should choose the icon you want to have in your app. We will use the default icon of android, so click Next.

Configure Launcher Icon

Figure 4. Configure the launcher icon

Select the “Blank Activity” option and press Next.

Blank Activity

Figure 5. Create the activity and select its type

You have to specify a name for the new Activity and a name for the layout description of your app. The .xml file for the layout will automatically be created in the res/layout folder. It will also be created a fragment layout xml, that we are not going to use in this project and you can remove it if you want. Then press Finish.

Figure 6. Create a new blank activity

Figure 6. Create a new blank activity

Here you can see, how will the structure of the project become when finished:

Figure 7. The tree of the project

Figure 7. The tree of the project

2. Creating the layout of the main AndroidDatabaseExample

We are going to make a very simple layout xml for the AndroidDatabaseExample.class, that only consists of a LinearLayout that contains the one ListView.

Open res/layout/activity_main.xml, go to the respective xml tab and paste the following:

activity_main.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:background="#ffffff"
    android:orientation="vertical" >

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

</LinearLayout>

3. Creating the Database and Data Model of the Activity AndroidDatabaseExample

Open src/com.javacodegeeks.androidcursorexample/Book.java file and paste the code below.

Book.java

package com.javacodegeeks.androiddatabaseexample;

public class Book {

	private int id;
	private String title;
	private String author;
	
	public Book(){}
	
	public Book(String title, String author) {
		super();
		this.title = title;
		this.author = author;
	}
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	
	@Override
	public String toString() {
		return "Book [id=" + id + ", title=" + title + ", author=" + author
				+ "]";
	}
}

The Book class is our model and contains the data we will save in the database and show in the user interface.

Now, open src/com.javacodegeeks.androidcursorexample/JCGSQLiteHelper.java file and paste the code below.

JCGSQLiteHelper.java

package com.javacodegeeks.androiddatabaseexample;

import java.util.LinkedList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class JCGSQLiteHelper extends SQLiteOpenHelper {

	// database version
	private static final int database_VERSION = 1;
	// database name
	private static final String database_NAME = "BookDB";
	private static final String table_BOOKS = "books";
	private static final String book_ID = "id";
	private static final String book_TITLE = "title";
	private static final String book_AUTHOR = "author";

	private static final String[] COLUMNS = { book_ID, book_TITLE, book_AUTHOR };

	public JCGSQLiteHelper(Context context) {
		super(context, database_NAME, null, database_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// SQL statement to create book table
		String CREATE_BOOK_TABLE = "CREATE TABLE books ( " + "id INTEGER PRIMARY KEY AUTOINCREMENT, " + "title TEXT, " + "author TEXT )";
		db.execSQL(CREATE_BOOK_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// drop books table if already exists
		db.execSQL("DROP TABLE IF EXISTS books");
		this.onCreate(db);
	}

	public void createBook(Book book) {
		// get reference of the BookDB database
		SQLiteDatabase db = this.getWritableDatabase();

		// make values to be inserted
		ContentValues values = new ContentValues();
		values.put(book_TITLE, book.getTitle());
		values.put(book_AUTHOR, book.getAuthor());

		// insert book
		db.insert(table_BOOKS, null, values);

		// close database transaction
		db.close();
	}

	public Book readBook(int id) {
		// get reference of the BookDB database
		SQLiteDatabase db = this.getReadableDatabase();

		// get book query
		Cursor cursor = db.query(table_BOOKS, // a. table
				COLUMNS, " id = ?", new String[] { String.valueOf(id) }, null, null, null, null);

		// if results !=null, parse the first one
		if (cursor != null)
			cursor.moveToFirst();

		Book book = new Book();
		book.setId(Integer.parseInt(cursor.getString(0)));
		book.setTitle(cursor.getString(1));
		book.setAuthor(cursor.getString(2));

		return book;
	}

	public List getAllBooks() {
		List books = new LinkedList();

		// select book query
		String query = "SELECT  * FROM " + table_BOOKS;

		// get reference of the BookDB database
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		// parse all results
		Book book = null;
		if (cursor.moveToFirst()) {
			do {
				book = new Book();
				book.setId(Integer.parseInt(cursor.getString(0)));
				book.setTitle(cursor.getString(1));
				book.setAuthor(cursor.getString(2));

				// Add book to books
				books.add(book);
			} while (cursor.moveToNext());
		}
		return books;
	}

	public int updateBook(Book book) {

		// get reference of the BookDB database
		SQLiteDatabase db = this.getWritableDatabase();

		// make values to be inserted
		ContentValues values = new ContentValues();
		values.put("title", book.getTitle()); // get title
		values.put("author", book.getAuthor()); // get author

		// update
		int i = db.update(table_BOOKS, values, book_ID + " = ?", new String[] { String.valueOf(book.getId()) });

		db.close();
		return i;
	}

	// Deleting single book
	public void deleteBook(Book book) {

		// get reference of the BookDB database
		SQLiteDatabase db = this.getWritableDatabase();

		// delete book
		db.delete(table_BOOKS, book_ID + " = ?", new String[] { String.valueOf(book.getId()) });
		db.close();
	}
}

This class is responsible for all the actions that will take place on the database. Let’s see in detail the code above.

The JCGSQLiteHelper.class contains the basic methods:
onCreate(SQLiteDatabase db), called when the database is created for the first time.
onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion), called when the database needs to be upgraded.

For our example, we have extended our data management class, in order to include the methods for inserting, deleting, and updating a book in our database.

	public void createBook(Book book) {	
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(book_TITLE, book.getTitle());
		values.put(book_AUTHOR, book.getAuthor());
		
		db.insert(table_BOOKS, null, values);
		db.close();
	}

In the code snipped above, we get reference of the BookDB database and create an insert query in order to insert the values of the book_TITLE and book_AUTHOR rows. We execute the insert and then close the transaction.

	public Book readBook(int id) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(table_BOOKS, // a. table
				COLUMNS, " id = ?", new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		Book book = new Book();
		book.setId(Integer.parseInt(cursor.getString(0)));
		book.setTitle(cursor.getString(1));
		book.setAuthor(cursor.getString(2));

		return book;
	}

With the code above, we get reference of the BookDB database and create an search query in order to find a specific book entry. We execute the search and then we return the first of the results as a book Object.

In the same way we get all the books of the database:

	public List getAllBooks() {
		List books = new LinkedList();

		// select book query
		String query = "SELECT  * FROM " + table_BOOKS;

		// get reference of the BookDB database
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		// parse all results
		Book book = null;
		if (cursor.moveToFirst()) {
			do {
				book = new Book();
				book.setId(Integer.parseInt(cursor.getString(0)));
				book.setTitle(cursor.getString(1));
				book.setAuthor(cursor.getString(2));

				// Add book to books
				books.add(book);
			} while (cursor.moveToNext());
		}
		return books;
	}

We have also implemented an update book method:

	public int updateBook(Book book) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("title", book.getTitle());
		values.put("author", book.getAuthor());r
		int i = db.update(table_BOOKS, values, book_ID + " = ?", new String[] { String.valueOf(book.getId()) });

		db.close();
		return i;
	}

In the code snipped above, we get reference of the BookDB database and create an update query in order to change the values of an already added book in the database. We execute the update and then close the transaction.

	public void deleteBook(Book book) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(table_BOOKS, book_ID + " = ?", new String[] { String.valueOf(book.getId()) });
		db.close();
	}

With the above code we delete a book entry from our database, after of course, we take the reference of the BookDB database and create an delete query.

4. Creating the source code of the main AndroidDatabaseExampleActivity

Open src/com.javacodegeeks.androidcursorexample/AndroidDatabaseExample.java file and paste the code below.

AndroidDatabaseExample.java

package com.javacodegeeks.androiddatabaseexample;

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

import android.app.ListActivity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;

public class AndroidDatabaseExample extends ListActivity implements OnItemClickListener {
	JCGSQLiteHelper db = new JCGSQLiteHelper(this);
	List list;
	ArrayAdapter myAdapter;

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);

		setContentView(R.layout.activity_main);

		// drop this database if already exists
		db.onUpgrade(db.getWritableDatabase(), 1, 2);

		db.createBook(new Book("The Great Gatsby", "F. Scott Fitzgerald"));
		db.createBook(new Book("Anna Karenina", "Leo Tolstoy"));
		db.createBook(new Book("The Grapes of Wrath", "John Steinbeck"));
		db.createBook(new Book("Invisible Man", "Ralph Ellison"));
		db.createBook(new Book("Gone with the Wind", "Margaret Mitchell"));
		db.createBook(new Book("Pride and Prejudice", "Jane Austen"));
		db.createBook(new Book("Sense and Sensibility", "Jane Austen"));
		db.createBook(new Book("Mansfield Park", "Jane Austen"));
		db.createBook(new Book("The Color Purple", "Alice Walker"));
		db.createBook(new Book("The Temple of My Familiar", "Alice Walker"));
		db.createBook(new Book("The waves", "Virginia Woolf"));
		db.createBook(new Book("Mrs Dalloway", "Virginia Woolf"));
		db.createBook(new Book("War and Peace", "Leo Tolstoy"));

		// get all books
		list = db.getAllBooks();
		List listTitle = new ArrayList();

		for (int i = 0; i < list.size(); i++) {
			listTitle.add(i, list.get(i).getTitle());
		}

		myAdapter = new ArrayAdapter(this, R.layout.row_layout, R.id.listText, listTitle);
		getListView().setOnItemClickListener(this);
		setListAdapter(myAdapter);
	}

	@Override
	public void onItemClick(AdapterView arg0, View arg1, int arg2, long arg3) {
		// start BookActivity with extras the book id
		Intent intent = new Intent(this, BookActivity.class);
		intent.putExtra("book", list.get(arg2).getId());
		startActivityForResult(intent, 1);
	}

	@Override
	protected void onActivityResult(int requestCode, int resultCode, Intent data) {
		super.onActivityResult(requestCode, resultCode, data);

		// get all books again, because something changed
		list = db.getAllBooks();

		List listTitle = new ArrayList();

		for (int i = 0; i < list.size(); i++) {
			listTitle.add(i, list.get(i).getTitle());
		}

		myAdapter = new ArrayAdapter(this, R.layout.row_layout, R.id.listText, listTitle);
		getListView().setOnItemClickListener(this);
		setListAdapter(myAdapter);
	}
}

Open src/com.javacodegeeks.androidcursorexample/BookActivity.java file and paste the code below.

BookActivity.java

package com.javacodegeeks.androiddatabaseexample;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

public class BookActivity extends Activity {
	TextView bookTitle;
	TextView authorName;
	Book selectedBook;
	JCGSQLiteHelper db;

	@Override
	public void onCreate(Bundle savedInstanceState) {

		super.onCreate(savedInstanceState);

		setContentView(R.layout.activity_book);

		bookTitle = (TextView) findViewById(R.id.title);
		authorName = (TextView) findViewById(R.id.author);

		// get the intent that we have passed from AndroidDatabaseExample
		Intent intent = getIntent();
		int id = intent.getIntExtra("book", -1);

		// open the database of the application context
		db = new JCGSQLiteHelper(getApplicationContext());

		// read the book with "id" from the database
		selectedBook = db.readBook(id);

		initializeViews();
	}

	public void initializeViews() {
		bookTitle.setText(selectedBook.getTitle());
		authorName.setText(selectedBook.getAuthor());
	}

	public void update(View v) {
		Toast.makeText(getApplicationContext(), "This book is updated.", Toast.LENGTH_SHORT).show();
		selectedBook.setTitle(((EditText) findViewById(R.id.titleEdit)).getText().toString());
		selectedBook.setAuthor(((EditText) findViewById(R.id.authorEdit)).getText().toString());

		// update book with changes
		db.updateBook(selectedBook);
		finish();
	}

	public void delete(View v) {
		Toast.makeText(getApplicationContext(), "This book is deleted.", Toast.LENGTH_SHORT).show();

		// delete selected book
		db.deleteBook(selectedBook);
		finish();
	}
}

5. Creating the layout of the BookActivity

We are going to make a simple layout xml for the BookActivity.class, the Activity that can delete and update the book entries in the database.

Open res/layout/activity_book.xml, go to the respective xml tab and paste the following:

activity_book.xml

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

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginBottom="10dp"
        android:layout_marginLeft="5dp" >

        <TextView
            android:id="@+id/titleLabel"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Title: "
            android:textAppearance="?android:attr/textAppearanceLarge" />

        <TextView
            android:id="@+id/title"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Book Title"
            android:textAppearance="?android:attr/textAppearanceLarge" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginBottom="10dp"
        android:layout_marginLeft="5dp" >

        <TextView
            android:id="@+id/authorLabel"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Author: "
            android:textAppearance="?android:attr/textAppearanceLarge" />

        <TextView
            android:id="@+id/author"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Book Title"
            android:textAppearance="?android:attr/textAppearanceLarge" />
    </LinearLayout>

    <Button
        android:id="@+id/delete"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginBottom="30dp"
        android:onClick="delete"
        android:text="Delete Book" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginBottom="10dp"
        android:layout_marginLeft="5dp" >

        <TextView
            android:id="@+id/titleEditLabel"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Title: "
            android:textAppearance="?android:attr/textAppearanceLarge" />

        <EditText
            android:id="@+id/titleEdit"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginLeft="5dp"
            android:layout_weight="1"
            android:ems="10" >

            <requestFocus />
        </EditText>
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginBottom="10dp"
        android:layout_marginLeft="5dp" >

        <TextView
            android:id="@+id/authorEditLabel"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Author: "
            android:textAppearance="?android:attr/textAppearanceLarge" />

        <EditText
            android:id="@+id/authorEdit"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginLeft="5dp"
            android:layout_weight="1"
            android:ems="10" >
        </EditText>
    </LinearLayout>

    <Button
        android:id="@+id/update"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:onClick="update"
        android:text="Update Book" />

</LinearLayout>

6. Build, compile and run

When we build, compile and run our project, the main AndroidDatabaseExample should look like this:

Figure 8. Figure This is how the main Activity looks like

Figure 8. Figure This is how the main Activity looks like.

Figure 9. This is how the BookActivity looks like

Figure 9. This is how the BookActivity looks like.

Figure 10. This is our book list, after we have deleted the book in the BookActivity.

Figure 10. This is our book list, after we have deleted the book in the BookActivity.

Figure 11. In this figure we see how we can update a book in our database.

Figure 11. In this figure we see how we can update a book in our database.

Download the Eclipse Project

This was an example of Android AndroidDatabaseExample.

Download
You can download the full source code of this example here: AndroidDatabaseExample
(+1 rating, 1 votes)
Start the discussion Views Tweet it!

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Leave a Reply

avatar
  Subscribe  
Notify of