Android Study Notes (5) ---- SQLite introduction and related operations

/*************************************** **************************************** *************
* Author: conowen @ Dazhong
* E-mail: conowen@hotmail.com
* Http://blog.csdn.net/conowen
* Note: This article is original and only used for learning and communication. For more information, indicate the author and its source.

**************************************** **************************************** ************/

1. SQLite

Sq is short for structured query (structured query). Lite indicates lightweight. SQLite is an open-source relational database. Almost all modern programming languages and operating systems are supported. The latest version of SQLite is SQLite 3.

SQLite features:
1. Acid transactions
2. Zero Configuration-no installation or configuration management required
3. A complete database stored in a single disk file
4. database files can be freely shared among machines with different byte sequences.
5. Support database size to 2 TB
6. Small enough. There are about 30 thousand lines of C code, 250 kb, And the runtime memory is about several hundred kb.
7. faster operations than some popular databases in most common databases
8. Simple and Easy APIs
9. Includes TCL binding and supports binding in other languages through wrapper.
10. Source Code with good comments and more than 90% test coverage
11. Independence: no additional dependencies
12. Source is completely open. You can use it for any purpose, including selling it.
13. Support for multiple development languages: C, PHP, Perl, Java, ASP. NET, and Python

2. SQLite database operations

Operations on the SQLite database generally include: creating a database, opening the database, closing the database, and deleting the database.

2.1. How to create and open a database:

Use the openorcreatedatabase () method to create a database. If the database does not exist, a new database is created. If yes, the database is opened. Similar to the use of openfileoutput (string filename, mode), please refer to this blog, http://blog.csdn.net/conowen/article/details/7296121

The Return Value of the openorcreatedatabase () method is a sqlitedatabase object. For details, see the official description of the openorcreatedatabase () method below.

Public
Sqlitedatabase openorcreatedatabase
(String name, int mode,
Sqlitedatabase. cursorfactory factory) since: API Level 1

Open a new private sqlitedatabase associated with this context's application package. Create the database file if it doesn't exist.

Parameters

Name	The name (unique in the application package) of the database.
Mode	Operating mode. Use 0 orMODE_PRIVATEFor the default operation,MODE_WORLD_READABLE AndMODE_WORLD_WRITEABLETo control permissions.
Factory	An optional factory class that is called to instantiate a cursor when query is called.

Returns

• The contents of a newly created database with the given name.

The first parameter ---- Database Name, string type.

The second parameter ---- constant, as shown below

Constant meaning
MODE_PRIVATEDefault mode. The value is 0. files can only be accessed by applications that call this method.

MODE_WORLD_READABLEAll applications have the permission to read the file.

MODE_WORLD_WRITEABLEAll applications have the write permission on the file.

The third parameter-used to instantiate the cursor when the query method is called, usually null

2.2 shut down the SQLite Database

Shut down the database after the database operation is completed. Otherwise, a sqliteexception is thrown. To close a database, you only need to call the. Close () method of the sqlitedatabase object.

2.3 Delete A Database

Call the deletedatebase () method directly, for example:

this.deleteDatabase("mysqlite.db")

3. In the SQLite database (Table) "Table" Operation Method

The first thing to note is that a database can have many tables, and a table contains a lot of data. That is to say, the data stored in the database is actually saved inTable(Table. Operations on existing and created databases include creating tables, adding data to tables, deleting data from tables, modifying data in tables, querying data in tables, and deleting existing tables.

3.1 create a table

Execsql of the database
The (string SQL) method can create a table. For details about the execsql (string SQL) method, see the following official instructions.

Public void execsql (string SQL) since: API Level 1

Execute a single SQL statement that is not a select or any other SQL statement that returns data.

It has no means to return any data (such as the number of affected rows). Instead, you're encouraged to useinsert(String,
String, ContentValues),update(String, ContentValues, String,
String[]), Et al, when possible.

When usingenableWriteAheadLogging(), Journal_mode is automatically managed by this class. So, do not set journal_mode using "Pragma
Journal_mode '"statement if your app is usingenableWriteAheadLogging()

Parameters

SQL	The SQL statement to be executed. Multiple statements separated by semicolons are not supported.

Throws

Sqlexception	If the SQL string is invalid

In fact, the parameter "SQL" of the execsql (string SQL) method is an SQL statement, which is a string type. For example

SQLiteDatabase db; String sql = "CREATE  TABLE pic (_id INTEGER PRIMARY KEY , filename VARCHAR, data TEXT)";db.execSQL(sql);

For SQL statements, see related SQL programming materials.

In addition, the execsql () method can also be used to insert and delete data to a table.

It is also implemented through different SQL statements.

Db.exe csql (SQL); // SQL is a standard SQL statement.

In addition, you can use rawquery (); To read table data.

Public
Cursor rawquery (string SQL, string [] selectionargs) since: API Level 1

Runs the provided SQL and returnsCursorOver the result set.

Parameters

SQL	The SQL query. The SQL string must not be; terminated
Selectionargs	You may include? S in where clause in the query, which will be replaced by the values from selectionargs. The values will be bound as strings.

Returns

• ACursorObject, which is positioned before the first entry. Note thatCursorS are not synchronized,
  See the documentation for more details.

Although db.exe csql (SQL); method can also implement insert and delete operations, in addition dB. rawquery (SQL, selectionargs); you can also query a piece of data in a table. However, because standard SQL statements are involved, except for creating a new table, execsql (SQL) is used) method and delete a table at. We recommend that you use the following methods for other insert, delete, and query operations.

3.2 insert a data entry into the table

To insert data into the table of the database, you can directly call the database. insert () method, but the third parameter in insert () method is a contentvalues, which is actually a map that contains some key-value pairs ). Through the PUT Method of contentvalues, you can put the key-value pair into contentvalues, and then insert it to the table of the database through the DB. insert () method.

Public long insert (string table, string
Nullcolumnhack,
Contentvalues values) since: API Level 1

Convenience Method for inserting a row into the database.

Parameters

Table	The table to insert the row
Nullcolumnhack	Optional; may benull. SQL doesn't allow inserting a completely empty row without naming at least one column name. If your providedvaluesIs empty, no column names are known and an empty row can't be inserted. If not set to null, ThenullColumnHackParameter provides the name of nullable column name to explicitly insert a null into in the case where yourvaluesIs empty.
Values	This map contains the initial column values for the row. The keys shocould be the column names and the values the column values

Returns

• The row ID of the newly inserted row, or-1 if an error occurred

The first parameter of insert is the table name, the second parameter is generally null, and the third parameter is contentvalues. If the insert operation is successful, the ID of the newly inserted row is returned. If the insert operation is unsuccessful,-1 is returned.

Contentvalues CV = new contentvalues (); cv. put (Num, 1); cv. put (data, "test my database"); dB. insert (table, null, CV );

3.3 delete a piece of data in the table

Directly call the DB. Delete () method of the database.

Public intdelete (string table, string
Whereclause, string [] whereargs) since:
API Level 1

Convenience Method for deleting rows in the database.

Parameters

Table	The table to delete from
Whereclause	The optional where clause to apply when deleting. Passing null will delete all rows.

Returns

• The number of rows affected if a whereclause is passed in, 0 otherwise. To remove all rows and get a count pass "1" as the whereclause.

The first parameter ---- table name
The second parameter ---- the deletion condition, which is a string. If it is null, all rows will be deleted.
The third parameter is a string array used with whereclause.
Usage 1. If the whereclause condition has been provided directly, for example, "name =" + Num, num is the input parameter. Then whereargs can be set to null.
Usage 2. When "?" is included in whereclause "?" Then, the values in the whereargs array will replace "?"

3.4 Modify Table Data

Call the DB. Update () method

Public int Update (string table, contentvalues
Values, string whereclause, string [] whereargs) since: API Level 1

Convenience Method for updating rows in the database.

Parameters

Table	The table to update in
Values	A map from column names to new column values. null is a valid value that will be translated to null.
Whereclause	The optional where clause to apply when updating. Passing null will update all rows.

Returns

• The number of rows affected

The Update () parameter is described in the preceding methods.

3.5. query table data

Call the DB. Query () method.

Public
Cursor query (string table, string [] columns, string
Selection, string [] selectionargs, string groupby, string having, string
Orderby, string limit) since: API Level 1

Query the given table, returningCursorOver the result set.

Parameters

Table	The table name to compile the query against.
Columns	A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage That isn' t going to be used.
Selection	A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the where itself). Passing null will return all rows for the given table.
Selectionargs	You may include? S in selection, which will be replaced by the values from selectionargs, in order that they appear in the selection. The values will be bound as strings.
Groupby	A filter declaring how to group rows, formatted as an SQL group by clause (excluding the group by itself). Passing null will cause the rows to not be grouped.
Having	A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL having clause (excluding the having itself ). passing null will cause all row groups to be pinned ded, and is required when row grouping is not being Used.
Orderby	How to order the rows, formatted as an SQL order by clause (excluding the order by itself). Passing null will use the default sort order, which may be unordered.
Limit	Limits the number of rows returned by the query, formatted as limit clause. Passing null denotes no limit clause.

Returns

• ACursorObject, which is positioned before the first entry. Note thatCursorS are not synchronized,
  See the documentation for more details.

Parameter description:

Table ---- name of the table to be queried

 

Columns ---- array of column names to return

 

Selection ---- optional where clause. If it is null, all rows are returned.

Selectionargs ---- when selection contains "?" If the value of selectionargs is not null, the values in this array will replace "?"

 

Groupby ---- the optional group by clause. If its value is null, the row is not grouped.

 

Having ---- an optional having clause. If its value is null, it will contain all groups.

 

Orderby ---- optional order by clause. If its value is null, the default sorting rule is used.

 

Limit ---- an optional limit clause. If its value is null, the limit clause is not included.

Cursor Cr = dB. Query ("pic", null); // query all database data

The return value type is cursor.

Then, you can call the relevant methods of cursor to operate the queried data. For more information about how to use cursor, see the official instructions. The following lists some common methods:

3.6 delete a table

Implemented through the db.exe csql (SQL) method. The parameter SQL is a standard SQL statement.

db.execSQl("DROP TABLE mytable");

For specific implementation examples of SQLite, click the following link and refer to another blog

Click Open Link