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.

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 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.javaand paste the following codes.

DatabaseConnector.java

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

packagecom.androidbegin.databasetutorial;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.SQLiteDatabase;

publicclassDatabaseConnector{

// Declare Variables

privatestaticfinalStringDB_NAME="MyNotes";

privatestaticfinalStringTABLE_NAME="tablenotes";

privatestaticfinalStringTITLE="title";

privatestaticfinalStringID="_id";

privatestaticfinalStringNOTE="note";

privatestaticfinalintDATABASE_VERSION=1;

privateSQLiteDatabase database;

privateDatabaseHelper dbOpenHelper;

publicDatabaseConnector(Context context){

dbOpenHelper=newDatabaseHelper(context,DB_NAME,null,

DATABASE_VERSION);

}

// Open Database function

publicvoidopen()throwsSQLException{

// Allow database to be in writable mode

database=dbOpenHelper.getWritableDatabase();

}

// Close Database function

publicvoidclose(){

if(database!=null)

database.close();

}

// Create Database function

publicvoidInsertNote(Stringtitle,Stringnote){

ContentValues newCon=newContentValues();

newCon.put(TITLE,title);

newCon.put(NOTE,note);

open();

database.insert(TABLE_NAME,null,newCon);

close();

}

// Update Database function

publicvoidUpdateNote(longid,Stringtitle,Stringnote){

ContentValues editCon=newContentValues();

editCon.put(TITLE,title);

editCon.put(NOTE,note);

open();

database.update(TABLE_NAME,editCon,ID+"="+id,null);

close();

}

// Delete Database function

publicvoidDeleteNote(longid){

open();

database.delete(TABLE_NAME,ID+"="+id,null);

close();

}

// List all data function

publicCursor ListAllNotes(){

returndatabase.query(TABLE_NAME,newString[]{ID,TITLE},null,

null,null,null,TITLE);

}

// Capture single data by ID

publicCursor GetOneNote(longid){

returndatabase.query(TABLE_NAME,null,ID+"="+id,null,null,

null,null);

}

}

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.

1

privatestaticfinalintDATABASE_VERSION=1;

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.

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.

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

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.

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.

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

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 ?

Brian

I did attempt. By the way, only white screen. What should I do?

Stephen Smith

Nice Tutorial.Really useful to me.Thank you.

Shuka

Great tutorial, thank you! Could someone please tell me how to implement the above with 3 tables? I actually tried but the app crashes.. Any help would be much appreciated!