Detailed explanation of adding, deleting, changing and querying Android SQLite database

1、 Using embedded relational SQLite database to store data

On the Android platform, an embedded relational database SQLite is integrated. SQLite3 supports null, integer, real (floating point number), text (string text) and blob (binary object) data types. Although it supports only five types, SQLite3 also accepts varchar (n), char (n), decimal (P, s) and other data types, But it will be converted to the corresponding five data types during operation or saving. The biggest feature of SQLite is that you can save various types of data into any field, regardless of the data type declared by the field. For example, you can store a string in an integer field, a floating point number in a Boolean field, or a date value in a character field. There is one exception: the field defined as integer primary key can only store 64 bit integers. When saving data other than integers to this field, an error will be generated. In addition, when writing the CREATE TABLE statement, you can omit the data type information following the field name, such as the following statement. You can omit the type information of the name field: create table person (personality integer primary key autoincrement, name varchar (20)) SQLite can parse most standard SQL statements, such as:

When writing database application software, we need to consider this problem: because the software we develop may be installed on the mobile phones of many users. If the application uses SQLite database, we must create the database table structure used by the application and add some initialization records when users use the software for the first time. In addition, when upgrading the software, The data table structure also needs to be updated. So, how can we automatically create the database tables required by the application on the user's mobile phone when the user first uses or upgrades the software? Can't we create database tables manually on every mobile phone that needs to install this software? Because this requirement is faced by every database application, an abstract class called sqliteopenhelper is provided for us in Android system. We must inherit it before we can use it. It realizes the requirements proposed above by managing the database version. In order to manage the database version, sqliteopenhelper class provides two important methods: oncreate (sqlitedatabase dB) and onupgrade (sqlitedatabase dB, int OldVersion, int newversion). The former is used to generate database tables when using the software for the first time, and the latter is used to update the database table structure when upgrading the software. When calling getwritabledatabase() or getreadabledatabase() method of sqliteopenhelper to obtain the sqlitedatabase instance used to operate the database, if the database does not exist, the Android system will automatically generate a database, and then call oncreate() method. Oncreate() method will be called only when the database is generated for the first time. In oncreate() Method can generate database table structure and add some initialization data used by applications. The onupgrade () method is called when the database version changes. Generally, the version number needs to be changed when the software is upgraded, and the database version is controlled by the programmer. Assuming that the current version of the database is 1 and the database table structure is modified due to business changes, the software needs to be upgraded, When upgrading the software, you want to update the database table structure in the user's mobile phone. In order to achieve this goal, you can set the original database version to 2 (some students asked whether it is OK to set it to 3? Of course, it is OK to set it to 100 if you like), and update the table structure in the onupgrade () method. When the software version is upgraded many times, you can judge according to the original version number and target version number in the onupgrade () method, and then make the corresponding table structure and data update. Both getwriteldatabase () and getreadabledatabase () methods can get an sqlitedatabase instance used to operate the database. However, the getwritabledatabase () method opens the database in read-write mode. Once the disk space of the database is full, the database can only be read but not written. If you use getwritabledatabase () to open the database, an error will occur. Getreadabledatabase () method first opens the database in read-write mode. If the disk space of the database is full, it will fail to open. When the opening fails, it will continue to try to open the database in read-only mode.

Note: the difference between getwritabledatabase() and getreadabledatabase is that when the database is full, calling the former will report an error, and calling the latter will not. Therefore, if the database is not updated, it is best to call the latter to obtain the database connection.

code:

3、 Operating SQLite database with sqlitedatabase

Android provides a class called sqlitedatabase, which encapsulates some APIs for operating the database. This class can be used to complete the operations of adding (create), querying (retrieve), updating (update) and deleting (crud for short). For the study of sqlitedatabase, we should focus on mastering the methods of execsql () and rawquery (). The execsql () method can execute SQL statements with change behavior such as insert, delete, update and create table; The rawquery () method is used to execute the select statement. Use example of execsql() method:

The rawquery() of sqlitedatabase is used to execute the select statement. Examples are as follows:

In addition to the execsql () and rawquery () methods introduced earlier, sqlitedatabase also provides operation methods corresponding to addition, deletion, update and query: insert (), delete (), update () and query (). These methods are actually used by novices who don't know much about SQL syntax. For programmers who are familiar with SQL syntax, they can add, delete, update and query data by directly using execsql () and rawquery () methods to execute SQL statements. The insert () method is used to add data, and the data of each field is stored using contentvalues. Contentvalues is similar to map. Compared with map, it provides put (string key, XXX value) and getasxxx (string key) methods corresponding to accessing data. Key is the field name, value is the field value, and XXX refers to various common data types, such as string, integer, etc.

Use of delete() method:

The query () method actually splits the select statement into several components, which are then used as input parameters of the method:

4、 Use sqliteopenhelper to obtain the sqlitedatabase instance used to operate the database public class databasehelper extensions sqliteopenhelper {private static final string name = "ljqdb"; / / database name private static final int version = 1; / / database version...... omitted} public class helloactivity extensions activity {@ override public void oncreate (Bundle savedInstanceState) { ...... Button button =(Button) this.findViewById(R.id.button); button.setOnClickListener(new View.OnClickListener(){ public void onClick(View v) { DatabaseHelper databaseHelper = new DatabaseHelper(HelloActivity.this); sqliteDatabase db = databaseHelper.getWritableDatabase(); db.execsql("insert into person (name, new object [] {"Lin Jiqin", 4}); dB. Close();}});}} after calling getwritabledatabase() or getreadabledatabase() method for the first time, sqliteopenhelper will cache the current sqlitedatabase instance, which will normally maintain the open state of the database, so please call close() in time when you no longer need the sqlitedatabase instance Method to release resources. Once the sqlitedatabase instance is cached, multiple calls to getwriteledatabase() or getreadabledatabase() methods will get the same instance.

5、 Using transactions to manipulate SQLite databases

The beginTransaction () method using sqliteDatabase can open a transaction. When the program executes to the endTransaction () method, it will check whether the flag of the transaction is successful. If the program executes to endTransaction (), it calls setTransactionSuccessful () method to set the transaction flag to commit the transaction for success, if it does not call setTransactionSuccessful (). Method rolls back the transaction.

Examples are as follows: