Android using SQLite

About SQLite

Google for Andriod's larger data processing provides SQLite, he is in the storage, management, maintenance and other aspects of the excellent, the function is very strong. SQLite has the following features:

1. Lightweight

With SQLite you only need to bring a dynamic library, you can enjoy its full functionality, and the size of the dynamic library want to be small.

2. Independence

The core engine of the SQLite database does not need to rely on third-party software, nor does it require the so-called "install".

3. Isolation

All information in the SQLite database (such as tables, views, triggers, etc.) is contained within a folder for easy administration and maintenance.

4. Cross-platform

SQLite currently supports most of the operating systems, not the computer operating system more in the many mobile phone systems can also be run, such as: Android.

5. Multi-lingual interface

The SQLite database supports multiple language programming interfaces.

6. Security

The SQLite database implements independent transaction processing through exclusive and shared locks at the database level. This means that multiple processes can read data from the same database at the same time, but only one can write data.

SQLite usage in Android

First create the Database class

PublicClass DatabasehelperExtendsSqliteopenhelper {PrivateStaticFinal String db_name = "mydata.db";//Database namePrivateStaticFinalint version = 1;//Database versionPublicDatabasehelper (Context context) {Super (context, db_name,null// TODO auto-generated constructor Stub} @Override public void  OnCreate (Sqlitedatabase db) {String sql =" CREATE TABLE user (username varchar) NOT NULL , password varchar () not NULL); " public void Onupgrade (Sqlitedatabase db, Span style= "color: #0000ff;" >int oldversion, int NewVersion) {// TODO auto-generated method Stub}} 
  

Sqliteopenhelper Class Introduction

Sqliteopenhelper is a helper class for sqlitedatabase that manages the creation of databases and the updating of versions. It is generally established that a class inherits it and implements its OnCreate and Onupgrade methods.

Method Name	Method Description
Sqliteopenhelper (Context context,string name,sqlitedatabase.cursorfactory factory,int version)	Constructs a method, typically passing a database name to be created so the parameter
OnCreate (Sqlitedatabase db)	Called when a database is created
Onupgrade (sqlitedatabase db,int oldversion, int newversion)	Called when the version is updated
Getreadabledatabase ()	Create or open a read-only database
Getwritabledatabase ()	Create or open a read-write database

Here's how to call this

Create a database

The special place here is to implement the creation of a database by invoking the Getreadabledatabase () method of the Sqliteopenhelper class.

New Databasehelper (this); // This code is put in the activity class to use this NULL  = Database.getreadalbedatabase ();

The Sqlitedatabase class provides us with many methods, and the more common methods are as follows

(return value) method name	Method Description
(int) Delete (String table,string whereclause,string[] whereargs)	A convenient way to delete data rows
(long) Insert (String table,string nullcolumnhack,contentvalues values)	A convenient way to add data rows
(int) Update (string table, contentvalues values, String whereclause, string[] whereargs)	A convenient way to update data rows
(void) execsql (String sql)	Executes an SQL statement that can be a select or other SQL statement
(void) Close ()	Close the database
(Cursor) query (string table, string[] columns, string selection, string[] Selectionargs, String groupBy, string having, St Ring, String limit)	Queries the specified data table to return a data set with a cursor
(Cursor) rawquery (String sql, string[] selectionargs)	Run a pre-built SQL statement that returns a data set with a cursor (the biggest difference from the above statement is that it prevents SQL injection)

The addition and deletion of data can be realized by 2 ways.

The addition of data

1. Using the Insert method

New Contentvalues (); // instantiate a contentvalues to load the data to be inserted cv.put ("username", "Jack Johnson"); // Add user name cv.put (// add password db.insert ("user",null, CV); // Perform an insert operation

2. Use the Execsql method to achieve

String sql = "INSERT INTO user" (Username,password) VALUES (' Jack Johnson ', ' ilovepopmuisc ');//INSERT operation SQL statement Db.execsql ( SQL); // Execute SQL statement

deletion of data

There are also 2 ways to achieve

String whereclause = "username=?"; // conditions to remove  = {"Jack Johnson"}; // deleted condition Parameters db.delete ("user", Whereclause,whereargs); // Perform the delete

Implementation using the Execsql method

String sql = "Delete from user where username= ' Jack Johnson '"; // SQL statement for delete operation db.execsql (SQL); // perform a delete operation

Data Modification

Ditto, still 2 different ways

New Contentvalues (); // instantiation of Contentvaluescv.put ("Password", "Ihatepopmusic"); // add the fields and content you want to change String whereclause = "username=?"; // Modify Condition String[] Whereargs = {"Jack Johnson"}; // Modify the parameters of the condition db.update ("user", Cv,whereclause,whereargs); // Perform modifications

Implementation using the Execsql method

String sql = "Update [user] Set password = ' ihatepopmusic ' where username= ' Jack Johnson '"; // modified SQL statement db.execsql (SQL); // Perform modifications

data Query

The data query is more complex than the previous methods because the query comes with many conditions

Queries are implemented using query

 Public Cursor Query (string table, string[] columns, string selection, string[] Selectionargs, String groupBy, string having, stri Ng, String limit)

Description of each parameter:

• Table: Tables Name
• Colums: Array of column names
• Selection: Conditional clauses, equivalent to where
• Selectionargs: Parameter array for conditional statements
• GroupBy: Grouping
• Having: grouping conditions
• ORDER BY: Sort class
• Limit: Limits for paged queries
• Cursor: The return value, equivalent to the result set resultset

There are also a number of ways to target cursors (cursor)

Method Name	Method Description
GetCount ()	Total number of records
IsFirst ()	Determine if the first record
Islast ()	Determine if the last record
Movetofirst ()	Move to the first record
Movetolast ()	Move to the last record
Move (int offset)	Move to the specified record
MoveToNext ()	Move to scare a record
Movetoprevious ()	Move to previous record
Getcolumnindex (String columnName)	Gets the int type value of the specified column index

Implementation code

 Cursor C = db.query ("User", null , null , null , null , null , null ); //  query and get cursor  if  (C.movetofirst ()) {//  to determine if the cursor is empty   (int  I=0;i<c.getcount (); I++) { C.move (i);  //         move to the specified record         String username = c.getstring (c.getcolumnindex ("username" );    String Password  = c.getstring (C.getcolumnindex ("Password" )); }}

Query with parameters implemented by Rawquery

Cursor C = Db.rawquery ("SELECT * from user where username=?",new stirng[]{"Jack Johnson"}); if (Cursor.movetofirst ()) {    = c.getstring (C.getcolumnindex ("password"));}

Finally, describe the use of transactions

 db.begintransaction (); //  try  {  bulk processing operations  for   (Collection c:colls) {Insert (DB, C    ); } db.settransactionsuccessful ();     //  Set the transaction to succeed, do not set automatically rollback does not commit.  //  No between settransactionsuccessful and Endtransaction   Any database operation }catch   (Exception e) { Mylog.printstacktracestring (e);}  finally  {db.endtransaction ();  //  processing complete } 

Android using SQLite