Android Create and use database Sqlite_android

First, relational database SQLite

Every application uses data, and Android apps are no exception, and Android uses Open-source, OS-independent SQL databases-sqlite. SQLite First Alpha version was born in May 2000, it is a lightweight database, its design goal is embedded, occupy resources very low, only need hundreds of K of memory is enough. SQLite has been used by a variety of software and products, and Mozilla Firefox uses SQLite to store configuration data, and both Android and iphone use SQLite to store data.

The SQLite architecture diagram is as follows:

The compiler includes the Tokenizer (lexical analyzer), the Parser (parser), the code generator (the generator). They work together to process structured query statements in textual form.

After driven by B-tree,pager,os interface composition. B-tree's responsibility is to sort, maintain the intricacies of multiple database pages, organize the pages into tree-like structures, and the pages are the leaves of the tree. Pager is responsible for the transmission, reads the page from the disk or writes to the page according to the B-tree request.

Public services have a variety of functional functions such as: memory allocation, string comparisons, Unicode conversion, and so on.

The SQLite database is an open source embedded database written by D.richard Hipp in C language, with a supported database size of 2TB. It has the following characteristics:

1. Lightweight

Unlike database software in the SQLite and c\s modes, it is the database engine within the process, so there is no client and server for the database. Using SQLite generally only need to take a dynamic library of it, you can enjoy its full functionality. And the size of that dynamic library is pretty small.

2. Independence

The core engine of the SQLite database does not rely on Third-party software, and it does not need to be "installed", so it can save a lot of trouble when used.

3, the isolation of

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

4. Cross platform

The SQLite database supports most operating systems, and many mobile operating systems, such as Android, Windows Mobile, Symbian, palm, can run as well as the operating system we use on our computers.

5. Multi-language interface

The SQLite database supports many language programming interfaces, such as c\c++, Java, Python, dotnet, Ruby, and Perl, and is more popular with developers.

6. Safety

The SQLite database enables 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. An exclusive lock must be obtained before a process or thread writes to the database. After an exclusive lock is issued, other read or write operations will not occur again.

SQLite official website (http://www.sqlite.org), for more information please go.

Second, export view database files

In Android, the database created for an application is accessible and inaccessible to other applications, and the database is located in the Android device/data/data/package_name/databases folder.

To export a database file, you can use Eclipse, as shown in the figure:

View the database using the SQLite db Browser, as shown in the figure:

Third, extended class

3.1 Extended Sqliteopenhelper

Android does not automatically provide a database. Using SQLite in an Android application, you must create your own database, and then create tables, indexes, and fill data. Android provides sqliteopenhelper to help you create a database, and you can easily create a database just by inheriting the Sqliteopenhelper class. The Sqliteopenhelper class encapsulates the logic used to create and update databases, depending on the needs of the development application. Sqliteopenhelper subclass, you need to implement at least three methods:

Constructor, calling the constructor of the parent class Sqliteopenhelper

OnCreate () Method://TODO database operations after the database is created

Onupgrage () method. TODO Change the operation of the database version

When you have finished working on the database (for example, your activity has been turned off), you need to call the Sqlitedatabase close () method to release the database connection.

The best practice for manipulating a database is to create a helper class, such as a contact module

Class Contactsdatabasehelper extends Sqliteopenhelper

3.2 Cursor Class

Android uses the cursor class to return a desired value, cursor as a pointer to return a result set from a database query, and using the cursor allows Android to more efficiently manage the rows and columns that they need, you use the Contentvalues object to store the key/value pairs, Its put () method allows you to insert key values for different data types.

3.3 Data types

The biggest difference between SQLite and other databases is support for data types, where you can specify the data type of a column in a CREATE TABLE statement, but you can put any data type into any column. When a value is inserted into the database, SQLite checks its type. If the type does not match the associated column, SQLite attempts to convert the value to the type of the column. If it cannot be converted, the value is stored as the type it has itself. For example, you can put a string into an INTEGER column. SQLite called this "weak type" (Manifest typing).

Four, database operations
4.1 Creating a database
         Android does not automatically provide a database. Using SQLite in an Android application, you must create your own database, and then create tables, indexes, and fill data. Android provides sqliteopenhelper to help you create a database, and you can easily create a database just by inheriting the Sqliteopenhelper class. The Sqliteopenhelper class encapsulates the logic used to create and update databases, depending on the needs of the development application. Sqliteopenhelper, you need to implement at least three methods: the
constructor, the constructor that calls the parent class Sqliteopenhelper. This method requires four parameters: a context environment (for example, an activity), a database name, an optional cursor factory (usually Null), and an integer representing the version of the database model you are using. The
OnCreate () method, which requires a Sqlitedatabase object as a parameter to populate the table and initialize the data as needed. The
Onupgrage () method, which requires three parameters, a Sqlitedatabase object, an old version number, and a new version number, so you can see how to transform a database from the old model to the new one.
The following sample code shows how to inherit Sqliteopenhelper to create a database:

public class Databasehelper extends Sqliteopenhelper { 
 Databasehelper (context context, String name, cursorfactory cursorfactory, int version) 
 { 
 Super (context, name, cursorfactory, version); 
 } 
 
 @Override public 
 void OnCreate (Sqlitedatabase db) { 
 //TODO to create a database, operations on the database are 
 
 @Override 
Public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) { 
 //TODO changes the operation of the database version 
 } 
 
@Override 
public void OnOpen (Sqlitedatabase db) { 
 super.onopen (db);  
 TODO first executed after each successful opening of the 
 database 
 

Next, we'll discuss how to create a table, insert data, delete a table, and so on. Call the Getreadabledatabase () or Getwriteabledatabase () method, and you can get the Sqlitedatabase instance, which is specifically called, depending on whether you need to change the contents of the database:

db= (New Databasehelper (GetContext ())). Getwritabledatabase (); 

The above code returns an instance of the Sqlitedatabase class, and you can query or modify the database using this object.
When you have finished working on the database (for example, your activity has been turned off), you need to call the Sqlitedatabase close () method to release the database connection.
4.2 Creating tables and indexes
in order to create tables and indexes, you need to invoke the Sqlitedatabase Execsql () method to execute the DDL statement. If there is no exception, this method has no return value.
For example, you can execute the following code:

Db.execsql ("CREATE TABLE mytable" (_id INTEGER PRIMARY KEY 
 

This statement creates a table named MyTable. The table has a column named _id and is a primary key, and the value of this column is an integer that will automatically grow (for example, when you insert a row, SQLite will automatically assign the column), and two more columns: Title (character) and value (floating-point number). SQLite automatically creates an index for the primary key column.
Typically, tables and indexes are created the first time a database is created. If you do not need to change the schema of the table, you do not need to delete the table and index. Deleting tables and indexes requires that the drop INDEX and drop TABLE statements be invoked using the Execsql () method.
4.3 Adding data to a table
The code above, which has created databases and tables, now needs to add data to the table. There are two ways to add data to a table.
Like the table above, you can use the Execsql () method to update the table's data by executing the INSERT, UPDATE, and DELETE statements. The Execsql () method applies to all SQL statements that do not return results. For example:

Db.execsql ("INSERT into widgets (name, Inventory)" + 

Another method is to use the Insert (), update (), delete () method of the Sqlitedatabase object. These methods take part of the SQL statement as an argument. Examples are as follows:

Contentvalues cv=new contentvalues (); 
Cv.put (Constants.title, "example TITLE"); 
Cv.put (Constants.value, Sensormanager.gravity_death_star_i); 
Db.insert ("MyTable", Getnullcolumnhack (), CV); 

The update () method has four parameters, namely the table name, the Contentvalues object that represents the column name and the value, the optional where condition and the optional string to populate the where statement, which replaces the "? Mark Update () Updates the value of the specified column according to the criteria, so the Execsql () method can achieve the same purpose.
The WHERE condition is similar to its parameters and other used SQL APIs. For example:

String[] Parms=new string[] {"This is a String"}; 
Db.update ("Widgets", Replacements, "Name=", parms); 

The Delete () method is used similar to update (), using the table name, the optional where condition, and the corresponding string that fills the where condition.
4.3 Querying the database
like INSERT, UPDATE, DELETE, there are two ways to use SELECT to retrieve data from the SQLite database.
1. Use Rawquery () to invoke the SELECT statement directly;
Use the query () method to build a query.
Raw Queries
Just like the API name, Rawquery () is the simplest solution. In this way you can invoke the SQL SELECT statement. For example:

Cursor c=db.rawquery ( 
 "select name from Sqlite_master WHERE type= ' table ' and name= ' mytable '"), 

In the example above, we query the SQLite system table (Sqlite_master) to check whether the table exists. The return value is a cursor object, and the method of this object can iterate through the query results.
If the query is dynamic, using this method can be very complicated. For example, when you need to query a column that is not sure when the program compiles, using the query () method is much easier.
Regular Queries
The query () method builds queries with the SELECT statement segment. The SELECT statement is used as a parameter to the query () method, such as the name of the table to be queried, the name of the field to get, the Where condition, and the optional positional parameter, which replaces the value of the position parameter in the Where condition, the GROUP by condition, the having condition.
In addition to the table name, other parameters can be null. So, the previous code snippet can be written as:

String[] columns={"ID", "Inventory"}; 
String[] parms={"Snicklefritz"}; 
Cursor result=db.query ("Widgets", Columns, "Name=", parms, NULL, NULL, NULL); 

Using cursors
No matter how you execute the query, you will return a Cursor, this is the Android SQLite database cursor, using cursors, you can:
The number of records in the result set is obtained by using the GetCount () method;
Traverse all records through Movetofirst (), MoveToNext (), and Isafterlast () methods;
The field name is obtained by Getcolumnnames ();
Convert to field number by Getcolumnindex ();
The value of the current record of the given field is obtained by means of getString () and getInt ();
The Requery () method is used to rerun the query to get the cursor;
The cursor resource is released through the Close () method;
For example, the following code traverses the MyTable table

Cursor result=db.rawquery ("Select ID, Name, inventory from mytable"); 
 Result.movetofirst (); 
 while (!result.isafterlast ()) { 
 int id=result.getint (0); 
 String name=result.getstring (1); 
 int Inventory=result.getint (2); 
 Do something useful with these 
 result.movetonext (); 
 } 
 Result.close (); 

Using the SQLite database management tool in Android
Development on other databases generally uses tools to examine and process the contents of the database, rather than just using the database's APIs. With the Android emulator, there are two alternative ways to manage the database.
First, the emulator binds the SQLITE3 console program, which can be invoked using the ADB shell command. As soon as you enter the shell of the simulator, execute the sqlite3 command on the path to the database. Database files are generally stored in:
/data/data/your.app.package/databases/your-db-name
If you prefer to use a more user-friendly tool, you can copy the database to your development machine and use the Sqlite-aware client to manipulate it. In this case, you operate on a copy of a database, and if you want your changes to be reflected on the device, you need to back up the database.
Test the database from the device and you can use the ADB pull command (or do it on the IDE). Stores a modified database to the device, using the ADB push command.
One of the most convenient SQLite clients is the FireFox SQLite Manager extension, which can be used across all platforms.

Figure 2. SQLite Manager

V. Practical application

Android integrates SQLite at runtime, so every Android application can use the SQLite database.
Where the database is stored:data/< the project folder >/databases/
First step: Create a Database
Android provides sqliteopenhelper classes to help create a database that can be easily created by inheriting the class SQLite database;
Note: The Sqliteopenhelper subclass must implement at least three methods:
* The construction method with the parameter;
* OnCreate ();
* Onupgrag ();
The Java code is as follows:

Package com.example.sqlite; 
 
  Import Android.content.Context; 
 
  Import Android.database.sqlite.SQLiteDatabase; 
 
  Import Android.database.sqlite.SQLiteDatabase.CursorFactory; 
 
  Import Android.database.sqlite.SQLiteOpenHelper; 
 
  /* Sqliteopenhelper subclass, for manipulating the database * * Sqliteopenhelper is a helper class that manages the creation and release of databases and provides two functions; 
 
  * First: Getreadabledatabase (), getwriteabledatabase () can obtain Sqlitedatabase objects, through which the database can be manipulated; * * Second: Provide oncreate (); 
 
  Onupgrade () Two callback functions, allowing us to create and delete the database when we do our own operations; 
 
  * * * * */public class Mysqliteopenhelper extends Sqliteopenhelper {*/* construction method, invoke parent class Sqliteopenhelper constructor. /* Parameter 1: Context environment; parameter 2: Database name (ending with. db); Parameter 3: Cursor factory (default is null); 
 
  Parameter 4: Represents a certificate that uses the database model version/Public mysqliteopenhelper (context, String name, Cursorfactory factory, int version) { 
 
  Super (context, name, Factory, version); void OnCreate (SQLItedatabase db) {//TODO database Operations}/* After database is created the database is converted from the old model to the new model *//* parameter 1: object; Parameter 2: old version number; 
 
  Parameter 3: New version number */public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//TODO changes the operation of the database version} 
 
  /* Open database Function/public void OnOpen (Sqlitedatabase db) {//TODO is executed first after each successful opening of the database Super.onopen (db); 
 
  }} package Com.example.sqlite; 
 
  Import Android.content.Context; 
 
  Import Android.database.sqlite.SQLiteDatabase; 
 
  Import Android.database.sqlite.SQLiteDatabase.CursorFactory; 
 
  Import Android.database.sqlite.SQLiteOpenHelper; 
 
  /* Sqliteopenhelper subclass, for manipulating the database * * Sqliteopenhelper is a helper class that manages the creation and release of databases and provides two functions; 
 
  * First: Getreadabledatabase (), getwriteabledatabase () can obtain Sqlitedatabase objects, through which the database can be manipulated; * * Second: Provide oncreate (); 
 
  Onupgrade () Two callback functions, allowing us to create and delete the database when we do our own operations; 
 
* * * * */public class Mysqliteopenhelper extends Sqliteopenhelper {*/* construction method, invoke parent class Sqliteopenhelper constructor.  /* Parameter 1: Context environment; parameter 2: Database name (ending with. db); Parameter 3: Cursor factory (default is null); 
 
  Parameter 4: Represents a certificate that uses the database model version/Public mysqliteopenhelper (context, String name, Cursorfactory factory, int version) { 
 
  Super (context, name, Factory, version); void OnCreate (Sqlitedatabase db) {//TODO after the database is created, operations on the database}/* Convert the database from the old model to the new model *//* parameter 1: object; Parameter 2: old version number; 
 
  Parameter 3: New version number */public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {//TODO changes the operation of the database version} 
 
  /* Open database Function/public void OnOpen (Sqlitedatabase db) {//TODO is executed first after each successful opening of the database Super.onopen (db); 
 }   }

The

Case code is as follows:

package com.example.sqlite;
 
Import android.app.Activity;
 
Import android.content.ContentValues;
 
Import android.database.Cursor;
 
Import android.database.sqlite.SQLiteDatabase;
 
Import android.os.Bundle;
 
Import android.view.View;
 
Import android.view.View.OnClickListener;
 
Import android.widget.Button;
 
Import android.widget.Toast;
 
Public class MainActivity extends Activity {
 
/ * Set the constant of the table related information * /
 
Final String MYTAB = "t_score";
 
Final String MYNAME = "name";
 
Final String MYSCORE = "score";
 
MySQLiteOpenHelper helper;
 
Private Button selectData, createDatabase, createTable, insertData, updateData, deleteData;
 
/ * Obtain the formation object * /
 
Public void init () {
 
SelectData = (Button) findViewById (R.id.selectData);
 
CreateDatabase = (Button) findViewById (R.id.createDatabase);
 
CreateTable = (Button) findViewById (R.id.createTable);
 
InsertData = (Button) findViewById (R.id.insertData);
 
UpdateData = (Button) findViewById (R.id.updateData);
 
DeleteData = (Button) findViewById (R.id.deleteData);
 
}
 
Protected void onCreate (Bundle savedInstanceState) {
 
Super.onCreate (savedInstanceState);
 
SetContentView (R.layout.main);
 
/ * Initialize the component object * /
 
Init ();
 
/ * Create a database * /
 
CreateDatabase.setOnClickListener (new OnClickListener () {
 
Public void onClick (View v) {
 
/ * Create a MySQLiteOpenHelper, this statement will not create or open a connection * /
 
Helper = new MySQLiteOpenHelper (MainActivity.this, "mydb.db", null, 1);
 
/ * Call the getWriteableDatabase () method of MySQLiteOpenHelper to create or open a connection * /
 
SQLiteDatabase sqlitedatabase = helper.getWritableDatabase ();
 
Toast.makeText (MainActivity.this, "Database created successfully", 1000) .show ();
 
}
 
});
 
/ * Create table * /
 
CreateTable.setOnClickListener (new OnClickListener () {
 
Public void onClick (View v) {
 
/ * Create a MySQLiteOpenHelper, this statement will not create or open a connection * /
 
Helper = new MySQLiteOpenHelper (MainActivity.this, "mydb.db", null, 1);
 
/ * Get a writable SQLiteDatabase object, create or open a connection * /
 
SQLiteDatabase sqliteDatabase = helper.getWritableDatabase ();
 
/ * Create two tables * /
 
SqliteDatabase.execSQL ("create table student (id INTEGER PRIMARY KEY autoincrement, name text);");
 
SqliteDatabase.execSQL ("create table" + MYTAB + "(" + MYNAME + "text," + MYSCORE + "integer);");
 
/ * Tips * /
 
Toast.makeText (MainActivity.this, "Data table created successfully", 1000) .show ();
 
}
 
});
 
/ * Insert data * /
 
InsertData.setOnClickListener (new OnClickListener () {
 
Public void onClick (View v) {
 
/ * Connect to the database * // * There are tables in the database, operate on the tables * /
 
SQLiteDatabase sqliteDatabase = helper.getWritableDatabase ();
 
/ * Add data to the table: * // * Method 1: * // * Add a piece of data * /
 
SqliteDatabase.execSQL ("insert into student (name) values ('mike')");
 
/ * Method 2: * // * Use the insert () method of the SQLiteDatabase object * /
 
/ * Create ContentValues object * // * Each time a data is inserted * /
 
ContentValues cv = new ContentValues ();
 
Cv.put ("name", "mary"); / * key == column value == value * /
 
SqliteDatabase.insert ("student", null, cv);
 
Cv.clear ();
 
/ * Add data to MYTAB * /
 
SqliteDatabase.execSQL ("insert into" + MYTAB + "values ('ray', 95)");
 
SqliteDatabase.execSQL ("insert into" + MYTAB + "values ('tom', 85)");
 
SqliteDatabase.execSQL ("insert into" + MYTAB + "values ('jone', 90)");
 
Cv.put (MYNAME, "jack");
 
Cv.put (MYSCORE, 78);
 
SqliteDatabase.insert (MYTAB, null, cv);
 
Cv.clear ();
 
Toast.makeText (MainActivity.this, "Data inserted successfully", 1000) .show ();
 
}
 
});
 
/ * Modify data * // * updateData click event listener * /
 
UpdateData.setOnClickListener (new OnClickListener () {
 
Public void onClick (View v) {
 
/ * Establish a connection to the database and obtain the SQLiteDatabase object * /
 
SQLiteDatabase sqliteDatabase = helper.getWritableDatabase ();
 
/ * Method 1: Use the sentence directly * /
 
//SqliteDatabase.execSQL("update student set name = 'mary key' where id = 1 ");
 
/ * Method 2: Use sqliteDatabase.update (); method * /
 
ContentValues cv = new ContentValues ();
 
Cv.put ("name", "mary key"); / * Confirm that the value of the corresponding column needs to be modified * /
 
/ * Reference 1: Table name; Reference 2: ContentValues object; Reference 3: Where sentence, equivalent to the statement behind where in SQL,? Is a placeholder * /
 
/ * Reference 4: placeholder value; * /
 
SqliteDatabase.update ("student", cv, "id =?", New String [] {"1"});
 
Toast.makeText (MainActivity.this, "Data modified successfully", 1000) .show ();
 
}
 
});
 
/ * Delete data; set deleteData click event listener * /
 
DeleteData.setOnClickListener (new OnClickListener () {
 
Public void onClick (View v) {
 
/ * Establish contact with the database and obtain the object of SQLiteDatabase * /
 
SQLiteDatabase sqliteDatabase = helper.getWritableDatabase ();
 
/ * Use SQL statement to delete directly * /
 
//SqliteDatabase.execSQL ("");
 
/ * Call: delete () method of SQLiteDatabase object to delete data * /
 
/ * Reference 1: Table name; Reference 2: Conditional statement; Reference 3: Value of corresponding placeholder in conditional statement * /
 
SqliteDatabase.delete ("student", "id =?", New String [] {"1"});
 
Toast.makeText (MainActivity.this, "Data deleted successfully", 1000) .show ();
 
}
 
});
 
/ * View data * // * selectData click event listener * /
 
SelectData.setOnClickListener (new OnClickListener () {
 
Public void onClick (View v) {
 
/ * Get the object of SQLiteDatabase * /
 
SQLiteDatabase sqliteDatabase = helper.getReadableDatabase ();
 
/ * Call the query () method of SQLiteDatabase to return a Cursor object: the result set object returned by the database query * /
 
/ * Reference 1 String: table name
 
* Refer to 2 String []: column to be queried;
* Refer to 3 String: query conditions;
 
* Refer to 4 String []: parameters for query conditions;
 
* Ref. 5 String: Group the results of the query;
 
* Refer to 6 String: Limit the grouping result;
 
* Refer to 7 String: Sort the query results;
 
*
 
* * /
 
Cursor cursor = sqliteDatabase.query ("student", new String [] {"id", "name"}, "id =?", New String [] {"1"}, null, null, null);
 
/ * Save the data of the corresponding field in the result set * /
 
String id = null;
 
String name = null;
 
/ * Read data from the result set * /
 
While (cursor.moveToNext ()) {
 
Id = cursor.getString (cursor.getColumnIndex ("id"));
 
Name = cursor.getString (cursor.getColumnIndex ("name"));
 
}
 
Toast.makeText (MainActivity.this, "The query data is: id =" + id + "\ n name =" + name, 1000) .show ();
 
}
 
});
 
}
 
}

If you want to develop an Android application, you'll need to store the data on Android, and using the SQLite database is a great choice.