My Android notes (II)-basic operations on the SQLite Database

SQLite is a lightweight database used by Android. Developing Android applications is naturally essential for database operations.

Android provides a SQLiteOpenHelper class to conveniently operate databases,

The main task of inheriting and extending the SQLiteOpenHelper class is to rewrite the following two methods.
OnCreate: When a database is created for the first time, this method is generally executed in the method to create tables and other initialization operations.
OnUpgrade: This method is called when the database is opened and the version number is different from the current version number.

 

The following is a demo of the basic SQLite operation I wrote.

 

It mainly contains two java classes --

DBUtil class inherited from SQLiteOpenHelper to implement various operation functions:

[Java]
Package barry. android. db;
 
Import android. content. ContentValues;
Import android. content. Context;
Import android. database. Cursor;
Import android. database. sqlite. SQLiteDatabase;
Import android. database. sqlite. SQLiteOpenHelper;
 
Public class DBUtil extends SQLiteOpenHelper {
 
Private final static String DATABASE_NAME = "db2004 ";
Private final static int DATABASE_VERSION = 1;
Private static final String TABLE_NAME = "students ";
Private static final String FILED_1 = "name ";
Private static final String FILED_2 = "password ";
 
Public DBUtil (Context context ){
Super (context, DATABASE_NAME, null, DATABASE_VERSION );
System. out. println ("new DBUtil ");
}
 
@ Override
Public void onCreate (SQLiteDatabase db ){
String SQL = "CREATE TABLE" + TABLE_NAME + "(" + FILED_1 + "TEXT," + FILED_2 + "TEXT );";
Db.exe cSQL (SQL );
System. out. println ("oncreate create table ");
}
 
@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){
Db.exe cSQL ("drop table if exists" + TABLE_NAME );
System. out. println ("onUpgrade Delete table ");
This. onCreate (db );
}

/**
* Query all the data in the table
* @ Return
*/
Public Cursor select (){
Return this. getReadableDatabase ()
. Query (TABLE_NAME, null );
}
 
/**
* Insert a piece of data into the table
* @ Param name value of Field 1
* @ Param password the value of field 2
*/
Public void insert (String name, String password ){
ContentValues cv = new ContentValues ();
Cv. put (FILED_1, name );
Cv. put (FILED_2, password );
This. getWritableDatabase (). insert (TABLE_NAME, null, cv );
This. getWritableDatabase (). close (); // closes the database object
}

/**
* Delete several data entries in the table.
* @ Param name: An array containing the "name" Field of all data to be deleted
*/
Public void delete (String [] name ){
String where = FILED_1 + "=? ";
String [] whereValues = name;
This. getWritableDatabase (). delete (TABLE_NAME, where, whereValues );
This. getWritableDatabase (). close ();
}

/**
* Update table data (Modify Field 2 "password ")
* @ Param name the value of the "name" Field of the data to be updated
* @ Param newPassword the new "password" Field
*/
Public void update (String name, String newPassword ){
ContentValues cv = new ContentValues ();
Cv. put (FILED_2, newPassword );
String where = FILED_1 + "=? ";
String [] whereValues = {name };
This. getWritableDatabase (). update (TABLE_NAME, cv, where, whereValues );
This. getWritableDatabase (). close ();
}

/**
* Clear table data
*/
Public void clean (){
This.getwritabledatabase(cmd.exe cSQL ("drop table if exists" + TABLE_NAME );
System. out. println ("clean Delete table ");
This. onCreate (this. getWritableDatabase ());
This. getWritableDatabase (). close ();
}
}
Package barry. android. db;

Import android. content. ContentValues;
Import android. content. Context;
Import android. database. Cursor;
Import android. database. sqlite. SQLiteDatabase;
Import android. database. sqlite. SQLiteOpenHelper;

Public class DBUtil extends SQLiteOpenHelper {

Private final static String DATABASE_NAME = "db2004 ";
Private final static int DATABASE_VERSION = 1;
Private static final String TABLE_NAME = "students ";
Private static final String FILED_1 = "name ";
Private static final String FILED_2 = "password ";

Public DBUtil (Context context ){
Super (context, DATABASE_NAME, null, DATABASE_VERSION );
System. out. println ("new DBUtil ");
}

@ Override
Public void onCreate (SQLiteDatabase db ){
String SQL = "CREATE TABLE" + TABLE_NAME + "(" + FILED_1 + "TEXT," + FILED_2 + "TEXT );";
Db.exe cSQL (SQL );
System. out. println ("oncreate create table ");
}

@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){
Db.exe cSQL ("drop table if exists" + TABLE_NAME );
System. out. println ("onUpgrade Delete table ");
This. onCreate (db );
}
 
/**
* Query all the data in the table
* @ Return
*/
Public Cursor select (){
Return this. getReadableDatabase ()
. Query (TABLE_NAME, null );
}

/**
* Insert a data entry to www.2cto.com.
* @ Param name value of Field 1
* @ Param password the value of field 2
*/
Public void insert (String name, String password ){
ContentValues cv = new ContentValues ();
Cv. put (FILED_1, name );
Cv. put (FILED_2, password );
This. getWritableDatabase (). insert (TABLE_NAME, null, cv );
This. getWritableDatabase (). close (); // closes the database object
}
 
/**
* Delete several data entries in the table.
* @ Param name: An array containing the "name" Field of all data to be deleted
*/
Public void delete (String [] name ){
String where = FILED_1 + "=? ";
String [] whereValues = name;
This. getWritableDatabase (). delete (TABLE_NAME, where, whereValues );
This. getWritableDatabase (). close ();
}
 
/**
* Update table data (Modify Field 2 "password ")
* @ Param name the value of the "name" Field of the data to be updated
* @ Param newPassword the new "password" Field
*/
Public void update (String name, String newPassword ){
ContentValues cv = new ContentValues ();
Cv. put (FILED_2, newPassword );
String where = FILED_1 + "=? ";
String [] whereValues = {name };
This. getWritableDatabase (). update (TABLE_NAME, cv, where, whereValues );
This. getWritableDatabase (). close ();
}
 
/**
* Clear table data
*/
Public void clean (){
This.getwritabledatabase(cmd.exe cSQL ("drop table if exists" + TABLE_NAME );
System. out. println ("clean Delete table ");
This. onCreate (this. getWritableDatabase ());
This. getWritableDatabase (). close ();
}
}

 

And the Activity that calls DBUtil:

[Java]
Package barry. android. db;
 
Import android. app. Activity;
Import android. database. Cursor;
Import android. OS. Bundle;
 
Public class Demo04_helperActivity extends Activity {
/** Called when the activity is first created .*/
@ Override
Public void onCreate (Bundle savedInstanceState ){
Super. onCreate (savedInstanceState );
SetContentView (R. layout. main );


DBUtil dbUtil = new DBUtil (this );
DbUtil. insert ("Jay Chou", "jaychou ");
DbUtil. insert ("Han", "twocolds ");
DbUtil. insert ("Guo Degang", "yunhejiuxiao ");

System. out. println ("********************************** all data information ");
PrintData (dbUtil );

DbUtil. delete (new String [] {"Jay Chou "});

System. out. println ("********************************** Delete' jay Chou's subsequent data ");
PrintData (dbUtil );

DbUtil. update ("Guo Degang", "longtengsihai ");;
System. out. println ("********************************** modify' guo Degang's password is 'longtengsihai '");
PrintData (dbUtil );

DbUtil. clean ();

}
 
Private void printData (DBUtil dbUtil ){
Cursor cursor = dbUtil. select ();
If (cursor. moveToFirst ()){
System. out. println ("number of data entries in the current table:" + cursor. getCount ());
Do {
System. out. println (cursor. getString (0) + cursor. getString (1 ));
} While (cursor. moveToNext ());
}
Cursor. close ();
}
}
Package barry. android. db;

Import android. app. Activity;
Import android. database. Cursor;
Import android. OS. Bundle;

Public class Demo04_helperActivity extends Activity {
/** Called when the activity is first created .*/
@ Override
Public void onCreate (Bundle savedInstanceState ){
Super. onCreate (savedInstanceState );
SetContentView (R. layout. main );


DBUtil dbUtil = new DBUtil (this );
DbUtil. insert ("Jay Chou", "jaychou ");
DbUtil. insert ("Han", "twocolds ");
DbUtil. insert ("Guo Degang", "yunhejiuxiao ");

System. out. println ("********************************** all data information ");
PrintData (dbUtil );

DbUtil. delete (new String [] {"Jay Chou "});

System. out. println ("********************************** Delete' jay Chou's subsequent data ");
PrintData (dbUtil );

DbUtil. update ("Guo Degang", "longtengsihai ");;
System. out. println ("********************************** modify' guo Degang's password is 'longtengsihai '");
PrintData (dbUtil );

DbUtil. clean ();

}

Private void printData (DBUtil dbUtil ){
Cursor cursor = dbUtil. select ();
If (cursor. moveToFirst ()){
System. out. println ("number of data entries in the current table:" + cursor. getCount ());
Do {
System. out. println (cursor. getString (0) + cursor. getString (1 ));
} While (cursor. moveToNext ());
}
Cursor. close ();
}
}
The procedure is as follows:

1. Create a database named "db2004" (that is, the "DATABASE_NAME" Field of DBUtil ).

2. When the database is created for the first time, run the onCreate method of DBUtil to create a table named students, which contains two fields (name and password ). (That is, the "TABLE_NAME, FILED_1, and FILED_2" fields of DBUtil ).

3. insert three pieces of data to the database: Jay Chou, Han, and Guo Degang ". Then, query all the data in the table and print it.

4. Delete Jay Chou ". Then, query all the data in the table and print it.

5. change the password of Data "Guo Degang" to "longtengsihai ". Then, query all the data in the table and print it.

6. Clear all data in the table and the program ends.

The execution result is:

02-07 11:22:47. 361: I/System. out (962): new DBUtil
02-07 11:22:47. 490: I/System. out (962 ): * *********************************** all data information
02-07 11:22:47. 490: I/System. out (962): number of data entries in the current table: 3
02-07 11:22:47. 500: I/System. out (962): Jay jaychou
02-07 11:22:47. 500: I/System. out (962): Han twocolds
02-07 11:22:47. 500: I/System. out (962): Guo Degang yunhejiuxiao
02-07 11:22:47. 511: I/System. out (962 ): * ********************************** after deleting 'Jay Chou 'data
02-07 11:22:47. 540: I/System. out (962): number of data entries in the current table: 2
02-07 11:22:47. 540: I/System. out (962): Han twocolds
02-07 11:22:47. 550: I/System. out (962): Guo Degang yunhejiuxiao
02-07 11:22:47. 560: I/System. out (962 ): * ********************************** modify the 'Guo Degang 'the password is 'longtengsihai'
02-07 11:22:47. 590: I/System. out (962): number of data entries in the current table: 2
02-07 11:22:47. 590: I/System. out (962): Han twocolds
02-07 11:22:47. 590: I/System. out (962): Guo Degang longtengsihai
02-07 11:22:47. 601: I/System. out (962): clean delete a table
02-07 11:22:47. 610: I/System. out (962): oncreate CREATE TABLE

The result is correct.

 

From Wolf's second nest