Android learning diary 13 -- SQLite of data storage

SQLite lightweight database is mainly used for embedded systems. It only occupies hundreds of K of system resources and has the following features: lightweight: you only need a dynamic library to enjoy all the functions, in addition, the dynamic library size is also very small and independent: the Core Engine does not need to rely on third-party software isolation: All database information (tables, views, triggers) is stored in the same file cross-platform: supports most operating systems, you can also use the security: exclusive and shared locks on the PC side for transaction processing. Multi-Process Data Reading is supported. Only one process can modify five common data types: null integer: integer real: floating-point VARCHAR: small BLOB: Big Data Note: SQLite does not support BOOLEAN and DATE. Therefore, you can use 0, 1 instead of BOOLEAN (which is often the case for other databases) to replace DATE with INTEGER or VARCHAR, more references http://www.sqlite.org/provides Android in the android system. database. sqlite package for SQLi Add, delete, modify, and query te databases. The main method is as follows: 1. Create a database connection 1 // create a database. The first method is recommended. The second method uses an absolute path, which is complicated. 2. SQLiteDatabase db = this. openOrCreateDatabase ("test_db.db", Context. MODE_PRIVATE, null); 3 SQLiteDatabase db2 = SQLiteDatabase. openOrCreateDatabase ("/data/com. example. sqlite/databases/test_db2.db ", null);/data/com. example. sqlite/databases/has two database files. 2. Create a tab table with two fields: _ id and name. _ id is an auto-incrementing sequence, and name is not empty. 1 // create a tab table 2 db.exe cSQL ("create table tab (_ id integer primary key autoincrement, name text not null )"); 3. insert data copy code 1 // insert data 2 // similar to map, key-value pairs store data 3 ContentValues values = new ContentValues (); 4 for (int I = 0; I <5; I ++) {5 values. put ("name", "test" + I); 6 // insert to database, parameter: Table name, specify a column name in the table, data 7 db. insert ("tab", "_ id", values); 8} copy Code 4. modify data copy code 1 // modify 2 ContentValues values2 = new ContentValues (); 3 values2.put (" Name "," name "); 4 // modify the data. The parameters are table name, new data, where condition, and Clause (which can be null) 5 db. update ("tab", values2, "_ id = 1", null); 6 // where condition? Is a placeholder. The last parameter can replace the placeholder 7 db. update ("tab", values2, "_ id =? ", New String [] {" 10 "}); copy the code to copy the database file to a local computer. You can use SQLite Expert Personal 3 to view the file, after modification, the value of the name column with the _ id of 0 and 10 is changed. Since my program executes four times and inserts three more times, there are 20 records. 5. query data replication code 1 // query data two methods: query and rawQuery 2 // Cursor refers to the Cursor. Anyone who has learned about the database knows it, followed by a series of parameters, the target table name, where clause, order by clause, and having clause cannot be remembered. Use the second method 3 Cursor c = db. query ("tab", null, null); 4 // 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. moveToFirst (); 6 while (! C. isAfterLast () {7 int index = c. getColumnIndex ("name"); 8 Log. d ("SQLite", c. getString (index); 9 c. moveToNext (); 10} 11 12 // This type is recommended and does not need to be noted as many parameters 13 c = db. rawQuery ("select * from tab", null); 14 c. moveToFirst (); 15 while (! C. isAfterLast () {16 int index = c. getColumnIndex ("name"); 17 Log. d ("SQLite", c. getString (index); 18 c. moveToNext (); 19} copy the code day print result: common methods for cursor Description: 6. delete data and close connection 1 // delete data 2 db. delete ("tab", "_ id =? Or name =? ", New String [] {" 8 "," test0 "}); 3 4 // close the database connection and release the resource 5 db. close (); the actual development inherits the database's auxiliary class SQLiteOpenHelper for convenient operations. The main task is to rewrite the following two methods: onCreate (SQLiteDatabase db ): when a database is created for the first time, this method is generally used to perform initialization operations such as table creation in this method. OnUpgrade (SQLiteDatabse dv, int oldVersion, int new Version): This method is called when the Version number passed in when the database is opened is different from the current Version number. In addition to the two methods that must be implemented, the onOpen method can also be selectively implemented. This method will be called each time the database is opened. General code structure: Copy code 1 public class DatabaseHelper extends SQLiteOpenHelper {2 3 private static final String DB_NAME = "mydata. db "; // database name 4 private static final int version = 1; // database version 5 6 public DatabaseHelper (Context context) {7 super (context, DB_NAME, null, version); 8 // TODO Auto-generated constructor stub 9} 10 11 @ Override12 public void onCreate (SQLiteDatabase db) {13 String SQL = "create table user (username varchar (20) not null, password varchar (60) not null); "; 14 db.exe cSQL (SQL); 15} 16 17 @ Override18 public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {19 // TODO Auto-generated method stub20 21} 22 23}