Application of SQLite in Android and sqliteandroid

Application of SQLite in Android and sqliteandroid

Android provides the Application Programming Interface (Application Programming Interface) for creating and using SQLite databases ).

In the Android system, the SQLite database is managed and operated mainly by SQLiteDatabase and SQLiteOpenHelper.

 

(1) SQLiteOpenHelper class

SQLiteOpenHelper is a help class for SQLiteDatabase to manage database creation and version updates.

Because it is an abstract class, you must create a class to inherit it and implement two Abstract METHODS: onCreate () and onUpgrade.

Method	Description
SQLiteOpenHelper (Context context, String name, SQLiteDatabase. CursorFactory factory, int version)	Constructor
Abstract void onCreate (SQLiteDatabase db)	Called when creating a database
Abstract void onUpgrade (SQLiteDatabase db, Int oldVersion, int newVersion)	Called during Database Upgrade
Void onOpen (SQLiteDatabase db)	Called when the database is opened
SQLiteDatabase getReadableDatabase ()	Create or open a database
SQLiteDatabase getWritableDatabase ()	Create or open a read/write Database

The following is a specific example: DatabaseHelper. java

private static class DatabaseHelper extends SQLiteOpenHelper {    static final String DATABASE_NAME = "College.db";    static final int DATABASE_VERSION = 1;    static final String STUDENTS_TABLE_NAME = "students";    static final String CREATE_DB_TABLE =        " CREATE TABLE " + STUDENTS_TABLE_NAME +        " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +        " name TEXT NOT NULL, " +        " grade TEXT NOT NULL);";    DatabaseHelper(Context context){        super(context, DATABASE_NAME, null, DATABASE_VERSION);    }    @Override    public void onCreate(SQLiteDatabase db)    {        db.execSQL(CREATE_DB_TABLE);    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        db.execSQL("DROP TABLE IF EXISTS " + STUDENTS_TABLE_NAME);        onCreate(db);    }}

 

(2) create a database

Create a database object in the Activity, which may be created in the onCreate () method or event:

DatabaseHelper database = new DatabaseHelper(this);SQLiteDatabase db = null;db = database.getWritableDatabase();

First, an object of the SQLiteOpenHelper help class is provided, and then the SQLiteDatabase object is obtained by calling the getWritableDatabase () or getReadableDatabase () method of the object.

 

There are two other creation methods:

1. Use the SQLiteDatabase object openDatabase () method and openOrCreateDatabase () method to create a database;

2. The application Activity inherits from the parent class android. content. Context to create a database using openOrCreateDatabase.

  In addition

1. In DDMS, the College. db is generated in the/data/xxxx (package name)/databases path.

2. Delete the database file and use the deleteDatabase (String name) method of the android. content. Context class to delete the specified database.

 

(3) SQLiteDatabase class

There are many methods for the SQLiteDatabase class. Below are the most commonly used methods. For general data operations, you can use the corresponding method or write the required SQL statement, and then call the execSQL () method for execution.

Method	Description
OpenOrCreateDatabase (String path, SQLiteDatabase. CursorFactory factory)	Open or create a database
OpenDatabase (String path, SQLiteDatabase. CursorFactory factory, int flags)	Open a specified database
Insert (String table, String nullColumnHack, ContentValues values)	Add a record
Delete (String table, String whereClause, String [] whereArgs)	Delete a record
Update (String table, ContentValues values, String whereClause, String [] whereArgs)	Modify record
Query (String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy)	Query a record
ExecSQL (String SQL)	Execute SQL statements
Close ()	Close Database

 

(4) add, delete, modify, and query

There are two ways to process data records:

• Write an SQL statement for adding, deleting, modifying, and querying records, and execute it using the exeSQL () method.
• Use the corresponding method of the SQLiteDatabase object for operations

Add data

Insert (String table, String nullColumnHack, ContentValues values) Method
1st parameter table: Add a record data table;
2nd parameter nullColumnHack: Default Value of the empty column, usually null;
ContentValues: The ContentValues object. It is actually the field name of a key-value pair. The key name is the field name in the table and the key value is the value of the record data to be added. Use the put () method of the ContentValues object to store data in the ContentValues object.

ContentValues cv = new ContentValues (); // instantiate ContentValues to load the data cv to be inserted. put ("name", "Apple"); // Add student cv. put ("grade", "A"); // Add the score db. insert ("user", null, cv); // execute the insert operation

Or

String sql = "insert into students (name,grade) values ('Apple','A');db.execSQL(sql);

 

Data deletion

Delete (String table, String whereClause, String [] whereArgs) Method
1st parameter tables: Modify the data table of the record;
2nd parameter whereClause: the condition for deleting data, which is equivalent to the where clause of an SQL statement;
3rd parameter whereArgs: Delete the parameter array of the condition.

String whereClause = "name =? "; // The deleted condition String [] whereArgs = {" Apple "}; // The deleted condition parameter db. delete ("students", whereClause, whereArgs); // execute delete

Or

String sql = "delete from students where name='Apple'";db.execSQL(sql);

 

Data Modification

Update (String table, ContentValues values, String whereClause, String [] whereArgs) Method
1st parameter tables: Modify the data table of the record;
2nd parameter ContentValues: ContentValues object, which stores the modified data;
3rd parameter whereClause: the condition for modifying data, which is equivalent to the where clause of an SQL statement;
4th parameter whereArgs: the array for modifying data values.

ContentValues cv = new ContentValues (); // instantiate ContentValuescv. put ("grade", "AA"); // Add the field and content to be changed String whereClause = "username =? "; // Modify the condition String [] whereArgs = {" Appke "}; // modify the condition parameter db. update ("students", cv, whereClause, whereArgs); // execute the modification

Or

String sql = "update students set grade = 'AA' where name='Apple'";db.execSQL(sql);

 

Data Query

Query (String table, String [] columns, String selection,
String [] selectionArgs, String groupBy, String having, String orderBy) Method
1st parameter table: the data table of the query record;
2nd columns: The queried field. If it is null, it is all fields;
3rd Parameter selection: Query condition. The wildcard "?" can be used.
4th parameter selectionArgs: parameter array, used to replace "?" In the query condition;
5th parameter groupBy: query results are grouped by specified fields;
6th parameters having: Conditions for defining groups;
7th parameter orderBy: Sorting condition of the query result.

db.query("students",null,null,null,null,null,null);

Or

String sql = "select * from students";db.execSQL(sql);

 

(5) Processing the query result cursor

The data queried by the query () method is encapsulated in the query result Cursor object. The Cursor is equivalent to a Cursor in the resultSet result set in the SQL statement and can be moved forward and backward.

Common Cursor object methods include:

Method	Description
GetCount ()	Retrieve the total number of records
IsFirst ()	Determine if the first record is used
IsLast ()	Determine whether the last record is used
MoveToFirst ()	Move to the first record
MoveToLast ()	Move to the last record
Move (int offset)	Move to the specified record
MoveToNext ()	Move to the next record
MoveToPrevious ()	Move to the previous record
GetColumnIndex (String columnName)	Obtains the int type value of the specified column index.

Example:

Cursor c = db. query ("students", null, null); // query and obtain the cursor if (c. moveToFirst () {// determines whether the cursor is null for (int I = 0; I <c. getCount (); I ++) {c. move (I); // move to the specified record String name = c. getString (c. getColumnIndex ("name"); String grade = c. getString (c. getColumnIndex ("grade "));}}