Android: Sqlitedatabase learning Summary

After learning the basic knowledge of the Sqlite database today, I will record what I learned for later reference. The following is my summary of the Sqlite database:
1. Sqlite Introduction
Sqlite is a lightweight database that is contained in a relatively small C library. It is designed to be embedded because it occupies a very small amount of resources, it may only require several hundred KB of memory, and supports mainstream operating systems such as Windows, Linux, and Unix. It can also be combined with many programming languages, such: C #/Java/php and so on, so it is particularly popular in embedded devices. This is also in line with android development requirements, so this database is often used in Android development.
2. Sqlite Internal Structure
Internally, Sqlite consists of the following components: SQL Compiler, kernel, backend, and attachment. Sqlite makes debugging, modification, and expansion of the Sqlite kernel more convenient by using virtual machines and virtual database engines. All SQL statements are compiled into readable and executable assemblies in the Sqlite virtual machine. Its structure is as follows:

Sqlite Internal Structure
3. How to Use Sqlite in android
To use the Sqlite database in Android, you must first create a class that inherits the SQLiteOpenHelper class. We name this class DatabaseHelper as an Assistant class to access Sqlite, two functions are provided:
First, getReadableDatabase ()/getWritableDatabase () can obtain the SQLiteDatabase object, through which you can operate on the database;
Second, we provide two callback functions, OnCreate () and onUpgrade (), which allow us to perform our own operations when creating and upgrading databases;
The instance code is as follows:
[Java]
Public class DatabaseHelper extends SQLiteOpenHelper {

Private static final int VERSION = 1;
// This constructor must exist in the subclass of SQLiteOepnHelper.
Public DatabaseHelper (Context context, String name, CursorFactory factory,
Int version ){
// The constructor in the parent class must be called through super
Super (context, name, factory, version );
// TODO Auto-generated constructor stub
}
Public DatabaseHelper (Context context, String name ){
This (context, name, VERSION );
}
Public DatabaseHelper (Context context, String name, int version ){
This (context, name, null, version );
}
 
// This function is executed when the database is created for the first time. In fact, this method is called only when the SQLiteDatabse object is obtained for the first time.
@ Override
Public void onCreate (SQLiteDatabase db ){
// TODO Auto-generated method stub
System. out. println ("create a Database ");
// ExecSQL function is used to execute SQL statements
Db.exe cSQL ("create table user (id int, name varchar (20 ))");
}
 
@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){
// TODO Auto-generated method stub
System. out. println ("update a Database ");
}
}
Note: When inheriting the SQLiteOpenHelper class, you must have the public DatabaseHelper (Context context, String name, CursorFactory factory, int version) constructor.
After defining the DatabaseHelper helper class, you can use this class to create an Sqlite database and perform operations on the database. The following describes a defined Activity. The Code is as follows:
[Java]
Public class SQLiteActivity extends Activity {
/** Called when the activity is first created .*/
Private Button createButton;
Private Button insertButton;
Private Button updateButton;
Private Button updateRecordButton;
Private Button queryButton;
@ Override
Public void onCreate (Bundle savedInstanceState ){
Super. onCreate (savedInstanceState );
SetContentView (R. layout. main );
CreateButton = (Button) findViewById (R. id. createDatabase );
UpdateButton = (Button) findViewById (R. id. updateDatabase );
InsertButton = (Button) findViewById (R. id. insert );
UpdateRecordButton = (Button) findViewById (R. id. update );
QueryButton = (Button) findViewById (R. id. query );
CreateButton. setOnClickListener (new CreateListener ());
UpdateButton. setOnClickListener (new UpdateListener ());
InsertButton. setOnClickListener (new InsertListener ());
UpdateRecordButton. setOnClickListener (new UpdateRecordListener ());
QueryButton. setOnClickListener (new QueryListener ());
}
Class CreateListener implements OnClickListener {
@ Override
Public void onClick (View v ){
// Create a DatabaseHelper object
DatabaseHelper dbHelper = new DatabaseHelper (SQLiteActivity. this, "test_mars_db ");
// A database is created or opened only after the getReadableDatabase () method of the DatabaseHelper object is called or the getWritableDatabase () method.
SQLiteDatabase db = dbHelper. getReadableDatabase ();
}
}
Class UpdateListener implements OnClickListener {
 
@ Override
Public void onClick (View v ){
DatabaseHelper dbHelper = new DatabaseHelper (SQLiteActivity. this, "test_mars_db", 2 );
SQLiteDatabase db = dbHelper. getReadableDatabase ();
}

}
Class InsertListener implements OnClickListener {
 
@ Override
Public void onClick (View v ){
// Generate the ContentValues object
ContentValues values = new ContentValues ();
// To insert a key-value pair to the object, the key-value pair is the column name and the value is the value to be inserted into the column. The value must be consistent with the data type in the database.
Values. put ("id", 1 );
Values. put ("name", "zhangsan ");
DatabaseHelper dbHelper = new DatabaseHelper (SQLiteActivity. this, "test_mars_db", 2 );
SQLiteDatabase db = dbHelper. getWritableDatabase ();
// Call the insert method to insert data to the database.
Db. insert ("user", null, values );
}
}
// The update operation is equivalent to executing the update statement in the SQL statement.
// UPDATE table_name set xxcol = xxx where xxcol = XX...
Class UpdateRecordListener implements OnClickListener {
 
@ Override
Public void onClick (View arg0 ){
// TODO Auto-generated method stub
// Get a writable SQLiteDatabase object
DatabaseHelper dbHelper = new DatabaseHelper (SQLiteActivity. this, "test_mars_db ");
SQLiteDatabase db = dbHelper. getWritableDatabase ();
ContentValues values = new ContentValues ();
Values. put ("name", "zhangsanfeng ");
// The first parameter is the name of the table to be updated.
// The second parameter is a ContentValeus object.
// The third parameter is the where clause.
Db. update ("user", values, "id =? ", New String [] {" 1 "});
}
}
Class QueryListener implements OnClickListener {
 
@ Override
Public void onClick (View v ){
System. out. println ("aaa ------------------");
Log. d ("myDebug", "myFirstDebugMsg ");

DatabaseHelper dbHelper = new DatabaseHelper (SQLiteActivity. this, "test_mars_db ");
SQLiteDatabase db = dbHelper. getReadableDatabase ();
Cursor cursor = db. query ("user", new String [] {"id", "name"}, "id =? ", New String [] {" 1 "}, null );
While (cursor. moveToNext ()){
String name = cursor. getString (cursor. getColumnIndex ("name "));
System. out. println ("query --->" + name );
}
}
}

}
As shown in the preceding example, before creating or operating a database, you must use the getReadbleDatabase () and getWritbleDatabase () methods in the DatabaseHelpe class to create the required database, the former is to create a read-only database, and the latter is to create a writable database.