【Android】資料存盤-SQLite

SQLite特點：

輕量級
獨立
隔離
跨平臺
多語言介面
安全性（多行程可以同時讀取，但同一時間只能有一個行程在寫入資料）

創建資料庫和表，以及資料庫的增刪改查操作

DatabaseHelper.java

package com.jsc4.aboutactivity.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String USER_TABLE_NAME = "user";
    public static final String USERNAME = "username";
    public static final String AGE = "age";
    public static final String DATABASE_NAME = "test.db";
    public static final int VERSION = 1;


    public DatabaseHelper(@Nullable Context context) {
        super(context, DATABASE_NAME, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table " + USER_TABLE_NAME + "(" + USERNAME + " varchar(20) not null, " + AGE + " varchar(10) not null);");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO on database upgrade operation

    }
}

創建一個activity：
DatabaseButtonActivity.java

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;

import androidx.annotation.Nullable;
import androidx.appcompat.app.AppCompatActivity;

import com.jsc4.aboutactivity.database.DatabaseHelper;

public class DatabaseButtonActivity extends AppCompatActivity {

    SQLiteDatabase mSqLiteDatabase;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_database);

        DatabaseHelper databaseHelper = new DatabaseHelper(this);
        mSqLiteDatabase = databaseHelper.getWritableDatabase();

        // Insert
        findViewById(R.id.database_add_button).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                // IO操作，建議后臺操作

                // 拼裝成contentValues
                ContentValues contentValues = new ContentValues();
                contentValues.put(DatabaseHelper.USERNAME, "miamia");
                contentValues.put(DatabaseHelper.AGE,"13");

                // 判斷插入是否成功
                long row =  mSqLiteDatabase.insert(DatabaseHelper.USER_TABLE_NAME, null, contentValues);
                Log.i("djtest", "onClick: row="+row);
                if(row != -1){
                    Toast.makeText(DatabaseButtonActivity.this, "插入成功！", Toast.LENGTH_SHORT).show();
                }

                // query
                Cursor cursor = mSqLiteDatabase.query(DatabaseHelper.USER_TABLE_NAME, null, null, null, null, null, null);
                if(cursor.moveToFirst()){
                    int count = cursor.getCount();
                    for (int i = 0; i < count; i++) {
                        String userName = cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.USERNAME));
                        String age = cursor.getString(cursor.getColumnIndexOrThrow(DatabaseHelper.AGE));
                        Log.i("djtest",i + " : " + userName + " | " + age + ".");
                    }
                }
            }
        });

        findViewById(R.id.database_delete_button).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // delete
                String whereClauseString = "username=?";
                String[] whereArgs = {"miamia"};
                mSqLiteDatabase.delete(DatabaseHelper.USER_TABLE_NAME, whereClauseString, whereArgs);
            }
        });

        findViewById(R.id.database_update_button).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // update
                // 當欄位username=miamia時，將age更新為18
                ContentValues contentValues = new ContentValues();
                contentValues.put(DatabaseHelper.AGE, "18");
                String whereClauseString = "username=?";
                String[] whereArgs = {"miamia"};
                mSqLiteDatabase.update(DatabaseHelper.USER_TABLE_NAME, contentValues, whereClauseString, whereArgs);
            }
        });

        findViewById(R.id.database_transactions_button).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                mSqLiteDatabase.beginTransaction();//開始事務，此時資料庫會被鎖定
                try {
                    // 做你的操作
                    for (int i = 0; i < 1000; i++) {
                        mSqLiteDatabase.execSQL("insert into user(username, age) values ('miamia', '5歲')");
                    }
                    mSqLiteDatabase.setTransactionSuccessful();//設定事務已經成功，否則自動回滾不提交
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    mSqLiteDatabase.endTransaction();//提交并關閉事務
                }
            }
        });
    }
}

xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical" android:layout_width="match_parent"
    android:layout_height="match_parent">

    <Button
        android:id="@+id/database_add_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="ADD" />

    <Button
        android:id="@+id/database_delete_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="DELETE" />

    <Button
        android:id="@+id/database_update_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="UPDATE" />
    <Button
        android:id="@+id/database_transactions_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="利用事務添加1000條資料" />
</LinearLayout>

使用Android Debug Database查看資料庫和表資訊

參考鏈接：https://juejin.im/post/6844903472479879182

先在 module 的 build.gradle 里面添加：

debugCompile 'com.amitshekhar.android:debug-db:1.0.0'

復制代碼讓手機和電腦處于一個局域網下，當專案跑起來的時候，在 logcat 里面會列印出這么一行：
D/DebugDB: Open http://XXX.XXX.X.XXX:8080 in your browser復制代碼把地址復制到瀏覽器，就會看到一個這樣的界面：