Android data storage (5) API of SQLite database in Android

Last time I introduced you to the basic information and usage process of SQLite. I believe my friends have some knowledge about SQLite. Today, I will share with you how to use SQLite in Android.

Currently, mainstream mobile devices such as Android and iPhone all use SQLite as the storage engine for complex data. When we develop applications for mobile devices, we may need to use SQLite to store a large amount of data, so we need to master the SQLite development skills on mobile devices. For the Android platform, the system has built-in rich APIs for developers to operate SQLite. We can easily access data.

I. sqliteopenhelper

You can directly use sqlitedatabase to directly create a database, but Android provides a safer and more elegant way to operate the database, that is, sqliteopenhelper. Sqliteopenhelper is a tool class used to manage databases. It can be used to manage database creation and version updates.

Sqliteopenhelper is an abstract class, so you need to create its subclass and override its method to use it to create and manage databases.

Subclass inherits the sqliteopenhelper method and must override the following 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, 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.

In addition to rewriting these two methods, you must also have a constructor:

Sqliteopenhelper provides two constructor methods:

1. Public sqliteopenhelper (context, string name,
Sqlitedatabase. cursorfactory factory, int Version)

Create a helper object for database management.

Parameters:

Context: context object, used to open or create a database

Name: Database Name

Factory: The cursor factory, used to create a cursor object. If null is used, the default cursor is used.

Version: the database version number, starting from 1. If the version number is upgraded, callOnupgrade (sqlitedatabase, Int, INT) method. If the version number is reduced, callOndowngrade (sqlitedatabase, Int, INT)Method.

2. Public sqliteopenhelper (context, string name,
Sqlitedatabase. cursorfactory factory, int version, databaseerrorhandler errorhandler)

Create a helper object for database management.

Parameters:

Context: context object, used to open or create a database

Name: Database Name

Factory: The cursor factory, used to create a cursor object. If null is used, the default cursor is used.

Version: the database version number, starting from 1. If the version number is upgraded, callOnupgrade (sqlitedatabase, Int, INT) method. If the version number is reduced, callonDowngrade(SQLiteDatabase, int, int)Method.

Errorhandler: Used to report databases

The sqliteopenhelper class also has other methods, but we generally do not need to rewrite them.

Public synchronized void close (): close any opened database.

Public String getdatabasename (): returns the name of the opened database, which is the database specified by the constructor.

Public synchronized sqlitedatabase getreadabledatabase (): Open or create a read-only Database

Public synchronized sqlitedatabase getwriteabledatabase (): Open or create a readable database.

Public void ondowngrade (sqlitedadabase dB, int oldversion, int newversion): called when the database version is downgraded

Public void onopen (sqlitedatabase dB): called when the database is opened

How to Use sqliteopenhelper subclass to create and manage databases:

1. Use the constructor to create a new helper object

2. Use the Helper Object to call getreadabledatabase () or getwriteabledatabase () to return a sqlitedatabase object

3. Use the sqlitedatabase object for database operations.

A simple example:

Public class dbhelper extends sqliteopenhelper {Private Static final string database_name = "test. DB "; Private Static final int database_version = 1; Public dbhelper (context) {// set cursorfactory to null and use the default value super (context, database_name, null, database_version );} // when the database is created for the first time, oncreate will be called @ override public void oncreate (sqlitedatabase dB) {db.exe csql ("create table if not exists person" + "(_ id integer primary key autoincrement, name varchar, age integer, info text )");} // If the database_version value is changed to 2, the system will call onupgrade @ override public void onupgrade (sqlitedatabase dB, int oldversion, int newversion) if the existing database version is different) {db.exe csql ("alter table person add column other string ");}}

Ii. sqlitedatabase

The sqlitedatabase object represents a database (the underlying layer is a database file ). We can call several static methods related to sqlitedatabase to obtain a database object. Of course, the better method is described above. Use the sqliteopenhelper subclass to obtain a sqlitedatabase object.

Of course, after obtaining the sqlitedatabase object in any way, we can perform database operations. Most operations are similar to those in JDBC and are easy to understand.

Database Operations are nothing more than curd. For addition, deletion, and modification, we can directly use

Public void execsql
(String SQL)

Public void execsql
(String SQL, object [] bindargs)

These two methods can be used to directly execute standard SQL statements or specific methods. However, for those familiar with the SQL language, the previous method is more direct.

As for the query, it must be a little more complex. We can also use it directly.

Public cursor rawquery (string SQL, string [] selectionargs)

Method to directly execute standard SQL query statements. Of course, it can be implemented in a specific way like add, delete, and modify.

If no query method is used, the result set is returned as a cursor object. The cursor object class is the resultset object in JDBC.

The following are common methods for cursor objects:

// Assume C is a returned cursor object C. move (INT offset); // move to the specified row C. movetofirst (); // move to the first line C. movetolast (); // move to the last row C. movetoposition (INT position); // move to the specified row C. movetoprevious (); // move to the previous Row C. movetonext (); // move to the next row C. isfirst (); // specifies whether to point to the first C. islast (); // indicates whether to point to the last C. isbeforefirst (); // specifies whether to point to C. isafterlast (); // whether to point to C. isnull (INT columnindex); // specifies whether the column is null (the column base is 0) C. isclosed (); // whether the cursor has been closed c. getcount (); // The total number of data items c. getposition (); // returns the number of rows pointed to by the current cursor C. getcolumnindex (string columnname); // returns the column index value C for a column name. getstring (INT columnindex); // return the value of the specified column in the current row. string type C. getint (INT columnindex); // returns the value of the specified column in the current row, int type C. getfloat (INT columnindex); // return the Float Type of the value of the specified column in the current row

For more information about cursor, see the description in the official documentation. If you want to use it skillfully, you still need to practice more.

Iii. Concepts of transactions

The SQLite database also uses the transaction processing method. The sqlitedatabase class also provides APIs for transaction processing.

Use the begintransaction () method to start a transaction, execute database operations, and then call the settransactionsuccessful () method to set the transaction success mark. End the transaction commit with endtransaction.

You can also use the intransaction () method to determine whether a transaction is in progress.

The following are examples:

Public void payment () {sqlitedatabase DB = dbopenhelper. getwritabledatabase (); // starts the transaction database. begintransaction (); try {// execute Database Operations db.exe csql ("Update person set amount = amount-10 where personid =? ", New object [] {1}); db.exe csql (" Update person set amount = Amount + 10 where personid =? ", New object [] {2}); // sets the transaction flag to successful. When the transaction ends, the transaction dB is committed. settransactionsuccessful ();} catch (exception e) {Throw (E);} finally {// end transaction dB. endtransaction ();}}

Finally, we have finished the use process of sqlitedatabase. The following describes how to process database operations and how to return data sets in SQLite.