SQLite's detailed use in Android

Disclaimer: This article was reproduced from: http://www.cnblogs.com/weixing/archive/2013/08/02/3232994.html

Thanks to the "no-hate stars," the accumulation of knowledge, which is a passion for learning people is a huge help ....

1. Introduction to SQLite

SQLite is a lightweight database, is to comply with acid-related database management system, it is designed to be embedded, and has been used in many embedded products, it occupies very low resources, in embedded devices, may only need hundreds of K of memory is enough.

It can support Windows/linux/unix and other mainstream operating systems, and can be combined with many programming languages, such as Tcl, PHP, Java, C + +,. NET, as well as the ODBC interface, it is faster than the two open source world-famous database management systems, such as Mysql and PostgreSQL.

Features of 2.SQLite:

-Lightweight

Unlike the database software for SQLite and C/s mode, it is a database engine within the process, so there is no client and server for the database. With SQLite, you can use only one of its dynamic libraries to enjoy its full functionality.

and the dynamic library size is also very small, in version 3.6.11 for example, under Windows 487KB, Linux under 347KB.

-No "install" required

SQLite's core engine itself does not rely on third-party software, and it does not require "installation" to use it. A little bit like that green software.

-Single File

All information in the database, such as tables, views, and so on, is contained within a single file. This file can be freely copied to other directories or other machines.

-cross-platform/portability

In addition to the mainstream operating system Windows,linux, SQLite also supports some other infrequently used operating systems.

-Weak type of field

Data in the same column can be of different types

-Open Source

3.SQLite Data types

Regular data uses fixed static data types, and SQLite uses Dynamic data types, which are automatically judged based on the value of the deposit. SQLite has the following five commonly used data types:

Null: This value is a null value

VARCHAR (N): A string whose length is not fixed and its maximum length is n, and N cannot exceed 4000.

CHAR (n): string with a fixed length of n, N cannot exceed 254.

Integer: The value is identified as an integer and can be stored sequentially as 1,2,3,4,5,6,7,8, depending on the size of the value.

REAL: All values are floating numeric values that are stored as 8-byte IEEE floating tag ordinals.

Text: The value is a literal string, stored using database encoding (TUTF-8, utf-16be, or Utf-16-le).

Blob: The value is a BLOB data block that is stored in the input data format. How to enter on how to store it without changing the format.

DATA: Contains the year, month, date.

Time: Contains hours, minutes, seconds.

Two. Introduction of Sqlitedatabase

Android provides an API to create and be a database with SQLite. Sqlitedatabase represents a database object and provides some methods for manipulating the database. With the Sqlite3 tool in the Android SDK directory, we can use it to create databases, create tables, and execute some SQL statements. The following are common methods of sqlitedatabase.

Common methods of Sqlitedatabase

Method name

Method represents meaning

Openorcreatedatabase (String path,sqlitedatabase.cursorfactory Factory)

Open or create a database

Insert (String table,string nullcolumnhack,contentvalues values)

Insert a record

Delete (String table,string whereclause,string[] whereargs)

Delete a record

Query (String table,string[] columns,string selection,string[] selectionargs,string groupby,string having,String )

Query a record

Update (String table,contentvalues values,string whereclause,string[] whereargs)

Modify a record

Execsql (String sql)

Execute an SQL statement

Close ()

Close the database

Google's name for these methods is very graphic. For example Openorcreatedatabase, we can see from the literal English meaning that this is a way to open or create a database

1. Open or create a database

Use Sqlitedatabase's static method Openorcreatedatabase (String path,sqlitedatabae.cursorfactory Factory) in Android to open or create a database.

It automatically detects the existence of the database, opens it if it exists, creates a database if it does not exist, and then returns an Sqlitedatabase object if the creation succeeds, or throws an exception filenotfoundexception.

Here is the code to create the database named "Stu.db":

Openorcreatedatabase (String path,sqlitedatabae.cursorfactory Factory)

Parameter 1 path to database creation

Parameter 2 is generally set to null.

Eg:db=sqlitedatabase.openorcreatedatabase ("/data/data/com.lingdududu.db/databases/stu.db", null);

2. Create a table

The steps to create a table are simple:

-Write the SQL statement that created the table

-Call Sqlitedatabase's Execsql () method to execute the SQL statement

The following code creates a user table with properties listed as: ID (primary key and auto increment), sname (student name), Snumber (school number)

private void CreateTable (Sqlitedatabase db) {//CREATE TABLE SQL statement String stu_table= "CREATE TABLE usertable (_id integer PRIMARY Key A Utoincrement,sname text,snumber text) "; Execute SQL Statement db.execsql (stu_table); }

3. Inserting data

Contentvalues is a storage mechanism that is commonly used in database operations. Only basic types can be stored.

There are two methods of inserting data:

①sqlitedatabase Insert (String table,string nullcolumnhack,contentvalues values) method,

Parameter 1 table name,

Parameter 2 default value for empty column

Parameter 3 A map of the contentvalues type that encapsulates the column name and column values;

private void Insert (Sqlitedatabase db) {//Instantiate constant value contentvalues cvalue = new Contentvalues ();         Add user name Cvalue.put ("Sname", "xiaoming");        Add Password Cvalue.put ("Snumber", "01005");  Call the Insert () method to insert the data Db.insert ("Stu_table", Null,cvalue); }

② writes SQL statements that insert data, calling Sqlitedatabase's Execsql () method directly to execute

private void Insert (Sqlitedatabase db) {//INSERT data SQL statement String stu_sql= "INSERT into Stu_table (Sname,snumber) Val         UEs (' xiaoming ', ' 01005 ') ";  Execute SQL statement db.execsql (SQL); }

4. Delete data

There are two ways of deleting data:

① Call Sqlitedatabase's Delete (String table,string whereclause,string[] Whereargs) method

Parameter 1 Table name

Parameter 2 Delete condition

Parameter 3 Deleting an array of condition values

private void Delete (Sqlitedatabase db) {//delete condition String whereclause = "id=?";       Delete condition parameter string[] Whereargs = {string.valueof (2)};  Execute Delete db.delete ("stu_table", Whereclause,whereargs); }

② writes the Delete SQL statement and calls Sqlitedatabase's Execsql () method to perform the deletion.

private void Delete (Sqlitedatabase db) {//delete SQL statement String sql = "Delete from stu_table where _id = 6";  Execute SQL statement db.execsql (SQL); }

5. Modify the data

There are two ways of modifying data:

① Call Sqlitedatabase's update (String table,contentvalues values,string whereclause, string[] Whereargs) method

Parameter 1 Table name

Parameter 2 is the key value pair for the row and column contentvalues type Key-value

Parameter 3 update criteria (WHERE clause)

Parameter 4 update Condition array

private void Update (Sqlitedatabase db) {//instanced content value contentvalues values = new Contentvalues ();        Add content Values.put in values ("Snumber", "101003");        Modify the condition String Whereclause = "id=?";        Modify add parameter string[] whereargs={string.valuesof (1)};  Modify Db.update ("Usertable", Values,whereclause,whereargs); }

② writes the updated SQL statement, calling Sqlitedatabase's execsql to perform the update.

private void Update (Sqlitedatabase db) {//Modify SQL statement String sql = "Update stu_table set snumber = 654321 where I         D = 1 ";   Execute SQL Db.execsql (SQL); }

6. Query data

Querying data in Android is done through the cursor class, and when we use the Sqlitedatabase.query () method, we get a cursor object that points to every piece of data. It provides a number of methods for querying, as follows:

Public Cursor query (String table,string[] columns,string selection,string[] selectionargs,string groupby,string have , String orderby,string limit);

The meaning of each parameter is explained:

Parameter table: Table name

Parameter columns: array of column names

Parameter selection: Conditional sentence, equivalent to where

Parameter Selectionargs: Conditional clause, parameter array

Parameter groupby: grouping columns

Parameters having: grouping conditions

Parameter order BY: Row sequence

Parameter limit: Paging query restrictions

Parameter cursor: The return value, equivalent to the result set resultset

The cursor is a cursor interface that provides methods for traversing query results, such as Move pointer method Move (), Get column value Method GetString (), and so on.

Cursor Cursor Common methods

Method name

Method description

GetCount ()

Get total number of data items

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 a specified record

MoveToNext ()

Move to Next record

Movetoprevious ()

Move to previous record

Getcolumnindexorthrow (String columnName)

Get column index based on column name

getInt (int columnindex)

Gets the int type value of the specified column index

getString (int columnindex)

Gets the string type value for the specified column miniature

The following is the cursor to query the database data, the specific code is as follows:

Private void query (SQLITEDATABASE&NBSP;DB)   {         //query Get cursor      Cursor cursor = db.query    (" Usertable ", null,null,null,null,null,null);   //Determines whether the cursor is empty      if ( Cursor.movetofirst ()  {            //traversal cursor          for (Int i=0;i<cursor.getcount (); i++) {                  cursor.move (i) ;               //Get id              int id = cursor.getint (0);                  //Get username                string username=cursor.getstring (1);                  //Get Password               string password=cursor.getstring (2);                 //Output User Information                system.out.println (id+ ":" +sname+ ":" +snumber);         }     }   }

7. Delete the specified table

Write the SQL statement that inserts the data, calling Sqlitedatabase's Execsql () method directly to execute

private void Drop (Sqlitedatabase db) {//delete table SQL statement String sql = "drop table stu_table";     Execute SQL Db.execsql (SQL); }

Three. Sqliteopenhelper

The class is an auxiliary class of sqlitedatabase. This class primarily generates a database and manages the version of the database.

When calling the method Getwritabledatabase () or Getreadabledatabase () method of this class in the program, if there is no data at that time, then the Android system will automatically generate a database.

Sqliteopenhelper is an abstract class, and we usually need to inherit it and implement the 3 functions inside it:

1.onCreate (Sqlitedatabase)

This method is called when the database is first generated, that is, it is called only when the database is created, and there are other situations where we typically generate database tables in this method.

2. Onupgrade (Sqlitedatabase,int,int)

When the database needs to be upgraded, the Android system will invoke this method on its own initiative. In general, we delete the data table in this method, and create a new data table, of course, whether we need to do other operations, depends entirely on the needs of the application.

3. OnOpen (sqlitedatabase):

This is a callback function when the database is opened, and is generally not used very often in programs.

SQLite's detailed use in Android