Conditional database Android: simple use of sqllite

SQLite Analysis

SQLite is a lightweight, embedded, and relational database. It has been applied in iPhone, Android, and other mobile phone systems. SQLite is highly portable, easy to use, small, efficient, and reliable. SQLite is embedded into the application, and they share the same process space, rather than a separate process. From the external perspective, it is not like an RDBMS, but inside the process, it is complete, self-contained database engine.

In android, when you need to operate the SQLite database, you need to lose a SQLiteOpenHelper object, while SQLiteOpenHelper is an abstract class. You need to inherit this class and implement some methods in this class.

1. inherit from SQLiteOpenHelper and you will have the following two methods:

◆ GetReadableDatabase () creates or opens a query database

◆ GetWritableDatabase () creates or opens a writable Database

◆ Their city returns the SQLiteDatabase object, and the user performs subsequent operations through the lost SQLiteDatabase object

2. At the same time, the user can override the following callback functions and call back the following methods when operating the database:

◆ OnCreate (SQLiteDatabase): This method is called when the database is first created. Generally, we create a database table in this method.

◆ OnUpgrade (SQLiteDatabase, int, int): When the database needs to be modified, the Android system will actively call this method. In general, we delete database tables and create new database tables in this method. Of course, whether other operations are required depends on the needs of the application.

◆ OnOpen (SQLiteDatabase): This is the callback function when the database is opened and is generally not used.

Note

1. The following constructor must exist in the subclass of SQLiteOepnHelper:

Copy codeThe Code is as follows: 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 );
} For convenience, you can also create other constructors that contain two or three parameters.

2. The public void onCreate (SQLiteDatabase db) function is executed when getReadableDatabase () or getWritableDatabase () is called to create a database for the first time. In fact, it is the first time that the SQLiteDatabse object is lost, to call this method.

Public void onCreate (SQLiteDatabase db ){
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 ))");
}

The above is a written example. For more information, see

Copy codeThe Code is as follows: public class DBHelper {

Private static DatabaseHelper mDbHelper;
Private static SQLiteDatabase mDb;

Private static final String DATABASE_NAME = "life ";

Private static final int DATABASE_VERSION = 1;

Private Context mCtx;

Private static class DatabaseHelper extends SQLiteOpenHelper {

DatabaseHelper (Context context ){
Super (context, DATABASE_NAME, null, DATABASE_VERSION );
}

@ Override
Public void onCreate (SQLiteDatabase db ){
// Create a table structure
Db.exe cSQL ("create table life_history (id integer primary key autoincrement, type TEXT, city TEXT, company TEXT, pucNum TEXT, pucName TEXT );");
}

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

Public DBHelper (Context ctx) throws SQLException {
This. mCtx = ctx;
MDbHelper = new DatabaseHelper (mCtx );
MDb = mDbHelper. getWritableDatabase ();
}

/**
* Close the data source
*/
Public void closeConnection (){
If (mDb! = Null & mDb. isOpen ())
MDb. close ();
If (mDbHelper! = Null)
MDbHelper. close ();
}

/**
* Insert data Parameters
* @ Param tableName: Table Name
* @ Param initialValues the value of the column to be inserted
* @ Return
*/
Public long insert (String tableName, ContentValues initialValues ){

Return mDb. insert (tableName, null, initialValues );
}

/**
* Delete data
* @ Param tableName: Table Name
* @ Param deleteCondition Condition
* @ Param deleteArgs value corresponding to the condition (if deleteCondition contains "?" Will be replaced with the values in this array, one-to-one correspondence)
* @ Return
*/
Public boolean delete (String tableName, String deleteCondition, String [] deleteArgs ){

Return mDb. delete (tableName, deleteCondition, deleteArgs)> 0;
}

/**
* Update data
* @ Param tableName: Table Name
* @ Param initialValues column to be updated
* @ Param selection: Conditions for update
* @ Param selectArgs "?" In the update Condition Corresponding value
* @ Return
*/
Public boolean update (String tableName, ContentValues initialValues, String selection, String [] selectArgs ){
Return mDb. update (tableName, initialValues, selection, selectArgs)> 0;
}

/**
* Get a list
* @ Param distinct whether to repeat
* @ Param tableName: Table Name
* @ Param columns column to be returned
* @ Param selection condition
* @ Param selectionArgs: "?" In the condition Parameter Value
* @ Param groupBy Group
* @ Param having: group filtering Condition
* @ Param orderBy sorting
* @ Return
*/
Public Cursor findList (boolean distinct, String tableName, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy, String limit ){

Return mDb. query (distinct, tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit );
}

/**
* Retrieve a single row record
* @ Param tableName: Table Name
* @ Param columns the array of columns obtained
* @ Param selection condition
* @ Param selectionArgs: "?" In the condition Corresponding value
* @ Param groupBy Group
* @ Param having: grouping Condition
* @ Param orderBy sorting
* @ Param limit: data interval
* @ Param distinct whether to repeat
* @ Return
* @ Throws SQLException
*/
Public Cursor findOne (boolean distinct, String tableName, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy, String limit) throws SQLException {

Cursor mCursor = findList (distinct, tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit );

If (mCursor! = Null ){
MCursor. moveToFirst ();
}
Return mCursor;

}

/**
* Execute SQL statements (with parameters)
* @ Param SQL
* @ Param args "?" in SQL Parameter Value
*/
Public void execSQL (String SQL, Object [] args ){
MDb.exe cSQL (SQL, args );

}

/**
* Execute SQL
* @ Param SQL
*/
Public void execSQL (String SQL ){
MDb.exe cSQL (SQL );

}

/**
* Determine whether a table exists
* @ Param tabName: Table Name
* @ Return
*/
Public boolean isTableExist (String tableName ){
Boolean result = false;
If (tableName = null ){
Return false;
}

Try {
Cursor cursor = null;
String SQL = "select count (1) as c from sqlite_master where type = 'table' and name = '" + tableName. trim () + "'";
Cursor = mDb. rawQuery (SQL, null );
If (cursor. moveToNext ()){
Int count = cursor. getInt (0 );
If (count> 0 ){
Result = true;
}
}

Cursor. close ();
}
Catch (Exception e ){
}
Return result;
}

/**
* Determine whether a table has a field (Note: This method cannot determine whether the table exists, so it should be applied with isTableExist)
* @ Param tabName: Table Name
* @ Param columnName: column name
* @ Return
*/
Public boolean isColumnExist (String tableName, String columnName ){
Boolean result = false;
If (tableName = null ){
Return false;
}

Try {
Cursor cursor = null;
String SQL = "select count (1) as c from sqlite_master where type = 'table' and name = '" + tableName. trim () + "'and SQL like' %" + columnName. trim () + "% '";
Cursor = mDb. rawQuery (SQL, null );
If (cursor. moveToNext ()){
Int count = cursor. getInt (0 );
If (count> 0 ){
Result = true;
}
}

Cursor. close ();
}
Catch (Exception e ){
}
Return result;
}

}