Android learning notes (basic operations of three databases SQLITE)

SQLITE is a standard database that comes with android. It supports SQL statements and is a lightweight embedded database.
To learn SQLITE, you must first understand some knowledge points:
1. SQLiteDatabase
An instance of SQLiteDatabase represents an SQLite database. Through some methods of the SQLiteDatabase instance, we can execute SQL statements to add, delete, modify, and query databases. A database is private to an application, and its name is unique among the applications.
2. SQLiteOpenHelper
This is an abstract class. When the program calls the getWritableDatebase () or getReadableDatebase () method of this class, if there is no data at that time, the android system will automatically generate a database. For SQLiteOpenHelper, we usually need to inherit it and implement the three functions in it.
(1) onCreate
This method is called when the database is generated for the first time. Generally, the database table is generated in this method.
(2) onUpgrade
When the database needs to be upgraded, the Android system will automatically call this method. Generally, we delete the database table in this method and create a new database table. You can also perform other operations based on application requirements.
(3) onOpen
This is the callback function when the database is opened, which is generally not used.
The following code is used to operate a database:
Public class ActivityMain extends Activity {
OnClickListener listener1 = null;
OnClickListener listener2 = null;
OnClickListener listener3 = null;
OnClickListener listener4 = null;
OnClickListener listener5 = null;
Button button1;
Button button2;
Button button3;
Button button4;
Button button5;
DatabaseHelper mOpenHelper;
Private static final String DATABASE_NAME = "dbForTest. db ";
Private static final int DATABASE_VERSION = 1;
Private static final String TABLE_NAME = "diary ";
Private static final String TITLE = "title ";
Private static final String BODY = "body ";
Private static class DatabaseHelper extends SQLiteOpenHelper {
DatabaseHelper (Context context ){
Super (context, DATABASE_NAME, null, DATABASE_VERSION );
}
@ Override
Public void onCreate (SQLiteDatabase db ){
String SQL = "CREATE TABLE" + TABLE_NAME + "(" + TITLE
+ "Text not null," + BODY + "text not null" + ");";
System. out. print (SQL );
Log. I ("haiyang: createDB =", SQL );
Db.exe cSQL (SQL );
}
@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){
}
}
@ Override
Public void onCreate (Bundle savedInstanceState ){
Super. onCreate (savedInstanceState );
SetContentView (R. layout. main );
PrepareListener ();
InitLayout ();
MOpenHelper = new DatabaseHelper (this );
}
Private void initLayout (){
Button1 = (Button) findViewById (R. id. button1 );
Button1.setOnClickListener (listener1 );
Button2 = (Button) findViewById (R. id. button2 );
Button2.setOnClickListener (listener2 );
Button3 = (Button) findViewById (R. id. button3 );
Button3.setOnClickListener (listener3 );
Button4 = (Button) findViewById (R. id. button4 );
Button4.setOnClickListener (listener4 );
Button5 = (Button) findViewById (R. id. button5 );
Button5.setOnClickListener (listener5 );
}
Private void prepareListener (){
Listener1 = new OnClickListener (){
Public void onClick (View v ){
CreateTable ();
}
};
Listener2 = new OnClickListener (){
Public void onClick (View v ){
DropTable ();
}
};
Listener3 = new OnClickListener (){
Public void onClick (View v ){
InsertItem ();
}
};
Listener4 = new OnClickListener (){
Public void onClick (View v ){
DeleteItem ();
}
};
Listener5 = new OnClickListener (){
Public void onClick (View v ){
ShowItems ();
}
};
}
 
Private void CreateTable (){
SQLiteDatabase db = mOpenHelper. getWritableDatabase ();
String SQL = "CREATE TABLE" + TABLE_NAME + "(" + TITLE
+ "Text not null," + BODY + "text not null" + ");";
Log. I ("haiyang: createDB =", SQL );
Try {
Db.exe cSQL ("drop table if exists diary ");
Db.exe cSQL (SQL );
SetTitle ("data table reconstruction succeeded ");
} Catch (SQLException e ){
SetTitle ("data table reconstruction error ");
}
}
 
Private void dropTable (){
SQLiteDatabase db = mOpenHelper. getWritableDatabase ();
String SQL = "drop table" + TABLE_NAME;
Try {
Db.exe cSQL (SQL );
SetTitle ("data table deleted successfully:" + SQL );
} Catch (SQLException e ){
SetTitle ("data table deletion error ");
}
}
 
Private void insertItem (){
SQLiteDatabase db = mOpenHelper. getWritableDatabase ();
String sql1 = "insert into" + TABLE_NAME + "(" + TITLE + "," + BODY
+ ") Values ('haiyang', 'android is developing rapidly ');";
String sql2 = "insert into" + TABLE_NAME + "(" + TITLE + "," + BODY
+ ") Values ('icesky ', 'android is developing rapidly ');";
Try {
Log. I ("haiyang: sql1 =", sql1 );
Log. I ("haiyang: sql2 =", sql2 );
Db.exe cSQL (sql1 );
Db.exe cSQL (sql2 );
SetTitle ("two data inserted successfully ");
} Catch (SQLException e ){
SetTitle ("failed to insert two data items ");
}
}
 
Private void deleteItem (){
Try {
SQLiteDatabase db = mOpenHelper. getWritableDatabase ();
Db. delete (TABLE_NAME, "title = 'haiyang'", null );
SetTitle ("delete a record whose title is haiyang ");
} Catch (SQLException e ){
}
}
 
Private void showItems (){
SQLiteDatabase db = mOpenHelper. getReadableDatabase ();
String col [] = {TITLE, BODY };
Cursor cur = db. query (TABLE_NAME, col, null );
Integer num = cur. getCount ();
SetTitle (Integer. toString (num) + "record ");
}
}
In the above Code
The DataBaseHelper class inherits the SQLieOpenHelper class and overwrites the onCreate and onUpgrade methods.
In oncreatestatement, we first constructed a SQL sentence, and then used db.exe cSQL (SQL) to execute the SQL statement. A table is generated.
Because currently we do not need to upgrade the database, we have not performed any operations in the onUpgrade () function.
========================================================== ========================================================== ====================
The following describes the addition, deletion, modification, and query methods.
The following describes the methods:
As mentioned above, create a SQLiteDatebase object to obtain a writable database.
Sring col [] = {TITLE, BODY };
SQLiteDatabase db = mOpenHelper. getReadableDatabase ();
Cursor cur = db. query (TABLE_NAME, col, null );
This statement puts the queried data in a Cursor. This Cursor encapsulates all the bar columns in TABLE_NAME of the data table. The query () method is described as follows:
The first parameter is the name of the table in the database.
The second parameter is the column information that we want to return data. As shown in the code above, we store the column name in an array named col.
The third parameter is selection, which is equivalent to the where part of the SQL statement. to return all the data, set it to null directly.
The fourth parameter is selectionArgs. In the selection section "? ", Then the string defined in selectionArgs will replace "? ".
The fifth parameter is groupBy. Defines whether the queried data is grouped. If it is null, it means no grouping is required.
The sixth parameter is having. It is equivalent to having in an SQL statement.
The seventh parameter is orderBy. This is sorting.
The last Integer num = cur. getCount () Statement uses the getCount () method to obtain the number of data in Cursor.
Understanding of Cursor:
Cursor itself is a pointer. It is a very useful interface in android. With Cursor, we can perform random read/write access to the result set queried from the database.
 
A simple demo of database operations is complete. It should be noted that, in the Android design philosophy, developers are encouraged to use internal classes, which is not only convenient, but also highly efficient.

 

This article is from "haiyang08101"