Storage of Android _ SQLite

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

Null: NULL

Integer: integer

Real: Floating Point Number

Text: String

BLOB: Big Data

In SQLite, the Boolean and date types are not specifically designed, because the boolean type can replace true and false with integer 0 and 1, the date type can have text, real, and integer values in a specific format instead of display. To facilitate the 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, see: http://www.sqlite.org/

The android. database. SQLite package is provided in the Android system for adding, deleting, modifying, and querying SQLite databases. The main method is as follows:

Begintransaction (): starts a transaction.

Close (): Close the connection and release resources.

Delete (string table, string whereclause, string [] whereargs): delete records that meet the specified conditions.

Endtransaction (): ends a transaction.

Execsql (string SQL): Execute the given SQL statement.

Insert (string table, string nullcolumnhack, contentvalues values): Insert a record based on the given conditions.

Openorcreatedatabase (string path, sqlitedatabase. cursorfactory factory): connects to the database based on the given conditions. If the 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): executes the query based on the given SQL.

Update (string table, contentvalues values, string whereclause, string [] whereargs): Modify the records that meet the conditions according to the given conditions.

In addition to the main appeal method, Android also provides many practical methods. In a word, it is very convenient for Android to access the database.

1. Create a database

Use openorcreatedatabase (string path, sqlitedatabase. cursorfactory factory) to create a database.

SQLiteDatabase db = this.openOrCreateDatabase("test_db.db", Context.MODE_PRIVATE, null);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 for sqlitedatabase. openorcreatedatabase, as seen in the Code, the native sqlitedatabase. the first parameter of the openorcreatedatabase () method requires an absolute path strength. All databases are stored in the "Data/data/application package name/databases" directory, therefore, completely inputting absolute paths is a repetitive and complex task. Using this. openorcreatedatabase saves this operation. The result after the operation is executed is as follows:

In addition, you can create a database by writing a method that inherits the sqliteopenhelper class and customize the database according to your needs. This is not described here.

2. Create a data table and insert data.

The Android system does not provide a special method for creating data tables. The data tables are created using SQL statements. The Code is as follows:

db.execSQL("CREATE TABLE tab(_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)");

After the table is created, use the insert (string table, string nullcolumnhack, contentvalues values) method to insert data. The parameter meanings are as follows:

Table: name of the target table.

Nullcolumnhack: Specifies the name of a column in the table. In SQLite, it is not allowed to insert records whose columns are null. Therefore, when the initial value has a null value, this column must be explicitly assigned null,

If you do not understand, please see here http://blog.csdn.net/jason0539/article/details/9977175

Values: contentvalues object, similar to map in Java. Store data in key-value pairs.

The data insertion code is as follows:

　  ContentValues values = new ContentValues();   for(int i=0;i<10;i++){       values.put("name", "test" + i);       db.insert("tab", "_id", values);   }

After this operation is performed, a data table named "tab" is added. The structure and data of this table can be easily viewed using the SQLite client (recommended: SQLite expert personal 3. For example:

3. modify data

The Update (string table, contentvalues values, string whereclause, string [] whereargs) method is used to modify data. The specific meanings of the four parameters are as follows:

Table: name of the target table.

Values: the new value to be modified.

Whereclause: WHERE clause, excluding the rest of the where keyword, which can contain? Placeholder. If there is no clause, it is null.

Whereargs: used to replace the whereclause parameter? Placeholder parameters. If you do not need to input a parameter, It is null.

The data modification code is as follows:

Contentvalues values = new contentvalues (); values. put ("name", "name"); dB. update ("tab", values, "_ id = 1", null); dB. update ("tab", values, "_ id =? ", New string [] {" 5 "}); // note the parameter passing

The execution result is the data of _ id = 1 and _ id = 5. The value of the Name field is changed to "name ".

4. query data.

We have been using the SQLite client to view data. Here we use the query () and rowquery () methods provided by Android to execute the query. The Code is as follows:

Cursor c = db.query("tab", null, null, null, null, null, null);c.moveToFirst();while(!c.isAfterLast()){    int index = c.getColumnIndex("name");    Log.d("SQLite", c.getString(index));    c.moveToNext();}c = db.rawQuery("select * from tab", null);c.moveToFirst();while(!c.isAfterLast()){    int index = c.getColumnIndex("name");    Log.d("SQLite", c.getString(index));    c.moveToNext();}

The query result is as follows:

It is clear that in the query results, the data on the red line is completely consistent, that is, the difference between the query and rawquery methods lies only in the difference of the required parameters. The rawquery method requires the developer to manually write the query SQL statement. The query method consists of the target table name, where clause, order by clause, having clause, and many other clauses. The two methods return the same cursor object. Therefore, the two methods are superior to the others when using them, depending on the specific situation. I prefer rawquery, because it is closer to traditional Java development and can be written by professional DBAs. This is more in line with the MVC idea, and the code is more readable. (There are too many parameters in the query method, so I cannot remember who is order.
By clause, who is having clause)

The cursor object can be understood as a cursor object. Anyone who has knowledge about the data is sure to be familiar with this object. Note that when reading data from the cursor object for the first time, you must move the cursor first. Otherwise, the cursor is located before the first record, causing an exception.

5. delete data

Deleting data is also very easy. You only need to call the delete method and pass in the parameter, delete (string table, string whereclause, string [] whereargs) the meanings of the three parameters are as follows:

Table: name of the target table.

Whereclause: WHERE clause, excluding the rest of the where keyword, which can contain? Placeholder. If there is no clause, it is null.

Whereargs: used to replace the whereclause parameter? Placeholder parameters. If you do not need to input a parameter, It is null.

The Code is as follows:

db.delete("tab", "_id=? or name=?", new String[]{"8", "name"});

The execution result is as follows:

The _ id = 8 and name = 'name' data are all deleted.

The addition, deletion, modification, and query operations of the entire database have been demonstrated. The last note is that after the data operation, you must call the close () method to close the connection and release the resource. I believe everyone understands this.