- Create:
insert(String table, String nullColumnHack, ContentValues values)
// Insert a new row into the database.
insertOrThrow(String table, String nullColumnHack, ContentValues values)
// Insert a new row, or throw an exception if an error occurs.
insertWithOnConflict(String table, String nullColumnHack, ContentValues initialValues, int conflictAlgorithm)
// Insert a new row with a conflict resolution strategy.
- Read:
query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
//Query the database and retrieve rows.
rawQuery(String sql, String[] selectionArgs)
// Perform a raw SQL query and retrieve the result set.
- Update:
update(String table, ContentValues values, String whereClause, String[] whereArgs)
//Update existing rows in the database.
- Delete:
delete(String table, String whereClause, String[] whereArgs)
//Delete rows from the database.
execSQL(String sql)
: Execute a single SQL statement.beginTransaction()
: Start a transaction.setTransactionSuccessful()
: Marks a transaction as successful.endTransaction()
: End a transaction.close()
: Close the database.onCreate(SQLiteDatabase db)
: Called when the database is created for the first time.onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
: Called when the database needs to be upgraded.compileStatement(String sql)
: Compiles an SQL statement into a reusable pre-compiled statement object.delete(String table, String whereClause, String[] whereArgs)
: Convenience method for deleting rows in the database.deleteDatabase(File file)
: Deletes a database including its journal file and other auxiliary files that may have been created by the database engine.execSQL(String sql)
: Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.isOpen()
: Returns true if the database is currently open.isReadOnly()
: Returns true if the database is opened as read only.openOrCreateDatabase(File file, SQLiteDatabase.CursorFactory factory)
: Equivalent to openDatabase(file.getPath(), factory, CREATE_IF_NECESSARY).query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
: Query the given table, returning a Cursor over the result set.rawQuery(String sql, String[] selectionArgs)
: Runs the provided SQL and returns a Cursor over the result set.update(String table, ContentValues values, String whereClause, String[] whereArgs)
: Convenience method for updating rows in the database.
SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)
// Constructor to create a new instance of `SQLiteOpenHelper`. It requires the context, database name, optional `CursorFactory`, and the initial database version.
onCreate(SQLiteDatabase db)
//Called when the database is created for the first time. You should create your database tables and initial data in this method.
onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
//Called when the database needs to be upgraded due to a version change. You should handle any necessary data migration or schema changes in this method.
getWritableDatabase()
//Returns a writable database instance. If the database does not exist, `onCreate` will be called. If the database needs to be upgraded, onUpgrade will be called.
getReadableDatabase()
//Returns a readable database instance. If the database does not exist, `onCreate` will be called. If the database needs to be upgraded, onUpgrade will be called.
onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)
//Called when the database needs to be downgraded due to a version change. You should handle any necessary data migration or schema changes in this method.
close()
//Closes the database. You should call this method when you are done using the database to free up resources.
getDatabaseName()
//Return the name of the SQLite database being opened, as given to the constructor.
onOpen(SQLiteDatabase db)
//Called when the database has been opened.
Demo Program
import android.content.*;
import android.database.Cursor;
import android.database.sqlite.*;
public class DatabaseHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "my_database";
private static final int DATABASE_VERSION = 1;
// Table name and columns
private static final String TABLE_NAME = "contacts";
private static final String COLUMN_ID = "id";
private static final String COLUMN_NAME = "name";
private static final String COLUMN_PHONE = "phone";
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String createTableQuery = "CREATE TABLE " + TABLE_NAME + " (" +
COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
COLUMN_NAME + " TEXT, " +
COLUMN_PHONE + " TEXT)";
db.execSQL(createTableQuery);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
onCreate(db);
}
// CRUD methods
// Create: Insert a new contact into the database
public long insertContact(String name, String phone) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COLUMN_NAME, name);
values.put(COLUMN_PHONE, phone);
long id = db.insert(TABLE_NAME, null, values);
db.close();
return id;
}
// Read: Query all contacts from the database
public Cursor getAllContacts() {
SQLiteDatabase db = this.getReadableDatabase();
return db.query(TABLE_NAME, null, null, null, null, null, null);
}
// Update: Update an existing contact in the database
public int updateContact(int id, String name, String phone) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COLUMN_NAME, name);
values.put(COLUMN_PHONE, phone);
int rowsAffected = db.update(TABLE_NAME, values, COLUMN_ID + " = ?", new String[]{String.valueOf(id)});
db.close();
return rowsAffected;
}
// Delete: Delete a contact from the database
public int deleteContact(int id) {
SQLiteDatabase db = this.getWritableDatabase();
int rowsAffected = db.delete(TABLE_NAME, COLUMN_ID + " = ?", new String[]{String.valueOf(id)});
db.close();
return rowsAffected;
}
// Other Important Methods
// Execute a raw SQL query
public void executeRawQuery(String sql) {
SQLiteDatabase db = this.getWritableDatabase();
db.execSQL(sql);
db.close();
}
// Start a transaction
public void beginTransaction() {
SQLiteDatabase db = this.getWritableDatabase();
db.beginTransaction();
}
// Mark a transaction as successful
public void setTransactionSuccessful() {
SQLiteDatabase db = this.getWritableDatabase();
db.setTransactionSuccessful();
}
// End a transaction
public void endTransaction() {
SQLiteDatabase db = this.getWritableDatabase();
db.endTransaction();
}
}