Quick start tutorial for operating SQLite database in Android

SQLite is a database product often used in the software development of Android platform. As a lightweight database, the design goal of SQLite is embedded, and it has been used in many embedded products. It occupies very low resources. In embedded equipment, it may only need hundreds of K of memory. Let's take a look at the use of SQLite 3 for Android platform.

The following concepts are understood under the SQLite limitation of the Android platform:

Basic concepts of database

1. SQLite saves the database through files. A file is a database. 2. The database contains several tables; 3. Each table contains multiple records; 4. Each record consists of multiple fields; 5. Each field has its corresponding value; 6. The type can be specified for each value.

Database name is the file name; The form has its own name; Record has no name; Each field has a name. In SQLite, records do not have the concept of order, and there are no concepts of first and second; You can only query to obtain records that meet the conditions. We operate the database by executing SQL instructions.

Database related classes under Android platform

1. Sqliteopenhelper abstract class: the user class is inherited from this class to provide database opening, closing and other operation functions. 2. Sqlitedatabase database access class: perform operations such as inserting records and querying records in the database. 3. Sqlitecursor query structure operation class: used to access records in query results.

Create database

Suppose a database is used to store a table. Multiple records are saved in the table. Each record contains three fields: ID, name and age. Inherit and implement a class from sqliteopenhelper, and all operations on the database are encapsulated in this class.

Constructor:

To create a table in the oncreate function:

The final effect of the above function is to execute SQL statements:

Adding data to a table

There are two ways to add data to a table. One is to directly execute SQL instructions through the execsql function provided by sqlitedatabase; One is the insert function provided by sqlitedatabase, which encapsulates SQL statements for users' convenience.

1. Through execsql ("insert into mytable values ('idstr ',' namestr ', 20);"); You can insert a record. When there is an error in inserting a record, an exception will be thrown and needs to be handled. 2. Through the insert function: the insert function requires a value of contentvalues type, which is similar to HashMap. A key and a value are matched into a pair. Key is the field name.

If the key in V does not completely specify all fields, such as less name or less ID, whether the record can be successfully inserted depends on the field restrictions when the table is created. For example, if there is less name, the record will be inserted, but the value of the name field is null; If the ID is less, the record will not be inserted because not null is specified during creation

You can view the results through the following instructions:

Enter the shell after the ADB shell is executed;

cd /data/data/cn.demo.sql

SQLite3 database.db enters the SQLite operation interface.

. help can see the command help.

. dump can see the data in the database.

For ease of use, you can customize an insert function to pass in values through parameters, and the contentvalues setting is implemented inside the function.

Query records from tables

SQL statement: select column list from table where expr order by column list; Through the query() function of sqlitedatabase class:

Table name of the table where the record to be updated is located. Values the field value to update. Where clause where clause. Which records are updated. '?' in the where args where clause Replace string. When performing the update operation, if only some fields are assigned values, the fields that are not assigned values will remain unchanged after update.

To delete a record from a table: