Android SQLite Tutorial With Example In Android Studio Step By Step

Hello, developers. Welcome to Android SQLite Tutorial with Example.

Android SQLite tutorial for beginners is available with source code to download.

This Android SQLite tutorial will cover the simple operation of SQLite databases like insert and display data.

If you want to do all the crud operations (insert, update, delete, display) with multiple tables, then visit SQLite with multiple tables android

If you want to do all the crud operations with one table, then visit Android Crud Operations In SQLite .

When you want to store the data in an effective manner and are useful to show to the user later, you should use SQLite for quick insertion and fetch of the data.

What is SQLite

SQLite is an in build database for every android device. In build means that you do not need to have any hosted server to store the database like MySQL. SQLite database is stored in android device(mobile and tablet) itself.

Because, it occupies very less memory space, SQLite works faster than other databases.

SQLite follows all the SQL standards and syntaxes. You need to write queries as per the SQL rules to insert, fetch, update or delete the data in SQLite.

Sometimes you want to give user an offline features, hence you need to store information in the SQLite Database.

Generally, data is stored in the text format in sqlite especially when the data is about user information.

You need to create tables and columns as per the general structure of any database.

After that, you can insert the data in terms of the rows.

SQLiteOpenHelper

SQLiteOpenHelper class is responsible for the management of database creation and version control management.

This class takes care of opening the database if it exists, creating it if it does not, and upgrading it as necessary. Transactions are used to make sure the database is always in a sensible state.

Following are the methods of this class.

1. close()

This method is used to close any particular sqlite database which is open.

You can use this method as below.

2. getDatabaseName()

To get the name of the currently opened database, this method is useful.

It will return the name in string format.

3. getReadableDatabase()

Create and/or open a database. This method will return an object of SQLiteDatabase class.

When the storage disk has no more space or when the database needs to be opened for Read-Only purpose, this method is used.

It will throw an SQLiteException if the database can not be opened by the system.

4. getWritableDatabase()

Create and/or open a database that will be used for reading and writing.

Once the database is opened successfully, it is cached by the system so that you can call this method anytime when you need o write new data into the database (Make sure to call close() when you no longer need the database.)

When user have not granted proper permissions or storage is full, this method may throw SQLiteException.

5. onConfigure(SQLiteDatabase db)

This method will be called when the database connection is being configured, to enable features such as write-ahead logging or foreign key support.

This method should modify the database, except to make some changes regarding the database connection configuration.

6. onCreate()

When the database is created for the very first time, this method is called.

Creation of tables and initial population of those tables are done at this time only.

7. onDowngrade()

To downgrade the database version, this need to be called. When calling this method, the requested version of the database must be older than the current version.

8. onOpen()

when database is opened, this method will be called.

This method is always called after the database connection is configured and after the database schema has been created, upgraded or downgraded as necessary.

9. onUpgrade()

When you want to upgrade the database structure, you should use this method.

Use this method when you need to drop any existing table, to update any table, to add a new table or anything else which is responsible to upgrade the schema of the database.

10. setIdleConnectionTimeout()

It defines the number of milliseconds up to which the database is allowed to be in the idle state.

Once the the database remains idle than specified time, it is closed and removed from the pool.

11. setLookasideConfig()

Generally, this method needs to be called from the constructor of the subclass, before opening the database because lookaside memory configuration can only be changed when no connection is using it.

SQLite default settings will be used, if this method isn’t called. Use below line to disable lookaside

12. setOpenParams()

Sets configuration parameters that are used for opening SQLiteDatabase class.

It will throw an IllegalStateException if the database is already open.

SQLiteDatabase

This class is used to manage various task relating to database using it’s many in built methods.

SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.

If you are going to create more than one database in the single application, then make sure that names of the databases are unique.

Database names are not required to be unique if they are not created withing single application.

Below are the methods of this class.

1. beginTransaction()

It begins a transaction in EXCLUSIVE mode.

2. beginTransactionNonExclusive()

It Begins a transaction in IMMEDIATE mode.

3. compileStatement()

Compiles an SQL statement into a reusable pre-compiled statement object.

Use this method as below

Pass sql statement as a parameter in string the format.

4. create()

When you want to create a new database, use this method.

It will also dedicate memory to the database.

5. delete()

This method is used to delete specific row from the database.

Use this method as below

Here, first parameter indicates the table from which row is needed to delete.

6. deleteDatabase()

This methods deletes a database with it’s journal file and other various files that are created by the database engine.

7. enableWriteAheadLogging()

In big enterprise applications, there are many threads running parallely, so if all these threads want to execute sql queries on the same database simultaneously, this method enables this feature.

8. disableWriteAheadLogging()

Feature of executing multiple sql queries (Create, retrieve , update, delete) on the same database simultaneously can be disabled with the use of this method.

9. getMaximumSize()

It returns a maximum number of memory size which can be allotted to a single database.

10. getPath()

It gives a path to the database file.

11. getVersion()

Current version of the database will be returned by this method.

12. getPageSize()

Returns the current database page size, in bytes.

13. isOpen()

This method returns a boolean value. If the current database is open then it will return true otherwise false.

14. isReadOnly()

If the database is opened as read only mode, then this method will return true.

15. needUpgrade(int newVersion)

Boolean vale will be returned by this method. It returns true if the new version code is greater than the current version code of the database.

Download Source Code For Android SQLite Tutorial

[sociallocker] Download Source Code [/sociallocker]

Follow all the below steps to create a simple sqlite database example.

Step 1: Create a new project in Android Studio.

Create a new standalone and fresh new android studio project.Make sure you have select empty activity while creating new project.

Step 2: Creating DatabaseHelper class

Create a new class named “DatabaseHelper” and all below source code

Step 3: Description of DatabaseHelper

Database name, Database version, table name and column names of tables are written as below.

The value of the DATABASE_VERSION variable describes the version of the database. It’s value is 1 at the first time when you create an app.

Understanding the most important part of the SQLite Database

See the below source code

Above is the query to create a new table named “students”.

We need to write this type of query for each table we want to create.

In onCreate() method, we have written create statement for the table.

If you want to make more than one table, then put above type of coding line for each table.

  • In onUpgrade() method, sqlite will deop already exist tables and then it will recreate all the tables.
  • onCreate() method is called within onUpgrade() method to create tables.

In following scenarios, you need to upgrade DATABASE_VERSION number in increment order.

  1. When you add, update or delete any column of any table in the whole database.
  2. If you update any column name of any table.
  3. After you add, update or delete any table.
  4. Update the table name and increase DATABASE_VERSION.
  • onUpgrade() method is called every time When you update the DATABASE_VERSION in the increment order.
  • addStudentDetail() method will add the name to the database.
  • getAllStudentsList() method will fetch all the values of names from database.

Step 5: Updating activity_main.xml

Put following code in activity_main.xml

One edittext, one textview and two buttons are defined in above code.

User will use edittext to enter the name of the student.

Step 6: Updating MainActivity.java

Add following  in MainActivity.java

When the user clicks on store button, the entered text is stored in the database as below.

addStudentDeatils method is called here, which is present in the DatabaseHelper class.

This method defines the sql query to add the name in the sqlite database under the column “name”.

When the user clicks on the getall button, all the names are fetched from the database.

getAllStudentsList method gives us all the names of the students in the arraylist.

Above method is also present in the DatabaseHelper class.

Output

First, check the output of SQLite In Android Studio example, then we will develop it.

So that is all for SQLite in Android example.

Feel free to comment your queries and reviews in the comment section. Thank you 🙂

Hardik Parsania

I am mobile app developer. I am always passionate about solving complex and real world problems.I have developed many apps with great interest and superior logical skills.

You may also like...

5 Responses

  1. Moazzem says:

    Where is the student_database.bd file?

  2. cuiqx says:

    great job!

    And i have one question .

    I would like for the done(Enter) button on the keyboard to trigger the STORE button.

    can you show me how to do this ?

    many thanks

  3. luke says:

    What if someone wants to retrieve data from a number of columns in the same table
    ?

Leave a Reply

Your email address will not be published. Required fields are marked *