"Android Basic article" SQLite database additions and deletions to change the basic operation

I. Overview

SQLite is one of the core data storage services of Android system, it is a lightweight embedded database, it occupies very little resources but can provide very fast data access service, many large-scale Android projects that need data storage are useful to SQLite (also can be used for desktop applications).

The following describes the creation of SQLite database, table operations, as well as basic additions and deletions to the operation.

Introduction of basic Operation API

In Android, the Sqlitedatabase class provides the underlying API for SQLite, but when using the SQLite database, we tend not to manipulate the Sqlitedatabase class directly, Instead, you create a subclass that inherits from Sqlitopenhelper to implement database operations. The purpose of this is for the future if the database upgrade is not to change too much code, has been implemented encapsulation, and two for our use more convenient.

1. Creating databases and Tables

Sqliteopenhelper is an abstract class in which there are two abstract methods, OnCreate and Onupgrade, which are used for the first time to create a database, which is used for database upgrades, and to create classes dbservices as follows:

 Public  class dbservices extends sqliteopenhelper{    Final Static intVersion =1;Final StaticString DbName ="Plan"; Public dbservices(Context context) {Super(Context,dbname,NULL, version); }@Override     Public void onCreate(Sqlitedatabase db) {//TODO auto-generated method stub        //Create today ScheduleString Create_today_plan_sql ="CREATE TABLE [_today_plan] ("+"[_date] varchar (TEN) is not NULL,"+"[Item] varchar,"+"[Check] varchar (5))";    Db.execsql (Create_today_plan_sql); }@Override     Public void Onupgrade(Sqlitedatabase DB,intOldversion,intNewVersion) {//TODO auto-generated method stub}}

The example defines two variables, one is the version number of the database, and the other is the database name. When the Android app runs, Sqliteopenhelper checks to see if the database already exists, creates the database if it does not exist, then opens the database, and finally calls the OnCreate method, so we need to create the table in OnCreate (view, etc.) If the database already exists and the version number is higher than the last created database version number, call Onupgrade for upgrade.

2, the data increase--insert

After we have created the database and the table, we can add data to the database and table.
The addition of data to other databases, as well as the use of INSERT, just sqlite Insert is a function, and very convenient to use, the following is the method (belonging to the Dbservices class above, the appendix has a complete code):

insert(String table, String nullColumnHack, ContentValues values){        SQLiteDatabase db = this.getWritableDatabase();        db.insert(table, nullColumnHack, values);    }

Parameter description:
Tables: table names, directly using string designations;
Nullcolumnhack: Specify null values for columns, SQLite does not allow blank lines, use this parameter can specify a column value is null, when the deposit behavior is empty, the value of this column is specified as null;
Values: Use a map-like key value to contentvalues the data structure of the mapping to specify the inserted

Add Data Example:

 string  [] args = { Today, content, boolean . Tostrin            g (checked)}; string  [] column = { "[_date]" ,             "[Item]" ,  "[Check]" };            //database             Contentvalues C = new  contentvalues (); for  (int i=0 ; i<args.length;i++)            {C.put (Column[i], args[i]); } dbservices.insert ( "_today_plan" , null , C); 

3. Deletion of data--delete

Delete and add are also implemented by calling methods with incoming parameters, as follows:

voiddelete(StringStringString[] whereArgs){        this.getWritableDatabase();        db.delete(table, whereClause, whereArgs);        Log.d("Delete",whereClause);    }

Parameter description:
Table: List name;
Whereclause: Optional, specifies the delete condition, equivalent to the class capacity after the SQL statement where statement, which can be passed? To specify the parameters;
Whereargs: When Whereclause specified? parameter, which is in this string array? The number of parameters to be represented should be the same as the? The number is consistent;

Example of deleting data:

String args[] ={    today,        content,        Boolean.toString(checked)};dbServices.delete("_today_plan""[_Date]=? and [Item]=? and [Check]=?",args);

4, the data modification--update

There is not much difference between the modification and the addition and deletion, the following is the Update method:

update(String table, ContentValues values,        String whereClause, String[] whereArgs){        SQLiteDatabase db = this.getWritableDatabase();        db.update(table, values, whereClause, whereArgs);    }

Parameter description:
Table: List name;
Values: Ibid, is a collection of mappings of columns and values that need to be modified;
Whereclause: The conditions to be met for the modified line;
Whereargs: Specify the parameters in the condition;

Example of modifying data:

String args[] ={    today,        content,        Booleannew ContentValues();c.put("[Check]"Boolean.toString(m));dbServices.update("_today_plan", c,"[_Date]=? and [StartTime]=? and [Item]=? and [Check]=?",args);

5, the data query--read

Here is different from the previous, the method used to read the data is to directly execute the query statement, get the cursor, and then through the cursor to traverse the database, the method is as follows:

publicread(String sql ,String[] args){        this.getReadableDatabase();        Cursor cursor = db.rawQuery(sql, args);        Log.d("Database",cursor.getColumnName(0));        return cursor;    }

Method Description:
Note: Here DB obtains a read-only database (Getreadabledatabase), while in the above three kinds of operations are used in the writable database (Getwritabledatabase), as for the use of cursors here will not repeat, Just look at the name of the API to master the basic usage, but finally remember to close the cursor (close)!

Third, appendix

Appendix Dbservices class, for reference only

 PackageCom.plan;ImportAndroid.content.ContentValues;ImportAndroid.content.Context;ImportAndroid.database.Cursor;ImportAndroid.database.sqlite.SQLiteDatabase;ImportAndroid.database.sqlite.SQLiteOpenHelper;ImportAndroid.util.Log; Public  class dbservices extends sqliteopenhelper{    Final Static intVersion =1;Final StaticString DbName ="Plan"; Public dbservices(Context context) {Super(Context,dbname,NULL, version); }@Override     Public void onCreate(Sqlitedatabase db) {//TODO auto-generated method stub        //Create today ScheduleString Create_today_plan_sql ="CREATE TABLE [_today_plan] ("+"[_date] varchar (TEN) is not NULL,"+"[Item] varchar,"+"[Check] varchar (5))";    Db.execsql (Create_today_plan_sql); }@Override     Public void Onupgrade(Sqlitedatabase DB,intOldversion,intNewVersion) {//TODO auto-generated method stub} PublicCursorRead(String sql, string[] args) {Sqlitedatabase db = This. Getreadabledatabase ();        cursor cursor = db.rawquery (sql, args); LOG.D ("Database", Cursor.getcolumnname (0));returnCursor } Public void Insert(string table, String nullcolumnhack, contentvalues values) {Sqlitedatabase db = This. Getwritabledatabase ();    Db.insert (table, nullcolumnhack, values); } Public void Delete(string table, String Whereclause, string[] whereargs) {Sqlitedatabase db = This. Getwritabledatabase ();        Db.delete (table, Whereclause, Whereargs); LOG.D ("Delete", Whereclause); } Public void Update(string table, contentvalues values, String whereclause, string[] whereargs) {Sqlitedatabase db = This. Getwritabledatabase ();    Db.update (table, values, Whereclause, Whereargs); }}

For the advanced features of SQLite, such as indexes, views, and triggers, you can take a look at the official SQLite documentation. In addition, if the amount of data added (deleted, etc.) is relatively large, transaction processing is recommended, which can greatly improve the efficiency and reliability.

"Android basic article" SQLite database additions and deletions to change the basic operation