Android SQLite introduction, Insert, Update, Delete records with SQLite database

SQLite, is a powerful SQL database library.

Two Mechanism gives Data Structured Data Persistent

1)      SQLite

2)      Content Providers

SQLite is a opensource, lightweight, single tier relational DBMS.

Each column in database is not strongly typed. So, type checking isn’t necessary when assigning or

extracting values from each column within a row.

Row and Resultset Mapping in SQLite:

“ContentValues” represents rows in a table, “Content Values” object represents a single table row.

Query in android will return as a “Cursor” objects. Cursors are pointers to the result set within the underlying data.

Following are some of functions in “Cursor” class, which helps navigation within result set.

• moveToFirst Moves the cursor to the first row in the query result
•  moveToNext Moves the cursor to the next row
•  moveToPrevious Moves the cursor to the previous row
•  getCount Returns the number of rows in the result set
•  getColumnName Returns the name of the specified column index
•  getColumnNames Returns a string array of all the column names in the current Cursor
•  moveToPosition Moves the Cursor to the specified row
•  getPosition Returns the current Cursor position

Following code snippet, imports some of namespaces (or packages) you need to import before starting database work in android application.

 

import android.content.Context;

import android.database.*;

import android.database.sqlite.*;

import android.database.sqlite.SQLiteDatabase.CursorFactory;

Opening and Creating Database

Use “openOrCreateDatabase” method from the application Context class to

private static final String DATABASE_NAME = “myTestDatabase.db”;

private static final String DATABASE_TABLE = “mainTestTable”;

private static final String DATABASE_CREATE =

“create table ” + DATABASE_TABLE + ” ( _id integer primary key autoincrement,” +

“column_one text not null);”;

SQLiteDatabase myDatabase;

private void createDatabase() {

myDatabase = openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);

myDatabase.execSQL(DATABASE_CREATE);

}

Once you have created database,

Use the “query” method to execute a query on database; following parameters need to specify in order to execute your query.

• An optional Boolean that specifies if the result set should contain only unique values.
• The name of the table to query.
• An array of strings (column names), that lists the columns to include in the result set.
•  A ‘‘where’’ clause that defines the rows to be returned. You can include ‘?’ wildcards that will

be replaced by the values passed in through the selection argument parameter.

•  An array of selection argument strings that will replace the ‘?’s in the where clause.
•  A ‘‘group by’’ clause that defines how the resulting rows will be grouped.
•  A ‘‘having’’ filter that defines which row groups to include if you specified a group by clause.
•  A string that describes the order of the returned rows.
•  An optional string that defines a limit for the number of returned rows.

SQLiteDatabase class has insert, update and delete methods to perform basic database operations.

Insert Method

To add record in database, use ContentValues object and use its put methods to provide a value for

each column.

ContentValues newValues = new ContentValues();

// Assign values for each row.

newValues.put(COLUMN_NAME, newValue);

[ … Repeat for each column … ]

Now, insert the new row by passing the Content Values object into the insert method called

on the target database — along with the table name

// Insert the row into your table

myDatabase.insert(DATABASE_TABLE, null, newValues);

Update Method

In order to update a row, you have to use update method, with ‘where’ parameter that specified condition for which row(s) data to be update, and table name and new values.

Following code snippet represents idea on update method:

// Define the updated row content.

ContentValues updatedValues = new ContentValues();

// Assign values for each row.

newValues.put(COLUMN_NAME, newValue);

[ … Repeat for each column … ]

String where = KEY_ID + “=” + rowId;

// Update the row with the specified index with the new values.

myDatabase.update(DATABASE_TABLE, newValues, where, null);

Delete Method

To delete a row simply call delete on a database, specifying the table name and a where clause that

returns the rows you want to delete.

String where = KEY_ID + “=” + rowId;

myDatabase.delete(DATABASE_TABLE, where, null);