Android SQLite database, androidsqlite

Android SQLite database, androidsqlite

I. Introduction to SQLite

Google provides SQLite,
He is outstanding in data storage, management, maintenance, and other aspects,
The function is also very powerful.

Ii. Features of SQLite

1. lightweight
To use SQLite, you only need to bring a dynamic library to enjoy all its functions,
And the size of the dynamic library is smaller.
2. Independence
The core engine of the SQLite database does not need to rely on third-party software or be "installed ".
3. Isolation
All information in the SQLite database (such as tables, views, triggers, etc)
Are included in a folder to facilitate management and maintenance.
4. cross-platform
SQLite currently supports most operating systems, not computer operating systems.
It can also run, such as Android.
5. multilingual interface
The SQLite Database supports multi-language programming interfaces.
6. Security
SQLite databases implement independent transaction processing through database-level exclusivity and shared locks.
This means that multiple processes can read data from the same database at the same time,
However, only one data can be written.

Iii. SQLite Data Type

· SQLite data types:

-SQLite supports NULL, INTEGER, REAL, TEXT, and BLOB data types.

-Values: NULL, integer, floating point, string, and binary

· Dynamic data type (weak reference)

-When a value is inserted into the database, SQLite checks its type. If the type does not match the associated column,

SQLite will try to convert the value to the type of the column. If the conversion fails, the value will be stored as its own type.

Iv. SQLiteDatabase

· SQLiteDatabase class

-Provides methods to create, delete, and execute SQL commands and execute other common database management tasks.

-The database name of each program is unique.

· The SQLiteDatabase class provides us with many methods, and the commonly used methods are as follows:

(Int) delete (String table, String whereClause, String [] whereArgs)
Convenient Method for deleting data rows

(Long) insert (String table, String nullColumnHack, ContentValues values)
Convenient Method for adding data rows

(Int) update (String table, ContentValues values, String whereClause, String [] whereArgs)
Convenient Method for updating data rows

(Void) execSQL (String SQL)
Execute an SQL statement, which can be a select statement or other SQL statement.

(Void) close ()
Close Database

(Cursor) query (String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy, String limit)
Returns a data set with a cursor.

(Cursor) rawQuery (String SQL, String [] selectionArgs)
Run a preset SQL statement to return a dataset with a cursor (the biggest difference from the preceding statement is to prevent SQL injection)

· Add data

 

// Use the insert method ContentValues cv = new ContentValues (); // instantiate a ContentValues to load the data cv to be inserted. put ("username", "Jack Johnson"); // Add the user name cv. put ("password", "iLovePopMusic"); // Add the password db. insert ("user", null, cv); // execute the insert operation using the execSQL method to implement String SQL = "insert into user (username, password) values ('Jack Johnson ', 'ilovepopmuisc'); // SQL statement of the SQL statement that is inserted into the operation.

 

· Data deletion

// Use the delete method String whereClause = "username =? "; // The deleted condition String [] whereArgs = {" Jack Johnson "}; // The deleted condition parameter db. delete ("user", whereClause, whereArgs); // delete String SQL = "delete from user where username = 'Jack Johnson '"; // delete the SQL statement db.exe cSQL (SQL) of the operation ); // perform the delete operation

· Data modification

// Use the update method ContentValues cv = new ContentValues (); // instantiate ContentValuescv. put ("password", "iHatePopMusic"); // Add the field and content to be changed String whereClause = "username =? "; // Modify the condition String [] whereArgs = {" Jack Johnson "}; // modify the condition parameter db. update ("user", cv, whereClause, whereArgs); // execute the modify String SQL = "update [user] set password = 'ihatepopmusic 'where username = 'Jack Johnson '"; // modify the SQL sentence db.exe cSQL (SQL); // execute the modification

· Data Query

Query
Public Cursor query (String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy, String limit)

Table: table Name
Colums: array of column names
Selection: Condition Clause, equivalent to where
SelectionArgs: parameter array of the Condition Statement
GroupBy: Group
Having: grouping Condition
OrderBy: Sorting class
Limit: The limit for querying by PAGE
Cursor: return value, equivalent to ResultSet

 

Cursor)
Total number of getCount () records
IsFirst () determines whether the first record is used
IsLast () determines whether it is the last record
MoveToFirst () moves to the first record
Move moveToLast () to the last record
Move (int offset) move [refers to offset instead of moving to the specified position]
MoveToNext () moves to scare a record
MoveToPrevious () moves to the previous record
GetColumnIndex (String columnName) obtains the int type value of the specified column index.

 

// Cursor (Cursor) Cursor c = db. query ("user", null, null); // query and obtain the cursor if (c. moveToFirst () {// determines whether the cursor is null for (int I = 0; I <c. getCount (); I ++) {c. moveToNext (); String username = c. getString (c. getColumnIndex ("username"); String password = c. getString (c. getColumnIndex ("password "));}

// RawQuery: Cursor c = db. rawQuery ("select * from user where username =? ", New Stirng [] {" Jack Johnson "}); if (cursor. moveToFirst () {String password = c. getString (c. getColumnIndex ("password "));}

Iv. SQLiteOpenHelper

· SQLiteOpenHelper

-SQLiteOpenHelper is a help class for SQLiteDatabase,
Used to manage database creation and version updates.
Generally, a class is created to inherit from it,
And implement its onCreate and onUpgrade methods.

-OnCreate (SQLiteDatabase db)
Called when creating a database

-OnUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)
Called during version update

-GetReadableDatabase ()
Create or open a read-only Database

-GetWritableDatabase ()
Create or open a read/write Database

· Create SQLiteOpenHelper class

Public class MyDatabaseOpenHelper extends SQLiteOpenHelper {private static final String db_name = "mydata. db "; // database name private static final int version = 1; // database version public MyDatabaseOpenHelper (Context context) {super (context, db_name, null, version );} // This method will execute public void onCreate (SQLiteDatabase db) only if the database does not exist. {// No database prints the Log. I ("Log", "no database, create DATABASE"); // create table statement String SQL _message = "create table t_mes Sage (id int primary key, userName varchar (50), lastMessage varchar (50), datetime varchar (50) "; // execute the table creation statement db.exe cSQL (SQL _message );} // public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {Log will be executed only when the data inventory is updated. I ("updateLog", "the database is updated! ");}}

· Call the getWritableDatabase () method of the SQLiteOpenHelper class to obtain a database

Public class MainActivity extends Activity {@ Override protected void onCreate (Bundle savedInstanceState) {super. onCreate (savedInstanceState); setContentView (R. layout. activity_main); DatabaseOpenHelper helper = new DatabaseOpenHelper (MainActivity. this); // obtain a writable database SQLiteDatabase db = helper. getWritableDatabase ();}}