Android SQLite應用

DB_Helper.java

public class DB_Helper extends SQLiteOpenHelper {

    private static final String DataBaseName = "PowerTest.db";
    private static final int DataBaseVersion = 1;

    public DB_Helper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version, String TableName) {
        super(context, DataBaseName, null, DataBaseVersion);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String SqlTable = "CREATE TABLE IF NOT EXISTS Settings (" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name text not null," +
                "value TEXT not null" +
                ")";
        sqLiteDatabase.execSQL(SqlTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        final String SQL = "DROP TABLE Users";
        sqLiteDatabase.execSQL(SQL);
    }

}

 

How to use:

private int settings_db_has_data(String name){
    String DataBaseTable = "Settings";
    SQLiteDatabase db;
    DB_Helper save_DB_Helper = new DB_Helper(getApplicationContext(), null, null, 3, DataBaseTable);
    db = save_DB_Helper.getReadableDatabase();

    String query = "select count(*) from "+ DataBaseTable + " where name = ?";
    Cursor c = db.rawQuery(query, new String[] {name});
    if (c.moveToFirst()) {
        return c.getInt(0);
    }
    return 0;
}

private void settings_db_insert(String name, String value){
    String DataBaseTable = "Settings";
    SQLiteDatabase db;
    DB_Helper save_DB_Helper = new DB_Helper(getApplicationContext(), null, null, 3, DataBaseTable);
    db = save_DB_Helper.getWritableDatabase();

    ContentValues contentValues_01 = new ContentValues();
    contentValues_01.put("name", name);
    contentValues_01.put("value", value );
    db.insert(DataBaseTable,null, contentValues_01);
}

private void settings_db_update_data(String name, String value){
    String DataBaseTable = "Settings";
    SQLiteDatabase db;
    DB_Helper save_DB_Helper = new DB_Helper(getApplicationContext(), null, null, 3, DataBaseTable);
    db = save_DB_Helper.getWritableDatabase();

    ContentValues contentValues = new ContentValues();
    contentValues.put("value", value);
    db.update(DataBaseTable, contentValues,"name=?", new String[] {name});
}

製作SqlDataBaseHelper.java

這個class的功用是要建立相對應的資料表及各個欄位，
注意extends繼承SQLiteOpenHelper後會跳出相對應的方法，
不要傻傻的全部自己打!
資料表名稱:Users
資料欄位三個:
(1)_id - INTEGER 整數 - 主索引(AUTOINCREMENT-自動編號)
(2)account - text 字串(not null-不可為空值)
(3)password - text 字串(not null-不可為空值)

package com.example.sqltest;

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

import androidx.annotation.Nullable;

public class SqlDataBaseHelper extends SQLiteOpenHelper {

    private static final String DataBaseName = "DataBaseIt";
    private static final int DataBaseVersion = 1;

    public SqlDataBaseHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version,String TableName) {
        super(context, DataBaseName, null, DataBaseVersion);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String SqlTable = "CREATE TABLE IF NOT EXISTS Users (" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "account text not null," +
                "password TEXT not null" +
                ")";
        sqLiteDatabase.execSQL(SqlTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        final String SQL = "DROP TABLE Users";
        sqLiteDatabase.execSQL(SQL);
    }
}

---

MainActivity.java - 查詢方法

不管你要做甚麼，
都要先開啟資料庫!

private static final String DataBaseName = "DataBaseIt";
private static final int DataBaseVersion = 1;
private static String DataBaseTable = "Users";
private static SQLiteDatabase db;
private SqlDataBaseHelper sqlDataBaseHelper;

// 建立SQLiteOpenHelper物件
        sqlDataBaseHelper = new SqlDataBaseHelper(this.getContext(),DataBaseName,null,DataBaseVersion,DataBaseTable);
        db = sqlDataBaseHelper.getWritableDatabase(); // 開啟資料庫

1. Select 查詢

public static String[] AccountArray,AccountID,PasswordArray;

Cursor c = db.rawQuery("SELECT * FROM " + DataBaseTable,null);
AccountArray = new String[c.getCount()];
AccountID = new String[c.getCount()];
PasswordArray = new String[c.getCount()];
c.moveToFirst();
for(int i=0;i<c.getCount();i++){
    AccountID[i] = c.getString(0);
    AccountArray[i] = c.getString(1);
    PasswordArray[i] = c.getString(2);
    c.moveToNext();
}

2. Insert 新增

(1)contentValues.put("你的欄位",新的字串)
(2)_id欄位因為已經設定為自動編號，
所以不需要給予字串。

long id;
ContentValues contentValues = new ContentValues();
contentValues.put("account",edit_Account_Text);
contentValues.put("password",edit_Password_Text);
id = db.insert(DataBaseTable,null,contentValues);

3. Update 更新

(1)contentValues.put("你的欄位",新的字串)
(2)"_id="+要鎖定的_id

int count;
ContentValues contentValues = new ContentValues();
contentValues.put("password",editUpdate);
count = db.update(DataBaseTable,contentValues,"_id="+AccountID[spinner.getSelectedItemPosition()],null);

4. Delete 刪除

int count;
count = db.delete(DataBaseTable,"_id="+AccountID[spinner.getSelectedItemPosition()],null);

---