Android Studio SQLite Guide

Introduction

Android Studio is a powerful Integrated Development Environment (IDE) for developing Android applications. One essential tool for storing and managing data in Android applications is the SQLite database. SQLite is a lightweight, self-contained, and serverless database widely used in mobile applications due to its simplicity and efficiency. This guide covers everything from setting up the environment to performing CRUD (Create, Read, Update, Delete) operations.

Setting Up the Environment

Before working with SQLite, set up your Android Studio environment:

1. Launch Android Studio: Open Android Studio on your computer. If not installed, download it from the official Android website.
2. Create a New Project: Click "Start a new Android Studio project." Choose the type of project you want to create. For this guide, create an empty activity project.
3. Choose Project Template: Select "Empty Activity" from the project templates for a basic structure.
4. Configure Project Settings:
   • Application Name: Name your application, e.g., "SQLite Demo."
   • Package Name: Use a unique identifier, e.g., "com.example.sqlitedemo."
   • Language: Choose Java or Kotlin.
   • Minimum SDK: Set the minimum API level, typically API level 14 (Android 4.0).
   • Save Location: Choose where to save your project.
5. Finish Setup: Click "Finish" to create a new project with necessary files and folders.

Understanding SQLite

SQLite is a relational database management system storing data in a single file. It supports SQL (Structured Query Language) commands for CRUD operations. Key points about SQLite include:

• Local Database: Stored locally on the device, making it secure and private.
• SQL Commands: Supports most SQL commands, including SELECT, INSERT, UPDATE, and DELETE.
• Lightweight: Very lightweight, requiring no separate server process.

Creating an SQLite Database in Android

To create an SQLite database in Android, use the SQLiteOpenHelper class, which manages the creation and versioning of your database.

Step-by-Step Guide to Creating an SQLite Database

1. Create a Database Helper Class:

   • Extend SQLiteOpenHelper in a new class to handle database interactions.
   • Override the onCreate method to create the initial database schema.
   • Override the onUpgrade method to handle database upgrades.

2. Define Database Schema:

   • Define the schema using SQL commands.
   • Use constants for table and column names for readability.

3. Initialize Database Helper:

   • Instantiate the database helper class in your activity or service.

Example Implementation

Java Implementation

java
public class FeedReaderDbHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "FeedReader.db";

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

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

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL(SQL_DELETE_ENTRIES);
    onCreate(db);
}

private static final String SQL_CREATE_ENTRIES =
        "CREATE TABLE " + FeedEntry.TABLE_NAME + " (" +
                FeedEntry._ID + " INTEGER PRIMARY KEY," +
                FeedEntry.COLUMN_NAME_TITLE + " TEXT," +
                FeedEntry.COLUMN_NAME_SUBTITLE + " TEXT)";

private static final String SQL_DELETE_ENTRIES =
        "DROP TABLE IF EXISTS " + FeedEntry.TABLE_NAME;

}

Kotlin Implementation

kotlin
class FeedReaderDbHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
override fun onCreate(db: SQLiteDatabase) {
db.execSQL(SQL_CREATE_ENTRIES)
}

override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
    db.execSQL(SQL_DELETE_ENTRIES)
    onCreate(db)
}

companion object {
    const val DATABASE_VERSION = 1
    const val DATABASE_NAME = "FeedReader.db"
}

private const val SQL_CREATE_ENTRIES =
        "CREATE TABLE " + FeedEntry.TABLE_NAME + " (" +
                FeedEntry._ID + " INTEGER PRIMARY KEY," +
                FeedEntry.COLUMN_NAME_TITLE + " TEXT," +
                FeedEntry.COLUMN_NAME_SUBTITLE + " TEXT)"

private const val SQL_DELETE_ENTRIES =
        "DROP TABLE IF EXISTS " + FeedEntry.TABLE_NAME

}

CRUD Operations

Once the database helper class is set up, perform CRUD operations using the SQLiteDatabase object.

Create Operation

To create a new entry, use the insert method of the SQLiteDatabase object.

java
public void addEntry(FeedEntry entry) {
SQLiteDatabase db = getWritableDatabase();
ContentValues values = new ContentValues();
values.put(FeedEntry.COLUMN_NAME_TITLE, entry.getTitle());
values.put(FeedEntry.COLUMN_NAME_SUBTITLE, entry.getSubtitle());
db.insert(FeedEntry.TABLE_NAME, null, values);
db.close();
}

Read Operation

To read entries, use the query method of the SQLiteDatabase object.

java
public List getAllEntries() {
List entries = new ArrayList<>();
SQLiteDatabase db = getReadableDatabase();
Cursor cursor = db.query(FeedEntry.TABLE_NAME, null, null, null, null, null, null);
if (cursor != null && cursor.moveToFirst()) {
do {
FeedEntry entry = new FeedEntry(
cursor.getInt(cursor.getColumnIndex(FeedEntry._ID)),
cursor.getString(cursor.getColumnIndex(FeedEntry.COLUMN_NAME_TITLE)),
cursor.getString(cursor.getColumnIndex(FeedEntry.COLUMN_NAME_SUBTITLE))
);
entries.add(entry);
} while (cursor.moveToNext());
}
if (cursor != null) {
cursor.close();
}
db.close();
return entries;
}

Update Operation

To update an existing entry, use the update method of the SQLiteDatabase object.

java
public void updateEntry(FeedEntry entry) {
SQLiteDatabase db = getWritableDatabase();
ContentValues values = new ContentValues();
values.put(FeedEntry.COLUMN_NAME_TITLE, entry.getTitle());
values.put(FeedEntry.COLUMN_NAME_SUBTITLE, entry.getSubtitle());
db.update(FeedEntry.TABLE_NAME, values, FeedEntry._ID + " = ?", new String[]{String.valueOf(entry.getId())});
db.close();
}

Delete Operation

To delete an entry, use the delete method of the SQLiteDatabase object.

java
public void deleteEntry(int id) {
SQLiteDatabase db = getWritableDatabase();
db.delete(FeedEntry.TABLE_NAME, FeedEntry._ID + " = ?", new String[]{String.valueOf(id)});
db.close();
}

Displaying Data in a ListView

After performing CRUD operations and retrieving data, display it in a ListView.

Steps to Display Data

1. Create an Adapter: Bind your data to the ListView using an adapter.
2. Set Up ListView: Set up your ListView in the activity layout.
3. Populate ListView: Populate the ListView with data retrieved from the database.

Example Implementation

java
public class MainActivity extends AppCompatActivity {
private ListView listView;
private MyAdapter adapter;

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

    listView = findViewById(R.id.list_view);
    adapter = new MyAdapter(this, getAllEntries());
    listView.setAdapter(adapter);
}

private List<FeedEntry> getAllEntries() {
    // Implementation as shown above
}

private class MyAdapter extends BaseAdapter {
    private List<FeedEntry> entries;

    public MyAdapter(Context context, List<FeedEntry> entries) {
        this.entries = entries;
    }

    @Override
    public int getCount() {
        return entries.size();
    }

    @Override
    public Object getItem(int position) {
        return entries.get(position);
    }

    @Override
    public long getItemId(int position) {
        return position;
    }

    @Override
    public View getView(int position, View convertView, ViewGroup parent) {
        View view = convertView;
        if (view == null) {
            view = LayoutInflater.from(parent.getContext()).inflate(R.layout.list_item, parent, false);
        }

        TextView titleTextView = view.findViewById(R.id.title_text_view);
        TextView subtitleTextView = view.findViewById(R.id.subtitle_text_view);

        FeedEntry entry = entries.get(position);
        titleTextView.setText(entry.getTitle());
        subtitleTextView.setText(entry.getSubtitle());

        return view;
    }
}

}

Using sqlite3 Command-Line Tool

The sqlite3 command-line tool is a powerful utility for managing SQLite databases. Use it to execute SQL commands directly on your database file.

Using sqlite3 from Remote Shell

To use sqlite3 from a remote shell, follow these steps:

1. Enter Remote Shell:

   • Use the following command to enter a remote shell:
     bash
     adb [-d|-e|-s {}] shell

2. Start sqlite3 Tool:

   • Once in the remote shell, start the sqlite3 tool by entering:
     bash
     sqlite3

3. Execute Commands:

   • Issue SQL commands directly in the shell. For example, to print out the contents of a table, use:
     sql
     .dump

4. Exit sqlite3 Tool:

   • To exit and return to the adb remote shell, enter:
     bash
     exit

Using sqlite3 Locally

If you prefer managing your database locally, pull the database file from your device and start sqlite3 on your host machine.

1. Pull Database File:

   • Use the following command to pull a database file from your device:
     bash
     adb pull

2. Start sqlite3 Tool Locally:

   • Start the sqlite3 tool specifying the database file:
     bash
     sqlite3

By following these steps and examples, you should be able to effectively use SQLite databases in your Android applications.