Summary of knowledge points of SQLite database in Android

  • 2021-11-01 04:56:52
  • OfStack

Introduction to SQLite Database

SQLite is a lightweight database, which is a public domain project established by D. Richard Hipp and released its first version in 2000. Its design goal is embedded, and it occupies very low resources. It only needs to occupy hundreds of kB storage space in memory, which is one of the important reasons why Android mobile devices adopt SQLite database.

SQLite is a relational database management system that complies with ACID. ACID here refers to the four basic elements of correct execution of database transactions, namely atomicity (Atomicity), uniformity (Consistency), isolation (lolation) and persistence (Durabilily). It can support mainstream operating systems such as Windows/Linux/UNIX, and can be combined with many programming languages, such as Tcl, C #, PHP, Java, etc. Compared with Mysql and PostgreSQL, the processing speed of SQLite is faster.

SQLite has no server process, it holds data in a file that is cross-platform and can be used in other platforms. When saving data, it supports five data types: null (zero), integer (integer), real (floating point number), text (string text) and blob (binary object). But in fact, SQLite also receives varchar (n), char (n), decimal (p, s) and other data types, but it will be converted into five corresponding data types during operation or storage. Therefore, you can save various types of data to any field, regardless of the data type declared by the field.

SQLite database creation

In an Android system, it is very simple to create an SQLite database. The Android system recommends using a subclass of SQLiteOpenHelper to create a database, so you need to create one that inherits from SQLiteOpenHelper and override the onCreate () and onUpgrade () methods in that class.

Why use a subclass of SQLiteOpenHelper, because SQLiteOpenHelper is an abstract class (abstract) that must be inherited using its subclass and override the abstract methods of the parent class, where both onCreate () and onUpgrade () methods are abstract methods.


public class MyHelper extends SQLiteOpenHelper {
  @Override
  private static String DATABASE_NAME = "alan.db";
  private static int DATABASE_VERSION = 2;
  public MyHelper(Context context){
    super(context,DATABASE_NAME,null,DATABASE_VERSION);     // Invoke the constructor of the parent class 
  }

  // Database 1 This method is called when the second time is created 
  public void onCreate(SQLiteDatabase db){
    // Initializes the table structure of the database and executes 1 Article building table SQL Statement 
    db.execSQL("CREATE TABLE IF NOT EXISTS person(_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20), price INTEGER);

  }
  // Called when the database version increases 
  public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){
  
  }
}

//SQLiteOpenHelper Class constructors are 4 Parameters 
//context Represents the context, name Is the database name, factory Is a cursor factory, 1 Under normal circumstances null Value, version It is a database version, and the software will be used in future upgrades. 
public SQLiteOpenHelper(Context context, String name, CursorFactory factory, int version) {
    this(context, name, factory, version, null);
}

SQLite database usage

The previous introduction of SQLite database and how to create a database, the next SQLite database for the addition, deletion, change, check operation for a detailed explanation.

1. SQLite basic operation method 1

1. Add a piece of data

Taking the person table in the alan. db database as an example, how to use the insert () method of the SQLiteDatabase object to insert a piece of data into the table is described below.


public void insert(String name, String price){
  SQLiteDatabase db = myHelper.getWritableDatabase(); // Gets the writable  SQLiteDatabase  Object 
  ContentValues values = new ContentValues();  // Create  ContentValues  Object  
  values.put("name",name);     // Add data to the ContentValues Object 
  values.put("price",price);
  long id = db.insert("person",null,values); // Insert 1 Data to person Table  
  db.close();  // Close the database     
}

In the above code, the SQLiteDatabase object is obtained by the getWritableDatabase () method, then the ContentValues object is obtained and the data is added to the ContentValues object, and finally the inser () method is called to insert the data into the person table.

The insert () method takes three arguments, the first being the name of the table, the second being the column name that will be set to null if the row to be inserted is found to be empty, and the third being the ContentValues object. The ContentValues class is similar to the Map class, which stores data in the form of key-value pairs, where key represents the column name of the inserted data and value represents the data to be inserted.

It should be noted that after using the SQLiteDatabase object, you must call the close () method to close, otherwise the database connection will exist directly and consume memory continuously. When the system memory is insufficient, you will not get the SQLiteDatabase object, and you will report that the database is not closed.

2. Modify a piece of data

Here's how to modify the data in the person table using the update () method of SQLiteDatabase, with the following sample code.


public int update(String name, string price) {
  SQLiteDatabase db = myHelper.getwritableDatabasel;
  ContentValues values = new ContentValues();
  values.put ("price", price);
  int number = db.update("person",values,"name =?",new String[]{name});
  db.close();
  return number;
}

In the above code, the update () method is called through the SQLiteDatebase object db to modify the data in the database. The update () method receives four parameters, the first parameter represents the table name, the second parameter receives an ContentValues object, the third parameter can select the where statement, and the fourth parameter represents the placeholder parameter list in the whereClause statement. These strings will replace the "? ".

3. Delete a piece of data

Here's how to modify the data in the person table using the delete () method of SQLiteDatabase, with the following sample code.


public int delete(long id) {
   SQLiteDatabase db = myHelper.getwritableDatabasel;
   int number = db.delete("person",_id =?,"name =?",new String[]{id});
   db.close();
   return number;
 }

As you can see from the above code, deleting data is different from adding and modifying data, because deleting data does not require ContentValues to add data.

4. Query a piece of data

The query0 method of SQLiteDatabase is used in the data query, which returns a set of rows, Cursor. Cursor is a cursor interface, which provides methods to traverse query results, such as moving pointer method move (), obtaining column value method getString (), etc. Through these methods, attribute values and serial numbers in collections can be obtained.

It should be noted that after using the Cursor object, 1 must be shut down in time, otherwise it will cause memory leakage. Here's how to query data using the query () method of SQLiteDatabase, with the following sample code.


public boolean find(long id) {
  SQLiteDatabase db = helper .getReadableDatabase ();// Gets the readable  SQLiteDatabase  Object 
Cursor cursor = db.query("person", null, " _id =?", new String[]{id}),null, null, null) ; boolean result = cursor.moveToNext () ; cursor.close(); //  Close the cursor  db.close() ; return result; }

//Cursor  Important methods in: 

c.move(int offset); // Refer to the current position , Move to the specified line  
c.moveToFirst();  // Move to the 1 Row  
c.moveToLast();   // Move to the end 1 Row  
c.moveToPosition(int position); // Move to the specified line  
c.moveToPrevious(); // Move to the front 1 Row  
c.moveToNext();   // Move to Down 1 Row  
c.isFirst();    // Whether to point to the 1 Article  
c.isLast();   // Whether to point to the last 1 Article  
c.isBeforeFirst(); // Whether to point to the 1 Before the article  
c.isAfterLast();  // Whether to point to the last 1 After the article  
c.isNull(int columnIndex); // Specifies whether the column is empty ( Column cardinality is 0) 
c.isClosed();    // Whether the cursor is closed  
c.getCount();    // Total number of data items  
c.getPosition();  // Returns the number of rows pointed to by the current cursor  
c.getColumnIndex(String columnName);// Returns the column index value corresponding to a column name, and if it does not exist, returns -1 
c.getString(int columnIndex);  // Returns the value of the specified column in the current row  
c · getColumnIndexOrThrow(String columnName)  // Returns the specified column name from zero, and throws if it does not exist IllegalArgumentException  Abnormal. 
c.close() // Close the cursor and free the resource 

In the above code, This paper introduces the use of query () method to query the data in person table, The query () method receives seven parameters, the first parameter represents the table name, the second parameter represents the column name of the query, the third parameter receives the query condition clause, the fourth parameter receives the condition value corresponding to the query clause, the fifth parameter represents the grouping mode, the sixth parameter receives the having condition, that is, the filter defining the group, and the seventh parameter represents the sorting mode.

2. SQLite basic operation method 2

This operation is similar to executing an SQL statement (the syntax is similar to SQL server).

1. Add a piece of data


 db.execSQL("insert into perosn (name, price) values(?,?)",new String[]{name,price})

2. Modify a piece of data


db.execSQL("update person set price =? where name =?", new String[]{price,name});

3. Delete a piece of data


db.execSQL("delete from person where _id = ?",new String[]{id});

4. Query a piece of data


//SQLiteOpenHelper Class constructors are 4 Parameters 
//context Represents the context, name Is the database name, factory Is a cursor factory, 1 Under normal circumstances null Value, version It is a database version, and the software will be used in future upgrades. 
public SQLiteOpenHelper(Context context, String name, CursorFactory factory, int version) {
    this(context, name, factory, version, null);
}
0

It can be seen from the above code that the query operation is different from the operation of adding, deleting and changing. The first three operations all execute SQL statements through execSQL () method, while the query operation uses rawQuery () method. This is because querying the database returns a result set Cursor, whereas the execSQL () method returns no value.


Related articles: