SQLite Database and CRUD Operations
SQLite is a lightweight and embedded database engine that is widely used in Android applications. It provides a reliable and efficient way to store and manage structured data. In this tutorial, we will explore how to work with SQLite databases in Android and perform CRUD (Create, Read, Update, Delete) operations.
Introduction to SQLite Database
SQLite is a self-contained, serverless, and zero-configuration database engine that stores data in a single file. It is ideal for mobile applications due to its small footprint and efficient performance. Android provides built-in support for SQLite, allowing you to create and interact with databases using SQL queries.
Creating a SQLite Database
Here's an example of how to create a SQLite database in Android:
- Create a new class that extends the
SQLiteOpenHelperclass and override the necessary methods:
public class MyDatabaseHelper extends SQLiteOpenHelper {
// Implement necessary methods such as onCreate(), onUpgrade(), etc.
}
- In the
onCreate()method, execute SQL statements to create the necessary tables:
@Override
public void onCreate(SQLiteDatabase db) {
String createTableQuery = "CREATE TABLE my_table (id INTEGER PRIMARY KEY, name TEXT)";
db.execSQL(createTableQuery);
}
- Create an instance of the database helper and get a writable database object:
MyDatabaseHelper databaseHelper = new MyDatabaseHelper(context);
SQLiteDatabase db = databaseHelper.getWritableDatabase();
CRUD Operations
CRUD operations involve creating, reading, updating, and deleting data in the database.
- Create: To insert data into the database, use the
insert()method:
ContentValues values = new ContentValues();
values.put("name", "John Doe");
long rowId = db.insert("my_table", null, values);
- Read: To query data from the database, use the
query()method:
String[] projection = {"id", "name"};
String selection = null;
String[] selectionArgs = null;
String sortOrder = null;
Cursor cursor = db.query("my_table", projection, selection, selectionArgs, null, null, sortOrder);
- Update: To update data in the database, use the
update()method:
ContentValues values = new ContentValues();
values.put("name", "Jane Smith");
String whereClause = "id = ?";
String[] whereArgs = {"1"};
int rowsAffected = db.update("my_table", values, whereClause, whereArgs);
- Delete: To delete data from the database, use the
delete()method:
String whereClause = "id = ?";
String[] whereArgs = {"1"};
int rowsAffected = db.delete("my_table", whereClause, whereArgs);
Common Mistakes with SQLite Database
- Not handling database upgrades properly when the schema or version changes.
- Not using placeholders or parameterized queries, which can lead to SQL injection vulnerabilities.
- Not closing database connections or cursors, resulting in resource leaks and potential performance issues.
SQLite Database FAQs
-
Q: Can I use multiple tables in a single SQLite database?
A: Yes, you can create and manage multiple tables within a single SQLite database.
-
Q: How can I handle database upgrades?
A: Override the
onUpgrade()method in yourSQLiteOpenHelperclass and provide the necessary logic to migrate the database schema or data. -
Q: Are there any size limitations for SQLite databases?
A: SQLite databases have a theoretical size limit of about 140 terabytes, but practical limits depend on the storage medium and available resources.
-
Q: Can I perform complex queries with SQLite?
A: Yes, SQLite supports various SQL features, including joins, aggregations, and subqueries, allowing you to perform complex database queries.
-
Q: Can I use ORMs (Object-Relational Mapping) with SQLite?
A: Yes, you can use ORMs like Room or GreenDAO to simplify the database interaction and mapping of objects to SQLite tables.
Summary
In this tutorial, we explored how to work with SQLite databases in Android and perform CRUD operations. SQLite provides a lightweight and efficient way to store structured data in Android applications. By understanding the concepts and using the appropriate API methods, you can effectively create, read, update, and delete data in your Android apps using SQLite databases.