Android (16) _ data storage and access _ SQLite database _ database creation and management

In addition to using files or SharedPreferences to store data, you can also choose to use the SQLite database to store data.

On the Android platform, an embedded relational database-SQLite is integrated. 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 any type of data to any field, regardless of the Data Type declared in this column.

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 parsing the create table statement, SQLite ignores the data type information following the field name in the create table statement. For example, the following statement ignores the type information of the name field:

Create table person (personid integer primary key autoincrement, name varchar (20 ))

SQLite can parse most standard SQL statements, such:
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

Use SQLiteOpenHelper to manage database versions

When writing database application software, we need to consider this problem: because the software we developed may be installed on mobile phones of hundreds of thousands of 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) generates a database table when using the software for the first time
OnUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) updates the database table structure when upgrading 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.

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 data. If the getWritableDatabase () method is used, 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.

 

DBOpenHelper

[Java] view plaincopyprint?
Package com. example. lession04_db.service;
 
Import android. content. Context;
Import android. database. sqlite. SQLiteDatabase;
Import android. database. sqlite. SQLiteOpenHelper;
Import android. util. Log;
 
Public class DBOpenHelper extends SQLiteOpenHelper {
// Database Name
Private static final String name = "CSDN. db ";
// Database version
Private static final int version = 1;
 
Public DBOpenHelper (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 );
Log. v ("DBSQLiteOpenHelper", "constructor ......");
}
 
// Method executed when the database is created for the first time
@ Override
Public void onCreate (SQLiteDatabase db ){
// ExecSQL to execute SQL statements
Db.exe cSQL ("create table person (personid integer primary key autoincrement, name varchar (20), age integer )");
 
Log. v ("DBSQLiteOpenHelper", "onCreate ...... create and execute once ");
}
 
// Method executed when the database version changes
@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){
Db.exe cSQL ("alter table person add account integer ");
Log. v ("DBSQLiteOpenHelper", "executed when the database version is updated ");
}
 
}

Package com. example. lession04_db.service;

Import android. content. Context;
Import android. database. sqlite. SQLiteDatabase;
Import android. database. sqlite. SQLiteOpenHelper;
Import android. util. Log;

Public class DBOpenHelper extends SQLiteOpenHelper {
// Database Name
Private static final String name = "CSDN. db ";
// Database version
Private static final int version = 1;

Public DBOpenHelper (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 );
Log. v ("DBSQLiteOpenHelper", "constructor ......");
}

// Method executed when the database is created for the first time
@ Override
Public void onCreate (SQLiteDatabase db ){
// ExecSQL to execute SQL statements
Db.exe cSQL ("create table person (personid integer primary key autoincrement, name varchar (20), age integer )");

Log. v ("DBSQLiteOpenHelper", "onCreate ...... create and execute once ");
}

// Method executed when the database version changes
@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){
Db.exe cSQL ("alter table person add account integer ");
Log. v ("DBSQLiteOpenHelper", "executed when the database version is updated ");
}

}

 

Create test class
[Java] view plaincopyprint?
Package com. example. lession04_db.test;
 
Import java. util. List;
 
Import com. example. lession04_db.domain.Person;
Import com. example. lession04_db.service.DBOpenHelper;
Import com. example. lession04_db.service.PersonService;
 
Import android. database. sqlite. SQLiteDatabase;
Import android. database. sqlite. SQLiteOpenHelper;
Import android. test. AndroidTestCase;
Import android. util. Log;
 
Public class DBTest extends AndroidTestCase {
//
Private static final String TAG = "DBTest ";
 
Public void testDBCreate () throws Exception {
DBOpenHelper dbOpenHelper = new DBOpenHelper (this. getContext ());
SQLiteDatabase db = dbOpenHelper. getWritableDatabase ();
}

Package com. example. lession04_db.test;

Import java. util. List;

Import com. example. lession04_db.domain.Person;
Import com. example. lession04_db.service.DBOpenHelper;
Import com. example. lession04_db.service.PersonService;

Import android. database. sqlite. SQLiteDatabase;
Import android. database. sqlite. SQLiteOpenHelper;
Import android. test. AndroidTestCase;
Import android. util. Log;

Public class DBTest extends AndroidTestCase {
//
Private static final String TAG = "DBTest ";

Public void testDBCreate () throws Exception {
DBOpenHelper dbOpenHelper = new DBOpenHelper (this. getContext ());
SQLiteDatabase db = dbOpenHelper. getWritableDatabase ();
}