General operation of the Android SQLite database

1.Sqlite Introduction

SQLite is a lightweight database, it is contained in a relatively small C library, it is designed to be embedded, because it occupies very little resources, it may only need hundreds of K of memory, and support Windows/linux/unix and so on the mainstream operating system, At the same time can be combined with a lot of programming languages, such as: c#/java/php, etc., so in the embedded device is particularly popular, which is precisely in line with the development requirements of Android, so in Android development is often used in the database.

2. Using Sqlite, it is generally necessary to have a sqliteopenhelper to help create the database and connect to the database.

eg:

public class Databasehelper extends Sqliteopenhelper {public static final String DBNAME = "cbg_download.db";p ublic static Final int VERSION = 1;//Public Databasehelper (context context) {////must call the constructor in the parent class via Super//super (Context,dbname,null,     VERSION)//}//Create multiple database connections public databasehelper (Context context,string name) {this (context,name,version);     } public Databasehelper (context context,string Name,int version) {This (context, name,null,version);  }//In Sqliteoepnhelper subclasses, you must have the constructor public Databasehelper (context context, String name, cursorfactory factory, int     Version) {//The constructor in the parent class must be called through Super super (context, name, Factory, version); } @Overridepublic void OnCreate (Sqlitedatabase db) {//exists database is not called. Perform the initial operation Db.execsql ("CREATE TABLE IF not EXISTS priasetable (ID integer PRIMARY key autoincrement,clickid varchar (100), Clicktype varchar (+), Clickcount INTEGER) "); @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion,int newversion) {//TODO auto-generated method Stubdb.execsql ("DROP TABLE IF EXISTS filedownlog"); onCreate (db);}} 

Attention:

(1) databasehelper inherit sqliteopenhelper. Must achieve 3 points,

Constructors: For initializing databases, creating databases

public void OnCreate (Sqlitedatabase db): Used to create a table. When you tune Getreadabledatabase or getwritabledatabase, you determine whether the specified database exists, does not exist, Sqlitedatabase.create is created, and OnCreate executes only when the database is first created

Public void Onupgrade (sqlitedatabase db, int oldversion, int newversion): for database update operations

3.//single-case mode Dao class, do a class to manipulate the data table

eg

Package Cdv.cq.mobilestation.activity.praise;import Android.content.context;import Android.database.cursor;import Android.database.sqlite.sqlitedatabase;import android.widget.toast;//Singleton mode public class DAO {private static DAO DAO = Null;private Context Context;private Dao (context contex) {this.context = Contex;} public static DAO getinstance (context context) {if (dao = = null) {DAO = new dao (context);} return DAO;} Connection Database public Sqlitedatabase getconnection () {sqlitedatabase sqlitedatabase = null;try {sqlitedatabase = new DatabaseHel Per (context, databasehelper.dbname). Getreadabledatabase ();} catch (Exception e) {e.printstacktrace ();} return sqlitedatabase;} public void CreateTable (Sqlitedatabase sqlitedatabase) {sqlitedatabase.execsql ("CREATE TABLE IF not EXISTS priasetable ( ID integer PRIMARY key autoincrement,clickid varchar (+ clicktype varchar), clickcount integer) "); public synchronized int Ishasinfors (string clickid, String clicktype) {sqlitedatabase database = getconnection (); int ID =-1; Cursor cursor = null;try {cursor = database.query ("priasetable", NULL, "Clickid=? And clicktype=? ", new string[] {clickid,clicktype}, NULL, NULL, NULL), if (Cursor.movetofirst ()) {id = cursor.getint (0);} } catch (Exception e) {e.printstacktrace ();} finally {if (null! = database) {database.close ();} if (null! = cursor) {cursor.close ();}} return ID;} Add public synchronized void Insert (String clickid, String Clicktype,int count) {sqlitedatabase database = getconnection () ;//int id = dao.ishasinfors (clickid,clicktype);//if (id = = 0) {//Contentvalues CV = new Contentvalues ();//Cv.put ("Clicki D ", clickid);//Cv.put (" Clicktype ", clicktype);//Cv.put (" Clickcount ", 1);//Database.insert (" priasetable ", NULL, CV) ;//String SQL =//"INSERT into priasetable (Clickid,clicktype,clickcount) VALUES (?,?,?)"; /object[] Info ={clickid,clicktype, "1"};//database.execsql (Sql,info);//}//else{////change data operation////}try {String sql = "ins ert into priasetable (clickid,clicktype,clickcount) VALUES (?,?,?) "; O Bject[]info = {Clickid, Clicktype, Count};d atabase.execsql (sql, info);} catch (Exception e) {e.printstacktrace ();} finally {if (null! = database) {database.close ();}}} Change public synchronized void update (int Id, Boolean isadd) {sqlitedatabase database = getconnection (); int count = 0;try{cu Rsor curor = Database.rawquery ("select * from priasetable where id =?", new String[]{string.valueof (ID)}); if ( Curor.movetofirst ()) Count = Curor.getint (3), if (isadd) {String sql = "Update priasetable set clickcount=? WHERE id =? "; O Bject[] info = {++count,id};d atabase.execsql (sql,info);//This is open on a new thread, Toast can only be modified in the main thread of UI//toast.maketext (context, " Likes plus 1 success ", 2*1000). Show ();} else{string sql = "Update priasetable set clickcount=?" WHERE id =? "; O Bject[] info = {--count,id};d atabase.execsql (Sql,info);//toast.maketext (context, "likes minus 1 success", 2*1000). Show ();}} catch (Exception e) {e.printstacktrace ();} Finally{if (Null! = database) {database.close ();}}} Enumeration of likes public synchronized int querypraise (String ID) {sqlitedatabase database = GetcoNnection (); int count = 0;try{cursor Curor = Database.rawquery ("Select from priasetable where id =?", new String[]{id}); whi Le (Curor.movetofirst ()) Count = Curor.getint (4);} catch (Exception e) {e.printstacktrace ();} Finally{if (Null! = database) {database.close ();}} return count;}}

Attention:

(1) The table column number in the SQLite database starts from 0,
Eg:cursor curor = Database.rawquery ("select * from priasetable where id =?", new String[]{string.valueof (ID)});
while (Curor.movetofirst ())
Count = Curor.getint (3); Altogether 4 columns

(2) A DAO class Singleton mode can be used to manipulate the tables in the database very well.

(3) The function of adding and checking is the same as SQL Server statement, but note that there is no need to remember too many methods of Android

additions and deletions:: Database.execsql (SQL, info); Working directly with the database

Query by: database.query (sql, info) use the Query() method to directly return a cursor for related operations

Android SQLite Database general operations