This article describes the operation method of Android SQLite database. Share it for your reference, as follows:
SQLite and Android
Introduction to SQLite
SQLite is a very popular embedded database that supports SQL language and has good performance using only a very little memory. Also, it is open source and can be used by anyone.
SQLite consists of the following components: SQL compiler, kernel, backend and attachments. SQLite makes debugging, modifying and extending the kernel of SQLite by leveraging virtual machines and virtual database engines (VDBE).
Data types supported by SQLite include:
1. TEXT (similar to Java's String)
2. INTEGER (similar to Java's long)
3. REAL (similar to Java's Double)
For more knowledge about SQLite data types, please refer to the previous related articles:Detailed explanation of data types in SQLite
SQLite In Android
Android integrates SQLite at runtime, so using SQLite database in Android does not require the installation process and obtain database usage permissions. You only need to define statements to create and update the database, and the rest will be handled by the Android platform for you.
Operating SQLite databases usually means operating the file system. This operation is still relatively time-consuming, so it is recommended to execute the database operations asynchronously.
Your application creates a SQLite database, and the data is stored by default in /DATA/data/APP_NAME/databases/FILENAME. Here DATA is the value returned by the() method, and APP_NAME is your application package name
Using SQLite database in Android development
Activity can access a database using Content Provider or Service.
Create a database
Android does not automatically provide databases. To use SQLite in Android applications, you must create your own database, then create tables, indexes, and populate data. Android provides a SQLiteOpenHelper to help you create a database. You can easily create a database by inheriting the SQLiteOpenHelper class.
The SQLiteOpenHelper class encapsulates the logic used to create and update databases according to the needs of developing applications. A subclass of SQLiteOpenHelper requires at least three methods to implement:
Constructor, call the constructor of the parent class SQLiteOpenHelper. This method takes four parameters: context, database name, an optional cursor factory (usually NULL), an integer representing the version of the database model you are using.
onCreate() method, it requires a SQLiteDatabase object as a parameter, fill the table and initialize the data on this object as needed.
onUpgrade() method, it requires three parameters, a SQLiteDatabase object, an old version number and a new version number, so you can understand how to transform a database from an old model to a new model.
First, define the table structure that needs to be created and use classes to abstract it. Here is an example to define a Sina meager account class:
public class AccountTable {
public static final String TABLE_NAME = "account_table";
public static final String UID = "uid";
public static final String USERNAME = "username";
public static final String USERNICK = "usernick";
public static final String AVATAR_URL = "avatar_url";
public static final String PORTRAIT = "portrait";
public static final String OAUTH_TOKEN = "oauth_token";
public static final String OAUTH_TOKEN_SECRET = "oauth_token_secret";
public static final String INFOJSON = "json";
}
The following code shows how to inherit SQLiteOpenHelper to create a database. It is recommended to use a singleton class:
import ;
import ;
import ;
import .*;
class DatabaseHelper extends SQLiteOpenHelper {
private static DatabaseHelper singleton = null;
private static final String DATABASE_NAME = "";
private static final int DATABASE_VERSION = 16;
static final String CREATE_ACCOUNT_TABLE_SQL = "create table " + AccountTable.TABLE_NAME
+ "("
+ + " integer primary key autoincrement,"
+ AccountTable.OAUTH_TOKEN + " text,"
+ AccountTable.OAUTH_TOKEN_SECRET + " text,"
+ + " text,"
+ + " text,"
+ + " text,"
+ AccountTable.AVATAR_URL + " text,"
+ + " text"
+ ");";
DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
(CREATE_ACCOUNT_TABLE_SQL);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
switch (oldVersion) {
default:
deleteAllTable(db);
onCreate(db);
}
}
public static synchronized DatabaseHelper getInstance() {
if (singleton == null) {
singleton = new DatabaseHelper(());
}
return singleton;
}
private void deleteAllTable(SQLiteDatabase db) {
("DROP TABLE IF EXISTS " + AccountTable.TABLE_NAME);
}
}
Add, delete, modify and check database
Because SQLite supports standard SQL statements, we can use standard SQL statements to add, delete, modify and search the database. It is recommended to use placeholder SQL statements, which look more refreshing. Here is my code example:
package ;
public class DatabaseManager {
private static DatabaseManager singleton = null;
private SQLiteDatabase wsd = null;
private SQLiteDatabase rsd = null;
private DatabaseManager() {
}
public static DatabaseManager getInstance(Context context) {
if (singleton == null) {
synchronized () {
if (singleton == null) {
DatabaseHelper databaseHelper = (context);
singleton = new DatabaseManager();
= ();
= ();
}
}
}
return singleton;
}
public void initAccountTable(List<AccountData> listDatas) {
if (listDatas == null || () <= 0) {
return;
}
();
try {
for (AccountData data : listDatas) {
insertAccountTable(data);
}
();
} finally {
();
}
}
private void insertAccountTable(AccountData accData) {
String sql = "insert into " + AccountTable.TABLE_NAME + "(" + + ", "
+ + ", " + AccountTable.AVATAR_URL + ", " + + ", "
+ AccountTable.OAUTH_TOKEN + ", " + AccountTable.OAUTH_TOKEN_SECRET + ", " +
+ " " + ")" + " values(?, ?, ?, ?, ?, ?, ?)";
(sql,
new Object[] { (), (), (), (),
(), (), (), (), });
}
public List<AccountData> getAccountDatas() {
List<AccountData> listDatas = selectAccountData();
return listDatas;
}
private List<AccountData> selectAccountData() {
List<AccountData> listAccountData = new ArrayList<AccountData>();
String querySql = "select " + + ", " + + ", " + AccountTable.AVATAR_URL + ", " + + ", " + AccountTable.OAUTH_TOKEN + ", " + AccountTable.OAUTH_TOKEN_SECRET + ", " + " " + " from " + BbsForumsTable.TABLE_NAME;
Cursor cursor = (querySql, null);
if (()) {
do {
AccountData data = new AccountData();
((()));
((()));
(((AccountTable.AVATAR_URL)));
((()));
(((AccountTable.OAUTH_TOKEN)));
(((AccountTable.OAUTH_TOKEN_SECRET)));
((()));
(data);
} while (());
}
();
return listAccountData;
}
public void deleteBbsDatas() {
String delSql = "delete from " + AccountTable.TABLE_NAME;
(delSql);
}
}
Things (DBTransaction)
Database cache is often used in Android, especially in case of data inconsistency in wifi, when there is data inconsistency, the cached data needs to be updated. At this time, things need to be processed to ensure the integrity and speed of operations. An example of using SQLite to ensure transaction integrity in Android is as follows:
public void initAccountTable(List<AccountData> listDatas) {
if (listDatas == null || () <= 0) {
return;
}
();
try {
for (AccountData data : listDatas) {
insertAccountTable(data);
}
();
} finally {
();
}
}
Start the transaction through beginTransaction(), endTransaction() ends the transaction, and set the transaction execution success flag setTransactionSuccessful().
For more information about Android related content, please check out the topic of this site:Summary of Android's SQLite database skills》、《Android database operation skills summary》、《Android programming activity operation skills summary》、《Android file operation skills summary》、《Android development introduction and advanced tutorial》、《Android resource operation skills summary》、《Android View View Tips Summary"and"Android control usage summary》
I hope this article will be helpful to everyone's Android programming design.