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