Android SQLite Database Tutorial

In this tutorial, you will learn how to create a SQLite Database that allows you to store data in your internal device memory. Android devices come with a built- in SQLite Database that has methods to create, update, delete, execute SQL commands, and perform other common database management tasks. We will create a simple application that allows users to create, update, delete and show a list of data. So lets begin…

Create a new project in Eclipse File > New > Android Application Project. Fill in the details and name your project DatabaseTutorial.

Application Name : DatabaseTutorial

Project Name : DatabaseTutorial

Package Name : com.androidbegin.databasetutorial

Open your MainActivity.java and paste the following code.

MainActivity.java

In this activity, we will show the list of items in a listview from the SQLite database by using an AsyncTask to call the ListAllNotes function from the Database Connector class. On listview item click will pass the row id into ViewNote class. Then we have created an options menu to open AddEditNotes class that allow users to add items into the listview.

Next, create a XML graphical layout for your MainActivity. Go to res > layout > Right Click on layout > New > Android XML File

Name your new XML file list_note.xml and paste the following code.

list_note.xml

Output:

SQLite Database Tutorial ViewNote XML

Next, create the database functions in a new class called DatabaseConnector.java. Go to File > New > Class and name it DatabaseConnector.java. Select your package named com.androidbegin.databasetutorial and click Finish.

Open your DatabaseConnector.java and paste the following codes.

DatabaseConnector.java

In this class, we have some basic database functions such as create, update, delete and list data. All functions in DatabaseConnector class can be called from other classes.

NOTE : To reset or wipe the database, just change the version number.

Next, create a SQlite Database Helper to help manage database creation and version management. Go to File > New > Class and name it DatabaseHelper.java. Select your package named com.androidbegin.databasetutorial and click Finish.

Open your DatabaseHelper.java and paste the following codes.

DatabaseHelper.java

We have created a database helper using a simple SQLite Query. We have set integer “id” as a PRIMARY KEY and is used to uniquely identify each record in a database table. The database will automatically be wiped or dropped if there are changes made to the version number as mentioned above.

Next, create an activity for the ListView item click that shows a single database data. Go to File > New > Class and name it ViewNote.java. Select your package named com.androidbegin.databasetutorial and click Finish.

Open your ViewNote.java and paste the following code.

ViewNote.java

In this activity, we have retrieved the Row ID from the listview item click in the MainActivity class and show it on ViewNote class. Then we used the Row ID as a primary key to update, edit or delete the data by using Database Connector functions.

Next, create a view to show results on ViewNote. Go to res > layout > Right Click on layout > New > Android XML File

Name your new XML file view_note.xml and paste the following code.

view_note.xml

Output:

SQLite Database Tutorial ViewNote XML

Next, create a new activity for create and update data in the database. Go to File > New > Class and name it AddEditNotes.java. Select your package named com.androidbegin.databasetutorial and click Finish.

Open your AddEditNotes.java and paste the following code.

AddEditNotes.java

In this activity, we have retrieved the Row ID and EditTexts from ViewNote class. Then the users are allowed to make modifications to the data and use the InsertNote() and UpdateNote() to insert or update the data in the database.

Next, create a XML graphical layout for AddEditNote class. Go to res > layout > Right Click on layout > New > Android XML File

Name your new XML file add_note.xml and paste the following code.

add_note.xml

Output:

SQLite Database Tutorial Main XML

Next, change the application name and texts. Open your strings.xml in your res > values folder and paste the following code.

strings.xml

In your AndroidManifest.xml, we need to declare some activities. Open your AndroidManifest.xml and paste the following code.

AndroidManifest.xml

Output:

SQLite Database Tutorial ScreenShots

Source Code 

DatabaseTutorial (337.7 KiB, 798 downloads)
  • Ariel

    Hi, i got a virtual table that uses full txt search. Is there a way to find a row with a column that has for example the word ‘orange’, but just writing ‘range’? I know i can use MATCH ‘or*’ but i want to be able to write the end of the word, like MATCH ‘*range’. Thnx

  • Real Muslim

    GOOD … Thanks .. its working like a charm!!

  • Farhaan

    First off, I wanted to really thank you for the awesome tutorials you create! I am creating an app that requires multiple tables in a database. I have seen many tutorials online which do 1 table scenario.

    If I need to do that, should I create multiple DatabaseConnector classes and multiple DatabaseHelper class?

    Can you create a project which uses multiple tables in the near future. Thanks.

  • muhammad saleem ul haq

    Hi, how can i do when launch app it display list when I pinch the list item it strike through(that mean) the task is completed and another I want in edit screen another option is added to view completed task when i click it all show the completed task, please I wait your prompt response.

  • Moises Dominguez

    hey, I think this is the right place, how can I export/import the whole sqlite database with a different format else than xml? can you help me?

  • Andrey

    Hey.

    How can we check if the “Name” record is already exists in database and tell it to user by, for example, toast ?