Android SQLite database operation example

SQLite introduces SQLite as a very popular embedded database. It supports the SQL language and has good performance only by using a small amount of memory. In addition, it is open-source and can be used by anyone.
SQLite consists of the following components: SQL Compiler, kernel, backend, and attachment. SQLite makes debugging, modification, and expansion of SQLite kernel more convenient by using virtual machines and virtual database engine (VDBE.
SQLite supported data types reference link: http://blog.csdn.net/wzy_1988/article/details/36005947
Android integrates SQLite at runtime, so every Android application can use the SQLite database. For developers familiar with SQL, using SQLite in Android development is quite simple. However, JDBC consumes too much system resources, so JDBC is not suitable for memory-constrained devices such as mobile phones. Therefore, Android provides some new APIs to use the SQLite database.
The database is stored in the/data/project package name/databases/directory.
When using SQLite database Activity in Android development, you can use Content Provider or Service to access a database.
Database creation is not automatically provided by Android. To use SQLite in Android applications, you must create your own database, and then create tables, indexes, and data filling. Android provides SQLiteOpenHelper to help you create a database. As long as you inherit the SQLiteOpenHelper class, you can easily create a database. The SQLiteOpenHelper class encapsulates the logic used to create and update databases based on the needs of application development. The subclass of SQLiteOpenHelper must implement at least three methods: constructor, which calls the constructor of the parent class SQLiteOpenHelper. This method requires four parameters: context, database name, an optional cursor Factory (usually NULL), and an integer representing the database model version you are using. OnCreate () method, which requires a SQLiteDatabase object as the parameter, fill in the table and initialization data for this object as needed. OnUpgrade () method, which requires three parameters: A SQLiteDatabase object, an old version number and a new version number, in this way, you can understand how to transform a database from an old model to a new model.
The following code inherits SQLiteOpenHelper to create a database:

import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class MyDBHelper extends SQLiteOpenHelper {private static final String COLUMN_ID = "_id";public static final String TABLE_NAME = "category";private static final String DATABASE_NAME = "category.db";private static final int DATABASE_VERSION = 1;private static final String DATABASE_CREATE = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "(" + COLUMN_ID+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + " fid TEXT, token TEXT, cid TEXT, cname TEXT)";public CategoryDBHelper(Context context) {super(context, DATABASE_NAME, null, DATABASE_VERSION);}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL(DATABASE_CREATE);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {db.execSQL("DROP TABLE IF EXISTS" + TABLE_NAME);onCreate(db);}}

Since SQLite supports standard SQL statements, we can use standard SQL statements to add, delete, modify, and query databases. We recommend that you use placeholder SQL statements, which looks more refreshing, the following is an example of my code:

Public class CategoryDBManager {private MyDBHelper myDBHelper; private static CategoryDBManager categoryDBManager = null; private CategoryDBManager (Context context) {myDBHelper = new MyDBHelper (context );} /*** Singleton mode */public static CategoryDBManager getInstance (Context context) {if (categoryDBManager = null) {synchronized (CategoryDBManager. class) {if (categoryDBManager = null) {categoryDBManager = new CategoryDBManager (context) ;}} return categoryDBManager;} public SQLiteDatabase getDB () {SQLiteDatabase db = myDBHelper. getWritableDatabase (); while (db. isDbLockedByCurrentThread () {} return db;} public void insertLists (String token, String fid, List
 
  
Lists) {// open the writable database SQLiteDatabase db = getDB (); for (PlateCategoryData pd: lists) {// execute the SQL sentence to replace the character db.exe cSQL ("insert into" + MyDBHelper. TABLE_NAME + "(cid, cname, fid, token) values (?, ?, ?, ?) ", New Object [] {pd. getId (), pd. getName (), fid, token}) ;}// release resource db. close () ;}public ArrayList
  
   
GetLists (String fid, String token) {ArrayList
   
    
Datas = new ArrayList
    
     
(); SQLiteDatabase db = getDB (); // execute the original query to obtain cursorString querySql = "select cid, cname from" + MyDBHelper. TABLE_NAME + "where fid =? And token =? "; Cursor cursor = db. rawQuery (querySql, new String [] {fid, token}); // move cursor to the first data (false if no data is returned) if (cursor. moveToFirst () {// while to determine whether there is any next data do {PlateCategoryData pd = new PlateCategoryData (cursor. getString (cursor. getColumnIndex ("cid"), cursor. getString (cursor. getColumnIndex ("cname"); datas. add (pd);} while (cursor. moveToNext ();} cursor. close (); db. close (); return datas;} public void updateLists (String fid, String token, ArrayList
     
      
Datas) {SQLiteDatabase db = getDB (); for (PlateCategoryData pd: datas) {String SQL = "update" + MyDBHelper. TABLE_NAME + "set cid = ?, Cname =? Where fid =? And token =? "Mongodb.exe cSQL (SQL, new Object [] {pd. getId (), pd. getName (), fid, token});} db. close ();} public void deleteLists (String fid, String token) {SQLiteDatabase db = getdb(mongomongodb.exe cSQL ("delete from" + MyDBHelper. TABLE_NAME + "where fid =? And token =? ", New Object [] {fid, token}); db. close ();} public void closeDB () {SQLiteDatabase db = getDB (); if (db. isOpen () {myDBHelper. close (); db. close ();}}}
     
    
   
  
 

Reference [1] http://www.ibm.com/developerworks/cn/opensource/os-cn-sqlite/