Android storage _ SQLite database _ lets you thoroughly learn how to use SQLite

The biggest feature of SQLite is that you can save various types of data to any field without worrying about the data type declared by the field.

For example, you can store strings in integer fields, floating point numbers in Boolean fields, or date values in numeric fields.

But there is one exception: the field defined as integer primary key can only store 64-bit integers. When saving data other than Integers to this field, an error will occur.

In addition, when parsing the create table statement, SQLite ignores the data type information following the field name in the create table statement. For example, the following statement ignores the type information of the Name field:

CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))

SQLite can parse most standard SQL statements, such:

Query statement: Select * from table name where Condition Clause group by grouping clause having... order by sorting clause (the order must not be wrong)

For example:

CREATE TABLE person (personid integer primary key autoincrement, name varchar (20))
SQLite can parse most standard SQL statements, such as:

Query statement: select * from table name where condition clause group by grouping clause having ... order by sort clause (the order must not be wrong)

Such as:

        select * from person

        select * from person order by id desc

        select name from person group by name having count (*)> 1

Pagination SQL is similar to mysql. The following SQL statement gets 5 records and skips the previous 3 records.

select * from Account limit 5 offset 3
or

select * from Account limit 3,5
Insert statement:

insert into table name (list of fields) values. Such as:

insert into person (name, age) values (‘小 明’, 3)
Update statement:

update table name set field name = value where condition clause. Such as:

update person set name = ‘Xiaoming’ where id = 10
Delete statement:

delete from table name where condition clause. Such as:

delete from person where id = 10
 

2.Use SQLiteOpenHelper to version the database

When we write database application software, we need to consider such issues: because the software we develop may be installed on the phones of many users, if the application uses the SQLite database, we must create the user's first The database table structure and some initialization records are added. In addition, when the software is upgraded, the data table structure needs to be updated. So, how can we realize that the database tables required by the application are automatically created on the user's mobile phone when the user first uses or upgrades the software? Can't we always create database tables manually on every mobile phone that needs this software installed? Because this requirement is faced by every database application, in the Android system, an abstract class called SQLiteOpenHelper is provided for us. It must be inherited before it can be used. It is achieved by managing the database version. demand.

To manage the database version, the SQLiteOpenHelper class provides two important methods, namely onCreate (SQLiteDatabase db) and onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion). It is used to update the database table structure when upgrading software. When calling the SQLiteOpenHelper's getWritableDatabase () or getReadableDatabase () method to obtain the SQLiteDatabase instance used to operate the database, if the database does not exist, the Android system will automatically generate a database, and then call the onCreate () method. It will only be called when the database. In the onCreate () method, you can generate the database table structure and add some initialization data used by the application. The onUpgrade () method is called when the version of the database changes. Generally, the version number only needs to be changed when the software is upgraded. The version of the database is controlled by the programmer. Assuming that the current version of the database is 1, due to changes in business The database table structure has been modified. At this time, the software needs to be upgraded. When upgrading the software, I want to update the database table structure in the user's mobile phone. In order to achieve this purpose, the original database version can be set to 2 ? Of course, if you want, set it to 100)), and update the table structure in the onUpgrade () method. When the number of software version upgrades is relatively large, at this time, the onUpgrade () method can be judged based on the original version number and the target version number, and then the corresponding table structure and data update are made.

Both the getWritableDatabase () and getReadableDatabase () methods can get a SQLiteDatabase instance for manipulating the database. However, the getWritableDatabase () method opens the database in read-write mode. Once the database's disk space is full, the database can only read but not write. If you use getWritableDatabase () to open the database, an error will occur. The getReadableDatabase () method first opens the database in read-write mode. If the disk space of the database is full, it will fail to open. When the open fails, it will continue to try to open the database in read-only mode.

public class DatabaseHelper extends SQLiteOpenHelper {

        // The class is not instantiated and cannot be used as a parameter of the parent constructor.It must be declared static

         private static final String name = "count"; // Database name

         private static final int version = 1; // database version

         public DatabaseHelper (Context context) {

              // The third parameter, CursorFactory, specifies the factory class that gets a cursor instance when the query is executed.It is set to null, which means the system default factory class is used.

                super (context, name, null, version);

         }

        @Override
        public void onCreate (SQLiteDatabase db) {

              db.execSQL ("CREATE TABLE IF NOT EXISTS person (personid integer primary key autoincrement, name varchar (20), age INTEGER)");

         }

        @Override
        public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {

               db.execSQL ("ALTER TABLE person ADD phone VARCHAR (12)"); // Add a column to the table

         }
}
In the actual project development, when the database table structure is updated, the data stored by the user in the database should be avoided.

 

3.Use SQLiteDatabase to operate SQLite database

Android provides a class called SQLiteDatabase, which encapsulates some APIs for manipulating the database. Use this class to complete the addition (Create), query (Retrieve), update (Update) and delete (Delete) operations (these Operation is referred to as CRUD). For the learning of SQLiteDatabase, we should focus on mastering the execSQL () and rawQuery () methods. The execSQL () method can execute SQL statements that change behavior such as insert, delete, update, and CREATE TABLE; the rawQuery () method is used to execute select statements.

Example of using execSQL () method:

SQLiteDatabase db = ....;

db.execSQL ("insert into person (name, age) values ('Blast Death', 4)");

db.close ();



Executing the above SQL statement will add a record to the person table. In practical applications, the parameter values of the "blasted special" in the statement will be provided by the user input interface. If the user input is spelled out into the insert above, Statements. When the content entered by the user contains single quotes, there will be a syntax error in the assembled SQL statement. To solve this problem, you need to escape single quotes, that is, to convert single quotes into two single quotes. Sometimes users often enter special SQL symbols such as "&". In order to ensure the correct syntax of the grouped SQL statements, these special SQL symbols in the SQL statements must be escaped. Obviously, every SQL statement is Doing this kind of processing is tedious. The SQLiteDatabase class provides an overloaded execSQL (String sql, Object [] bindArgs) method. Using this method can solve the previously mentioned problem, because this method supports the use of placeholder parameters (?).

Examples are as follows:

SQLiteDatabase db = ....;

db.execSQL ("insert into person (name, age) values (?,?)", new Object [] {"炸死 特", 4});

db.close ();
The first parameter of the execSQL (String sql, Object [] bindArgs) method is the SQL statement, and the second parameter is the value of the placeholder parameter in the SQL statement. The order of the parameter values in the array must correspond to the position of the placeholder. .

The rawQuery () of SQLiteDatabase is used to execute the select statement. The usage example is as follows:

SQLiteDatabase db = ....;

Cursor cursor = db.rawQuery (“select * from person”, null);

while (cursor.moveToNext ()) {

  int personid = cursor.getInt (0); // Get the value of the first column, the index of the first column starts from 0

  String name = cursor.getString (1); // Get the value of the second column

  int age = cursor.getInt (2); // Get the value of the third column

}

cursor.close ();

db.close ();
The first parameter of the rawQuery () method is the select statement; the second parameter is the value of the placeholder parameter in the select statement. If no placeholder is used in the select statement, this parameter can be set to null. An example of using a select statement with placeholder parameters is as follows:

Cursor cursor = db.rawQuery ("select * from person where name like? And age =?", New String [] {"% 炸死 特%", "4"});
Cursor is a result set cursor. It is used to randomly access the result set. If everyone is familiar with jdbc, Cursor and JDBC's ResultSet function is very similar. Use the moveToNext () method to move the cursor from the current row to the next row. If the last row of the result set has been moved, the result is false, otherwise it is true. In addition, Cursor has the commonly used moveToPrevious () method (for moving the cursor from the current row to the previous row, if the first row of the result set has been moved, the return value is false, otherwise true), the moveToFirst () method ( Used to move the cursor to the first row of the result set, if the result set is empty, the return value is false, otherwise true) and the moveToLast () method (to move the cursor to the last row of the result set, if the result set is Empty, the return value is false, otherwise true).

In addition to the execSQL () and rawQuery () methods introduced earlier, SQLiteDatabase also provides operation methods corresponding to add, delete, update, and query: insert (), delete (), update (), and query (). These methods are actually used by rookies who do not know much about SQL syntax. For programmers who are familiar with SQL syntax, directly use the execSQL () and rawQuery () methods to execute SQL statements to complete the addition, deletion, and update of data Query operation.

The Insert () method is used to add data, and the data of each field is stored using ContentValues. ContentValues is similar to MAP, compared to MAP, it provides put (String key, Xxx value) and getAsXxx (String key) methods for accessing data, where key is the field name and value is the field value. Xxx refers to various commonly used data types , Such as: String, Integer, etc.

SQLiteDatabase db = databaseHelper.getWritableDatabase ();

ContentValues values = new ContentValues ();

values.put ("name", "Blast Death");

values.put ("age", 4);

long rowid = db.insert ("person", null, values); // Returns the row number of the newly added record, which has nothing to do with the id of the primary key
Regardless of whether the third parameter contains data, executing the Insert () method will inevitably add a record. If the third parameter is empty, a record with a field value other than the primary key will be null. The Insert () method actually completes the addition of data by constructing an insert SQL statement. The second parameter of the Insert () method is used to specify the name of the null field. I believe everyone will be confused about this parameter. What is the role of this parameter ?

This is the case: if the third parameter values is Null or the number of elements is 0, because the Insert () method requires that a record with a Null value other than the primary key be added, in order to meet the needs of SQL syntax, the insert statement must Given a field name, such as: insert into person (name) values (NULL), if the field name is not given, the insert statement will become: insert into person () values (), obviously this does not meet the standard SQL syntax . For the field name, it is recommended to use a field other than the primary key. If an INTEGER type primary key field is used, after executing an insert statement like insert into person (personid) values (NULL), the value of the primary key field will not be NULL. If the third parameter values is not Null and the number of elements is greater than 0, you can set the second parameter to null.

A more detailed explanation of insert here is here http://blog.csdn.net/jason0539/article/details/9977175

Use of delete () method:

SQLiteDatabase db = databaseHelper.getWritableDatabase ();

db.delete ("person", "personid <?", new String [] {"2"});

db.close ();
The above code is used to delete records with personid less than 2 from the person table.

Use of update () method:

SQLiteDatabase db = databaseHelper.getWritableDatabase ();

ContentValues values = new ContentValues ();

values.put ("name", "Blasted"); // key is the field name and value is the value

db.update ("person", values, "personid =?", new String [] {"1"});

db.close ();
The above code is used to change the value of the name field of the record with the personid equal to 1 in the person table to "blasted".

query () method

In fact, the select statement is split into several components and then used as input parameters of the method:

SQLiteDatabase db = databaseHelper.getWritableDatabase ();

Cursor cursor = db.query ("person", new String [] {"personid, name, age"}, "name like?", New String [] {"% 炸死 特%"}, null, null, " personid desc "," 1,2 ");

while (cursor.moveToNext ()) {

         int personid = cursor.getInt (0); // Get the value of the first column, the index of the first column starts from 0

          String name = cursor.getString (1); // Get the value of the second column

          int age = cursor.getInt (2); // Get the value of the third column

}

cursor.close ();

db.close ();
The above code is used to find records in the person table whose name field contains the term "blasted". The matching records are sorted in descending order by personid. The sorted result skips the first record and only 2 records are obtained.

The meaning of the parameters of the query (table, columns, selection, selectionArgs, groupBy, having, orderBy, limit) method:

table: The table name. Equivalent to the part after the from keyword of the select statement. If it is a multi-table joint query, you can separate the two table names with a comma.

columns: The name of the column to be queried. Equivalent to the part of the select statement following the select keyword.

selection: Query condition clause, equivalent to the part after the where keyword of the select statement. The placeholder "?" is allowed in the condition clause.

selectionArgs: Corresponds to the value of the placeholder in the selection statement. The position of the value in the array and the placeholder in the statement must be the same, otherwise there will be an exception.

groupBy: equivalent to the part after the group by keyword of the select statement

having: equivalent to the part after the having keyword of the select statement

orderBy: equivalent to the part after the order by keyword of the select statement, such as: personid desc, age asc;

limit: Specify the offset and the number of records obtained, which is equivalent to the part after the limit keyword of the select statement.

 

Database instance:

This is a database helper class in my exercise, copied directly, can be used as a template to understand the database

package jason.demo;

import android.content.Context;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;

class RestaurantHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "lunchlist.db"; // Database name
private static final int SCHEMA_VERSION = 2; // The version number is after the upgrade. For the upgrade method, see the judgment in the onUpgrade method.
Ranch
public RestaurantHelper (Context context) {// Constructor, receives the context as a parameter, and directly calls the constructor of the parent class
super (context, DATABASE_NAME, null, SCHEMA_VERSION);
}
Ranch
@Override
public void onCreate (SQLiteDatabase db) {// Created is a list of lunch orders, id, dish name, address, etc.
db.execSQL ("CREATE TABLE restaurants (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, address TEXT, type TEXT, notes TEXT, phone TEXT);");
}

@Override
public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
if (oldVersion == 1 && newVersion == 2) {// Upgrade judgment, if you upgrade again, two more judgments will be added, from 1 to 3, from 2 to 3
db.execSQL ("ALTER TABLE restaurants ADD phone TEXT;");
}
}

public Cursor getAll (String where, String orderBy) {// Returns the data in the table, where is the search content passed in when called, and orderby is the list sort type passed in the settings
StringBuilder buf = new StringBuilder ("SELECT _id, name, address, type, notes, phone FROM restaurants");
Ranch
if (where! = null) {
buf.append ("WHERE");
buf.append (where);
}
Ranch
if (orderBy! = null) {
buf.append ("ORDER BY");
buf.append (orderBy);
}
Ranch
return (getReadableDatabase (). rawQuery (buf.toString (), null));
}
Ranch
public Cursor getById (String id) {// Get the id according to the click event, query the database
String [] args = {id};

return (getReadableDatabase ()
.rawQuery ("SELECT _id, name, address, type, notes, phone FROM restaurants WHERE _ID =?",
args));
}
Ranch
public void insert (String name, String address, String type, String notes, String phone) {
ContentValues cv = new ContentValues ();
Ranch
cv.put ("name", name);
cv.put ("address", address);
cv.put ("type", type);
cv.put ("notes", notes);
cv.put ("phone", phone);
Ranch
getWritableDatabase (). insert ("restaurants", "name", cv);
}
Ranch
public void update (String id, String name, String address,
String type, String notes, String phone) {
ContentValues cv = new ContentValues ();
String [] args = {id};
Ranch
cv.put ("name", name);
cv.put ("address", address);
cv.put ("type", type);
cv.put ("notes", notes);
cv.put ("phone", phone);
Ranch
getWritableDatabase (). update ("restaurants", cv, "_ID =? ",
args);
}
Ranch
public String getName (Cursor c) {
return (c.getString (1));
}
Ranch
public String getAddress (Cursor c) {
return (c.getString (2));
}
Ranch
public String getType (Cursor c) {
return (c.getString (3));
}
Ranch
public String getNotes (Cursor c) {
return (c.getString (4));
}
Ranch
public String getPhone (Cursor c) {
return (c.getString (5));
}
}
 

So how to use this helper class, here is an example, which is also a section extracted from the code

public class LunchList extends ListActivity {
 Cursor model = null; // Used to store query data
 RestaurantAdapter adapter = null; // Custom database helper class above
 public void onCreate (Bundle savedInstanceState) {
   super.onCreate (savedInstanceState);
   setContentView (R.layout.main);

   helper = new RestaurantHelper (this); // The parameter is context

   initList (); // Initialize the listview list, which is omitted a lot, and you can generally understand the database process

}

private void initList () {
  if (model! = null) {
   stopManagingCursor (model);
   model.close ();
  }
  
  String where = null;

  if (Intent.ACTION_SEARCH.equals (getIntent (). getAction ())) {// If there is a query instruction, all the conditional data in the table is obtained from the database according to the query content
   where = "name LIKE \"% "+ getIntent (). getStringExtra (SearchManager.QUERY) +"% \ "";
  }
  model = helper.getAll (where, prefs.getString ("sort_order", "name")); // Call the method in the helper class to get the data
  startManagingCursor (model);
  adapter = new RestaurantAdapter (model); // Set adapter
  setListAdapter (adapter); // Show it in the list
 }
Here is only an example of querying. The addition, deletion, and modification operations are similar, just use the helper to call the required method, and there is no problem in passing the parameters correctly.

Author: jason0539

Weibo: http://weibo.com/2553717707

Blog: http://blog.csdn.net/jason0539 (reproduced, please indicate the source)