Android data storage (iii) -- SQLite

If you need a more robust data storage mechanism, you need to use a relational database. on Android, sqllite is required.

 

SQLite features: lightweight, embedded, and relational databases. It is portable, easy to use, small, efficient, and reliable. It shares a process space with the application that uses it.

 

Disadvantages of SQLite: Foreign keys are not supported and must be manually controlled using triggers.

 

We will first perform simple SQLite operations and learning:

9.3.1 create and use a database

1. Create SQLite

Openorcreatedatabase

Openorcreatedatabase ("mydatabase. DB ",

Sqlitedatabase.Create_if_necessary,Null);

Code snippet 9.3.1.1 SQLite database creation

 

Note: the return value is sqlitedatabase. Through this class, you can perform a series of database operations, including adding, deleting, modifying, and querying.

2. SQLite executes SQL

SQLite can directly execute SQL statements, but it must be an execution statement instead of a query statement because it does not return any results.

 

This.Sqlitedatabase.exe csql (sqlstr.Create_user_table);

Code snippet 9.3.1.2 Execute SQL to create a table

Note: sqlstr.Create_user_tableTable creation statement for the database:

Public
Static finalStringCreate_user_table= "Createtable user (ID integer primary key, Name text not null )";

Code snippet 9.3.1.3 SQL table creation statement

3. Foreign key constraints

This is important because SQLite (3.6.19) does not support foreign keys. Therefore, when using foreign keys, you must note that you need to manually create a trigger to execute the defined foreign key trigger when adding, modifying, and deleting the trigger.

Because around Android, SQLite is later than 3.6.19, that is, it supports foreign key constraints, so the use of triggers to customize Foreign keys is not described here.

4. Add records

To add data to a table, we create a contentvalues object to write the data field names and values to be inserted in pairs.

Contentvalues values =NewContentvalues ();

Values. Put ("ID", 1 );

Values. Put ("name ",
"Admin ");

This. Sqlitedatabase. insert ("user ",Null, Values );

Code snippet 9.3.1.4 add statement

Note: The insert statement has three parameters: Table Name,Value inserted when a column in values is null, Passed columns and values.

5. update records

For the data that needs to be modified in the table, you only need to save the corresponding column and modified value to contentvalues.

Contentvalues =NewContentvalues ();

Contentvalues. Put ("name ",
"Guest ");

This. Sqlitedatabase. Update ("user", contentvalues, "id =? ",
NewString [] {"1 "});

Code snippet 9.3.1.5 update statement

Note: update has four parameters, corresponding to the key-value pairs, where fields, and where fields to be modified.

6. delete records

The delete operation is similar.

This. Sqlitedatabase. Delete ("user", "id =? ",
NewString [] {"1 "});

Code snippet 9.3.1.6 Delete statement

7. query records

Query is performed by using the query method, which returns a cursor.

While(Cursor. movetonext ()){

String id = string.Valueof(Cursor. getint (cursor. getcolumnindex ("ID ")));

String name = cursor. getstring (cursor. getcolumnindex ("name "));

}

Code snippet 9.3.1.7 query statement

Note: The above is a simple query. You can use the cursor movement to determine whether the next row exists. Then, you can find the column number through the column name, and then obtain the column value corresponding to this loop based on the column number. Remember, you must disable cursor after each query, that isCursor. Close ()

Generally, the query parameters are as follows (from left to right ):

Parameters	Description	Remarks
[String]	Name of the table to be queried
[String Array]	List of returned column names	You can use null to return all
[String]	Where substatement	Null can be used, that is, unconditional query, with "?" Query Parameters
[String Array]	Query parameter value	When the query substatement is null, it must be null.
[String]	Group by clause	Null indicates no group
[String]	Having clause	When group by is null, it must be null.
[String]	Order by clause	If it is null, the default sorting is used.
[String]	Limit clause	If it is null, it indicates no limit.

Table 9.3.1 query statement parameter table

8. Complex Query

Note: There are two methods for complex queries: one is to construct complex queries through the sqlitequerybuilder class, and the other is to execute native SQL statement queries through rawquery. Relatively speaking, rawqeruy queries are simpler than sqlitequerybuilder

Sqlitequerybuilder:

Sqlitequerybuilder builder =NewSqlitequerybuilder ();

Builder. settables ("user, Info ");

Builder. appendwhere ("user.info = info. ID ");

String [] returncolumns = {

"User. ID ",

"User. Name ",

"Info. Sex"

};

String sortorder = "user. id asc ";

 

Cursor cursor = builder. Query (This. Sqlitedatabase, returncolumns,Null,Null,Null,Null, Sortorder );

Stringbuffer buffer =NewStringbuffer ();

While(Cursor. movetonext ()){

String id = string.Valueof(Cursor. getint (cursor. getcolumnindex ("ID ")));

String name = cursor. getstring (cursor. getcolumnindex ("name "));

String sex = cursor. getstring (cursor. getcolumnindex ("sex "));

 

Buffer. append ("[ID:" + ID + "," + "name:" + name + "," + "sex: "+ sex +"] "+" \ r \ n ");

}

Result. settext (buffer );

Cursor. Close ();

Code snippet 9.3.1.8 sqlitequerybuilder statement

Note: The query parameters are as follows:

Parameters	Description	Remarks
[Sqlitedatabase]	Database	Database entity used for query
[String Array]	Array of returned column names
[String]	Where clause	Query Conditions
[String Array]	Value corresponding to the selection condition	If the condition is null, it must be null.
[String]	Group by clause	Null indicates no group
[String]	Having clause	When group by is null, it must be null.
[String]	Sort order	Sorting Field

Table 9.3.2 query parameter table

Rawquery:

Public
Static finalStringRaw_complex_query= "Selectuser. ID, user. Name, info. Sex from user, Info"

+ "Whereuser. ID = info. ID order by user. id asc ";

Code snippet 9.3.1.9 native query statement

 

Cursor cursor =This. Sqlitedatabase. rawquery (sqlstr.Raw_complex_query,Null);

Stringbuffer buffer =NewStringbuffer ();

While(Cursor. movetonext ()){

String id =

String.Valueof(Cursor. getint (cursor. getcolumnindex ("ID ")));

String name = cursor. getstring (cursor. getcolumnindex ("name "));

String sex = cursor. getstring (cursor. getcolumnindex ("sex "));

 

Buffer. append ("[ID:" + ID + "," + "name:" + name + "," + "sex: "+ sex +"] "+" \ r \ n ");

}

Result. settext (buffer );

Cursor. Close ();

Code snippet 9.3.1.10 native SQL CALL statement

Note: rawquery parameters are as follows:

Parameters	Description	Remarks
[String]	SQL statement
[String Array]	Where ARGs	Value corresponding to the condition Query

Table 9.3.3 rawquery Parameters

9.3.2 bind data in view

The above explains how to perform data query. Now we can use the adapter to bundle the data for display in the view, instead of simply displaying the above.

Sqlitequerybuilder builder =NewSqlitequerybuilder ();

Builder. settables ("user, Info ");

Builder. appendwhere ("user.info = info. ID ");

String [] returncolumns = {

"User. ID as _ id ",

"User. Name ",

"Info. Sex"

};

String sortorder = "user. id asc ";

Cursor cursor = builder. Query (sqlitedatabase, returncolumns,Null,Null,Null,Null, Sortorder );

Startmanagingcursor(Cursor );

Listadapter adapter =NewSimplecursoradapter(This, R. layout.Activity_listitem, Cursor,

NewString [] {"_ id", "name", "sex "},

New
Int[] {R. Id.Item_id, R. Id.Item_name, R. Id.Item_sex});

This. Listview. setadapter (adapter );

Code snippet 9.3.2.1 bind data to view

Note: The basic query has been described above. When binding to an adapter, you needNote that there must be a _ ID column. If not, you can use an alias to map the primary key to _ id.

 

Case:

Androidstudy_database