[Android Basics] basic operations for adding, deleting, modifying, and querying SQLite databases, androidsqlite

[Android Basics] basic operations for adding, deleting, modifying, and querying SQLite databases, androidsqlite
I. Overview

SQLite is one of the core data storage services of the Android system. It is a lightweight embedded database that uses a very small amount of resources but can provide very fast data access services, many large Android projects that require data storage are useful for SQLite (or desktop applications ).

The following describes how to create databases and tables for SQLite and how to add, delete, modify, and query data.

II. Introduction to basic operation APIs

In Android, The SQLiteDatabase class provides the underlying API of SQLite, but when using the SQLite database, we often do not directly operate the SQLiteDatabase class, instead, you can create a subclass inherited from SQLitOpenHelper to perform database operations. The purpose of this solution is to implement encapsulation if the Database Upgrade does not require too many code changes in the future, and to make it easier to use.

1. create databases and tables

SQLiteOpenHelper is an abstract class. There are two abstract methods in this class: OnCreate and OnUpgrade. The former is used to create a database for the first time, and the latter is used for Database Upgrade. The DBServices class is created as follows:

Public class DBServices extends SQLiteOpenHelper {final static int version = 1; final static String dbName = "plan"; public DBServices (Context context) {super (context, dbName, null, version) ;}@ Override public void onCreate (SQLiteDatabase db) {// TODO Auto-generated method stub // CREATE today's schedule String create_today_plan_ SQL = "CREATE TABLE [_ today_plan] (" + "[_ Date] varchar (10) not null, "+" [Item] varchar (200), "+" [Check] varchar (5) "; db.exe cSQL (create_today_plan_ SQL);} @ Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stub }}

Two variables are defined in the example. One is the database version number and the other is the database name. When the Android Application is running, SQLiteOpenHelper first checks whether a database already exists. If not, it creates a database, opens the database, and finally calls the OnCreate method, therefore, we need to create a table (view, etc.) in OnCreate. If the database already exists and the version number is higher than the previous database version number, we will call OnUpgrade for upgrade.

2. Data addition-insert

After creating a database and a table, we can add data to the database and table.
The operation of adding data is the same as that of other databases, but the insert function of SQLite is very convenient to use. below is the method (which belongs to the preceding DBServices class, complete code in the appendix ):

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

Parameter description:
Table: table name, which is directly specified using a string;
NullColumnHack: Specifies a column with a null value. Empty rows are not allowed in SQLite. This parameter can be used to specify a column with a null value. When the storage behavior is null, the value of this column is specified as null;
Values: use the data structure ContentValues similar to map key-value pairs to specify the inserted data

Example of adding data:

String [] args = {today, content, Boolean. toString (checked)}; String [] column = {"[_ Date]", "[Item]", "[Check]"}; // Add data 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. delete data -- delete

Like adding, deleting is implemented by passing in a parameter call method. Method:

public void delete(String table , String whereClause , String[] whereArgs){        SQLiteDatabase db = this.getWritableDatabase();        db.delete(table, whereClause, whereArgs);        Log.d("Delete",whereClause);    }

Parameter description:
Table: table name;
WhereClause: (optional) specifies the deletion condition, which is equivalent to the class capacity after the SQL WHERE statement? To specify parameters;
WhereArgs: When whereClause is specified? Parameter, which is in the string array? The number of represented parameters should match? Consistent number;

Data deletion example:

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

4. data modification-update

Modification is similar to addition or deletion. The update method is as follows:

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

Parameter description:
Table: table name;
Values: Same as above. It is the ing set of columns and values to be modified;
WhereClause: conditions that must be met by the modified row;
WhereArgs: Specify the parameters in the condition;

Data modification example:

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

5. Data Query-read

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

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

Method description:
Note: Here db obtains the read-only database (getReadableDatabase), and all the above three operations use the writable database (getWritableDatabase). The usage of the cursor will not be described here, you only need to check the API name to understand the basic usage, but remember to close the cursor )!

Iii. Appendix

The DBServices class is provided in the appendix for reference only.

Package com. plan; import android. content. contentValues; import android. content. context; import android. database. cursor; import android. database. sqlite. SQLiteDatabase; import android. database. sqlite. SQLiteOpenHelper; import android. util. log; public class DBServices extends SQLiteOpenHelper {final static int version = 1; final static String dbName = "plan"; public DBServices (Context context) {super (context, dbName, null, version) ;}@ Override public void onCreate (SQLiteDatabase db) {// TODO Auto-generated method stub // CREATE today's schedule String create_today_plan_ SQL = "CREATE TABLE [_ today_plan] (" + "[_ Date] varchar (10) not null, "+" [Item] varchar (200), "+" [Check] varchar (5) "; db.exe cSQL (create_today_plan_ SQL);} @ Override public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stub} public Cursor read (String SQL, String [] args) {SQLiteDatabase db = this. getReadableDatabase (); Cursor cursor = db. rawQuery (SQL, args); Log. d ("Database", cursor. getColumnName (0); return cursor;} 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 more information about the advanced features of SQLite, such as indexes, views, and triggers, visit the official SQlite documentation. In addition, if you add (delete) a large amount of data at a time, we recommend that you use transaction processing to greatly improve efficiency and reliability.