java - ContentValue 对未从 RAW JSON 插入 SQLite 数据库

Question

我正在尝试获取 RAW JSOn 文件并将其插入到我的表名 insectsTable 中。

这是我在 BugsContract 类中定义列名称的位置:

import android.provider.BaseColumns;

/**
 * Created by man on 9/19/2017.
 */

public class BugsContract
{

    public static final class BugEntry implements BaseColumns
    {
        public static final String TABLE_NAME = "insectTable";
        public static final String COLUMN_NAME = "friendlyName";
        public static final String COLUMN_SCIENTIFIC = "scientificName";
        public static final String COLUMN_CLASS = "classification";
        public static final String COLUMN_IMAGE = "imageAsset";
        public static final String COLUMN_DANGER = "dangerLevel";
    }
}

在我的BugsDbHelper类中定义如下:

import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.google.developer.bugmaster.R;

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

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

/**
 * Database helper class to facilitate creating and updating
 * the database from the chosen schema.
 */
public class BugsDbHelper extends SQLiteOpenHelper
{
    private static final String TAG = BugsDbHelper.class.getSimpleName();

    private static final String DATABASE_NAME = "insect.db";
    private static final int DATABASE_VERSION = 1;

    //Used to read data from res/ and assets/
    private Resources mResources;

    Context context;
    SQLiteDatabase db;


    public BugsDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);

        mResources = context.getResources();
        db = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE "+BugsContract.BugEntry.TABLE_NAME+" ("+ BugsContract.BugEntry._ID + " INTEGER AUTOINCREMENT"+BugsContract.BugEntry.COLUMN_NAME+" TEXT, "
        + BugsContract.BugEntry.COLUMN_SCIENTIFIC + " TEXT," + BugsContract.BugEntry.COLUMN_CLASS+" TEXT,"+
        BugsContract.BugEntry.COLUMN_IMAGE + " TEXT," + BugsContract.BugEntry.COLUMN_DANGER + " INTEGER);");

        Log.d(TAG, "Database created successfully");

        try
        {
            readInsectsFromResources(db);
        } catch(IOException e)
        {
            e.printStackTrace();
        } catch(JSONException e)
        {
            e.printStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + BugsContract.BugEntry.TABLE_NAME);
        onCreate(db);
    }


    private String readJsonDataFromFile() throws IOException
    {
        InputStream inputStream = null;
        StringBuilder builder = new StringBuilder();

        try
        {
            String jsonDataString = null;
            inputStream = mResources.openRawResource(R.raw.insects);
            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream, "UTF-8"));
            while((jsonDataString = bufferedReader.readLine()) != null)
            {
                builder.append(jsonDataString);
            }
        }finally
        {
            if(inputStream != null)
            {
                inputStream.close();
            }
        }
        Log.d("Suck", builder.toString());

        return new String(builder);
    }

    /**
     * Streams the JSON data from insect.json, parses it, and inserts it into the
     * provided {@link SQLiteDatabase}.
     *
     * @param db Database where objects should be inserted.
     * @throws IOException
     * @throws JSONException
     */
    private void readInsectsFromResources(SQLiteDatabase db) throws IOException, JSONException
    {
        try {

            final String NAME            = "friendlyName";
            final String SCIENTIFIC_NAME = "scientificName";
            final String CLASSIFICATION  = "classification";
            final String IMAGE_ASSET     = "imageAsset";
            final String DANGER_LEVEL    = "dangerLevel";

            //Parse resource into key/values
            final String rawJson = readJsonDataFromFile();
            JSONArray j_array = new JSONArray(rawJson);

            for (int i = 0; i < j_array.length(); ++i) {
                String friendlyName;
                String scientificName;
                String classification;
                String image;
                int dangerLevel;

                JSONObject jsonObj = j_array.getJSONObject(i);

                friendlyName = jsonObj.getString(NAME);
                scientificName = jsonObj.getString(SCIENTIFIC_NAME);
                classification = jsonObj.getString(CLASSIFICATION);
                image = jsonObj.getString(IMAGE_ASSET);
                dangerLevel = jsonObj.getInt(DANGER_LEVEL);

                ContentValues contentValues = new ContentValues();

                contentValues.put(BugsContract.BugEntry.COLUMN_NAME, friendlyName);
                contentValues.put(BugsContract.BugEntry.COLUMN_SCIENTIFIC, scientificName);
                contentValues.put(BugsContract.BugEntry.COLUMN_CLASS, classification);
                contentValues.put(BugsContract.BugEntry.COLUMN_IMAGE, image);
                contentValues.put(BugsContract.BugEntry.COLUMN_DANGER, dangerLevel);

                db.insert(BugsContract.BugEntry.TABLE_NAME, null, contentValues);

                Log.d(TAG, "Inserted successfully" + contentValues);
            }
        }catch(Exception e)
        {
            Log.e(TAG, e.getMessage(), e);
            e.printStackTrace();
        }
    }
}

我的 JSON 文件是在资源文件夹的 raw 文件夹中定义的:

"insects": [
    {
      "friendlyName": "Black Widow",
      "scientificName": "Latrodectus mactans",
      "classification": "Arachnida",
      "imageAsset": "spider.png",
      "dangerLevel": 10
    },
    {
      "friendlyName": "Brown Recluse",
      "scientificName": "Loxosceles reclusa",
      "classification": "Arachnida",
      "imageAsset": "spider.png",
      "dangerLevel": 10
    },

以上只是 JSON 的示例。每次我运行该程序时，数据库、表以及表的列都会成功创建，但 JSON 不会插入到这些列中。我为我的数据编写了 ContentValues，但它们似乎不起作用。

我已经研究这个问题好几天了，但一直没能找到解决方案，所以对此的帮助就太好了!

Answer 1

首先，在BugsDbHelper的onCreate中，代码:

" INTEGER AUTOINCREMENT"

应该是:

" INTEGER PRIMARY KEY AUTOINCREMENT,"

请不要忘记将,放在最后。

那么 JSON 文件可能是这样的:

{
    "insects": [
        {
            "friendlyName": "BlackWidow",
            "scientificName": "Latrodectusmactans",
            "classification": "Arachnida",
            "imageAsset": "spider.png",
            "dangerLevel": 10
        },
        {
            "friendlyName": "BrownRecluse",
            "scientificName": "Loxoscelesreclusa",
            "classification": "Arachnida",
            "imageAsset": "spider.png",
            "dangerLevel": 10
        }
    ]
}

在readInsectsFromResources中，代码:

JSONArray j_array = new JSONArray(rawJson);

应该是:

JSONObject rawJSONObject = new JSONObject(rawJson);
JSONArray j_array = rawJSONObject.getJSONArray("insects");

我已经运行了上面的代码，它可以工作。

希望有帮助。