SQlite Crud
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.content.Context;
public class SQLiteExample extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "sample.db";
private static final int DATABASE_VERSION = 1;
private static final String TABLE_NAME = "employees";
public SQLiteExample(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
@Override
public void onCreate(SQLiteDatabase db) {
// Create table (if it doesn't exist)
String createTableQuery = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (id INTEGER
PRIMARY KEY, name TEXT, age INTEGER, salary REAL);";
db.execSQL(createTableQuery);
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Handle database upgrades if needed
}
public void insertEmployee(String name, int age, double salary) {
SQLiteDatabase db = getWritableDatabase();
String insertQuery = "INSERT INTO " + TABLE_NAME + " (name, age, salary) VALUES ('" + name +
"', " + age + ", " + salary + ");";
db.execSQL(insertQuery);
db.close();
public Cursor getAllEmployees() {
SQLiteDatabase db = getReadableDatabase();
String selectQuery = "SELECT * FROM " + TABLE_NAME + ";";
return db.rawQuery(selectQuery, null);
public void updateSalary(int id, double newSalary) {
SQLiteDatabase db = getWritableDatabase();
String updateQuery = "UPDATE " + TABLE_NAME + " SET salary = " + newSalary + " WHERE id = "
+ id + ";";
db.execSQL(updateQuery);
db.close();
}
public void deleteEmployee(int id) {
SQLiteDatabase db = getWritableDatabase();
String deleteQuery = "DELETE FROM " + TABLE_NAME + " WHERE id = " + id + ";";
db.execSQL(deleteQuery);
db.close();
}
}
• The class SQLiteExample extends SQLiteOpenHelper to handle database creation and
upgrades.
• The onCreate method is overridden to create the "employees" table if it doesn't already
exist using db.execSQL.
• The insertEmployee method inserts a new employee record using db.execSQL.
• The getAllEmployees method retrieves all records from the "employees" table using
db.rawQuery and returns a Cursor object.
• The updateSalary method updates the salary of an employee using db.execSQL.
• The deleteEmployee method deletes an employee record using db.execSQL.
SQLite Database Creation in Android:
Create a class that extends SQLiteOpenHelper.
Override the onCreate method to create tables and define their schema using db.execSQL().
Override the onUpgrade method to handle database upgrades if needed.
Use getWritableDatabase() or getReadableDatabase() to get an instance of the database for
performing operations.
Execute SQL queries using db.execSQL() or db.rawQuery().
Close the database when finished using db.close().
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "sample.db";
private static final int DATABASE_VERSION = 1;
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
// Create the database tables
String createTableQuery = "CREATE TABLE IF NOT EXISTS mytable (_id INTEGER PRIMARY KEY,
name TEXT, age INTEGER);";
db.execSQL(createTableQuery);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Handle database upgrades if needed
public SQLiteDatabase openDatabase() {
return getWritableDatabase();
public void closeDatabase(SQLiteDatabase db) {
if (db != null && db.isOpen()) {
db.close();
}
}
1. We create a DatabaseHelper class that extends SQLiteOpenHelper. This class helps in
managing the creation and upgrading of the database.
2. The DATABASE_NAME variable holds the name of the database file (mydatabase.db in this
example).
3. The DATABASE_VERSION variable represents the version number of the database.
Increment this value when you make changes to the database schema to handle upgrades.
4. In the constructor of DatabaseHelper, we pass the DATABASE_NAME and
DATABASE_VERSION to the parent class constructor.
5. The onCreate() method is called when the database is created for the first time. We execute
an SQL query to create the table mytable if it doesn't already exist.
6. The onUpgrade() method is called when the database needs to be upgraded to a higher
version. You can implement your upgrade logic here.
7. The openDatabase() method returns a writable instance of the database. You can call this
method to obtain an instance and perform database operations.
8. The closeDatabase() method is used to close the database instance when you're done using
it.
To use this DatabaseHelper class, you can instantiate it in your activity or fragment and call the
openDatabase() method to get a reference to the database. Remember to call the closeDatabase()
method when you're finished with the database to release resources.
import android.database.Cursor;
import android.os.Bundle;
import android.widget.Toast;
import androidx.appcompat.app.AppCompatActivity;
public class MainActivity extends AppCompatActivity {
private SQLiteExample dbHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// Create an instance of the SQLiteExample class
dbHelper = new SQLiteExample(this);
// Perform SQLite operations
performSQLiteOperations();
private void performSQLiteOperations() {
// Insert an employee
dbHelper.insertEmployee("John Doe", 25, 5000.00, this);
// Retrieve all employees
Cursor cursor = dbHelper.getAllEmployees();
if (cursor.moveToFirst()) {
do {
int id = cursor.getInt(cursor.getColumnIndex("id"));
String name = cursor.getString(cursor.getColumnIndex("name"));
int age = cursor.getInt(cursor.getColumnIndex("age"));
double salary = cursor.getDouble(cursor.getColumnIndex("salary"));
// Do something with the retrieved employee data
Toast.makeText(this, "Employee: " + id + ", " + name + ", " + age + ", " + salary,
Toast.LENGTH_SHORT).show();
} while (cursor.moveToNext());
}
cursor.close();
// Update the salary of an employee
dbHelper.updateSalary(1, 5500.00, this);
// Delete an employee
dbHelper.deleteEmployee(2, this);
@Override
protected void onDestroy() {
super.onDestroy();
// Close the database when the activity is destroyed
dbHelper.close();
