Android SQLite Quick Start Tutorial

SQLite is the Android platform software development will be used in the database products, as a lightweight database, SQLite design goal is embedded, and has been used in many embedded products, it occupies a very low resource, in embedded devices, It may take only hundreds of K of memory to be sufficient. Let's take a look at the use of SQLite 3 for the Android platform.

The following concepts are understood under the SQLite limitations of the Android platform: 650) this.width=650; "Title=" Android <wbr>sqlite Quick Start Tutorial "name=" aimg_108 "alt = "Android <wbr>sqlite Quick Start Tutorial" src= "http://techfoxbbs.com/data/attachment/forum/201504/07/ 135755gyozyemmmmyk38l1.png "width=" 312 "/>

Database Basics Concepts

• SQlite uses a file to save the database, and a file is a database.

• Several tables are included in the database;

• Each table contains multiple records;

• Each record consists of multiple fields;

• Each field has its corresponding value;

• Each value can specify a type.
  

The database name is the filename, the table has its own name, the record has no name, and each field has a name. In SQLite, the concept of no order is recorded, there is no concept of the first and second such, and only the records satisfying the condition can be obtained by querying. We operate the database by executing SQL instructions.

database related classes under Android platform

• Sqliteopenhelper Abstract class: Provides operation functions such as database opening and closing by implementing user classes from this class of inheritance.

• Sqlitedatabase Database Access classes: Perform operations such as inserting records into the database, querying records, and so on.

• SQLITECURSOR Query structure Operation class: Used to access records in query results.
  

Create a database

Suppose you use a database to store a table with multiple records stored in the table, with 3 fields per record: ID, name, age. A class is implemented from Sqliteopenhelper inheritance, and all operations on the database are encapsulated in this class.

constructor function:

Public DBHelper (Context context, String name, Cursorfactory factory, int version) {Super (context, name, factory, ver  sion);  
}

The parameter in this function, name, is the database name, which is the database filename, and version starts at 1, and when the tables and fields in the future database change, the user can raise this version to trigger Onupgrade () to achieve the effect of the old data upgrade.

Create a table in the OnCreate function:

Public void OnCreate (Sqlitedatabase db) {
StringBuilder sql = new StringBuilder ();
sql.append ("CREATE TABLE");
sql.append (tbl_name);
sql.append ("(");
sql.append (field_id + "TEXT PRIMARY KEY not NULL,");
sql.append (Field_name + "TEXT,");
sql.append (field_age + "INTEGER");
sql.append (");");
Db.execsql (sql.tostring ());  
}

The OnCreate function is only called when the database is first created, so it is suitable for some database initialization operations.

The final effect of the above function is to execute the SQL statement:

CREATE TABLE mytable (ID text PRIMARY KEY not NULL, name text, age INTEGER);

add data to a table

There are two ways to add data to a table, one is to execute SQL instructions directly through the Execsql function provided by Sqlitedatabase, and the Insert function provided by Sqlitedatabase, which encapsulates the SQL statement to make it easy for the user to use.

• Through Execsql ("INSERT into MyTable VALUES (' idstr ', ' nameStr ', 20);"); You can insert a record that throws an exception when an error is inserted in the record to be processed.

• Through the Insert function: The Insert function requires a value of type contentvalues, which is similar to HashMap, a key, and a value paired with a pair. Key is the field name.
  

contentvalues v = new Contentvalues (), V.put ("id", "101"), V.put ("name", "User 101"), V.put ("Age", Mdb.insert); Table "," null ", V);

If the key in V does not fully specify all fields, such as less name or less ID, then the record can be successfully inserted according to the field limit when the table is created, such as less name, then this record will be inserted, but the Name field value is NULL, if the ID is less, then the record will not be inserted, Because not NULL was specified when it was created.

You can view the results by using the following directives:

The ADB shell enters the shell after execution;

Cd/data/data/cn.demo.sql

Sqlite3 Database.db entered the SQLite operator interface.

. Help can see command assistance.

. Dump can see the data in the database.

For ease of use, you can customize an insert function to pass values through parameters, while the Contentvalues setting is implemented inside the function.

Public void Insert (string ID, string name, Int. age)
{ 
if (mDb = = null )
mDb = Getwritabledatabase ();  
contentvalues v = new Contentvalues ();
v.put ("id", id);
v.put ("name", name);  
if (age > 0)
v.put ("age", age);
Mdb.insert (tbl_name, "null", V);
querying records from a table

SQL statement: SELECT column list from table WHERE expr is ORDER by column list; Through the query () function of the Sqlitedatabase class:

Cursor sqlitedatabase.query (string table, string[] columns, string selection, string[] Selectionargs, String groupBy , string has a, string is the same);

Parameters:
Table name of the record where the table is to be updated.
Values of the fields to update.
Whereclause the WHERE clause. Which records are updated.
Whereargs '? ' in the WHERE clause Replace the string.
When you perform an update operation, if you assign a value to only a subset of the fields, the fields that do not have the assigned value remain unchanged after update.

To delete a record from a table:

int Sqlitedatabase.delete (string table, String Whereclause, string[] whereargs);

From the entire creation and query process of SQLite we can see that on the Android platform, due to the features of SQLite, everything is dominated by files, without the concept of order. We can use the query to get the record that satisfies the condition, and manipulate the database through the SQL instruction. In addition, we should memorize several classes of the Android platform to manipulate SQLite: Sqliteopenhelper abstract class, Sqlitedatabase database access class, and SQLITECURSOR query structure operation class.

More Android Http://techfoxbbs.com

Android SQLite Quick Start Tutorial