Storing data using the SQLite database

1.SQL basic Commands

1.1. Create a table

  Tables are made up of rows and columns, which are called fields, and rows are called records.

Use the Create command to make a table:

1   CREATE TABLE tab_student (studentId INTEGER PRIMARY KEY AUTOINCREMENT, 
2                             studentName VARCHAR(20), 
3                             studentAge INTEGER);

1.2. Inserting records (lines)

  Use the Insert command to insert one record at a time, with the general format of the Insert command:

  

INSERT into Tab_student (StudentID, Studentname, Studentage) VALUES (1, "Jack", 23);

1.3. Update record (line)

  You can use the Update command to update a record in a table that modifies one or more fields in one or more rows in a table. The general format of the Update command is:

  

UPDATE tab_student SET studentname= "Tom", studentage= "where studentid=1;"

1.4. Delete records (lines)

Use the delete command to delete records from a table, and the general format of the Delete command is:

DELETE from Tab_student WHERE studentid=1;

1.5. Query record (line)

The Select command is the only command that queries the database. The Select command is also the largest and most complex command in the SQL command.

The general form of the Select command is as follows:

SELECT [DISTINCT] heading

From tables

WHERE predicate

GROUP by columns

Having predicate

ORDER by columns

LIMIT Count,offset;

where each keyword (such as from, where, has, and so on) is a separate clause, each clause consists of a keyword and a followed parameter. Group BY and having to work together can constrain group by. Order by causes the recordset to be sorted by the value of one or more fields before it is returned, you can specify whether to sort by ASC (the default ascending order) or desc (descending). In addition, you can use the limit to qualify the size and range of the result set, count specifies the maximum number of records returned, and offset specifies the number of records to offset.

In the general form of the Select command above, all clauses are optional except for SELECT. The most common Select command currently consists of three clauses: SELECT, from, WHERE, with the following basic syntax:

  SELECT heading from tables WHERE predicate;

For example, to query the record you just inserted, you can use the following statement to complete:

  

SELECT StudentID, Studentname, studentage from Tab_student WHERE studentid=1;

2. Database Operation Helper Class Sqliteopenhelper

Android provides an important class sqliteopenhelper that is used to assist users in manipulating the SQLite database.

The constructor prototype for Sqliteopenhelper is as follows:

  Public Sqliteopenhelper (Context context, String name, Sqlitedatabase.cursorfactory factory, int version);

Where the parameter context represents the environment in which the application runs, containing the shared resources required by the application. The parameter name indicates the name of the Android database. The parameter factory is a Sqlitedatabase.cursorfactory class object that stores the result set that queries the Android SQLite database. The version of the parameter, which represents the edition of the database used by the application, is not the true version of SQLite, but rather the version of the SQLite database in the specified application, which triggers the Onupgrade () in the Sqliteopenhelper class when the version number is changed or Ondowngrade () method.

All Method 1 of the Sqliteopenhelper class is shown.

where the Close () method is used to close the SQLite database in the Sqliteopenhelper object, and the Getreadabledatabase () method is similar to the Getwriteabledatabase () method, The Getreadabledatabase () method opens the SQLite database specified in the Sqliteopenhelper object in a read-only state, and any operation that wants to modify the database is not allowed; Getwriteabledatabase () The method also opens the database, but allows normal read/write operations of the database, and when any method is called on a nonexistent database, the OnCreate () method of the Sqliteopenhelper object is called implicitly, and when the application accesses the database for the first time, OnOpen is called ( ) method, however, if the version number has changed, the Onupgrade () or Ondowngrade () method is called.

3. Database Class Sqlitedatabase

The Sqlitedatabase class is used to perform operational tasks on the database, such as table selection, insert, UPDATE, and DELETE statements.

Some of the methods commonly used in the Sqlitedatabase class for executing SQL statements are as follows.

(1) Execsql () method:

public void Execsql (String sql);

public void Execsql (String sql, object[] bindargs);

(2) query () method:

Public Cursor query (string table, string[] columns, string selection, string[] Selectionargs, String groupBy, String ha ving,string, String limit);

Public Cursor Query (boolean distinct, string table, string[] columns, string selection, string[] Selectionargs, String gr Oupby, string having, string-by-clause, string limit, cancellationsignal cancellationsignal);

Public Cursor query (string table, string[] columns, string selection, string[] Selectionargs, string groupBy, string hav ing,string);

Public Cursor Query (boolean distinct, string table, string[] columns, string selection, string[] Selectionargs, String gr Oupby, string having, string-by-clause, string limit);

(3) Querywithfactory () method:

Public Cursor querywithfactory (Sqlitedatabase.cursorfactory cursorfactory, Boolean distinct, String table, string[] Columns, string selection, string[] Selectionargs, String groupBy, String having, string-by-clause, string Limit,cancella Tionsignal cancellationsignal);

Public Cursor querywithfactory (Sqlitedatabase.cursorfactory cursorfactory, Boolean distinct, String table, string[] Columns, string selection, string[] Selectionargs, String groupBy, String having, string-by-clause, string limit);

(4) Rawquery () method:

Public Cursor rawquery (String sql, string[] Selectionargs, cancellationsignal cancellationsignal);

Public Cursor rawquery (String sql, string[] selectionargs);

(5) Rawquerywithfactory () method:

Public Cursor rawquerywithfactory (sqlitedatabase.cursorfactory cursorfactory, String sql, string[] Selectionargs, String edittable);

Public Cursor rawquerywithfactory (sqlitedatabase.cursorfactory cursorfactory, String sql, string[] Selectionargs, String edittable, cancellationsignal cancellationsignal);

where the Execsql () method has a parameter SQL, which is an SQL statement. The second parameter, Bindargs, receives an array in which each member of the array bundles a query. The Execsql () method is used to run query statements that do not return values, such as creating, inserting, updating, and modifying tables.

The query () method and the Querywithfactory () method run some lightweight, single-query statements in the database, including the SQL statement keywords table, columns, groupBy, have, order-by, limit, and so on. These methods allow you to pass SQL statements to related methods without having to use SQL statements directly.

Both the Rawquery () method and the Rawquerywithfactory () method also have a parameter SQL that executes the SQL query statement, and the return value is the cursor object. Both methods have a version that can receive a string array Selectionargs as a parameter, through which the Sqlitedatabase object will bind the question mark in the SQL statement (? Replace with the values in this array and replace them with the corresponding one by one positional relationships.

The Sqlitedatabase class provides approximately 50 methods, in addition to some methods for opening the database (such as OpenDatabase (), Openorcreatedatabase (), and so on). Methods used to manage SQLite transactions (such as BeginTransaction (), endtransaction (), etc.) to test whether the database is locked (such as Isdblockedbycurrentthread (), Isdblockedbyotherthread (), and methods for obtaining basic information about the database (such as Getmaximumsiza (), getversion (), etc.). This is not covered here, specifically, you can refer to the API Help documentation for the Sqlitedatabase class.

4. Cursor class cursors

In Android, the query data is implemented through the cursor class, and when we use the Sqlitedatabase.query () or Sqlitedatabase.rawquery () method, we get a cursor object, The cursor points to each record, which provides a lot of methods for querying, as shown in 2.

5. Package interface

With the above foundation, we can encapsulate an interface layer in accordance with MVC architecture, and implement the specific operation of SQLite database in this interface layer.

The following are examples of how to add data, update data, and query data to illustrate their specific implementation. Before implementing these methods, we first need to create a table. Here I created a class named Mysqliteopenhelper, which inherits from the Sqliteopenhelper class and implements the OnCreate () method of the Sqliteopenhelper class, which implements the operation of creating a table in the method. The specific source code is as follows:

1     /*
2 * Function : Create a table
3 * Author : Blog Park - still light
4 */
5 public void onCreate(SQLiteDatabase db) {
6 db.execSQL("CREATE TABLE tab_student (studentId INTEGER PRIMARY KEY AUTOINCREMENT, " +
                                                "studentName VARCHER(20), " +
                                                "studentAge INTEGER)");
7 }

With the above code, we created a table named "Tab_student" and created three fields in the table: StudentID, Studentname, and Studentage. And the StudentID field is specified as the primary key for the table.

5. 1 Adding data

Add data can be implemented using the Sqlitedatabase.execsql (String sql, object[] Bindargs) method, as follows:

1     /*
2 * Function : Add data
3 * Author : Blog Park - pres_cheng
4 */
5 public void addStudentInfo(Student student) {
6 db = mySQLiteOpenHelper.getWritableDatabase();
7 db.execSQL("INSERT INTO tab_student (studentId, studentName, studentAge) values (?, ?, ?)",
8 new Object[] {student.getStudentId(), student.getStudentName(), student.getStudentAge()});
9     }

Where, with the second parameter Bindargs, the question mark (?) in the SQL statement is ) to form a one by one correspondence to the values in this array, thus writing the values to the corresponding fields in the "tab_student" table.

5.2 Updating data

The method of updating data is approximately the same as the method for adding data, as follows:

1    /*
2 * Function : Update data
3 * Author : Blog Park - pres_cheng
4 */
5 public void updateStudentInfo(Student student) {
6 db = mySQLiteOpenHelper.getWritableDatabase();
7 db.execSQL("UPDATE tab_student SET studentName = ?, studentAge = ? WHERE studentId = ?",
         New Object[] {student.getStudentName(), student.getStudentAge(), student.getStudentId()});
8    }

5.3 Querying data

When querying the data, you need to return the results of the query using the Sqlitedatabase.rawquery () method, because you need to return the results of the query, as follows:

1     /*
  2 * Function : Query data
  3 * Author : Blog Park - pres_cheng
  4 */
  5 public Student findStudentInfo(int id) {
  6 db = mySQLiteOpenHelper.getWritableDatabase();
  7 String sql = "SELECT studentId, studentName, studentAge FROM tab_student WHERE studentId = ?";
  8 Cursor cursor = db.rawQuery(sql, new String[] {String.valueOf(id)});
  9 if(cursor.moveToNext()) {
10 return new Student(cursor.getInt(cursor.getColumnIndex("studentId")),
                                   cursor.getString(cursor.getColumnIndex("studentName")),
11 cursor.getInt(cursor.getColumnIndex("studentAge")));
12 }
13 return null;
14 } 

As you can see, the results of the query can be stored in the cursor object by using the Sqlitedatabase.rawquery () method. We can then use the Cursor object's GetXXX () method to remove the query result from the cursor object.

Of course, we can also implement more interface methods according to the actual needs, for example, delete data, get data list, get the number of data and so on.

Encapsulating the above interface methods, it is convenient to call these methods directly in the program, no need to care about the underlying database calls, but focus on the design of the UI interface implementation.

Storing data using the SQLite database