Four storage methods for Android data: sharedpreferences, SQLite, content provider, and file (2) -- SQLite

SQLite is a lightweight database designed for embedded devices. It has only five data types, namely:

NULL: Null value

INTEGER: integer

REAL: floating point number

TEXT: character string

BLOB: Big data

In SQLite, the BOOLEAN and DATE types are not specifically designed, because the BOOLEAN type can use INTEGER 0 and 1 to replace true and false, and the DATE type can have specific format TEXT, REAL and INTEGER values instead of display For convenient operation of the DATE type, SQLite provides a set of functions. For details, see: http://www.sqlite.org/lang_datefunc.html. This simple data type design is more in line with the requirements of embedded devices. For more information about SQLite, please refer to: http://www.sqlite.org/

Provides the android.database.sqlite package in the Android system, which is used to add, delete, modify and check the SQLite database. The main methods are as follows:

BeginTransaction (): Start a transaction.

Close (): Close the connection and release resources.

Delete (String table, String whereClause, String [] whereArgs): According to the given conditions, delete the records that meet the conditions.

EndTransaction (): End a transaction.

ExecSQL (String sql): Execute the given SQL statement.

Insert (String table, String nullColumnHack, ContentValues values): Insert a record according to the given conditions.

OpenOrCreateDatabase (String path, SQLiteDatabase.CursorFactory factory): Connect to the database according to the given conditions, if this database does not exist, it is created.

Query (String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy): Execute the query.

RawQuery (String sql, String [] selectionArgs): Execute the query according to the given SQL.

Update (String table, ContentValues values, String whereClause, String [] whereArgs): According to the given conditions, modify the records that meet the conditions.

In addition to the main method of appeal, Android also provides many practical methods, in a word: in fact, Android access to the database is a very convenient thing.

First, create a database

Create database by openOrCreateDatabase (String path, SQLiteDatabase.CursorFactory factory) method.

1 SQLiteDatabase db = this.openOrCreateDatabase ("test_db.db", Context.MODE_PRIVATE, null);
2 SQLiteDatabase db2 = SQLiteDatabase.openOrCreateDatabase ("/ data / data / com.test / databases / test_db2.db3", null);
Both of the above methods can create a database. This.openOrCreateDatabase is from SQLiteDatabase.openOrCreateDatabase. As you can see in the code, the first parameter of the native SQLiteDatabase.openOrCreateDatabase () method requires an absolute path to be entered, and all databases are stored Under the "data / data / application registration / databases" directory, it is a repetitive and cumbersome task to input the complete absolute road strength. Using this.openOrCreateDatabase eliminates this operation. The result after the operation is as follows:



In addition, you can create a database by writing a method that inherits the SQLiteOpenHelper class. This method is a more advanced creation method, so it will not be described here.

Second, create a data table and insert data.

Android system does not provide a special method to create a data table, the data table is created by SQL statements, the code is as follows:

1 db.execSQL ("create table tab (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)");
After the table is created, insert the data through the insert (String table, String nullColumnHack, ContentValues values) method, where the meanings of the parameters are:

Table: target table name

NullColumnHack: Specifies the column name of a column in the table. Because in SQLite, it is not allowed to insert records where all columns are null, so when the initial value is empty, this column needs to be explicitly assigned null

Values: ContentValues object, similar to Map in java. Save data in key-value pairs.

Data insertion code is as follows:

1 ContentValues values = new ContentValues ();
2 for (int i = 0; i <10; i ++) {
3 values.put ("name", "test" + i);
4 db.insert ("tab", "_id", values);
5}
After performing this operation, a new data table named "tab" will be added, which can be easily viewed using the SQLite client (recommended: SQLite Expert Personal 3). Such as:



3. Modify the data

 The update (String table, ContentValues values, String whereClause, String [] whereArgs) method is used to modify the data. The specific meanings of its four parameters are as follows:

Table: target table name

Values: new values to be modified

WhereClause: where clause, remove the remaining part of where keyword, which can be taken? Placeholder. If there is no clause, it is null.

WhereArgs: Used to replace whereClause parameter? Placeholder parameters. If no parameters are required, it is null.

Data modification code is as follows:

1 ContentValues values = new ContentValues ();
2 values.put ("name", "name");
3 db.update ("tab", values, "_id = 1", null);
4 db.update ("tab", values, "_id =?", New String [] {"5"});
Execution results such as _id = 1 and _id = 5 data, the value of the name field is modified to "name".

Fourth, query data.

I have been using the SQLite client to view the data before. Here I use the query () and rowQuery () methods provided by android to execute the query. The specific code is as follows:

 1 Cursor c = db.query ("tab", null, null, null, null, null, null);
 2 c.moveToFirst ();
 3 while (! C.isAfterLast ()) {
 4 int index = c.getColumnIndex ("name");
 5 Log.d ("SQLite", c.getString (index));
 6 c.moveToNext ();
 7}
 8 c = db.rawQuery ("select * from tab", null);
 9 c.moveToFirst ();
10 while (! C.isAfterLast ()) {
11 int index = c.getColumnIndex ("name");
12 Log.d ("SQLite", c.getString (index));
13 c.moveToNext ();
14}
The query results are as follows:

It is clear that in the query results, the data on the red line is completely consistent, that is to say, the difference between the query and rawQuery methods is only the difference in the required parameters. The rawQuery method requires the developer to manually write out the query SQL, and the query method consists of the target table name, where clause, order by clause, having clause, and many other clauses, which are composed of SQL statements by the system. Both methods return the Cursor object at the same time, so which one is better or worse depends on the specific situation. I prefer the rawQuery method, because this method is closer to traditional Java development, and SQL statements can also be written by professional DBAs, which is more in line with MVC's ideas, and such code is more readable. (There are too many parameters in the query method, I ca n’t remember who is the order
by clause, who is having clause)

Cursor object can be understood as a cursor object. Anyone who has some knowledge of the data, I believe that this object will not be unfamiliar, and I will not be tired of it here. Just to remind you that when reading the data in the Cursor object for the first time, you must first move the cursor, otherwise the position of this cursor will cause an exception before the first record.

Fifth, delete data

Delete data is also a very simple thing. Just call the delete method and pass in the parameters. The three parameters of delete (String table, String whereClause, String [] whereArgs) have the following meanings:

Table: target table name

WhereClause: where clause, remove the remaining part of where keyword, which can be taken? Placeholder. If there is no clause, it is null.

WhereArgs: Used to replace whereClause parameter? Placeholder parameters. If no parameters are required, it is null.

The specific code is as follows:

db.delete ("tab", "_id =? or name =?", new String [] {"8", "name"});
The results are as follows:

The data with _id = 8 and name = ‘name’ was deleted.

The CRUD operation of the entire database is now over. As a final reminder, after operating the data, you must remember to call the close () method to close the connection and release resources. I believe everyone understands this reason.