askIT

Android Database tutorial (Android SQLite)

In this guide you will get an insight into how to create a simple database with Android. The article makes an application that can write notes.

e34559d41311fa4ac86dfe915ac7fa2a Download source code for database tutorial

01 Create project

Start up Android Studio and select New Project. Click next until the project is created.

01_nytt_prosjekt.jpg

01_nytt_prosjekt.jpg

03_nytt_prosjektb.jpg

04_add_an_activity_to_mobile.jpg

05_customize_the_activity.jpg

02 Run app in emulator

Always run your project in the emulator after creating it. This will ensure that the project was created without errors. You can also clean your project by clicking «Build -> Clean project», then «Run app».

06_clean_project.jpg

07_run_app.jpg

08_lunch_emulator.jpg

You should see an application that is empty with only the text «Hello World».

09_ny_tom_app.jpg

03 Database

Now we thought how we want the database table. We want to save notes, and think therefore that the notes should have the following:

The table «notes» will look like this:

Field Type Key
note_id int Primary key
note_title varchar
note_text text

03 DBAdapter java class

You need to create a class named «DBAdapter». This class wll run queries against the database. To create DBAdapter open «app -> java». Right click on «com.nettport.notepad». You will get a menu vhere you can choose «New -> Java Class».

10_new_java_class.jpg

Give the new class the name «DBAdapter».

11_create_new_class.jpg

04 Create table

We will create the table «notes» in our «DBAdapter» class.
01: Here we store variables on the database.
02: Here we make the creation of the table into a variable.

DBadapter.class

package com.nettport.notepad;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

public class DBAdapter {
        /*- 01 Database and table names ------------------------------ */
        private static final String databaseName        = "MyDB";
        private static final String databaseTableNotes = "notes";
        private static final int databaseVersion        = 1;


        /*- 02 Create table statements ------------------------------ */
        // Create table notes
        private static final String databaseCreateTableNotes =
        "CREATE TABLE IF NOT EXISTS " + databaseTableNotes + " " +
        "( note_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
        " note_title VARCHAR," +
        " note_text TEXT);";


}

Vi run the methods onCreate and onUpgrade. These must ensure that we create the tables, and they upgrade if we change the version number to the table that is stored in the variable databaseVersion.

        /*- 03 Database variables ----------------------------------- */
        /*- Dont edit! ---------------------------------------------- */
        private final Context context;
        private DatabaseHelper DBHelper;
        private SQLiteDatabase db;

        /*- 04 Class ------------------------------------------------ */
        /*- Dont edit! ---------------------------------------------- */
        public DBAdapter(Context ctx) {
                this.context = ctx;
                DBHelper = new DatabaseHelper(context);
        } // end public DBAdapter


        /*- 05 Start DBAdapter -------------------------------------- */
        private static class DatabaseHelper extends SQLiteOpenHelper {
                DatabaseHelper(Context context) {
                        super(context, databaseName, null, databaseVersion);
                }

                @Override
                public void onCreate(SQLiteDatabase db)
                {
                        try {
                                // ! All tables that are going to be created need to be listed here
                                db.execSQL(databaseCreateTableNotes);
                        } catch (SQLException e) {
                                e.printStackTrace();
                        }
                } // end public void onCreate

                @Override
                public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {


                        // ! All tables that are going to be dropped need to be listed here
                        db.execSQL("DROP TABLE IF EXISTS " + databaseTableNotes);
                        onCreate(db);

                        String TAG = "Tag";
                        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                                        + newVersion + ", which will destroy all old data");

                } // end public void onUpgrade
        } // end private static class DatabaseHelper extends SQLiteOpenHelper

We will code the method open to open a database connection and close to end the database connection.


        /*- 06 Open and close database ---------------------------- */
        /*- Dont edit! -------------------------------------------- */
        //---opens the database---
        public DBAdapter open() throws SQLException {
                db = DBHelper.getWritableDatabase();
                return this;
        }

        //---closes the database---
        public void close() {
                DBHelper.close();
        }

You can have a look at the complete source code for DBAdapter to ensure that you have everything programmed correctly.

05 Queries to the database

We want to write data to the table, read the data, update data and delete data. We do this with queries. All queries should be written into the class DBAdapter.


        /*- 07 Queries for notes ----------------------------------- */
        // Insert record
        public long insertRecordToNotes(String inpTitle, String inpText)
        {
                ContentValues initialValues = new ContentValues();
                initialValues.put("note_title", inpTitle);
                initialValues.put("note_text", inpText);

                return db.insert(databaseTableNotes, null, initialValues);
        }
        // Retrieve
        public Cursor getAllRecordsFromNotes()
        {
                return db.query(databaseTableNotes, new String[]{
                                "note_id",
                                "note_title",
                                "note_text"
                }, null, null, null, null, null, null);
        }
        public Cursor getAllRecordsFromNotesListView()
        {
                return db.query(databaseTableNotes, new String[]{
                                "note_id AS _id",
                                "note_title",
                                "note_text"
                }, null, null, null, null, null, null);
        }
        // Retrieves a particular record
        public Cursor getRecordFromNotes(long rowId) throws SQLException {
                Cursor mCursor = db.query(databaseTableNotes, new String[] {
                                                "note_id",
                                                "note_title",
                                                "note_text"
                                },
                                "note_id" + "=" + rowId, null, null, null, null, null);
                if (mCursor != null) {
                        mCursor.moveToFirst();
                }
                return mCursor;
        }
        public int truncateNotes()
        {
                return db.delete(databaseTableNotes, "1", null);
        }

Download complete DBAdapter.

06 MainActivity

You can now open the class «MainActivity». The MainActivity class shall have the rest of the program code. The database procedure for MainActivity is:

We will code our code in the method onCreate. To display data from the database we will create a method displayRecordFromNotes.

12_mainactivity.jpg

onCreate


                /*- Database ---------------------------------------------------------------------------- */
                /* Open DBAdapter */
                DBAdapter db = new DBAdapter(this);

                /* Open db */
                db.open();

                // Insert row
                long id = 0;
                id = db.insertRecordToNotes("Shopping list", "Cola Zero, Crispbread");
                Toast.makeText(this, "Insered ID: " + id, Toast.LENGTH_LONG).show();

                // Display row
                Cursor cNote = db.getAllRecordsFromNotes();
                cNote.moveToLast();
                displayRecordFromNotes(cNote);

                // Update Row
                int feedback = db.updateNote(id, "Shopping list", "Cola Light, Crispbread");
                Toast.makeText(this, "Feedback: " + feedback, Toast.LENGTH_LONG).show();

                // Display row
                cNote = db.getAllRecordsFromNotes();
                cNote.moveToLast();
                displayRecordFromNotes(cNote);

                /* Close db */
                db.close();

New method displayRecordFromNotes

        /*- Notes ------------------------------------------------- */
        // Print notes table content
        private void displayRecordFromNotes(Cursor inpCursor) {
                Toast.makeText(this,
                                "Id: " + inpCursor.getString(0) + "\n" +
                                "Title: " + inpCursor.getString(1) + "\n" +
                                "Text: " + inpCursor.getString(2),
                                Toast.LENGTH_LONG).show();
        } // end private void displayRecordFromNotes

When you run the application it will first create a row called «Shopping List«. This will include the text «Coke Zero, Crackers».

Then the list will be printed with Toast method.

We will then update the row from the text «Cola Zero» to «Cola Light».

Then we will print the new list with Toast method.

13_run.jpg

14_inserted.jpg
Figure: Data inserted.

15print.jpg
Figure: Data printed.

17_ny_print.jpg
Figure: New data printed.

Add comment

avatar_blank_60.png

Comment:

Name:

Email:

Comments

No comments yet. You can be the first one to comment. Just write your comment in the form and click on the submit button.