Detailed instructions on how to add, delete, modify, and query an Android SQLite Database

I. Use an embedded relational SQLite database to store data

The Android platform integrates an embedded relational database named SQLite. SQLite3 supports NULL, INTEGER, REAL (floating point number), TEXT (string TEXT), and BLOB (binary object) data types, although it supports only five types, sqlite3 actually accepts data types such as varchar (n), char (n), decimal (p, s, it is only converted to the corresponding five data types during operation or storage. The biggest feature of SQLite is that you can save various types of data to any field without worrying about the data type declared by the field. For example, you can store strings in Integer fields, floating point numbers in Boolean fields, or date values in numeric fields. But there is one exception: the field defined as integer primary key can only store 64-bit integers. When saving data other than Integers to this field, an error will occur. In addition, when writing the create table statement, you can omit the data type information following the field name. For example, you can omit the type information of the name field in the following statement:
Create table person (personid integer primary key autoincrement, name varchar (20 ))
SQLite can parse most standard SQL statements, such:

Copy codeThe Code is as follows: query statement: select * from table name where Condition Clause group by grouping clause having... order by sorting clause
For example, select * from person
Select * from person order by id desc
Select name from person group by name having count (*)> 1
Paging SQL is similar to mysql. The following SQL statement obtains five records and skips the first three records.
Select * from Account limit 5 offset 3 or select * from Account limit 3, 5
Insert statement: insert into Table Name (Field List) values (Value List ). For example, insert into person (name, age) values ('chuanzhi ', 3)
Update statement: update table name: set field name = value: where Condition Clause. For example, update person set name = 'chuanzhi 'where id = 10
Delete statement: delete from table name where Condition Clause. For example, delete from person where id = 10

Ii. Use SQLiteOpenHelper to manage database versions

When writing database application software, we need to consider the following issues: because the software we developed may be installed on mobile phones of many users. If the application uses the SQLite database, we must create the database table structure used by the application and add some initialization records when users use the software for the first time. In addition, we also need to update the data table structure during software upgrade. Then, how can we automatically create the database tables required by the application on the user's mobile phone when the user first uses or upgrades the software? We cannot manually create database tables on every mobile phone that requires the software to be installed? This requirement is required for every database application. Therefore, in the Android system, an abstract class named SQLiteOpenHelper is provided, which must be inherited before it can be used, it manages the database version to meet the previous requirements.

To manage database versions, the SQLiteOpenHelper class provides two important methods: onCreate (SQLiteDatabase db) and onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ), the former is used to generate a database table when you first use the software, and the latter is used to update the database table structure when you upgrade the software. When the getWritableDatabase () or getReadableDatabase () method of SQLiteOpenHelper is called to obtain the SQLiteDatabase instance used to operate the database, if the database does not exist, the Android system automatically generates a database and then calls onCreate () method. The onCreate () method is called only when the database is generated for the first time. In the onCreate () method, you can generate the database table structure and add the initialization data used by some applications. The onUpgrade () method is called when the database version changes. Generally, the version number needs to be changed during software upgrade. The database version is controlled by programmers, assume that the current database version is 1 and the database table structure is modified due to business changes. In this case, you need to upgrade the software to update the database table structure on your mobile phone, to achieve this goal, you can set the original database version to 2 (some people may ask if it is set to 3 rows? Of course, if you want to, set it to 100) and update the table structure in the onUpgrade () method. When the number of software version upgrades is large, you can use the onUpgrade () method to determine based on the original number and target version number, and then update the table structure and data.

Both the getWritableDatabase () and getReadableDatabase () methods can obtain a SQLiteDatabase instance used to operate the database. However, the getWritableDatabase () method opens the database in read/write mode. Once the disk space of the database is full, the database can only read but cannot write. If getWritableDatabase () is used to open the database, an error occurs. The getReadableDatabase () method first opens the database in read/write mode. If the disk space of the database is full, it will fail to be opened. When the opening fails, it will continue to attempt to open the database in read-only mode.

Note: The difference between getWritableDatabase () and getReadableDatabase is that when the database is full, an error is reported when the former is called, but not when the latter is called. If the database is not updated, it is best to call the latter to obtain the database connection.

Code:

Copy codeThe Code is as follows: public class DatabaseHelper extends SQLiteOpenHelper {
// The class is not instantiated. It cannot be used as a parameter of the parent class constructor and must be declared as static.
Private static final String name = "ljqdb"; // Database name
Private static final int version = 1; // database version
Public DatabaseHelper (Context context ){
// The third parameter CursorFactory specifies the factory class for obtaining a cursor instance during query execution. If it is set to null, it indicates that the default factory class is used.
Super (context, name, null, version );
}

@ Override
Public void onCreate (SQLiteDatabase db ){
Db.exe cSQL ("create table if not exists person (
Personid integer primary key autoincrement, name varchar (20), age INTEGER )");
}

@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){
Db.exe cSQL ("alter table person ADD phone VARCHAR (12) NULL"); // ADD a column to the TABLE
// Drop table if exists person Delete TABLE
}
}

In actual project development, when the database table structure is updated, the loss of data stored in the database should be avoided.

Iii. Use SQLiteDatabase to operate SQLite Databases

Android provides a class named SQLiteDatabase, which encapsulates APIs for database operations. This class can be used to Create, query, and Update data) and Delete operations (CRUD ). For SQLiteDatabase learning, we should master the execSQL () and rawQuery () methods. The execSQL () method can be used to execute SQL statements with changing behaviors such as insert, delete, update, and CREATE TABLE. The rawQuery () method is used to execute select statements.
ExecSQL () method example:

Copy codeThe Code is as follows: SQLiteDatabase db = ....;
Db.exe cSQL ("insert into person (name, age) values ('logging', 24 )");
Db. close ();

When the preceding SQL statement is executed, a record is added to the person table. In actual application, the "Lin ji qin" parameter values in the statement are provided on the user input interface, if you splice the content entered by the user into the preceding insert statement as is, when the content entered by the user contains single quotes, the assembled SQL statement will have a syntax error. To solve this problem, you need to escape single quotes, that is, convert single quotes into two single quotes. In some cases, users often enter special SQL symbols such as "&". To ensure that the SQL statement syntax is correct, these special SQL symbols in the SQL statement must be escaped, obviously, it is cumbersome to do this for each SQL statement. The SQLiteDatabase class provides an overloaded execSQL (String SQL, Object [] bindArgs) method. This method can solve the problem mentioned above, this method supports using placeholder parameters (?). Example:Copy codeThe Code is as follows: SQLiteDatabase db = ....;
Db.exe cSQL ("insert into person (name, age) values (?,?) ", New Object [] {" Chuanzhi podcast ", 4 });
Db. close ();

The first parameter of the execSQL (String SQL, Object [] bindArgs) method is the SQL statement, and the second parameter is the value of the placeholder parameter in the SQL statement, the order of parameter values in the array must correspond to the position of the placeholder.

RawQuery () of SQLiteDatabase is used to execute select statements,Example:

Copy codeThe Code is as follows: SQLiteDatabase db = ....;
Cursor cursor = db. rawQuery ("select * from person", null );
While (cursor. moveToNext ()){
Int personid = cursor. getInt (0); // obtain the value of the first column. The index of the first column starts from 0.
String name = cursor. getString (1); // obtain the value of the second column
Int age = cursor. getInt (2); // get the value of the third column
}
Cursor. close ();
Db. close ();

The first parameter of the rawQuery () method is the select statement. The second parameter is the value of the placeholder parameter in the select statement. If the select statement does not use a placeholder, this parameter can be set to null. An example of a select statement with placeholder parameters is as follows:Copy codeThe Code is as follows: Cursor cursor = db. rawQuery ("select * from person where name like? And age =? ", New String [] {" % linguin % "," 4 "});

Cursor Is a result set Cursor used for Random Access to the result set. If you are familiar with jdbc, Cursor works very similar to the ResultSet in JDBC. You can use the moveToNext () method to move the cursor from the current row to the next row. If the last row of the result set has been moved, false is returned; otherwise, true is returned. In addition, Cursor also has the commonly used moveToPrevious () method (used to move the Cursor from the current row to the previous row. If the first row of the result set has been moved, the return value is false; otherwise, the return value is true), moveToFirst () method (used to move the cursor to the first row of the result set. If the result set is empty, the return value is false; otherwise, it is true) and moveToLast () method (used to move the cursor to the last row of the result set. If the result set is empty, the return value is false. Otherwise, the return value is true ).

In addition to the execSQL () and rawQuery () methods described earlier, SQLiteDatabase also provides operation methods for adding, deleting, updating, and querying: insert (), delete (), update (), and query (). These methods are actually used by cainiao who are not familiar with SQL syntax. For programmers who are familiar with SQL syntax, execSQL () and rawQuery () are used directly () to add, delete, update, and query data.

The Insert () method is used to add data. The data of each field is stored using ContentValues. ContentValues is similar to MAP. Compared with MAP, it provides the put (String key, Xxx value) and getAsXxx (String key) methods for data access. key is the field name and value is the field value, xxx refers to various common data types, such as String and Integer.

Copy codeThe Code is as follows: SQLiteDatabase db = databaseHelper. getWritableDatabase ();
ContentValues values = new ContentValues ();
Values. put ("name", "Lin ji qin ");
Values. put ("age", 24 );
Long rowid = db. insert ("person", null, values); // returns the row number of the newly added record, regardless of the primary key id.

No matter whether the third parameter contains data, the Insert () method will inevitably add a record. If the third parameter is Null, a record with Null values other than the primary key will be added. The Insert () method actually adds data by constructing an insert SQL statement. The second parameter of the Insert () method is used to specify the name of the null field. I believe you will be confused about this parameter, what is the role of this parameter? If the value of the third parameter is Null or the number of elements is 0, the Insert () method requires that you add a record with Null values in other fields except the primary key, to meet the needs of SQL syntax, the insert statement must be given a field name, for example, insert into person (name) values (NULL). If the field name is not given, the insert statement is like this: insert into person () values (), apparently this does not meet the standard SQL syntax. For field names, we recommend that you use a field other than the primary key. If an INTEGER type primary key field is used, after executing an insert statement similar to insert into person (personid) values (NULL, the value of this primary key field is not NULL. If the value of the third parameter is not Null and the number of elements is greater than 0, you can set the second parameter to null.

Use of the delete () method:

Copy codeThe Code is as follows: SQLiteDatabase db = databaseHelper. getWritableDatabase ();
Db. delete ("person", "personid <? ", New String [] {" 2 "});
Db. close ();

The above code deletes a record whose personid is less than 2 from the person table.

Use of the update () method:Copy codeThe Code is as follows: SQLiteDatabase db = databaseHelper. getWritableDatabase ();
ContentValues values = new ContentValues ();
Values. put ("name", "Lin ji qin"); // The key is the field name, and the value is the value.
Db. update ("person", values, "personid =? ", New String [] {" 1 "});
Db. close ();

The above code is used to change the value of the name field of the record whose personid is equal to 1 in the person table to "Lin ji qin ".

The query () method splits the select statement into several components and serves as the input parameter of the method:

Copy codeThe Code is as follows: SQLiteDatabase db = databaseHelper. getWritableDatabase ();
Cursor cursor = db. query ("person", new String [] {"personid, name, age"}, "name like? ", New String [] {" % ljq % "}, null, null," personid desc "," 1, 2 ");
While (cursor. moveToNext ()){
Int personid = cursor. getInt (0); // obtain the value of the first column. The index of the first column starts from 0.
String name = cursor. getString (1); // obtain the value of the second column
Int age = cursor. getInt (2); // get the value of the third column
}
Cursor. close ();
Db. close ();

The above code is used to find records whose name field contains "intelligence" from the person table. matched records are sorted in descending order by personid. The first record is skipped for the sorted results and only two records are obtained.
Meanings of parameters in the query (table, columns, selection, selectionArgs, groupBy, having, orderBy, limit) method:
Table: table name. It is equivalent to the part after the select statement from keyword. For multi-table join queries, you can use commas to separate the two table names.
Columns: name of the column to be queried. It is equivalent to the part after the select keyword of the select statement.
Selection: the query Condition Clause, which is equivalent to the part after the where keyword of the select statement. The placeholder "?" can be used in the Condition Clause.
SelectionArgs: it corresponds to the placeholder value in the selection statement. The position of the value in the array must be the same as that of the placeholder in the statement. Otherwise, an exception occurs.
GroupBy: equivalent to the part after the select statement group by keyword
Having: equivalent to the part after the having keyword of the select statement
OrderBy: equivalent to the part after the select statement order by keyword, such as: personid desc, age asc;
Limit: Specifies the offset and the number of records obtained, which is equivalent to the part following the limit keyword of the select statement.

4. Use SQLiteOpenHelper to obtain the SQLiteDatabase instance for database operations
Public class DatabaseHelper extends SQLiteOpenHelper {
Private static final String name = "ljqdb"; // Database name
Private static final int version = 1; // database version
...
}
Public class HelloActivity extends Activity {
@ Override public void onCreate (Bundle savedInstanceState ){
......
Button button = (Button) this. findViewById (R. id. button );
Button. setOnClickListener (new View. OnClickListener (){
Public void onClick (View v ){
DatabaseHelper databaseHelper = new DatabaseHelper (HelloActivity. this );
SQLiteDatabase db = databaseHelper. getWritableDatabase ();
Db.exe cSQL ("insert into person (name, age) values (?,?) ", New Object [] {" Lin ji qin ", 4 });
Db. close ();
}});
}
}
After the getWritableDatabase () or getReadableDatabase () method is called for the first time, SQLiteOpenHelper caches the current SQLiteDatabase instance, and the SQLiteDatabase instance normally keeps the database open, therefore, when you no longer need a SQLiteDatabase instance, call the close () method to release the resource in time. Once the SQLiteDatabase instance is cached, the same instance is obtained by calling the getWritableDatabase () or getReadableDatabase () method multiple times.

5. Use transactions to operate SQLite Databases

Use the beginTransaction () method of SQLiteDatabase to start a transaction. When the program runs to the endTransaction () method, it checks whether the transaction flag is successful. If the program runs to the endTransaction () method () previously, the setTransactionSuccessful () method was called to set the transaction flag to successful, then the transaction is committed. If the setTransactionSuccessful () method is not called, the transaction is rolled back.

Example:

Copy codeThe Code is as follows: SQLiteDatabase db = ....;
Db. beginTransaction (); // start the transaction
Try {
Db.exe cSQL ("insert into person (name, age) values (?,?) ", New Object [] {" Lin ji qin ", 4 });
Db.exe cSQL ("update person set name =? Where personid =? ", New Object [] {" abc ", 1 });
Db. setTransactionSuccessful (); // call this method to submit the current transaction when it is executed to endTransaction (). If this method is not called, the transaction will be rolled back.
} Finally {
Db. endTransaction (); // The transaction identifier determines whether to commit or roll back the transaction.
}
Db. close ();

The preceding two SQL statements are executed in the same transaction.