java - 将 JSON 数据传递到 SQLite 并检索它

Question

我制作了一个应用程序，它从在线网络服务器获取 JSON 数据，然后将数据放入 SQLite 数据库中。但是，当我尝试检索所述信息时，应用程序崩溃了。这是我的代码:

MainActivty.java

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import org.apache.http.HttpResponse;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.DefaultHttpClient;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.database.Cursor;
import android.net.ConnectivityManager;
import android.net.NetworkInfo;
import android.os.AsyncTask;
import android.os.Bundle;
import android.util.Log;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
import android.app.Activity;

public class MainActivity extends Activity {

private DBHelper mydb;

EditText etResponse;
TextView tvIsConnected;

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

    // get reference to the views
    etResponse = (EditText) findViewById(R.id.etResponse);
    tvIsConnected = (TextView) findViewById(R.id.tvIsConnected);

    mydb = new DBHelper(this);

    // check if you are connected or not
    if(isConnected()){
        tvIsConnected.setBackgroundColor(0xFF00CC00);
        tvIsConnected.setText("You are connected");
    }
    else{
        tvIsConnected.setText("You are NOT connected");
    }

    // call AsynTask to perform network operation on separate thread
    new HttpAsyncTask().execute("http://test.com/android_connect/get_all_events.php");
  }

  public static String GET(String url){
    InputStream inputStream = null;
    String result = "";
    try {

        // create HttpClient
        HttpClient httpclient = new DefaultHttpClient();

        // make GET request to the given URL
        HttpResponse httpResponse = httpclient.execute(new HttpGet(url));

        // receive response as inputStream
        inputStream = httpResponse.getEntity().getContent();

        // convert inputstream to string
        if(inputStream != null)
            result = convertInputStreamToString(inputStream);
        else
            result = "Did not work!";

    } catch (Exception e) {
        Log.d("InputStream", e.getLocalizedMessage());
    }

    return result;
}

   private static String convertInputStreamToString(InputStream inputStream) throws IOException{
    BufferedReader bufferedReader = new BufferedReader( new InputStreamReader(inputStream));
    String line = "";
    String result = "";
    while((line = bufferedReader.readLine()) != null)
        result += line;

    inputStream.close();
    return result;

}

   public boolean isConnected(){
    ConnectivityManager connMgr = (ConnectivityManager)       getSystemService(Activity.CONNECTIVITY_SERVICE);
    NetworkInfo networkInfo = connMgr.getActiveNetworkInfo();
    if (networkInfo != null && networkInfo.isConnected())
        return true;
    else
        return false;
}
private class HttpAsyncTask extends AsyncTask<String, Void, String> {
    @Override
    protected String doInBackground(String... urls) {

        return GET(urls[0]);
    }
    // onPostExecute displays the results of the AsyncTask.
    @Override
    protected void onPostExecute(String result) {
        Toast.makeText(getBaseContext(), "Received!", Toast.LENGTH_LONG).show();

       updateDatabase(result);


        etResponse.setText(mydb.getData("FND"));

    }
}

  private void updateDatabase(String result){



    try {
        JSONObject json = new JSONObject(result);
        JSONArray events = json.getJSONArray("events");

       for (int i=0; i<events.length(); i++){


            String name = events.getJSONObject(i).getString("name");
            String date =events.getJSONObject(i).getString("date");
            String category = events.getJSONObject(i).getString("category");
            String description = events.getJSONObject(i).getString("description");
            String time_start = events.getJSONObject(i).getString("time_start");
            String time_end = events.getJSONObject(i).getString("time_end");
            String address = events.getJSONObject(i).getString("address");
            String postcode = events.getJSONObject(i).getString("postcode");

            mydb.insertEvent(name,date,category,description,time_start,time_end,address,postcode);

        }


    } catch (JSONException e) {
        e.printStackTrace();
    }
  }
}

DBHelper.java

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase;

public class DBHelper extends SQLiteOpenHelper {

public static final String DATABASE_NAME = "Events.db";
public static final String EVENTS_TABLE_NAME = "events";
public static final String EVENTS_COLUMN_ID = "event_ID";
public static final String EVENTS_COLUMN_NAME = "name";
public static final String EVENTS_COLUMN_DATE = "date";
public static final String EVENTS_COLUMN_CATEGORY = "category";
public static final String EVENTS_COLUMN_DESCRIPTION = "description";
public static final String EVENTS_COLUMN_TIME_START = "time_start";
public static final String EVENTS_COLUMN_TIME_END = "time_end";
public static final String EVENTS_COLUMN_ADDRESS = "address";
public static final String EVENTS_COLUMN_POSTCODE = "postcode";

private HashMap hp;

public DBHelper(Context context)
{
    super(context, DATABASE_NAME , null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    db.execSQL(
            "create table events " +
                    "(event_ID integer primary key, name text,date text,category text, description text,time_start text," +
                    "time_end text, address text, postcode text)"
    );
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    db.execSQL("DROP TABLE IF EXISTS events");
    onCreate(db);
}

public boolean insertEvent  (String name, String date, String category, String description,String time_start,String time_end, String address,String postcode)
{
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues contentValues = new ContentValues();

    contentValues.put("name", name);
    contentValues.put("date", date);
    contentValues.put("category", category);
    contentValues.put("description", description);
    contentValues.put("time_start", time_start);
    contentValues.put("time_end", time_end);
    contentValues.put("address", address);
    contentValues.put("postcode", postcode);

    db.insert("events", null, contentValues);
    return true;
 }
 public String getData(String name){
    String response = null;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res =  db.rawQuery("select * from events where name=" + name + "", null);
    res.moveToFirst();
    while(res.isAfterLast() == false){
        response += (res.getString(res.getColumnIndex(EVENTS_COLUMN_CATEGORY)));
        res.moveToNext();
    }
    return response;
 }
public int numberOfRows(){
    SQLiteDatabase db = this.getReadableDatabase();
    int numRows = (int) DatabaseUtils.queryNumEntries(db, EVENTS_TABLE_NAME);
    return numRows;
  }
}

日志猫

12-09 05:46:26.420    1701-1701/team08.httpapp D/dalvikvm﹕ Not late-enabling CheckJNI (already on)
12-09 05:46:26.590    1701-1701/team08.httpapp D/﹕ HostConnection::get() New Host Connection established 0xb7a7f180, tid 1701
12-09 05:46:26.620    1701-1701/team08.httpapp W/EGL_emulation﹕ eglSurfaceAttrib not implemented
12-09 05:46:26.640    1701-1721/team08.httpapp D/dalvikvm﹕ GC_FOR_ALLOC freed 142K, 8% free 3197K/3448K, paused 3ms, total 5ms
12-09 05:46:26.640    1701-1701/team08.httpapp D/OpenGLRenderer﹕ Enabling debug mode 0
12-09 05:46:27.290    1701-1701/team08.httpapp E/SQLiteLog﹕ (1) no such column: FND
12-09 05:46:27.290    1701-1701/team08.httpapp D/AndroidRuntime﹕ Shutting down VM
12-09 05:46:27.290    1701-1701/team08.httpapp W/dalvikvm﹕ threadid=1: thread exiting with uncaught exception (group=0xb0c98b20)
12-09 05:46:27.290    1701-1701/team08.httpapp E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: team08.httpapp, PID: 1701
android.database.sqlite.SQLiteException: no such column: FND (code 1): , while compiling: select * from events where name=FND
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
        at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
        at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
        at team08.httpapp.DBHelper.getData(DBHelper.java:72)
        at team08.httpapp.MainActivity$HttpAsyncTask.onPostExecute(MainActivity.java:118)
        at team08.httpapp.MainActivity$HttpAsyncTask.onPostExecute(MainActivity.java:104)
        at android.os.AsyncTask.finish(AsyncTask.java:632)
        at android.os.AsyncTask.access$600(AsyncTask.java:177)
        at android.os.AsyncTask$InternalHandler.handleMessage(AsyncTask.java:645)
        at android.os.Handler.dispatchMessage(Handler.java:102)
        at android.os.Looper.loop(Looper.java:136)
        at android.app.ActivityThread.main(ActivityThread.java:5017)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:515)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
        at dalvik.system.NativeStart.main(Native Method)
12-09 06:06:06.815    1845-1845/team08.httpapp D/dalvikvm﹕ Not late-enabling CheckJNI (already on)
12-09 06:06:07.005    1845-1858/team08.httpapp D/dalvikvm﹕ GC_FOR_ALLOC freed 130K, 7% free 3208K/3448K, paused 4ms, total 9ms
12-09 06:06:07.025    1845-1845/team08.httpapp D/﹕ HostConnection::get() New Host Connection established 0xb7b74070, tid 1845
12-09 06:06:07.075    1845-1845/team08.httpapp W/EGL_emulation﹕ eglSurfaceAttrib not implemented
12-09 06:06:07.085    1845-1845/team08.httpapp D/OpenGLRenderer﹕ Enabling debug mode 0
12-09 06:06:07.625    1845-1845/team08.httpapp E/SQLiteLog﹕ (1) no such column: FND
12-09 06:06:07.625    1845-1845/team08.httpapp D/AndroidRuntime﹕ Shutting down VM
12-09 06:06:07.625    1845-1845/team08.httpapp W/dalvikvm﹕ threadid=1: thread exiting with uncaught exception (group=0xb0c98b20)
12-09 06:06:07.635    1845-1845/team08.httpapp E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: team08.httpapp, PID: 1845
android.database.sqlite.SQLiteException: no such column: FND (code 1): , while compiling: select *    from events where name=FND
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at      android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
        at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
        at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
        at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
        at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
        at team08.httpapp.DBHelper.getData(DBHelper.java:72)
        at team08.httpapp.MainActivity$HttpAsyncTask.onPostExecute(MainActivity.java:118)
        at team08.httpapp.MainActivity$HttpAsyncTask.onPostExecute(MainActivity.java:104)
        at android.os.AsyncTask.finish(AsyncTask.java:632)
        at android.os.AsyncTask.access$600(AsyncTask.java:177)
        at android.os.AsyncTask$InternalHandler.handleMessage(AsyncTask.java:645)
        at android.os.Handler.dispatchMessage(Handler.java:102)
        at android.os.Looper.loop(Looper.java:136)
        at android.app.ActivityThread.main(ActivityThread.java:5017)
        at java.lang.reflect.Method.invokeNative(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:515)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
        at dalvik.system.NativeStart.main(Native Method)

Answer 1

更新您的 getData 方法，为值添加引号:

db.rawQuery("select * from events where name='" + name + "'", null);