android - 如何在 android sqlite 数据库中存储 MySQL 查询(JSON)？

Question

我正在尝试编写代码，以便我的 android 应用程序可以查询 MySQL 数据库，然后检索 JSON 中的数据，然后对其进行解析。然后我想获取数据并将其插入到应用程序在 SQLite 上的本地数据库中。我也希望这些数据以设定的频率更新。

这是 JSON 数组和 HTTP Get 协议(protocol) Activity 。

package com.connector;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.LinearLayout;
import android.widget.TextView;


public class whitehat extends Activity {
/** Called when the activity is first created. */

TextView txt;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
// Create a crude view - this should really be set via the layout resources  
// but since its an example saves declaring them in the XML.  
LinearLayout rootLayout = new LinearLayout(getApplicationContext());  
txt = new TextView(getApplicationContext());  
rootLayout.addView(txt);  
setContentView(rootLayout);  

// Set the text and call the connect function.  
txt.setText("Connecting..."); 
//call the method to run the data retreival
txt.setText(getServerData(KEY_121)); 



}
public static final String KEY_121 = "http://xx.xx.xxx.xxx/hellomysql/mysqlcon.php"; //i use my real ip here



private String getServerData(String returnString) {

InputStream is = null;

String result = "";
//the year data to send
ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();
nameValuePairs.add(new BasicNameValuePair("publisher","Penguin Books UK"));

//http post
try{
        HttpClient httpclient = new DefaultHttpClient();
        HttpPost httppost = new HttpPost(KEY_121);
        httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
        HttpResponse response = httpclient.execute(httppost);
        HttpEntity entity = response.getEntity();
        is = entity.getContent();

}catch(Exception e){
        Log.e("log_tag", "Error in http connection "+e.toString());
}

//convert response to string
try{
        BufferedReader reader = new BufferedReader(new InputStreamReader(is,"iso-8859-1"),8);
        StringBuilder sb = new StringBuilder();
        String line = null;
        while ((line = reader.readLine()) != null) {
                sb.append(line + "\n");
        }
        is.close();
        result=sb.toString();
}catch(Exception e){
        Log.e("log_tag", "Error converting result "+e.toString());
}
//parse json data
try{
        JSONArray jArray = new JSONArray(result);
        for(int i=0;i<jArray.length();i++){
                JSONObject json_data = jArray.getJSONObject(i);
                Log.i("log_tag","id: "+json_data.getInt("id")+
                        ", isbn: "+json_data.getString("isbn")+
                        ", title: "+json_data.getString("title")+
                        ", publisher: "+json_data.getString("publisher")
                );
                //Get an output to the screen
                returnString += "\n\t" + jArray.getJSONObject(i); 
        }
}catch(JSONException e){
        Log.e("log_tag", "Error parsing data "+e.toString());
}
return returnString; 
}    

}

下面两个是数据库相关的 Activity 。

package com.example.database;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class DBAdapter {
public static final String KEY_ROWID = "_id";
public static final String KEY_ISBN = "isbn";
public static final String KEY_TITLE = "title";
public static final String KEY_PUBLISHER = "publisher";    
private static final String TAG = "DBAdapter";

private static final String DATABASE_NAME = "books";
private static final String DATABASE_TABLE = "titles";
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_CREATE =
    "create table titles (_id integer primary key autoincrement, "
    + "isbn text not null, title text not null, " 
    + "publisher text not null);";

private final Context context; 

private DatabaseHelper DBHelper;
private SQLiteDatabase db;
public DBAdapter(Context ctx) 
{
    this.context = ctx;
    DBHelper = new DatabaseHelper(context);
}

private static class DatabaseHelper extends SQLiteOpenHelper 
{
    DatabaseHelper(Context context) 
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase db) 
    {
        db.execSQL(DATABASE_CREATE);        }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, 
    int newVersion) 
    {
        Log.w(TAG, "Upgrading database from version " + oldVersion 
                + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS titles");
        onCreate(db);
    }
}    

//---opens the database---
public DBAdapter open() throws SQLException 
{
    db = DBHelper.getWritableDatabase();
    return this;
}
//---closes the database---    
public void close() 
{
    DBHelper.close();
}

//---insert a title into the database---
public long insertTitle(String isbn, String title, String publisher) 
{
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_ISBN, isbn);
    initialValues.put(KEY_TITLE, title);
    initialValues.put(KEY_PUBLISHER, publisher);
    return db.insert(DATABASE_TABLE, null, initialValues);
}
//---deletes a particular title---
public boolean deleteTitle(long rowId) 
{
    return db.delete(DATABASE_TABLE, KEY_ROWID + 
     "=" + rowId, null) > 0;
}
//---retrieves all the titles---
public Cursor getAllTitles() 
{
    return db.query(DATABASE_TABLE, new String[] {
            KEY_ROWID,
            KEY_ISBN,
            KEY_TITLE,
            KEY_PUBLISHER}, 
            null, 
            null, 
            null, 
            null, 
            null);
}
//---retrieves a particular title---
public Cursor getTitle(long rowId) throws SQLException 
{
    Cursor mCursor =
            db.query(true, DATABASE_TABLE, new String[] {
             KEY_ROWID,
            KEY_ISBN, 
            KEY_TITLE,
            KEY_PUBLISHER
            }, 
        KEY_ROWID + "=" + rowId, 
        null,
        null, 
        null, 
        null, 
        null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}
//---updates a title---
public boolean updateTitle(long rowId, String isbn, 
String title, String publisher) 
{
    ContentValues args = new ContentValues();
    args.put(KEY_ISBN, isbn);
    args.put(KEY_TITLE, title);
    args.put(KEY_PUBLISHER, publisher);
    return db.update(DATABASE_TABLE, args, 
                     KEY_ROWID + "=" + rowId, null) > 0;
}
}

第二个是:

public class DatabaseActivity extends Activity {
DBAdapter db;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    db = new DBAdapter(this); 
    System.out.println("bool1");
    insert();
disAll();
    //update();
    //dis(3);
    //del(5);
}
private void update(){ 

    db.open();
if (db.updateTitle(1, 
     "0470285818", 
"C# 2008 Programmer's Reference",
"Wrox Press"))
        Toast.makeText(this, "Update successful.", 
            Toast.LENGTH_LONG).show();
    else
        Toast.makeText(this, "Update failed.", 
            Toast.LENGTH_LONG).show();
    //-------------------
    //---retrieve the same title to verify---
    Cursor c = db.getTitle(1);
    if (c.moveToFirst())        
        DisplayTitle(c);
    else
        Toast.makeText(this, "No title found", 
         Toast.LENGTH_LONG).show();        
    //-------------------        
db.close();

}
private void insert(){ 

    db.open();        
    long id;
    id = db.insertTitle(
     "0470285819",
    "Teach yourself Java",
    "Wrox");        
    id = db.insertTitle(
    "047017661y",
    "Professional Windows 07 Gadgets Programming",
    "Wrox");
    System.out.println("bool211");
    db.close();
}
private void disAll(){

    db.open();
/*Cursor c = db.getAllTitles();
    if (c.moveToFirst())
    {
        do {          
         System.out.println("bool2");
            DisplayTitle(c);
        } while (c.moveToNext());
    }
    else
     System.out.println("boo3l");*/
    try{
Cursor c = db.getAllTitles();
        if (c.moveToFirst())
        {
            do {          
System.out.println("bool2");
                DisplayTitle(c);
            } while (c.moveToNext());
        }
    }catch(Exception e){
     System.out.println(e);
    }
    db.close();
}

private void   dis(   int   j){

    db.open();
    Cursor c = db.getTitle(j);
    if (c.moveToFirst())        
        DisplayTitle(c);
    else
        Toast.makeText(this, "No title found", 
         Toast.LENGTH_LONG).show();
    db.close();
}        

 private void   del(   int   j){

    db.open();
    if (db.deleteTitle(j))
        Toast.makeText(this, "Delete successful.", 
            Toast.LENGTH_LONG).show();
    else
        Toast.makeText(this, "Delete failed.", 
            Toast.LENGTH_LONG).show();            
    db.close();        
}
public void DisplayTitle(Cursor c)
{
 System.out.println("bool");
    Toast.makeText(this, 
            "id: " + c.getString(0) + "\n" +
            "ISBN: " + c.getString(1) + "\n" +
            "TITLE: " + c.getString(2) + "\n" +
            "PUBLISHER:  " + c.getString(3),
            Toast.LENGTH_LONG).show();        
} 
}

如果有人能够提供帮助，我们将不胜感激。

Answer 1

您的 getServerData 方法将返回一个 JSONString。在您的 getServerData 方法中，您有一个日志语句，它实际上是在解析 JSONObject。您可以调用 dbAdapter 类中的 insertTitle 而不是记录它。

要完成这项工作，您必须进行一些更改。您的 DBAdapter 构造函数应该如下所示

  public DBAdapter(Context ctx) 
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
        this.db=DBHelper.getWritableDatabase();
    }

您的创建语句应如下所示。你从服务器获取 id。所以将 id 作为自动递增是没有意义的。

private static final String DATABASE_CREATE =
    "create table titles (_id integer primary key , "
    + "isbn text not null, title text not null, " 
    + "publisher text not null);";

你的 insertTitle 应该是这样的。同样更改您的更新和其他方法。

public long insertTitle(String _id,String isbn, String title, String publisher) 
{
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_ID, _id);
    initialValues.put(KEY_ISBN, isbn);
    initialValues.put(KEY_TITLE, title);
    initialValues.put(KEY_PUBLISHER, publisher);
    return db.insert(DATABASE_TABLE, null, initialValues);
}

但请确保您在 for 循环之外创建了 dbAdapter 对象。希望这会有所帮助