Android SQLite development tutorial (4): read/write database operations

Previous: http://www.bkjia.com/kf/201205/131948.html

With database objects, you can use execSQL to add, delete, modify, or query databases. Apart from common execSQL statements, SQLiteDatabase provides insert, update, delete, and query methods to simplify database addition, deletion, modification, or query operations.

In addition, SQLite does not forcibly check the data type of the database. You can use DBAdapter to modify or delete data with strong data types, which is also a benefit of using DBAdapter:

Here we first define a TodoItem class to represent a Todo item:

[Java]
Public class TodoItem {

Private String mTask;

Private Date mCreated;

Public String getTask (){
Return mTask;
}

Public Date getCreated (){
Return mCreated;
}

Public TodoItem (String task ){
This (task, new Date (System. currentTimeMillis ()));
}

Public TodoItem (String task, Date created ){
MTask = task;
MCreated = created;
}

@ Override
Public String toString (){
SimpleDateFormat sdf = new SimpleDateFormat ("dd/mm/yy ");
String dateString = sdf. format (mCreated );
Return "(" + dateString + ")" + mTask;
}

}
Public class TodoItem {
 
Private String mTask;
 
Private Date mCreated;
 
Public String getTask (){
Return mTask;
}
 
Public Date getCreated (){
Return mCreated;
}
 
Public TodoItem (String task ){
This (task, new Date (System. currentTimeMillis ()));
}
 
Public TodoItem (String task, Date created ){
MTask = task;
MCreated = created;
}
 
@ Override
Public String toString (){
SimpleDateFormat sdf = new SimpleDateFormat ("dd/mm/yy ");
String dateString = sdf. format (mCreated );
Return "(" + dateString + ")" + mTask;
}
 
}

The following method provides the TodoItem type for parameter addition, deletion, and modification.

[Java]
// Insert a new task
Public long insertTask (TodoItem task ){
ContentValues newTaskValues = new ContentValues ();
// Assign values for each row
NewTaskValues. put (KEY_TASK, task. getTask ());
NewTaskValues. put (KEY_CREATION_DATE, task. getCreated (). getTime ());

// Insert row
Return mDb. insert (DATABASE_TABLE, null, newTaskValues );
}

Public boolean removeTask (long rowIndex ){
Return mDb. delete (DATABASE_TABLE, KEY_ID + "=" + rowIndex, null)> 0;
}

Public boolean updateTask (long rowIndex, String task ){
ContentValues newValue = new ContentValues ();
NewValue. put (KEY_TASK, task );
Return mDb. update (DATABASE_TABLE, newValue,
KEY_ID + "=" + rowIndex, null)> 0;
}
// Insert a new task
Public long insertTask (TodoItem task ){
ContentValues newTaskValues = new ContentValues ();
// Assign values for each row
NewTaskValues. put (KEY_TASK, task. getTask ());
NewTaskValues. put (KEY_CREATION_DATE, task. getCreated (). getTime ());
 
// Insert row
Return mDb. insert (DATABASE_TABLE, null, newTaskValues );
}
 
Public boolean removeTask (long rowIndex ){
Return mDb. delete (DATABASE_TABLE, KEY_ID + "=" + rowIndex, null)> 0;
}
 
Public boolean updateTask (long rowIndex, String task ){
ContentValues newValue = new ContentValues ();
NewValue. put (KEY_TASK, task );
Return mDb. update (DATABASE_TABLE, newValue,
KEY_ID + "=" + rowIndex, null)> 0;
}
ContentValues defines the ing between column names and column values, similar to Hashtable.

A Query method of SQLiteDatabase is defined as follows:

Public Cursorquery (String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy)

Table: Database Name
Columns: name of the column to be returned,
Selection: Query condition, WHERE Statement (excluding WHERE ).
SelectionArgs: If the selection contains? , Which can be provided here? .
GroupBy: The Group statement removes group.
Having: Having statement except Having
OrderBy: Order by statement.
The following code queries a todoItem:

[Java]
Public Cursor getAllToDoItemsCursor (){
Return mDb. query (DATABASE_TABLE,
New String [] {KEY_ID, KEY_TASK, KEY_CREATION_DATE },
Null, null );
}

Public Cursor setCursorToToDoItem (long rowIndex)
Throws SQLException {
Cursor result = mDb. query (DATABASE_TABLE,
New String [] {KEY_ID, KEY_TASK },
KEY_ID + "=" + rowIndex, null );
If (result. getCount () = 0 |! Result. moveToFirst ()){
Throw new SQLException ("No to do item found for row :"
+ RowIndex );
}
Return result;
}

Public TodoItem getToDoItem (long rowIndex)
Throws SQLException {
Cursor cursor = mDb. query (DATABASE_TABLE,
New String [] {KEY_ID, KEY_TASK, KEY_CREATION_DATE },
KEY_ID + "=" + rowIndex, null );
If (cursor. getCount () = 0 |! Cursor. moveToFirst ()){
Throw new SQLException ("No to do item found for row :"
+ RowIndex );
}
String task = cursor. getString (TASK_COLUMN );
Long created = cursor. getLong (CREATION_DATE_COLUMN );
TodoItem result = new TodoItem (task, new Date (created ));
Return result;

}
Public Cursor getAllToDoItemsCursor (){
Return mDb. query (DATABASE_TABLE,
New String [] {KEY_ID, KEY_TASK, KEY_CREATION_DATE },
Null, null );
}
 
Public Cursor setCursorToToDoItem (long rowIndex)
Throws SQLException {
Cursor result = mDb. query (DATABASE_TABLE,
New String [] {KEY_ID, KEY_TASK },
KEY_ID + "=" + rowIndex, null );
If (result. getCount () = 0 |! Result. moveToFirst ()){
Throw new SQLException ("No to do item found for row :"
+ RowIndex );
}
Return result;
}
 
Public TodoItem getToDoItem (long rowIndex)
Throws SQLException {
Cursor cursor = mDb. query (DATABASE_TABLE,
New String [] {KEY_ID, KEY_TASK, KEY_CREATION_DATE },
KEY_ID + "=" + rowIndex, null );
If (cursor. getCount () = 0 |! Cursor. moveToFirst ()){
Throw new SQLException ("No to do item found for row :"
+ RowIndex );
}
String task = cursor. getString (TASK_COLUMN );
Long created = cursor. getLong (CREATION_DATE_COLUMN );
TodoItem result = new TodoItem (task, new Date (created ));
Return result;
 
}
The Query method also has several overload methods, For details, see the Android document http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

 

 

Excerpted from the mobile app