Android Sqlite数据库读写辅助类

Android Sqlite数据库读写封装辅助类，类似C#的数据库读写，非实体操作。

package com.rc114.scanner;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import org.json.JSONArray;
import org.json.JSONObject;

import java.io.File;
import java.io.IOException;

//sqlite数据库读写辅助类
public class SqliteHelper {
    public String DbFile;

    //构造函数，需传入sqlite数据文件路径
    public SqliteHelper(String dbpath) {
        DbFile = dbpath;
        File file = new File(DbFile);
        File parentFile = file.getParentFile();
        if (!parentFile.exists()) {
            parentFile.mkdirs();
        }
        if (!file.exists()) {
            try {
                file.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    //执行sql语句
    public void excute(String sql) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DbFile, null);
        db.execSQL(sql);
        db.close();
    }

    //执行sql语句返回单个值（数值）
    public int excuteScalerInt(String sql) {
        int ret = -1;
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DbFile, null);
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            ret = cursor.getInt(0); //获取第一列的值,第一列的索引从0开始
        }
        cursor.close();
        db.close();
        return ret;
    }

    //执行sql语句返回单个值（字符串）
    public String excuteScaler(String sql) {
        String ret = "";
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DbFile, null);
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            ret = cursor.getString(0); //获取第一列的值,第一列的索引从0开始
        }
        cursor.close();
        db.close();
        return ret;
    }

    //执行sql语句返回json数组
    public JSONArray queryJsonArray(String sql) {
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(DbFile, null);
        Cursor cursor = db.rawQuery(sql, null);
        JSONArray array = new JSONArray();
        cursor.moveToFirst();
        while (cursor.isAfterLast() == false) {
            int totalColumn = cursor.getColumnCount();
            JSONObject object = new JSONObject();
            for (int i = 0; i < totalColumn; i++) {
                if (cursor.getColumnName(i) != null) {
                    try {
                        if (cursor.getString(i) != null) {
                            object.put(cursor.getColumnName(i), cursor.getString(i));
                        } else {
                            object.put(cursor.getColumnName(i), "");
                        }
                    } catch (Exception e) {
                        Log.d("QueryJsonErr", e.getMessage());
                    }
                }
            }
            array.put(object);
            cursor.moveToNext();
        }
        cursor.close();
        db.close();
        return array;
    }

    //执行sql语句返回json字符串
    public String queryJson(String sql) {
        JSONArray array = queryJsonArray(sql);
        return array.toString();
    }


}

使用方式：

//数据库
String dbFile = "/data/data/" + getPackageName() + "/databases/scan.db";
SqliteHelper db = new SqliteHelper(dbFile);

//初始化数据表
if (db.excuteScalerInt("select count(*)  from sqlite_master where type='table' and name ='RC_Scan'") == 0) {
	db.excute("CREATE TABLE [RC_Scan](\n" +
			"  [GID] NTEXT, \n" +
			"  [Raw] NTEXT, \n" +
			"  [Time] DATETIME, \n" +
			"  [Status] INT, \n" +
			"  [PName] NTEXT, \n" +
			"  [Pid] NTEXT);");
}
//插入数据
db.excute("INSERT INTO RC_Scan(GID,Raw,Time,Status,PName,Pid)VALUES('" + UUID.randomUUID().toString() + "','test','2023-02-08 10:11:00',0,'张三','123456789')");

//查询数据（Json字符串）
String json = db.queryJson("SELECT * FROM RC_Scan WHERE Pid='123456789'");

//查询数据（JsonArray）
JSONArray array = db.queryJsonArray("SELECT * FROM RC_Scan WHERE Pid='123456789'");

//查询数据（单个值-字符串）
String name = db.excuteScaler("SELECT PName FROM RC_Scan WHERE Pid='123456789'");

//查询数据（单个值-数字）
String status = db.excuteScaler("SELECT Status FROM RC_Scan WHERE Pid='123456789'");