preface

• inAndroidIn the development, there are five main data stores, as follows
  

  

Among them, SQLite database storage is very common

• Today I’m going to take you through all aboutAndroid SQLiteDatabase operations (add, delete, search, change)

---

directory

---

1. Introduction to the SQLlite database

---

2. The SQLiteOpenHelper class

2.1 introduction

2.2 Common methods of SQLiteOpenHelper

/** * create database */ / 1. Create or open database readable/write (by returning SQLiteDatabase object) getWritableDatabase () // 2. Create or open a readable database (via the returned SQLiteDatabase object) getReadableDatabase () // 3. The first time the database is created, // Overwrite onCreate(SQLiteDatabase db) // 4 in a subclass derived from SQLiteOpenHelper.  // Overwrite onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) // 5. Close () /** * Database operations (add, Delete, subtract, query) */ // 1. Query (String table, String[] columns, String Selection, String[] selectionArgs, String groupBy, String having, String orderBy, Stringlimit) // Querying the specified table returns a cursor set of data. // The parameters are described as follows: // table: table name // colums: array of column names // Selection: conditional clausewhere// groupBy: having group conditions // orderBy: sorting class //limit: paging query limits // Cursor: ResultSet (Cursor) rawQuery(String SQL, String[] selectionArgs) Return data set with cursor (biggest difference from above = prevent SQL injection) // 2. Delete (int) delete(String table,StringwhereClause,String[] whereThe Args) / / 3. Add data row (long) insert (String table, String nullColumnHack, ContentValues values) / / 4. Update (String table, ContentValues VALUES, StringwhereClause, String[] whereSQL > select * from String; // Select * from String;execSQL(String sql) 
Copy the code

---

3. Specific use

• Use steps = custom database subclasses (inheritanceSQLiteOpenHelperCreate database & create database (add, delete, query, modify)
• Below, I’ll walk you through each step

3.1 Custom Database Subclass (Inheriting SQLiteOpenHelper)

/** * create database subclass, Public class DatabaseHelper extends SQLiteOpenHelper {// Database version number private static Integer Version = 1; /** * constructor * in subclasses of SQLiteOpenHelper, Must have the constructor * / public DatabaseHelper (Context Context, String name, SQLiteDatabase. CursorFactory factory, Int version) {// Parameter Description // context: context object // name: database name // param: an optional cursor factory (usually Null) // version: The current version of the database, the value must be an integer and the state must be increasing // The super constructor (context, name, factory, version) must be called through super; } /** * echo onCreate () * call time: when the database is created for the first time * Int data, string, date, binary */ @override public void onCreate(SQLiteDatabase db) {// Create a database table // PassexecSQL () execute SQL statement (here a table named person is created) String SQL ="create table person(id integer primary key autoincrement,name varchar(64),address varchar(64))"; db.execSQL(sql); / / note: The database is not actually created/opened (because the method has not been called) // it is not created/opened until getWritableDatabase()/getReadableDatabase() is called for the first time} /** * overwrite onUpgrade () * Call time: automatically call when the database is upgraded (i.e. when the database version changes) * Function: update the database table structure * Note: When you create an SQLiteOpenHelper subclass object, you must pass in a version argument, */ @override public void onUpgrade(SQLiteDatabase db, int oldVersion, Int newVersion) {// parameter description: // db: database // oldVersion: database of the oldVersion // newVersion: database of the newVersion // ALTER statement using SQL String SQL ="alter table person add sex varchar(8)"; db.execSQL(sql); }}Copy the code

3.2 Creating a database: getWritableDatabase (), getReadableDatabase ()

SQLiteOpenHelper dbHelper = new DatabaseHelper(sqliteActivity. this,"test_carson"); / / step 2: create/open the real database SQLiteDatabase SQLiteDatabase. = dbHelper getWritableDatabase (); / / to create or open can read/write database SQLiteDatabase SQLiteDatabase. = dbHelper getReadableDatabase (); // Create or open a readable databaseCopy the code

Note: Whenever you need to operate a database, you must first create a database object & Create/open a database.

1. For operations = “add, Delete, modify (update)”, read/write permissions are required:getWritableDatabase()
2. For operation = “query”, “read” permission is requiredgetReadableDatabase()

3.3 Operating the Database (Add, Delete, Query, and Modify)

/** * 1. Create &open database */ / SQLiteOpenHelper = new DatabaseHelper(sqliteActivity.this,"test_carson", 2); / / b. create or open can read/write database SQLiteDatabase SQLiteDatabase. = dbHelper getWritableDatabase (); /** * operation 1: insert data = insert() */ Create ContentValues object ContentValues values = new ContentValues(); // b. Insert the key-value pair values.put("id", 1);
        values.put("name"."carson"); Key = column name, value = insert value ContenValues Key can only be String; Value can store basic data & String // c. Insert data into the database: insert() sqlitedatabase.insert ("user", null, values); // Parameter 1: the name of the table to operate on // Parameter 2: SQl does not allow an empty column. If ContentValues are empty, this column is explicitly specified as NULL. // Parameter 3: ContentValues object // Note: String SQl = can also be inserted into SQl statements"insert into user (id,name) values (1,'carson')"; Db. ExecSQL (SQL); /** * Operation 2: Modify data = update () */ / a. Create a ContentValues object ContentValues values = new ContentValues(); values.put("name"."zhangsan"); Id =1; name = zhangsan SQlitedatabase.update ("user", values, "id=?", new String[] { "1"}); // Parameter 1: table name (String) // Parameter 2: ContentValues object to be modified // Parameter 3: WHERE expression (String), row to be updated; If this parameter is null, all rows are modified; ? // Parameter 4: WHERE selects the statement arguments (String[]), replacing the "? "in the WHERE expression one by one. A placeholder. // After the upgrade () is invoked, the onUpgrade() of the database subclass is called back"update [user] set name = 'zhangsan' where id="1"; db.execSQL(sql); Sqlitedatabase.delete (" sqlitedatabase.delete (");user","id=?", new String[]{"1"}); // Parameter 1: table name (String) // Parameter 2: WHERE expression (String), delete row; If this parameter is null, all rows are deleted. ? // Parameter 3: WHERE selects the statement arguments (String[]), replacing the "? "in the WHERE expression one by one. A placeholder. String SQL ="delete from user where id="1"; db.execSQL(sql); /** * c = db.rawQuery();"select * from user where id=?",new Stirng[]{"1"}); // Returns the value of a cursor object // Iterates over the results of the query through the cursor methodif(cursor.moveToFirst()) { 
           String password = c.getString(c.getColumnIndex("password")); } //Cursor objects are used as follows: c.move(int offset); // Move to the specified line c.movetofirst () with the current position as a reference; // Move to the first line c.movetolast (); // move to the last line c.movetoposition (int position); // Move to the specified line c.movetoprevious (); // Move to the previous line c.movetonext (); // Move to the next line, c.isfirst (); // Whether to point to first c.islast (); // Whether to point to the last c.i. SBeforeFirst (); // whether to point to the first c.i. SAfterLast (); C.i null (int columnIndex); // Specify whether the column is empty (column cardinality is 0) c.isclosed (); // Whether the cursor is closed c.gottcount (); C.gott position (); // Returns the number of rows pointed to by the current cursor. C. goetstring (int columnIndex); // return the columnIndex corresponding to a column name. Cursor result=db.rawQuery("SELECT _id, username, password FROM user");  
         result.moveToFirst();  
         while(! result.isAfterLast()) { int id=result.getInt(0); String name=result.getString(1); String password =result.getString(2); //dosomething useful with these result.moveToNext(); } result.close(); // This method can be complicated if the query is dynamic. ExecSQL (SQL) execSQL(db) execSQL(SQL); /** * operation 4: Db.query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy); db.query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, Stringlimit);  
        db.query(String distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit); // Table: the table to be operated on. // Columns: the set of names of the columns to be queried. // Selection: the conditional statement after which placeholders can be used. Having group conditions in conjunction with groupBy using // Order Derby to specify the sorted column name //limitSpecify paging parameters // distinct can specify"true"Or"falseCursor = sqliteDatabase.query()"user", new String[] { "id"."name" }, "id=?", new String[] { "1"}, null, null, null); // Parameter 1: (String) Table name // Parameter 2: (String[]) Column name to be queried // Parameter 3: (String) Query condition // Parameter 4: (String[]) Query condition parameter // Parameter 5: (String) Grouping query results // Parameter 6: ExecSQL (String) execSQL(String) execSQL(String) execSQL(String) execSQL(String) execSQL(String) execSQL(String) execSQL(String) execSQL Sqlitedatabase.close (); sqlitedatabase.close (); /** * deleteDatabase(person) */ / deleteDatabase(person)"test.db");


Copy the code

3.4 Special Attention

• All database operations except “query” are availableSQLStatement, which uses the following method instead:

db.executeSQL(String sql);  
db.executeSQL(String sql, Object[] bindArgs); // SQL statements use placeholders, parameter 2 = the actual parameter setCopy the code

• Usage advice: Use SQL statements because:SQLStatement generic & simple

Native methods such as insert() and delete() have multiple parameters and are complicated to use

---

4. The Demo

• Demo description: Create a database and perform operations on the database (add, delete, query, modify)
• Implementation steps:
  1. Implementing subclass InheritanceSQLiteOpenHelperClass (copyonCreat(),onUpgrade())
  2. Perform various operations on the database (add, delete, search, change)

4.1 The specific code is as follows:

Github:DataBase_Demo by Carson

Step 1: Subclass SQLiteOpenHelper (clone onCreat(), onUpgrade()))

MySQLiteOpenHelper.java

package scut.carson_ho.database_demo; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; /** * Created by Carson_Ho on 16/11/18. */ public class MySQLiteOpenHelper extends SQLiteOpenHelper {// Database version private static Integer Version = 1; // In subclasses of SQLiteOpenHelper, Must have the constructor public MySQLiteOpenHelper (Context Context, String name, SQLiteDatabase. CursorFactory factory, Int version) {// The constructor super(context, name, factory, version) must be called through super; } // Parameter Description //context: context object //name: database name //param:factory //version: current database version, Value must be an integer and is increasing the status of public MySQLiteOpenHelper (Context Context, String name, int version) {this (the Context, the name, null, version); } public MySQLiteOpenHelper(Context context,String name) { this(context, name, Version); @override public void onCreate(SQLiteDatabase db) {system.out.println ("Create databases and tables"); // create the database and create a table called records //SQLite data create supported data types: integer data, String data, date data, binary data type String SQL ="create table user(id int primary key,name varchar(200))";
        //execDb. ExecSQL (SQL); // The database is not actually created or opened, } // is not created or opened until one of the getWritableDatabase() or getReadableDatabase() methods is called // If the DATABASE_VERSION value is changed to 2, the onUpgrade() method is called @override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { System.out.println("Update database version to :"+newVersion); }}Copy the code

Step 2: Implement the required database operations (add, delete, search, change) in MainActivity

MainActivity.java

package scut.carson_ho.database_demo;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;

public class MainActivity extends AppCompatActivity implements View.OnClickListener {

    private Button instablish;
    private Button insert;
    private Button upgrade;
    private Button modify;
    private Button delete;
    private Button query;
    private Button delete_database;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main); // Bind Button instablish = (Button) findViewById(R.i.nstablish); insert = (Button) findViewById(R.id.insert); upgrade = (Button) findViewById(R.id.upgrade); modify = (Button) findViewById(R.id.modify); delete = (Button) findViewById(R.id.delete); query = (Button) findViewById(R.id.query); delete_database = (Button) findViewById(R.id.delete_database); / / set the listener instablish. SetOnClickListener (this); insert.setOnClickListener(this); upgrade.setOnClickListener(this); modify.setOnClickListener(this); delete.setOnClickListener(this); query.setOnClickListener(this); delete_database.setOnClickListener(this); @override public void onClick(View v) {switch (v.getid ()) {// Click create databasecaseMySQLiteOpenHelper dbHelper = new MySQLiteOpenHelper(this,"test_carson"); // The database is not actually created or opened, SQLiteDatabase is not created or opened until one of the getWritableDatabase() or getReadableDatabase() methods is called. SQLiteDatabase = dbHelper.getWritableDatabase(); // SQLiteDatabase sqliteDatabase = dbHelper.getReadbleDatabase();break; // Click update datacaseMySQLiteOpenHelper dbHelper_upgrade = new MySQLiteOpenHelper(this,"test_carson", 2); // Call getWritableDatabase() to create or open a readable database SQLiteDatabase sqliteDatabase_upgrade = dbHelper_upgrade.getWritableDatabase(); // SQLiteDatabase sqliteDatabase = dbHelper.getReadbleDatabase();break; // Click Insert data to databasecase R.id.insert:

                System.out.println("Insert data"); MySQLiteOpenHelper dbHelper1 = new MySQLiteOpenHelper(this,"test_carson", 2); / / calls getWritableDatabase () method to create or open a database that can be read SQLiteDatabase sqliteDatabase1 = dbHelper1. GetWritableDatabase (); // create ContentValues object ContentValues values1 = new ContentValues(); // Insert the key-value pair values1.put("id", 1);
                values1.put("name"."carson"); // Call the insert() method to insert data into the database."user", null, values1);

                // sqliteDatabase.execSQL("insert into user (id,name) values (1,'carson')"); Sqlitedatabase1.close ();break; // Click query databasecase R.id.query:

                System.out.println("Query data"); MySQLiteOpenHelper dbHelper4 = new MySQLiteOpenHelper(MainActivity. This,"test_carson", 2); / / calls getWritableDatabase () method to create or open a database that can be read SQLiteDatabase sqliteDatabase4 = dbHelper4. GetReadableDatabase (); Cursor Cursor = sqlitedatabase4.query (sqliteDatabase4.query("user", new String[] { "id"."name" }, "id=?", new String[] { "1"}, null, null, null); String id = null; String name = null; // Move the cursor to the next row to determine if there is another data item in the result set // Return if there istrueIf no, returnfalse
                while (cursor.moveToNext()) {
                    id = cursor.getString(cursor.getColumnIndex("id"));
                    name = cursor.getString(cursor.getColumnIndex("name")); System.out.println(system.out.println ("The queried data is :"+"id: "+id+""+"name: "+name); } // Close database sqlitedatabase4.close ();break; // Click modify datacase R.id.modify:
                System.out.println("Modify data"); // Create a DatabaseHelper object // update the database version to 2 // pass in version 2, which is larger than the previous version (1), DbHelper2 = new MySQLiteOpenHelper(mainactivity.this,"test_carson", 2); / / calls getWritableDatabase () to get a writable SQLiteDatabase object SQLiteDatabase sqliteDatabase2 = dbHelper2. GetWritableDatabase (); // Create a ContentValues object ContentValues values2 = new ContentValues(); values2.put("name"."zhangsan"); Update sqliteDatabase2.update("user", values2, "id=?", new String[]{"1"}); // Close the database sqliteDatabase2.close();break; // Click delete datacase R.id.delete:

                System.out.println("Delete data"); MySQLiteOpenHelper dbHelper3 = new MySQLiteOpenHelper(mainActivity. this,"test_carson", 2); / / calls getWritableDatabase () method to create or open a database that can be read SQLiteDatabase sqliteDatabase3 = dbHelper3. GetWritableDatabase (); // Delete data sqliteDatabase3.delete("user"."id=?", new String[]{"1"}); // Close the database sqliteDatabase3.close();break; // Click delete databasecase R.id.delete_database:

                System.out.println("Delete database");

                MySQLiteOpenHelper dbHelper5 = new MySQLiteOpenHelper(MainActivity.this,
                        "test_carson", 2); / / call getReadableDatabase () method to create or open a database that can be read SQLiteDatabase sqliteDatabase5 = dbHelper5. GetReadableDatabase (); // deleteDatabase named test.db deleteDatabase("test_carson");
                break;

            default:
                break; }}}Copy the code

Step 3: Control the database action activity_main.xml in the main layout file Settings button

<? xml version="1.0" encoding="utf-8"? > <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context="scut.carson_ho.database_demo.MainActivity">



    <Button
        android:id="@+id/instablish"
        android:layout_centerHorizontal="true"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Create database"/>



    <Button
        android:id="@+id/upgrade"
        android:layout_below="@+id/instablish"
        android:layout_centerHorizontal="true"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Update database"/>

    <Button
        android:id="@+id/insert"
        android:layout_below="@+id/upgrade"
        android:layout_centerHorizontal="true"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Insert data"/>

    <Button
        android:id="@+id/modify"
        android:layout_centerHorizontal="true"
        android:layout_below="@+id/insert"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Modify data"/>



    <Button
        android:id="@+id/query"
        android:layout_centerHorizontal="true"
        android:layout_below="@+id/modify"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Query data"/>

    <Button
        android:id="@+id/delete"
        android:layout_centerHorizontal="true"
        android:layout_below="@+id/query"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Delete data"/>


    <Button
        android:id="@+id/delete_database"
        android:layout_centerHorizontal="true"
        android:layout_below="@+id/delete"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Delete database"/>



</RelativeLayout>



Copy the code

4.2 Test Results

• Interface display

  

• Click in order: Create – Update – Insert – query – Modify – Query – Delete data

4.3 Demo address

Carson’s Github address is DataBase_Demo

---

5. To summarize

• This article gives a comprehensive introductionAndroid SQLiteAll the knowledge in the database
• And I’m going to continue with thatAndroidLibraries of commonly used databases, such asRealm,GreenDao3.0And Tencent just open sourceWCDB, you can continue to pay attention toCarson_Ho android Development Notes

---

Thumb up, please! Because your approval/encouragement is my biggest motivation to write!

---

Welcome to follow Carson_ho on wechat