How to Use SQLiteOpenHelper for Android

1. SQLiteOpenHelper

SQliteOpenHelper is an abstract class used to manage database creation and version management. To use it, you must implement its nCreate (SQLiteDatabase), onUpgrade (SQLiteDatabase, int, int) method.

OnCreate: the database is executed when it is created for the first time, such as creating tables and initializing data.

OnUpgrade: it is executed when the database needs to be updated, such as deleting a long-time table and creating a new table.

2. Implementation Code

Package xqh. utils;

Import android. content. Context;
Import android. database. sqlite. SQLiteDatabase;
Import android. database. sqlite. SQLiteOpenHelper;
Import android. database. sqlite. SQLiteDatabase. CursorFactory;

Public class DBHelper extends SQLiteOpenHelper {

// Database version
Private static final int VERSION = 1;
// Create a table
String SQL = "create table if not exists TestUsers" +
"(Id int primary key, name varchar, sex varchar )";

Public DBHelper (Context context, String name, CursorFactory factory,
Int version ){
Super (context, name, factory, version );
}

Public DBHelper (Context context, String name, int version ){
This (context, name, null, version );
}

Public DBHelper (Context context, String name ){
This (context, name, VERSION );
}

@ Override
Public void onCreate (SQLiteDatabase db ){
Db.exe cSQL (SQL );
}

@ Override
Public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion ){

}

}

 

3. Use of SQLite

Android provides a class named SQLiteDatabase, which encapsulates APIs for database operations. It can be used to implement basic CRUD operations. You can obtain database instances through getWritableDatabase () and getReadableDatabase.

4. Implementation Code

Package xqh. sqlite;

Import xqh. utils. DBHelper;
Import android. app. Activity;
Import android. database. SQLException;
Import android. database. sqlite. SQLiteDatabase;
Import android. OS. Bundle;
Import android. widget. Button;
Import android. util. Log;
Import android. view. View;
Import android. view. View. OnClickListener ;;

Public class TestSQLite extends Activity {

Button textBtn = null;
Button btnCreateDb = null;
Button btnCreateTb = null;
Button btnInsert = null;
Button btnUpdate = null;
Button btnDelete = null;
DBHelper dbHelper = null;
SQLiteDatabase db = null;

@ Override
Protected void onCreate (Bundle savedInstanceState ){
// TODO Auto-generated method stub
Super. onCreate (savedInstanceState );
SetContentView (R. layout. sqlitetest );

OpenDb ();

TextBtn = (Button) findViewById (R. id. btnHeader );
TextBtn. setFocusable (true );

// BtnCreateDb = (Button) findViewById (R. id. btnCreateDb );
// BtnCreateDb. setOnClickListener (createDbListener );
//
// BtnCreateTb = (Button) findViewById (R. id. btnCreateTb );
// BtnCreateTb. setOnClickListener (createTbListener );

BtnInsert = (Button) findViewById (R. id. btnInsert );
BtnInsert. setOnClickListener (insertTbListener );

BtnUpdate = (Button) findViewById (R. id. btnUpdate );
BtnUpdate. setOnClickListener (updateTbListener );

BtnDelete = (Button) findViewById (R. id. btnDelete );
BtnDelete. setOnClickListener (deleteTbListener );

}

Public OnClickListener deleteTbListener = new OnClickListener (){
Public void onClick (View v ){
DeleteTb ();
}
};

Public OnClickListener updateTbListener = new OnClickListener (){
Public void onClick (View v ){
UpdateTb ();
}
};

Public OnClickListener insertTbListener = new OnClickListener (){
Public void onClick (View v ){
InsertTb ();
}
};

// Public OnClickListener createDbListener = new OnClickListener (){
// Public void onClick (View v ){
// CreateDatabase ("TestDb01 ");
//}
//};

// Public OnClickListener createTbListener = new OnClickListener (){
// Public void onClick (View v ){
// CreateTable ();
//}
//};

///**
// * Create a database
// * @ Param dbName
// * @ Return
//*/
// Public SQLiteDatabase CreateDatabase (String dbName ){
// DbHelper = new DBHelper (this, dbName );
// Return dbHelper. getWritableDatabase ();
//}

/**
* Create a table
* @ Param db
*/
Public void CreateTable (){
Db = dbHelper. getWritableDatabase ();
String SQL = "create table if not exists TestUsers" +
"(Id int primary key, name varchar, sex varchar )";
Try {
Db.exe cSQL (SQL );
} Catch (SQLException e ){
Log. I ("err", "create table failed ");
}
}

/**
* Insert data www.2cto.com
*/
Public void InsertTb (){
Db = dbHelper. getWritableDatabase ();
String SQL = "insert into TestUsers (id, name, sex) values (2, 'hongkong', 'men ')";
Try {
Db.exe cSQL (SQL );
} Catch (SQLException e ){
Log. I ("err", "insert failed ");
}
}

/**
* Update data
*/
Public void UpdateTb (){
Db = dbHelper. getWritableDatabase ();
String SQL = "Update TestUsers set name = 'anhong ', sex = 'men' where id = 2 ";
Try {
Db.exe cSQL (SQL );
} Catch (SQLException e ){
Log. I ("err", "update failed ");
}
}

/**
* Delete data
*/
Public void DeleteTb (){
Db = dbHelper. getWritableDatabase ();
String SQL = "delete from TestUsers where id = 2 ";
Try {
Db.exe cSQL (SQL );
} Catch (SQLException e ){
Log. I ("err", "delete failed ");
}
}

/**
* Open a database
*/
Public void OpenDb (){
DbHelper = new DBHelper (this, "TestDb01 ");
Db = dbHelper. getWritableDatabase ();
}

/**
* Shut down the database.
*/
Public void CloseDb (){
DbHelper. close ();
}

@ Override
Protected void onDestroy (){
Super. onDestroy ();
If (db! = Null ){
Db. close ();
}
If (dbHelper! = Null ){
DbHelper. close ();
}
}

}

 

5. Some SQLite operation commands

5.1 adb shell enters command mode

5.2 enter the cd File Name

5.3 ls or ls-l

5.4 sqlite3 database name Access Database

5.5. view database information in schema

5.6 ctrl + d Exit sqlite Mode

6. Test

 

 

SQLiteDatabase is one of the core classes used to operate databases in the Android SDK. You can use SQLiteDatabase to open or operate a database. However, SQLiteOpenHelper sub-classes are often used to create and open databases and perform various database operations for Database Upgrade needs and ease of use.
SQLiteOpenHelper is an abstract class. There are two abstract methods in this class. The subclass of SQLiteOpenHelper must implement these two methods.
Public abstract void onCreate (SQLiteDatabase db );
Public abstract void onUpdate (SQLiteDatabase db, int oldVersion, int newVersion );
SQLiteOpenHelper automatically checks whether database files exist. If yes, the database will be opened. In this case, the onCreate () method will not be called. If the database file does not exist, SQLiteOpenHelper first creates a database file, opens the database, and calls the onCreate () method. Therefore, the onCreate () method is generally used to create tables, views, and other databases in a newly created database. That is to say, the oncreate () method is called when the database file is created for the first time.
First look at the SQLiteOpenHelper class constructor and then explain when the onUpdate () method will be called.
Public SQLiteOpenHelper (Context context, String name, CursorFactory factory, int version );
The name parameter indicates the database file name (excluding the file path). SQLiteOpenHelper creates a database file based on the file name. Version indicates the version number of the database. If the input database version is later than the version number created or upgraded, SQLiteOpenHelper calls the onUpdate () method. That is to say, an initial version number is generated when the database is created for the first time. When you need to build and upgrade tables and views in the database, you can increase the version number and recreate them. Now let's summarize the oncreate () and onUpdate () call processes.
1. If the database file does not exist, SQLiteOpenHelper will call the oncreate () method after the database is automatically created. In this method, you generally need to create tables, views, and other components. The database is generally empty before creation, so you do not need to delete the relevant components in the database.
2. If the database file exists and the current version number is higher than the version number created or upgraded last time, SQLiteOpenHelper calls the onUpdate () method and updates the database version number after calling this method. In addition to creating tables, views, and other components in the onupdate () method, you also need to delete these related components. Therefore, the database exists before calling the onupdate () method, it restores many database structures.
Based on the above two points, we can draw a conclusion. If the database file does not exist, only oncreate () is called (this method is called once when the database is created ). If the database file exists, the onupdate () method is called to upgrade the database and update the version number.

 
From the wangjia55 Column