Android Operating SQLite Database QuickStart Tutorial _android

SQLite is a database product that will be used frequently in the software development of the Android platform, as a lightweight database, SQLite's design goal is embedded, and has been used in many embedded products, it occupies very low resources, in embedded devices, It may take only hundreds of K of memory to be enough. Now 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:

Database Basic Concepts

1.SQlite file to save the database, a file is a database.
2. The database also contains a number of tables;
3. Each form contains a number of records;
4. Each record consists of multiple fields;
5. Each field has its corresponding value;
6. 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, there is no concept of order in the records, there is no concept of first or second such; only records that meet the criteria can be obtained through a query. We manipulate the database by executing SQL instructions.

Database-related classes under the Android platform

1.SQLiteOpenHelper Abstract class: Implements user classes from this class to provide operation functions such as database opening, closing, and so on.
2.SQLiteDatabase database Access class: Perform operations on inserting records, querying records, and so on for the database.
3.SQLITECURSOR Query structure action class: Used to access records in query results.

Creating a Database

Suppose you use a database to store a table with more than one record in it, each containing 3 fields: ID, name, age. Implements a class from Sqliteopenhelper inheritance, and all operations on the database are encapsulated in this class.

constructor function:

Copy Code code as follows:

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

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

To create a table in the OnCreate function:

Copy Code code as follows:

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 invoked when the database is first created, so it is appropriate to do some database initialization.

The above function ultimately has the effect of executing the SQL statement:

Copy Code code as follows:

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 the SQL instruction directly via the EXECSQL function provided by Sqlitedatabase, and one is the Insert function provided by Sqlitedatabase, which encapsulates the SQL statement for the convenience of the user.

1. Through Execsql ("INSERT into MyTable VALUES (' idstr ', ' nameStr ', 20);"); You can insert a record that throws an exception to be handled when an error is inserted.
2. Insert Function: The Insert function requires a value of type contentvalues, which is similar to HashMap, a key, and a value that matches a pair. The key is the field name.

Copy Code code as follows:

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

If the key in V does not fully specify all the fields, for example, less name or less ID, then the record can be successfully inserted to be based on the field limit when the table was created, such as less name, then this record will be inserted, only the Name field value is null, if the less ID, then the record will not be inserted, Not NULL was specified because it was created.

You can view the results with the following directives:

The ADB shell executes and enters the shell;

Cd/data/data/cn.demo.sql

Sqlite3 database.db into the SQLite operation 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 a value through a parameter, while the Contentvalues setting is implemented inside the function.

Copy Code code as follows:

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 order by column list; Through the query () function of the Sqlitedatabase class:

Copy Code code as follows:

Cursor sqlitedatabase.query (String table,
string[] columns, String selection, string[] Selectionargs,
String groupBy, String having, string by);

Parameters:

Table name of the record in which to update the table.
Values of the field to update.
Whereclause the WHERE clause. Which records to update.
Whereargs the '? ' in the WHERE clause Replace the string.
When you perform an update operation, if you assign only a partial field, the field with no assignment remains unchanged after update.

To delete a record from a table:

Copy Code code as follows:

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

From the sqlite of the entire creation and query process we can see that, in the Android platform, due to the SQLite features, everything to document-oriented, without the concept of order. We can use the query to get the records that meet the conditions, and to manipulate the database through SQL instructions. In addition, we should memorize several classes of Android platform Operations SQLite: Sqliteopenhelper abstract classes, sqlitedatabase database access classes, and SQLITECURSOR query structure operations classes.