Android Learning Note: SQLite database

(1)SQLite database

Let's start with a brief introduction to the SQLite database:

the year was released by D.richard Hipp .

is a lightweight, open source embedded relational database that occupies very low resources. Currently used in many embedded products, in embedded devices,SQLite may only need hundreds of K of memory is enough.

Compared to the Two open source world-renowned database management systems, Mysql and PostgreSQL, it is faster to process.

Under the Android platform, in addition to the operation of the SQLite database in the Android program , you can also perform various database operations in the command-line mode , including the various operations of the table, the data to increase, delete, modify, query.

（2) using command-line OperationsSQLite

Let's first describe how to use the command line to manage the database, where we need to use the Sqlite3.exe in the platform-tools directory of the Android SDK file, which is a simple SQLite Database management tool, similar to the command-line windows provided by MySQL, the operation of database statements with SQL standards are roughly the same.

Using command-line operationsSQLitethe steps:

1. Run the Android emulator or connect the real device.

2. cmd input command adb shell, login to the device's shell(need to get root permission of the device , prompt word identifier #).

3. Enter the command sqlite3 data/data/<package name>/databases_name to open the database.

4. After you open the database, you can use various commands to manipulate the database.

5. After you have completed the operation of the database, enter . exit to exit sqlite3and close the database access.

Of course, we can also export the database file to the computer, and then use the command line to manipulate the file.

When we need to extract information from a file (such as a heap txt file), you can write a C + + or Java program to create and edit the database.

Command-line Operation SQLite 's specific content is not explained in depth, understand the SQL statement, this will not be difficult.

(3) operation with codeSQLiteDatabase

For ease of use,Android encapsulates the operation of a SQLite database into two classes. The operation of the database is implemented through the class. These two classes are sqliteopenhelper,sqlitedatabase.

1. Sqliteopenhelper

Help classes for creating database and database versioning. With this class, you must create a subclass and implement the following methods:onCreate (sqlitedatabase),onupgrade (sqlitedatabase, int, int) .

OnCreate (Sqlitedatabase db): A database table is generated when the software is first used. Called only when the database is built.

Onupgrade (sqlitedatabase db, int oldversion, int newversion): Updates the database when the software is upgraded. the version parameter that is passed in when you Create the sqliteopenhelper object represents the database build number and is created again Sqliteopenhelper This method is triggered if the version number is higher than the old version number.

the Sqliteopenhelper method for opening and creating a database is as follows:

Sqlitedatabase getreadabledatabase (): Opens the Sqlitedatabase object in a read-write manner . Open as read-only when disk space is full.

Sqlitedatabase getwritabledatabase (): Opens the Sqlitedatabase object in a written manner . An error occurs when the disk space is full.

void Close (): Closes all open sqlitedatabase objects.

2. Sqlitedatabase

Sqlitedatabase itself provides some static methods to open and create databases, but it is not recommended:

Static Sqlitedatabase OpenDatabase (String path, sqlitedatabase.cursorfactory factory, int flags): Open path The database that the file represents.

Static Sqlitedatabase openorcreatedatabase (String path, Sqlitedatabase.cursorfactory Factory): Open path The database that the file represents is created if it does not exist.

Static sqlitedatabase openorcreatedatabase (file file, Sqlitedatabase.cursorfactory Factory):

Open The database that the file represents, and create it if it does not exist.

After getting the sqlitedatabase object, we have a common way to manipulate the database:

Transaction processing:

Transaction processing can help us improve the efficiency of processing data in large batches.

Use sqlitedatabase 's begintransaction () method to open a transaction, and the program executes to endtransaction () the Endtransaction method checks whether the flag of the transaction is successful if the program is executed before the settransactionsuccessful () is called ( ) the Settransactionsuccessful method sets the flag for the transaction to commit the transaction successfully, and then rolls back the transaction if no call to the () method is called.

void BeginTransaction (): Start transaction.

void Endtransaction (): End transaction.

Boolean intransaction (): Determines whether it is in a transaction.

Look at the following example:

public void Payment () {Sqlitedatabase db = Dbopenhelper.getreadabledatabase ();d b.begintransaction ();//Start transaction try { Db.execsql ("Update person set amount=amount-10 where personid=2") is;d b.execsql ("Update person set amount=amount+10 where Personid=5 ");d b.settransactionsuccessful ();//Set the transaction's flag to true, calling this method will commit the transaction in the execution to the Endtransaction () method, If you do not call this method, the transaction is rolled back to the Endtransaction () method. } catch (SQLException e) {} finally {db.endtransaction ();} To end a transaction, there are two cases where the commit or rollback of a commit,rollback,//transaction is determined by the transaction's flag, if the transaction's flag is true, it is committed, otherwise rolled back, and the transaction's flag is false by default}

int Delete (string table, String Whereclause, string[] whereargs): Deletes the specified data from the specified table. table represents the name of the tables that you want to delete, andwhereclause indicates that the records that satisfy the clause will be deleted,Whereargs Whereclause incoming parameters.

Long Insert (string table, String nullcolumnhack, contentvalues values): Inserts data into the specified table. Table represents the nameof thenullcolumnhack column that represents the column in which null is inserted ,value represents the data for a row of records.

int update (string table, contentvalues values, String whereclause, string[] whereargs): Updates the specified data in the specified table. Table represents the name of the tables,values represent the data that you want to update,andwhereclause indicates that the records that satisfy the clause are updated . Whereargs to be Whereclause clause to pass in the parameter.

void Execsql (String sql): Executes the SQL statement.

void Execsql (String sql, object[] bindargs): Executes an SQL statement with placeholders.

cursor query (string table, string[] columns, string selection, string[] Selectionargs, String groupBy , string has, string by) : Executes a query on the specified table. columns selection query conditional clauses, selectionargs selection incoming parameters, groupby control group, having Filtering the grouping, orderby

Cursor Query (string table, string[] columns, string selection, string[] Selectionargs, String groupBy, string having, stri Ng, String limit): Executes a query on the specified table. the Limit parameter specifies a maximum of several records to query.

Cursor query (boolean distinct, string table, string[] columns, string selection, string[] Selectionargs, string groupBy, S Tring having, string-by-clause, string limit): Executes a query on the specified table, and the first parameter controls whether duplicate values are removed.

Cursor rawquery (String sql, string[] selectionargs): Executes a SQL query with placeholders.

The Query method returns a cursor object, and we can think of the cursor as A collection of query results. the cursor provides the following way for us to move the record pointer of the query result.

Boolean Move (int offset): Moves the record pointer up or down the specified number of rows, andoffset positive is moved downward.

Boolean Movetofirst (): Moves the record pointer to the first row.

Boolean movetolast (): Moves the record pointer to the last row.

Boolean MoveToNext (): Moves the record pointer to the next line.

Boolean movetoposition (int position): Moves the record pointer to the specified row.

Boolean movetoprevious (): Moves the record pointer to the previous row.

The above method, the move succeeds returns true.

The steps to manipulate the database using Sqlitedatabase can be summarized as follows:

<1> gets the sqlitedatabase object.

<2> invokes a specific method to manipulate the database.

<3> Close sqlitedatabase, recycle resources.

(4) Visualization tools

Sqlitespy is very good.

Android Learning Note: SQLite database