Android SQLite database application (ii)

Today, let's summarize the application of SQLite database in Android system. First look at some of the database introduction:

The SQLite database is an untyped database, which means that you can save any type of data to any column in any table, no matter what type the column is declared in when the table is create. Because SQLite will automatically ignore the type when it executes the Database Build table statement.

It is a kind of embedded database written in C language, it is a lightweight database, it is much faster than oracle/mysql in some basic simple statement processing, and its memory requirements are very low.

Note: It is important to note that the SQLite database is in a situation where the type matching is required, and when we build the table is like the Create tables table1 (ID integer primary key), SQLite corresponds to an integer primary The column value of key allows you to store 64-bit integers. It is completely valid for SQLite to not specify a type for a field.  Create Table Ex1 (A, B, c); -------------------------------------------------------------------------------

It is true that SQLite allows data types to be ignored, but it is still recommended to specify the data type in your CREATE TABLE statement. Because the data type is for you to communicate with other programmers, or you are ready to replace your database engine. SQLite supports common data types, such as:

CREATE TABLE EX2 (

A VARCHAR (10),

b NVARCHAR (15),

C TEXT,

D INTEGER,

E FLOAT,

F BOOLEAN,

G CLOB,

H BLOB,

I TIMESTAMP,

J NUMERIC (10,5)

K VARYING CHARACTER (24),

L National VARYING CHARACTER (16)

);

We introduced the basic information of SQLite database, and now we look at the database syntax of SQLite.

As for the syntax, such as additions and deletions, there is really nothing to say, and we usually use the same SQL basically a touch. My main tips here is how to page through SQLite, to be paged, you need to use the following statement

SELECT * FROM Tb_name limit offset 1

The limit 10 here represents the number of data to get, offset 1 indicates starting from the first few rows of data to obtain. (offset of the first line is 0)

---------------------------------------------------------------------

The combination of Android and SQLite:

Android provides us with a range of APIs to access the SQLite database. These classes or interfaces are typically stored in android.database and android.database.sqlite two packages.

How to create and open a SQLite database

In Android we use the Sqlitedatabase class to manipulate the database, and the methods to get its objects are generally as follows:

1, in Sqlitedatabase, we can see these methods

The path in the parameter represents the paths to the database (if it is under the default path/data/data/<package_name>/databases/, only the database name is provided here), and factory represents when the cursor object is created. The factory class used, if NULL, uses the default factory (where we can implement some data processing for our own factory), and flags represents some of the permissions that are set when the table is created, and multiple permissions are separated by |

Open_readonly represents opening a database as read-only

Open_readwrite to open the database as read-write

Create_if_necessary Creating a database when the database does not exist

No_localized_collators databases are not sorted according to the native language when the database is opened

In addition, the class has a method that provides us with a memory table (that is, a temporary table that is deleted when the connection to the database is closed). When creation fails, returns null

 Public Static Sqlitedatabase Create (Cursorfactory factory)

2, and create, open the file similar to this, the context here also provides us with some more convenient way to get the database object sqlitedatabase.

* @mode #MODE_PRIVATE     * @mode #MODE_WORLD_READABLE     * @mode #MODE_WORLD_WRITEABLE     * @mode #deleteDatabase     * Create or open a database     of the specified name */ public    int Mode, Cursorfactory Factory);

In addition, it provides a

Public abstract Boolean deletedatabase (String name);

This method removes the database named name under the current directory of the private database.

Public string[] Databaselist () returns the names of all private databases associated with the current activity.

3, the third way is through the realization of their own sqliteopenhelper.

Sqliteopenhelper is an abstract class that is provided by the system to manage the creation and updating of database tables, and we must implement our own helper classes by inheriting Sqliteopenhelper.

Generally we want to rewrite three methods, constructors, OnCreate methods, Onupgrade methods.

Sqliteopenhelper Creating a database is actually using the context inside of the method to create and open the database file under the private directory. This class gives us two ways to get a Sqlitedatabase object.

It is important to note here that the Getreadabledatabase () and Getwritabledatabase () methods are not the same as we imagined, one that returns read-only, and one that returns a read-write database.

The Getwritabledatabase method returns a writable database (if the database does not exist and creates one), and once it is created, the database is cached, and the next time the method is called, the cached object is returned directly to you. An exception is thrown when a writable object cannot be obtained due to a disk and a write full or permission problem! If after a while, the problem has been fixed, when you call the method again, you can still get a writable database.

Getreadabledatabase method, normally, the returned result is exactly the same as the Getwritabledatabase method. When encountering a disk full of such an accident, it does not run out of exception, but instead returns a read-only database object. When we call the change method again later, if the accident has been resolved, it will continue to return to the same database object as Getwritabledatabase.

------------------------------------------------------------------------------------

Using Database object Sqlitedatabase, the database is used for pruning and checking operations.

Here to emphasize, no matter how we get the database connection, we can see, we finally are to obtain a Sqlitedatabase object, and our so adding and removing the method is to be implemented through this object.

Add Data:

Parameter description:

Table names of tables to insert data into

Values: A Contentvalues object, similar to a map. Stores the value in the form of a key-value pair.

Conflictalgon: conflict resolution. For example, when the uniqueness of the primary key of the data table detects an error, it is processed according to the value set by that value.

Nullcolumnhack: When the values parameter is empty or there is no content in it, we will fail the insert (the underlying database does not allow inserting a blank line), in order to prevent this, we will specify a column name here, then if we find the behavior to be inserted empty row, The value of the column name that you specify is set to null and then inserted into the database.

(Here a lot of people will be puzzled, nullcolumnhack exactly what to use, why will appear?) When we do not set a column, it is not all the database to set the default value? Many fields set the default value is also null, the setting shown here is also null, what is the difference, how can it be displayed after the settings are allowed to insert it? In order to find the reason, I went to check the source code)

In fact, at the bottom, the various insert methods finally go back to call insertwithonconflict method, here we paste out the method of partial implementation

 /*** General method for inserting a row into the database. *     * @paramtable The table to insert the row into *@paramnullcolumnhack SQL doesn ' t allow inserting a completely empty row, * So if initialvalues are empty this Column would explicitly be * assigned a NULL value *@paraminitialvalues This map contains the initial column values for the * row. The keys should be the column names and the values the * column values *@paramconflictalgorithm for Insert Conflict Resolver *@returnThe row ID of the newly inserted row * OR The primary key of the existing row if the input param ' Conflictalgorit HM ' = * {@link#CONFLICT_IGNORE} * OR-1 if any error*/     Public Longinsertwithonconflict (String table, String nullcolumnhack, Contentvalues initialvalues,intconflictalgorithm) {        if(!IsOpen ()) {            Throw NewIllegalStateException ("Database not open"); }        //measurements Show Most SQL lengths <=StringBuilder sql =NewStringBuilder (152); Sql.append ("INSERT");        Sql.append (Conflict_values[conflictalgorithm]); Sql.append ("Into");        Sql.append (table); //measurements Show most values lengths <StringBuilder values =NewStringBuilder (40); Set<map.entry<string, object>> entryset =NULL; if(Initialvalues! =NULL&& initialvalues.size () > 0) {EntrySet=Initialvalues.valueset (); Iterator<map.entry<string, object>> entriesiter =Entryset.iterator (); Sql.append (‘(‘); BooleanNeedseparator =false;  while(Entriesiter.hasnext ()) {if(needseparator) {sql.append (", "); Values.append (", "); } needseparator=true; Map.entry<string, object> entry =Entriesiter.next ();                Sql.append (Entry.getkey ()); Values.append (‘?‘); } sql.append (‘)‘); } Else{sql.append ("(" + Nullcolumnhack + ")"); Values.append ("NULL"); }

Here we can see that when the data of our contentvalues type is initialvalues to null, or size<=0, the Nullcolumnhack setting is added to the SQL statement. We can imagine that if we do not add nullcolumnhack, then the final result of our SQL statement will be similar to insert into TableName () values (), which is obviously not allowed. And if we add the Nullcolumnhack, then SQL will turn into this, insert into TableName (nullcolumnhack) VALUES (null), which is obviously possible.

Delete Related:

Table: The names of the tables where the data is to be deleted

Whereclause: Conditional statement, note that the conditional statement here does not include where the word. For example Name= "Chenzheng_java" and age=23

Whereargs: We are in the WHERE clause, in order to prevent SQL injection, we usually pass? The way to do instead, and then again for? Assigned value.

When the deletion is performed normally, the number of deleted data is returned, otherwise, 0 is returned. Whereclause is set to "1" if we want to delete it all.

Query Related:

The main methods are

Parameter description:

Table name

Columns the name of the column to query, provided as an array.

Selection: Conditional statements

Selectionargs: In conditional statements? The content parameters,

Limit: Limits on the number of records taken

Eidttable: The first editable table name

Where Rawquery is the query through SQL statements OH.

Modify the operation:

----------------------------------------------------------------------------

Transaction Related:

and transaction-related methods

Transactionlistener: Triggered when a transaction executes the Begin/commit/rollback method

Notes on the exclusive and immediate modes:

Exclusive represents an exclusive, independent, when we use this pattern to get a transaction, before our transaction ends, the other threads and processes can neither read the database nor write to the database;

Immediate stands for instant, and when we use this mode to get transactions, other processes and threads cannot write to the database, but can read them normally.

To set the method for a transaction submission

Settransactionsuccessful (), remember to call this method, either the transaction is not committed, but it is automatically rolled back.

End transaction: Endtransaction (), usually after calling the Settransactionsuccessful () method and then calling the Endtransaction () method,

--------------------------------------------------------------------------------------

Remember, at the end of the program, be sure to call the close () method of Sqlitedatabase to close the database.

---------------------------------------------------------------------------------------

Traversal of the result set:

Android.database.Cursor interface

If you have used a cursor in JDBC, you should be familiar with all the methods in it. There is not much detail about usage here.

Android SQLite database application (ii)