Android-embedded SQLite

An embedded relational database Integrated on the Android platform. SQLite3 supports NULL, INTEGER, and REAL (floating point

TEXT (string TEXT) and BLOB (binary object) data types. Although only five types are supported

Sqlite3 also accepts data types such as varchar (n), char (n), decimal (p, s), but will be transferred during operation or storage

The corresponding five data types.

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.

But the field defined as integer primary key can only store 64-bit integers.

Will produce an error.

In addition, SQLite ignores the data type information after the field name when parsing the create table statement.

SQLite can parse most standard SQL statements, such:

Query statement: select * from... where group by... having... order by sort clause

SQLite pages are the same as mysql pages:

... Limit 5 offset 3 | limit 3, 5

When using the database for the first time, you need to create tables and initialize some information. During the upgrade, You need to modify the table information. android provides SQLiteOpenHelper to complete this function.

The onCreate (SQLiteDatabase db) method is used to create a database when it is used for the first time, and the onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) method is used to update a database when version changes are detected.

The getWriteableDatabase () and getReadableDatabase () methods obtain a SQLiteDatabase instance used to operate the database.

GetWriteableDatabase () method 1: Open the database in read/write mode. Once the disk space of the database is full, the database can only read but cannot write. If getWriteableDatabase () is used to open the database, an error occurs. The getReadableDatabase () method opens the database in Read mode.

Public class DatabaseHelper extends SQLiteOpenHelper {

Private static final String name = "itcast"; // Database name

Private static final int version = 1; // database version

Public DatabaseHelper (Context context ){

Super (context, name, null, version );

}

Public void onCreate (){

// Drop table if exists MERs

// Create table if not exists MERs (...

Db.exe cSQL ("create table if not exists person (personid integer primary key autoincrement, name varchar (20), age INTEGER )");

}

Public void onUpgrade (SQLiteDatabase db, int oldVersion, intnewVersion ){

Db.exe cSQL ("alter table person ADD phone VARCHAR (12) NULL ");

}

}

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

Use SQLiteDataBase

SQLiteDatabase db = ....;

Db.exe cSQL ("insert into person (name, age) values (?,?) ", New Object [] {...});

Db. close ();

// Query operation

Cursor cursor = db. rawQuery ("select * from person", null );

While (cursor. moveToNext ){

Int personid = cursor. getInt (0 );

String name = cursor. getString (1 );

Int age = cursor. getInt (2 );

Cursor. close ();

Db. close ();

}

Cursor. close ();

Db. close ();

SQLiteDatabase db = databaseHelper. getWritableDatabase ();

ContentValues values = new ContentValues ();

Values. put ("name", "Chuanzhi podcast ");

.....

Long rowid = db. insert ("person", null, values );

// Delete

SQLiteDatabase db = databaseHelper. getWritableDatabase ();

Db. delete ("person", "personid <? ", New String [] {" 2 "}"> ");

Db. close ();

// Update

SQLiteDatabase db = databaseHelper. getWritableDatabase ();

ContentValues values = new ContentValues ();

// Key is the field name and value is the value

Values. put ("name", "Chuanzhi podcast ");

Db. update ("person", values, "personid =? ", New String [] {" 1 "});

Db. close ();

Note: After the getWritableDatabase () or getReadableDatabase () method is called for the first time, SQLiteOpenHelper

The current SQLiteDatabase instance will be cached, And the SQLiteDatabase instance will maintain the database open State normally. Therefore, when you do not need the SQLiteDatabase instance, please call the close () method in time to release resources, once the SQLiteDatabase instance is cached, the same instance is obtained by calling the getWritableDatabase () or getReadableDatabase () method multiple times.

Transaction processing:

SQLiteDatabase db = ...;

Db. beginTransaction ();

Try {

Db.exe cSQL (...)

Db.exe cSQL (...);

// Mark successfully set

Db. setTransactionSuccessful ();

} Catch (){

....

} Finally {

Db. endTransaction (); // The transaction identifier determines whether to commit or roll back the transaction.

}

Db. close (); www.2cto.com

Note: When creating a table, only integer type can be used, and int cannot be used as the primary key; otherwise, autoincrement is not supported.

Create table MERs (id integer primary key autoincrement, name text );

Author: to1297488504