Andriod SQLite Detailed Reprint

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

public class DatabaseHelper extends SQLiteOpenHelper {    private static final String DB_NAME = "mydata.db"; //数据库名称    private static final int version = 1; //数据库版本        public DatabaseHelper(Context context) {        super(context, DB_NAME, null, version);        // TODO Auto-generated constructor stub    }    @Override    public void onCreate(SQLiteDatabase db) {        String sql = "create table user(username varchar(20) not null , password varchar(60) not null );";                  db.execSQL(sql);    }    @Override    public void onUpgrade(SQLiteDatabase db, 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.

123	DatabaseHelper database = newDatabaseHelper(this);//这段代码放到Activity类中才用thisSQLiteDatabase db = null;db = 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

123	ContentValues cv = newContentValues();//实例化一个ContentValues用来装载待插入的数据cv.put("username","Jack Johnson");//添加用户名cv.put("password","iLovePopMusic"); //添加密码db.insert("user",null,cv);//执行插入操作

2. Use the Execsql method to achieve

12	String sql = "insert into user(username,password) values (‘Jack Johnson‘,‘iLovePopMuisc‘);//插入操作的SQL语句db.execSQL(sql);//执行SQL语句

Deletion of data

There are also 2 ways to achieve

123	String whereClause = "username=?";//删除的条件String[] whereArgs = {"Jack Johnson"};//删除的条件参数db.delete("user",whereClause,whereArgs);//执行删除

Implementation using the Execsql method

12	String sql = "delete from user where username=‘Jack Johnson‘";//删除操作的SQL语句db.execSQL(sql);//执行删除操作

Data modification

Ditto, still 2 different ways

12345

contentvalues CV = new contentvalues (); //instantiation contentvalues cv.put ( "password" "Ihatepopmusic" //add the fields and contents you want to change string whereclause = "Username=?" //Modify condition string[] Whereargs = { }; //Modify the parameters of the condition db.update ( ,CV, Whereclause,whereargs); //perform modifications

Implementation using the Execsql method

12	String sql = "update [user] set password = ‘iHatePopMusic‘ where username=‘Jack Johnson‘";//修改的SQL语句db.execSQL(sql);//执行修改

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 havin G, string by-clause, 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)

TD valign= "Top" to determine whether the last record tr> /table>

method Name	method description
getcount ()	Total Number of records
isFirst ()	determine whether the first record
islast ()
movetofirst ()	move to the first record
movetolast ()	move to last record
m Ove (int offset)	move to the specified record
movetonext ()	move to scare a record
movetoprevious ()	move to previous record
getcolumnindex (String columnName)

Implementation code

12345678

Cursor c = db.query("user",null,null,null,null,null,null);//查询并获得游标if(c.moveToFirst()){//判断游标是否为空    for(int i=0;i<c.getCount();i++){        c.move(i);//移动到指定记录        String username = c.getString(c.getColumnIndex("username");        String password = c.getString(c.getColumnIndex("password"));    }}

Query with parameters implemented by Rawquery

1234	Cursor c = db.rawQuery("select * from user where username=?",newStirng[]{"Jack Johnson"});if(cursor.moveToFirst()) {    String password = c.getString(c.getColumnIndex("password"));}

Andriod SQLite Detailed Reprint