Android Studio SQLite Tutorial

SQLite is a lightweight relational database management system that is widely used in Android application development. Android Studio provides a convenient way to manage SQLite databases in your application. This tutorial will guide you through the process of creating a database, creating tables, inserting data, and querying data in Android Studio.

Syntax

SQLite is a SQL-based database management system. To perform database operations in Android Studio, you need to use the SQLiteOpenHelper class and SQLiteDatabase class.

SQLiteOpenHelper Class

The SQLiteOpenHelper class is used to manage database creation and version management. It provides methods to create, upgrade, and open a database. The following is the syntax to create a subclass of SQLiteOpenHelper:

public class MyDatabaseHelper extends SQLiteOpenHelper {

    public MyDatabaseHelper(Context context) {
        super(context, "myDatabase", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // Create tables and insert data here
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Upgrade the database here
    }
}

SQLiteDatabase Class

The SQLiteDatabase class is used to perform database operations. It provides methods to insert, update, delete, and query data from the database. The following is an example of how to open a database and query data:

MyDatabaseHelper dbHelper = new MyDatabaseHelper(this);
SQLiteDatabase db = dbHelper.getReadableDatabase();

String[] projection = {
    "_id",
    "title",
    "author"
};

Cursor cursor = db.query(
    "myTable",
    projection,
    null,
    null,
    null,
    null,
    null
);

while (cursor.moveToNext()) {
    long itemId = cursor.getLong(cursor.getColumnIndexOrThrow("_id"));
    String title = cursor.getString(cursor.getColumnIndexOrThrow("title"));
    String author = cursor.getString(cursor.getColumnIndexOrThrow("author"));
}
cursor.close();
db.close();

Example

Let's create a simple database that stores the details of students. The database will have one table called "students" with three columns: _id, name, and age.

Create Database

Create a new Android Studio project and add the following code in the MainActivity.java file to create a database:

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        MyDatabaseHelper dbHelper = new MyDatabaseHelper(this);
        SQLiteDatabase db = dbHelper.getWritableDatabase();
    }

    private static class MyDatabaseHelper extends SQLiteOpenHelper {

        private static final String DATABASE_NAME = "students.db";
        private static final int DATABASE_VERSION = 1;

        private static final String CREATE_TABLE_STUDENTS =
                "CREATE TABLE students (" +
                        "_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        "name TEXT NOT NULL, " +
                        "age INTEGER NOT NULL" +
                        ")";

        public MyDatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_TABLE_STUDENTS);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS students");
            onCreate(db);
        }
    }
}

Insert Data

Add the following code to insert data in the table:

ContentValues values = new ContentValues();
values.put("name", "John");
values.put("age", 25);

long newRowId = db.insert("students", null, values);

Query Data

Add the following code to query data from the table:

String[] projection = {
        "_id",
        "name",
        "age"
};

Cursor cursor = db.query(
        "students",
        projection,
        null,
        null,
        null,
        null,
        null
);

while (cursor.moveToNext()) {
    long itemId = cursor.getLong(cursor.getColumnIndexOrThrow("_id"));
    String name = cursor.getString(cursor.getColumnIndexOrThrow("name"));
    int age = cursor.getInt(cursor.getColumnIndexOrThrow("age"));

    Log.d("MainActivity", "Item ID: " + itemId + ", Name: " + name + ", Age: " + age);
}

cursor.close();
db.close();

Output

When you run the application, the database will be created and data will be inserted. When you query data from the table, the output will be displayed in the Android Studio logcat.

D/MainActivity: Item ID: 1, Name: John, Age: 25

Explanation

In this tutorial, we created a database with one table and three columns using SQLiteOpenHelper and SQLiteDatabase classes. We inserted data into the table using ContentValues. We then queried data from the table using Cursor and displayed the output in logcat.

Use

SQLite is widely used in Android application development to store data locally. You can use SQLite to store data that doesn't need to be shared across applications or devices. SQLite is fast and lightweight, making it ideal for mobile applications.

Important Points

1. Use SQLiteOpenHelper class to manage database creation and version management.
2. Use SQLiteDatabase class to perform database operations.
3. Use ContentValues to insert data into the database.
4. Use Cursor to query data from the database.
5. Close the database after performing operations.

Summary

In this tutorial, we learned how to use SQLite in Android Studio. We created a database, inserted data, and queried data from the database using SQLiteOpenHelper and SQLiteDatabase classes. We also learned important points to keep in mind while working with SQLite in Android Studio.