Android SQLite Database Operation example

SQLite Introduction SQLite is a very popular embedded database, it supports the SQL language, and only use very little memory to have good performance. In addition, it is open source and can be used by anyone.
SQLite consists of several components: The SQL compiler, the kernel, the backend, and the attachments. SQLite makes it easier to debug, modify, and extend SQLite's kernel by leveraging virtual machines and virtual database engines (VDBE).
Data types supported by SQLite reference link: http://blog.csdn.net/wzy_1988/article/details/36005947
Android integrates SQLite at runtime (run-time), so each Android application can use the SQLite database. For developers who are familiar with SQL, using SQLite in Android development is fairly straightforward. However, because JDBC consumes too much system resources, JDBC is not suitable for memory-constrained devices such as phones. As a result, Android provides some new APIs to work with the SQLite database.
The database is stored under the/data/data/Project package name/databases/directory.
Using SQLite database activity in Android development can use content provider or service to access a database.
Creating a database Android does not automatically provide a database. To use SQLite in an Android application, you must create your own database, and then create tables, indexes, and populate the data. Android provides a sqliteopenhelper to help you create a database that you can easily create by inheriting the Sqliteopenhelper class. The Sqliteopenhelper class encapsulates the logic used to create and update databases, based on the needs of the development application. Sqliteopenhelper, you need to implement at least three methods:

• constructor to invoke the constructor of the parent class Sqliteopenhelper. This method requires four parameters: context, database name, an optional cursor factory (usually null), an integer representing the version of the database model you are using.
• The OnCreate () method, which requires a Sqlitedatabase object as a parameter, populates the table and initializes the data as needed.
• The Onupgrade () method, which requires three parameters, a Sqlitedatabase object, an old version number, and a new version number, so you can clearly see how to transform a database from the old model into a new one.

The following code shows how to inherit 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";p ublic static final String table_name = "category";p rivate static final string database_name = "Category . db ";p rivate static final int database_version = 1;private static final String database_create =" CREATE TABLE IF not Exis TS "+ table_name +" ("+ column_id+" INTEGER PRIMARY KEY AutoIncrement, "+" FID text, token text, cid text, CNAME text ) ";p ublic 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" + Tab Le_name); onCreate (db);}}

Additions and deletions to the database because SQLite supports standard SQL statements, so we can use standard SQL statements to change the database, recommend using the placeholder SQL statement, looks more refreshing, here is My code example:

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 (C Ategorydbmanager.class) {if (Categorydbmanager = = null) {Categorydbmanager = new Categorydbmanager (context);}}} return Categorydbmanager;} Public Sqlitedatabase Getdb () {Sqlitedatabase db = Mydbhelper.getwritabledatabase (); Db.isdblockedbycurrentthread ()) {}return db;} public void Insertlists (string token, string fid, list<platecategorydata> lists) {//Open writable database sqlitedatabase db = getd B (); for (Platecategorydata pd:lists) {//Execute SQL statement, replace placeholder db.execsql ("insert into" + Mydbhelper.table_name + "(CID, CNAME, FID, token) VALUES (?,?,?,?) ", new object[] {Pd.getid (), Pd.getname (), FID, token});} Release resource Db.close ();} Public arraylist<platecategorydata> getlists (STring FID, String token) {arraylist<platecategorydata> datas = new arraylist<platecategorydata> (); Sqlitedatabase db = Getdb ();//execute original query, get cursorstring querysql = "Select CID, CNAME from" + Mydbhelper.table_name + "wher E FID =? and token =? ";  cursor cursor = db.rawquery (querysql, new string[] {FID, token});//move cursor to First data (no data return false) if (Cursor.movetofirst ()) {//While determining if there is a next data do {platecategorydata PD = new Platecategorydata (cursor.getstring (Cursor.getcolumnindex ("CID")), Cursor.getstring (Cursor.getcolumnindex ("CNAME")));d Atas.add (PD);} while (Cursor.movetonext ());} Cursor.close ();d b.close (); return datas;} public void Updatelists (string fid, string token, arraylist<platecategorydata> datas) {sqlitedatabase db = Getdb (); for (Platecategorydata pd:datas) {String sql = "Update" + mydbhelper.table_name+ "Set cid =?, CNAME =?") where FID =? and token =? "; Db.execsql (SQL, new object[] {Pd.getid (), Pd.getname (), FID, token});} Db.close ();} public void Deletelists (string FID, String token) {Sqlitedatabase db = Getdb ();d b.execsql ("delete from" + Mydbhelper.table_name + "where fid =?"). and token =? ", new object[] {fid,token});d b.close ();} public void Closedb () {Sqlitedatabase db = Getdb (); if (Db.isopen ()) {mydbhelper.close ();d b.close ();}}}

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