Getting started with Android: SQLite

Document directory

• Main steps:
• The template code is as follows:
• 7. Statements for obtaining the number of records

I. Introduction to SQLite

SQLite is a small database embedded in Android. We can access it without introducing a driver;

SQLite visualization tool: SQLite expert professional 3;

: Http://www.kuaipan.cn/file/id_125546433842511875.htm

2. In this section, we will prepare for database operations, that is, this section does not actually operate the database, but only provides some prerequisites. In the next section, we will explain how to operate the database.
Main steps:

(1) create a class (usually called databasehelper) to inherit sqliteopenhelper and override the following three methods:

• Public databasehelper (context); // constructor with the context parameter, used to create a database

• Public void oncreate (sqlitedatabase dB); // called during database creation

• Public void onupgrade (sqlitedatabase dB, int old, int newversion); // called when the database version is changed

(2) create a database:

• Sqliteopenhelper helper = New databaseopenhelper (this. getcontext ());

• Sqlitedatabase DB = helper. getwritabledatabase ();

(3) SQL statement: db.exe csql (string SQL );

The template code is as follows:

Import android. content. context; import android. database. SQLite. sqlitedatabase; import android. database. SQLite. sqliteopenhelper; import android. util. log; public class databasehelper extends sqliteopenhelper {Private Static final string tag = "databasehelper"; Private Static int version = 1; Public databasehelper (context) {super (context, "test. DB ", null, Version) ;}@ overridepublic void oncreate (sqlitedatabase dB) mongodb.exe csql (" create table statement ") ;}@ overridepublic void onupgrade (sqlitedatabase dB, int oldversion, int newversion) {log. I (TAG, "version update... ");}}

When creating a database, the database is stored in the/data/package/databases directory;

Question 1: Test whether the database can be successfully created

If you want to test whether the database is successfully created (whether the creation statement you wrote is correct), you only need to write two lines of code in androidtestcase:

• Sqliteopenhelper helper = New databaseopenhelper (this. getcontext ());

• Sqlitedatabase DB = helper. getwritabledatabase ();

After running the test file, you can generate the DB file in/data/package/databases. After the export is sent to the local computer, you can see it with the SQLite visualization tool.

Question 2: How to complete version update in onupdate

If you want to update the database when updating the version, you can use the following tips:

• Db.exe csql ("Drop table if exists Table1"); // If table 1 exists, delete the table. You can perform the operation multiple times to delete all the tables.
• Oncreate (db );
  // Create a table structure for the database again

In this way, the version update can be completed successfully.

3. add, delete, modify, and query operations

This section describes how to add, delete, modify, and query data. Note the following:

• Db.exe csql () executes "Modify operation SQL.
• DB. rawquery () executes "query SQL.

These crud operations are encapsulated in the sqlitedatabase class. The following sections describe:

1) obtain the sqlitedatabase object

2) insert operation

3) Delete

4) query operations

5) update operations

1. Obtain the sqlitedatabase object sqliteopenhelper helper = new databasehelper (this. getcontext (); sqlitedatabase DB = helper. getwritabledatabase (); sqlitedatabase DB = helper. getreadabledatabase (); // This function actually calls the getwritabledatabase () function, that is, this function can also be used to write data.2. insert operations can be completed in three ways:

1. (General)Completed with the original SQL statement: Db.exe csql ("insert statement"); obviously, this method is flawed. Generally, some data is obtained from the user interface and then inserted into the database, so it is not flexible, grouping SQL statements is tiring.
2. (Recommended)Insert records in the parameter Array: Db.exe csql ("insert into person (name, age) values (?,?) ", New object [] {" xiazdong ", 20}); this is similar to preparedstatement in JDBC. The first parameter is an SQL statement, which can contain a placeholder"? ", The second parameter is an array of objects filled in by placeholders.
3. (If you are not familiar with SQL, you can use this)Use the built-in insert Function: DB. insert (string "tablename", string "nullcolums", contentvalues values); the first parameter is the name of the inserted Table; the second parameter is the column with the null value, that is, which columns have null values. If no column has null values, enter null in the second parameter. The third column is the object that stores data, there are many columns in the database table, such as name and age. We use the column name as the key, the corresponding data as the value, and insert <key, value> into contentvalues.

Example:
Method 1:
DB. insert ("insert into person (name, age) values ('xiazdong', 20 );"); Method 2:
DB. insert ("insert into person (name, age) values (?,?); ", New object [] {" xiazdong ", 20 });
Method 3:

ContentValues values = new ContentValues();values.put("name","xiazdong");values.put("age",20);db.insert("tablename",null,values);

Some people cannot figure out why the first method is tiring. Here is an example:For example, you need to enter a name value on the interface. If you enter a 'B' C' E, the SQL statement in the group spelling needs to be escaped with single quotes, Which is troublesome, this is because you need to switch it manually. 3. The delete operation Android provides three methods to perform the delete operation, which is similar to the insert operation.

1. Original SQL statement: db.exe csql ("delete statement ");
2. SQL statement with placeholder: db.exe csql ("delete from person where id =? ", NewObject[] {ID });
3. Built-in delete function: DB. Delete ("tablename", "id =? ", New string [] {ID +" "}); the first parameter is used to specify the table name. The second parameter is used to write the condition following the WHERE clause in the SQL statement, the third parameter is the parameter string array, which stores the placeholder information in the WHERE clause.

4. The update statement Android provides three methods to perform the update operation, which is similar to the insert operation.

1. Original SQL statement: db.exe csql ("Update statement ");
2. SQL statement with placeholder: db.exe csql ("Update person set age =? Where name =? ", NewObject[] {30, "xiazdong "});
3. Built-in update function: DB. update ("tablename", contentvalues values, string "WHERE clause", new string [] {"WHERE clause Parameters"}); the first parameter is used to specify the table name, the second parameter specifies the data updated by the set clause. The third parameter is the WHERE clause, and the fourth parameter stores the placeholder information in the WHERE clause.

The third method is hard to understand, so an example is given:

Contentvalues values = new contentvalues (); Values. Put ("Age", 30);/* set clause */ DB. Update ("tablename", values, "name =? "/* Where clause */, new string [] {" xiazdong "}/* Where clause parameter */);

5. query statement (1)

Cursor cursor = dB. rawquery ("select * From person where name =? ", New object [] {" xiazdong "}); While (cursor. movetonext () {// int Index = cursor. getcolumnindex (string name); // obtain the index based on name // string name = cursor. getstring (INT index); // obtain the value string name = cursor Based on the index. getstring (cursor. getcolumnindex ("name "));}

(2) cursor = dB. Query ("tablename", null/* Indicates select **/, "Name =? "/* Where statement */, New string [] {"xiazdong"}, null/* Group
By statement */, Null/* Having statement */, Null/* Order by statement */, Null/* Limit statement */); 6. Paging statement cursor = dB. rawquery ("select * From person limit ?,? ", New object [] {5, 5}); // The first 5 indicates that five records are skipped, And the next 5 indicates the number of records in the query result while (cursor. movetonext () {string name = cursor. getstring (cursor. getcolumnindex ("name "));}

7. Statement for obtaining the number of records (1)

Cursor cursor = db.rawQuery("select count(*) from person", null);cursor.moveToFirst();int count = cursor.getInt(0);

(2) dB. query ("person", new string [] {"count (*)"}, null); 3. The code of the transaction operation template is as follows:

DB. begintransaction (); try {// transaction operation dB. settransactionsuccessful (); // This sentence must exist, otherwise dB. endtransaction () is rolled back by default} finally {dB. endtransaction ();}