Android SQLite database to fully master data storage _android

The biggest feature of SQLite is that you can save all types of data to any field without worrying about what the data type of the field declaration is.

For example, you can hold a string in an integer-type field, or hold a floating-point number in a Boolean field, or hold a date-type value in a character field.

One exception: A field defined as an integer PRIMARY key can store only 64-bit integers, and an error occurs when you save data other than integers to such a field.

In addition, when parsing a CREATE TABLE statement, SQLite ignores data type information that follows the field name in the CREATE TABLE statement, such as the following statement ignores type information for the Name field:

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 conditional clause GROUP BY group clause having ... order by sort clauses (must not be wrong)

Such as:

SELECT * from ' person ' select * ' person ' ORDER by 
 
ID desc 
 
Select name from person group by name have Count (*) &G T;1 

Paging SQL is similar to MySQL, the following SQL statement gets 5 records, skipping the previous 3 records

SELECT * From account limit 5 offset 3

Or

SELECT * From account limit 3,5

Insert statement:

Insert into Table name (field list) VALUES (value list). Such as:

INSERT into person (name, age) VALUES (' Xiaoming ', 3)

UPDATE statement:

Update table name SET field name = value WHERE Condition clause. Such as:

Update person set name= ' xiaoming ' where id=10

To delete a statement:

Delete from table name WHERE Condition clause. Such as:

Delete from person where id=10

2, the use of Sqliteopenhelper to the database version management

When we are writing database application software, the question needs to be considered: because the software we develop may be installed on many users ' phones, and if the application uses the SQLite database, we must create a database table structure to use and add some initialization records when the user first uses the software. In addition, the data table structure needs to be updated when the software is upgraded. So how can we implement a database table that automatically creates the application needs on the user's phone when the user first uses or upgrades the software? Never let us create database tables manually on every phone that needs to have this software installed? Because this requirement is faced by every database application, the Android system provides us with an abstract class called Sqliteopenhelper that must be inherited in order to be used, and it is through the management of the database version that the preceding requirements are implemented.

To manage the database version, the Sqliteopenhelper class provides two important methods, namely, OnCreate (Sqlitedatabase db) and Onupgrade (sqlitedatabase db, int oldversion, int newversion), used to generate database tables for initial use of software, which updates the database table structure when upgrading software. When the Sqliteopenhelper getwritabledatabase () or Getreadabledatabase () method is invoked to obtain the Sqlitedatabase instance used to manipulate the database, if the database does not exist, The Android system automatically generates a database, then calls the OnCreate () method, and the OnCreate () method is invoked when the database is first built, and in the OnCreate () method, the database table structure is generated and some initialization data used by the application is added. The Onupgrade () method is invoked when the version of the database changes. Generally in the software upgrade to change the version number, and the version of the database is controlled by the programmer, assuming that the database is now 1 version, due to changes in the Business database table structure, this time need to upgrade the software, Upgrade the software to update the user's phone database table structure, in order to achieve this, you can set the original database version to 2 (with a classmate asked to set to 3 lines?) Of course, if you like, set to 100 also line, and in the Onupgrade () method to implement the table structure update. When the software version of the upgrade more frequently, then in the Onupgrade () method can be based on the original number and target version number to judge, and then make the corresponding table structure and data updates.

Both the Getwritabledatabase () and Getreadabledatabase () methods can obtain a sqlitedatabase instance that is used to manipulate the database. However, the Getwritabledatabase () method opens the database read-write, and once the database is full of disk space, the database is read-only and cannot be written, and if you use Getwritabledatabase () to open the database, an error occurs. The Getreadabledatabase () method opens the database read-write, and if the database has full disk space, it will fail, and will continue to attempt to open the database as read-only when the failure is turned on.

The public class Databasehelper extends Sqliteopenhelper { 
 
    //class is not instantiated, is a parameter that cannot be used as a parent constructor, and 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 that the factory class that obtains a cursor instance when executing the query is set to NULL, representing the factory class 
 
        super (context, name, NULL, version) using the system default; 
 
    @Override public 
    void OnCreate (Sqlitedatabase db) { 
 
       db.execsql (' CREATE TABLE IF not ' EXISTS person (personid inte GER primary key AutoIncrement, name varchar, 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 user should avoid the loss of data stored in the database.

3, using sqlitedatabase operation SQLite Database

Android provides a class called Sqlitedatabase that encapsulates the API for manipulating databases that can be used to add (Create), query (Retrieve), update (update), and delete (delete) Operations (these operations are referred to as CRUD). For sqlitedatabase study, we should focus on mastering the Execsql () and Rawquery () methods. The Execsql () method executes SQL statements that have changed behavior, such as inserts, deletes, update, and create table; the Rawquery () method executes the SELECT statement.

Examples of the use of the Execsql () method:

Sqlitedatabase db = ...; 
 
Db.execsql ("INSERT INTO Man" (name, age) VALUES (' Killed special ', 4) "); 
 

Executing the above SQL statement adds a record to the person table. In practice, the parameter values in the statement are provided by the user input interface, and if the user input content is set to the INSERT statement as is, when the user input contains single quotes, There is a syntax error in the SQL statement that is spelled out by the group. To solve this problem, you need to escape the single quotation marks, which translates single quotes into two single quotes. Sometimes users will also input like "&" These special SQL symbols, in order to ensure that the group spelled the SQL statement correctly syntax, must be in the SQL statements of these special SQL symbols are escaped, obviously, for each of the SQL statements do this kind of processing work is more cumbersome. The Sqlitedatabase class provides an overloaded Execsql (String sql, object[] Bindargs) method that solves the problem mentioned earlier because this method supports the use of placeholder arguments (?).

Examples are as follows:

Sqlitedatabase db = ...; 
 
Db.execsql ("INSERT into Man (name, age) VALUES (?,?)", New object[]{"Dead Special", 4});  
 
Db.close (); 

The first parameter of the Execsql (String sql, object[] Bindargs) method is the SQL statement, the second parameter is the value of the placeholder parameter in the SQL statement, and the order of the parameter values in the array corresponds to the position of the placeholder.

The Sqlitedatabase rawquery () is used to execute the SELECT statement, using the following example:

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, and the parameter can be set to NULL if the SELECT statement does not use a placeholder. Examples of SELECT statements with placeholder parameters are as follows:

Cursor Cursor = Db.rawquery ("select * from person where name is like?") And age=? ", new string[]{"% dead Special% "," 4 "});

cursor is a result set cursor that is used to randomly access the result set, and if you are familiar with JDBC, cursor is similar to the ResultSet effect in JDBC. You can use the MoveToNext () method to move the cursor from the current row to the next line, or True if you have moved past the last row of the result set and the result is false. In addition cursor also has a common movetoprevious () method (used to move the cursor from the current row to the previous line, if it has moved past the first row of the result set, the return value is false, otherwise true), Movetofirst () method, which is used to move the cursor to the first row of the result set, true if the result set is empty, the return value is false, or true) and the Movetolast () method (used to move the cursor to the last row of the result set, if the result set is empty, and the return value is false).

In addition to the Execsql () and Rawquery () methods described earlier, Sqlitedatabase specifically provides actions for adding, deleting, updating, querying: Insert (), delete (), update (), and query (). These methods are actually for those who are unfamiliar with SQL syntax, for those familiar with SQL syntax, directly using the Execsql () and Rawquery () methods to execute SQL statements can complete the data add, delete, update, query operations.

The Insert () method is used to add data , and the data for each field is stored using contentvalues. Contentvalues is similar to map, which provides the put (string key, Xxx value) and getasxxx (string key) methods for accessing data, and key is the field name, and value is the field value. XXX refers to a variety of commonly used data types, such as: String, Integer, and so on.

Sqlitedatabase db = Databasehelper.getwritabledatabase (); 
 
Contentvalues values = new Contentvalues (); 
 
Values.put ("name", "Death Special"); 
 
Values.put ("Age", 4); 
 
Long rowID = Db.insert ("person", null, values);//Returns the line number of the newly added record, regardless of the primary key ID 

Regardless of whether the third parameter contains data, executing the Insert () method inevitably adds a record, and if the third argument is empty, a record is added that has a null field value other than the primary key. Inside the Insert () method actually completes the addition of the data by constructing the Insert SQL statement, and the second parameter of the Insert () method is used to specify the name of the null field. I'm sure you'll be wondering about this parameter.

Is this: if the third parameter values are null or the number of elements is 0, because the Insert () method requires that a record be added with a null value other than the primary key, the INSERT statement must be given a field name in order to satisfy the needs of the SQL syntax. For example, insert into person (name) values (NULL), if no field name is given, the INSERT statement becomes this way: INSERT into person () values (), which obviously does not satisfy standard SQL syntax. For field names, it is recommended that you use a field other than the primary key, and that the primary key field value will not be null after you execute an INSERT statement that resembles insert into person (PersonID) VALUES (NULL), if you use an integer type of primary key field. If the third argument values are not null and the number of elements is greater than 0, you can set the second argument to null.

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

Use of the Delete () method:

Sqlitedatabase db = Databasehelper.getwritabledatabase (); 
 
Db.delete ("Person", "personid<?", New string[]{"2"}); 
 
Db.close (); 

The code above is used to delete records PersonID less than 2 from the person table.

Use of the Update () method:

Sqlitedatabase db = Databasehelper.getwritabledatabase (); 
 
Contentvalues values = new Contentvalues (); 
 
Values.put ("name", "Kill Special");//key is the field name, value is 
 
db.update ("person", values, "Personid=?", New string[]{"1"})  
 
; Db.close (); 

The code above is used to change the value of the name field of a record in the person table equal to 1 to "PersonID".

Query () method

In effect, the SELECT statement is split into several components, and then as an input parameter to the method:

Sqlitedatabase db = Databasehelper.getwritabledatabase (); 
 
Cursor Cursor = db.query ("Person", new string[]{"Personid,name,age"}, "name like?", new string[]{"% kill extra%"}, NULL, NULL, "p Ersonid 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 code above is used to look up the records in the person table that contain the "kill", and the matching records are sorted in descending order, PersonID the first record for the sorted results, and get only 2 records.

Query (table, columns, selection, Selectionargs, GroupBy, having, by, limit) method The meaning of each parameter:

Table: List name. Corresponds to the section following the SELECT statement from the keyword. If you are a multiple table union query, you can separate the two table names with commas.

Columns: The name of the column to query. Corresponds to the part of the SELECT statement following the SELECT keyword.

Selection: The query condition clause, which is equivalent to the section after the SELECT statement where keyword, allows the placeholder "?" in the conditional clause.

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

GroupBy: Equivalent to the section following the SELECT statement GROUP BY keyword

Having: the equivalent of the part of a SELECT statement after having a keyword

By: Equivalent to the section following the SELECT statement order by keyword, such as: PersonID DESC, age ASC;

Limit: Specifies the offset and the number of records fetched, which is equivalent to the portion following the Limit keyword of the SELECT statement.

Database instance:

This is a database help class in my practice, copied directly, 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.
   
  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);
  }
   
  @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");
     
    if (where! = null) {
      buf.append ("WHERE");
      buf.append (where);
    }
     
    if (orderBy! = null) {
      buf.append ("ORDER BY");
      buf.append (orderBy);
    }
     
    return (getReadableDatabase (). rawQuery (buf.toString (), null));
  }
   
  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));
  }
   
  public void insert (String name, String address, String type, String notes, String phone) {
    ContentValues cv = new ContentValues ();
           
    cv.put ("name", name);
    cv.put ("address", address);
    cv.put ("type", type);
    cv.put ("notes", notes);
    cv.put ("phone", phone);
     
    getWritableDatabase (). insert ("restaurants", "name", cv);
  }
   
  public void update (String id, String name, String address,
                     String type, String notes, String phone) {
    ContentValues cv = new ContentValues ();
    String [] args = {id};
     
    cv.put ("name", name);
    cv.put ("address", address);
    cv.put ("type", type);
    cv.put ("notes", notes);
    cv.put ("phone", phone);
     
    getWritableDatabase (). update ("restaurants", cv, "_ID =?",
                                 args);
  }
   
  public String getName (Cursor c) {
    return (c.getString (1));
  }
   
  public String getAddress (Cursor c) {
    return (c.getString (2));
  }
   
  public String getType (Cursor c) {
    return (c.getString (3));
  }
   
  public String getNotes (Cursor c) {
    return (c.getString (4));
  }
   
  public String getPhone (Cursor c) {
    return (c.getString (5));
  }
} 

Here only examples of queries, additions and deletions are similar, just use the helper to call the necessary method on the line, the parameters of the transmission is no problem.

Thank you for reading, I hope to help you, thank you for your support for this site!