java - 想要将json数据插入Sqlite数据库

Question

我想将 json 数据存储到数据库，它必须显示我的应用程序必须显示一些以前的数据，而无需互联网时间。为此，我想创建一个用于存储 json 数据的数据库。

这是我为 json 数据创建的数据库部分。

public class GinfyDbAdapter {

    private static final String DATABASE_NAME = "ginfy.db";
    private static final String DATABASE_TABLE_PROJ = "prayers";
    private static final int DATABASE_VERSION = 2;
    public static final String CATEGORY_COLUMN_ID = "id";
    public static final String CATEGORY_COLUMN_TITLE = "title";
    public static final String CATEGORY_COLUMN_CONTENT = "content";
    public static final String CATEGORY_COLUMN_COUNT = "count";


    private static final String TAG = "ProjectDbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    public GinfyDbAdapter(MainActivity mainActivity) {
        // TODO Auto-generated constructor stub
    }

    public GinfyDbAdapter GinfyDbAdapter(Context context){
        mDbHelper = new DatabaseHelper(context);

        mDb = mDbHelper.getWritableDatabase();
         return this;
    }

    public void saveCategoryRecord(String id, String title, String content, String count) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(CATEGORY_COLUMN_ID, id);
        contentValues.put(CATEGORY_COLUMN_TITLE, title);
        contentValues.put(CATEGORY_COLUMN_CONTENT, content);
        contentValues.put(CATEGORY_COLUMN_COUNT, count);
        mDb.insert(DATABASE_NAME, null, contentValues);
        }
    public Cursor getTimeRecordList() {
        return mDb.rawQuery("select * from " + DATABASE_NAME, null);
        }
    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }



    private static final String DATABASE_CREATE_PROJ =
            "create table " + DATABASE_TABLE_PROJ + " ("
                    + CATEGORY_COLUMN_ID + " integer primary key autoincrement, "
                    + CATEGORY_COLUMN_TITLE + " text not null, " + CATEGORY_COLUMN_CONTENT + " text not null, " + CATEGORY_COLUMN_COUNT + " integer primary key autoincrement   );" ;

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL("CREATE TABLE " + DATABASE_TABLE_PROJ + "( "
                + CATEGORY_COLUMN_ID + " INTEGER PRIMARY KEY, "
                + CATEGORY_COLUMN_TITLE + " TEXT, " + CATEGORY_COLUMN_CONTENT + " TEXT, " + CATEGORY_COLUMN_COUNT + " INTEGER PRIMARY KEY )" );

    }

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

}


}

这是显示 ListView 的主 Activity

public class MainActivity extends Activity implements FetchDataListener,OnClickListener{
    private static final int ACTIVITY_CREATE=0;
    private static final String TAG_CATEGORY = "post";
    private static final String CATEGORY_COLUMN_ID = "id";
    private static final String CATEGORY_COLUMN_TITLE = "title";
    private static final String CATEGORY_COLUMN_CONTENT = "content";
    private static final String CATEGORY_COLUMN_COUNT = "count";
    private static final int Application = 0;
    private ProgressDialog dialog;
    ListView lv;
    ListView lv1;
    private List<Application> items;
    private Button btnGetSelected;
    private Button praycount;
    public int pct;
    private String stringVal;
    private TextView value;
    private int prayers;
    private int prayerid;
    EditText myFilter;
    ApplicationAdapter adapter;
     private GinfyDbAdapter mDbHelper;
     JSONArray contacts = null;
        private SimpleCursorAdapter dataAdapter;

    @Override
    protected void onCreate(Bundle savedInstanceState){
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_list_item); 
        mDbHelper=new GinfyDbAdapter(MainActivity.this);

        lv1 =(ListView)findViewById(R.id.list); 
        lv =(ListView)findViewById(R.id.list);

         ArrayList<HashMap<String, String>> contactList = new ArrayList<HashMap<String, String>>();

            // Creating JSON Parser instance
            //JSONParser jParser = new JSONParser();
            JSONObject jsonObject = new JSONObject();
            //JSONArray aJson = jsonObject.getJSONArray("post");
            String url = "http://www.ginfy.com/api/v1/posts.json";
            // getting JSON string from URL
            JSONArray aJson = jsonObject.getJSONFromUrl(url);

            try {
                // Getting Array of Contacts
                contacts = aJson.getJSONObject(TAG_CATEGORY);

                // looping through All Contacts
                for(int i = 0; i < contacts.length(); i++){
                    JSONObject c = contacts.getJSONObject(i);

                    // Storing each json item in variable
                    String id = c.getString(CATEGORY_COLUMN_ID);
                    String title = c.getString(CATEGORY_COLUMN_TITLE);
                    String  content = c.getString(CATEGORY_COLUMN_CONTENT);
                    String  count = c.getString(CATEGORY_COLUMN_COUNT);

                    mDbHelper.saveCategoryRecord(id,title,content,count);

                    // creating new HashMap
                    HashMap<String, String> map = new HashMap<String, String>();

                    // adding each child node to HashMap key => value
                    map.put(CATEGORY_COLUMN_ID, id);
                    map.put(CATEGORY_COLUMN_TITLE, title);
                    map.put(CATEGORY_COLUMN_CONTENT, content);
                    map.put(CATEGORY_COLUMN_COUNT, count);
                    // adding HashList to ArrayList
                    contactList.add(map);
                }
            } catch (JSONException e) {
                e.printStackTrace();
            }





        btnGetSelected = (Button) findViewById(R.id.btnget);
        btnGetSelected.setOnClickListener(this);

        myFilter = (EditText) findViewById(R.id.myFilter);




        //praycount.setOnClickListener(this);
        initView();
    }

    private void initView(){
        // show progress dialog
        dialog = ProgressDialog.show(this, "", "Loading...");
        String url = "http://www.ginfy.com/api/v1/posts.json";
        FetchDataTask task = new FetchDataTask(this);
        task.execute(url);


    }   

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        super.onCreateOptionsMenu(menu);
        MenuInflater mi = getMenuInflater();
        mi.inflate(R.menu.activity_main, menu); 
        return true;
    }

    @Override
    public boolean onMenuItemSelected(int featureId, MenuItem item) {
        createProject();
        return super.onMenuItemSelected(featureId, item);
    }

    private void createProject() {
        Intent i = new Intent(this, AddPrayerActivity.class);
        startActivityForResult(i, ACTIVITY_CREATE);   
    }

    @Override
    protected void onActivityResult(int requestCode, int resultCode, Intent intent) {
        super.onActivityResult(requestCode, resultCode, intent);
        initView();
    }

    @Override
    public void onFetchComplete(List<Application> data){
        this.items = data;
        // dismiss the progress dialog
        if ( dialog != null )
            dialog.dismiss();
        // create new adapter
        ApplicationAdapter adapter = new ApplicationAdapter(this, data);
        /*dataAdapter adapter = new SimpleCursorAdapter(this,
                    R.layout.activity_row,
                    new String[] { CATEGORY_COLUMN_TITLE, CATEGORY_COLUMN_CONTENT, CATEGORY_COLUMN_COUNT }, new int[] {
                            R.id.text2, R.id.text1, R.id.count });*/

           //lv.setListAdapter(adapter);
        // set the adapter to list
        lv.setAdapter(adapter);
        lv.setOnItemClickListener(new OnItemClickListener() {

            @Override
            public void onItemClick(AdapterView<?> parent, View view,
                    int position, long id) {

                CheckBox chk = (CheckBox) view.findViewById(R.id.checkbox);
                Application bean = items.get(position);
                if (bean.isSelected()) {
                    bean.setSelected(false);
                    chk.setChecked(false);
                } else {
                    bean.setSelected(true);
                    chk.setChecked(true);
                }

            }
        });
    }

    // Toast is here...
    private void showToast(String msg) {
        Toast.makeText(this, msg, Toast.LENGTH_SHORT).show();
    }

    @Override
    public void onFetchFailure(String msg){
        if ( dialog != null )
            dialog.dismiss();
        Toast.makeText(this, msg, Toast.LENGTH_LONG).show();
    }

在使用数据库之前它也会显示在 ListView 中，现在我也想创建数据库，为此我在 mainactivity 中编写了一些代码。

Fetchdatatask.java

public class FetchDataTask extends AsyncTask<String, Void, String>
{
    private final FetchDataListener listener;
    private  OnClickListener onClickListener;
    private String msg;

    public FetchDataTask(FetchDataListener listener)
    {
        this.listener = listener;
    }



    @Override
    protected String doInBackground(String... params)
    {
        if ( params == null )
            return null;
        // get url from params
        String url = params[0];
        try
        {
            // create http connection
            HttpClient client = new DefaultHttpClient();
            HttpGet httpget = new HttpGet(url);
            // connect
            HttpResponse response = client.execute(httpget);
            // get response
            HttpEntity entity = response.getEntity();
            if ( entity == null )
            {
                msg = "No response from server";
                return null;
            }
            // get response content and convert it to json string
            InputStream is = entity.getContent();
            return streamToString(is);
        }
        catch ( IOException e )
        {
            msg = "No Network Connection";

        }
        return null;
    }

    @Override
    protected void onPostExecute(String sJson)
    {
        if ( sJson == null )
        {
            if ( listener != null )
                listener.onFetchFailure(msg);
            return;
        }
        try
        {
            // convert json string to json object
            JSONObject jsonObject = new JSONObject(sJson);
            JSONArray aJson = jsonObject.getJSONArray("post");
            // create apps list
            List<Application> apps = new ArrayList<Application>();
            for ( int i = 0; i < aJson.length(); i++ )
            {
                JSONObject json = aJson.getJSONObject(i);
                Application app = new Application();
                app.setContent(json.getString("content"));
                app.setTitle(json.getString("title"));
                 app.setCount(Integer.parseInt(json.getString("count"))); 
                 app.setId(Integer.parseInt(json.getString("id")));



                // add the app to apps list
                apps.add(app);


            }
            //notify the activity that fetch data has been complete
            if ( listener != null )
                listener.onFetchComplete(apps);
        }
        catch ( JSONException e )
        {
            e.printStackTrace();
            msg = "Invalid response";
            if ( listener != null )
                listener.onFetchFailure(msg);
            return;
        }
    }

    /**
     * This function will convert response stream into json string
     * 
     * @param is
     *            respons string
     * @return json string
     * @throws IOException
     */
    public String streamToString(final InputStream is) throws IOException
    {
        BufferedReader reader = new BufferedReader(new InputStreamReader(is));
        StringBuilder sb = new StringBuilder();
        String line = null;
        try
        {
            while ( (line = reader.readLine()) != null )
            {
                sb.append(line + "\n");
            }
        }
        catch ( IOException e )
        {
            throw e;
        }
        finally
        {
            try
            {
                is.close();
            }
            catch ( IOException e )
            {
                throw e;
            }
        }
        return sb.toString();
    }
}

这个 fetchdatatask 从 json 获取并显示在 ListView 中，我希望在没有互联网时间的情况下它也必须显示在 ListView 中，因为我正在创建数据库。

你能检查一下我的代码是否正确，实际上它在主要 Activity 行 JSONArray aJson = jsonObject.getJSONFromUrl(url); 中显示错误

Answer 1

创建一个类，充当数据库类和主 Activity 之间的中间层，以将数据插入数据库，反之亦然

public class Category {

    String id; 
    String title; 
    String content;
    String count;
    public Category(String id, String title, String content, String count) {
        super();
        this.id = id;
        this.title = title;
        this.content = content;
        this.count = count;
    }
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getContent() {
        return content;
    }
    public void setContent(String content) {
        this.content = content;
    }
    public String getCount() {
        return count;
    }
    public void setCount(String count) {
        this.count = count;
    }
}

在您进行 json 解析的主要 Activity 中，创建一个 DB 类对象并调用其中的保存记录方法，就像我在下面所做的那样

DatabaseHelper mDbHelper;
public class ABC extends Activity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.abc);
                mDbHelper= new DatabaseHelper (this);
                new GetSyncDataAsyncTask().execute();

    }   
}


    private class GetDataAsyncTask extends AsyncTask<Void, Void, Boolean> {
            private ProgressDialog Dialog = new ProgressDialog(ABC.this);

            protected void onPreExecute() {
                Dialog.setMessage("Loading.....");
                Dialog.show();
            }
            @Override
            protected void onPostExecute(Boolean result) {
                super.onPostExecute(result);
                Dialog.dismiss();
                Intent intent = new Intent(ABC.this, XYZ.class);
                startActivity(intent);
            }
            @Override
            protected Boolean doInBackground(Void... params) {
                getData();
                return null;
            }
        }


    public void getProdData() {
        // getting JSON string from URL
        JSONParser parser = new JSONParser();
        JSONObject jsonObject = new JSONObject();
            //JSONArray aJson = jsonObject.getJSONArray("post");
            String url = "http://www.ginfy.com/api/v1/posts.json";
            // getting JSON string from URL
            JSONArray aJson = jsonObject.getJSONFromUrl(url);

            try {
                // Getting Array of Contacts
                contacts = aJson.getJSONObject(TAG_CATEGORY);

                // looping through All Contacts
                for(int i = 0; i < contacts.length(); i++){
                    JSONObject c = contacts.getJSONObject(i);

                    // Storing each json item in variable
                    String id = c.getString(CATEGORY_COLUMN_ID);
                    String title = c.getString(CATEGORY_COLUMN_TITLE);
                    String  content = c.getString(CATEGORY_COLUMN_CONTENT);
                    String  count = c.getString(CATEGORY_COLUMN_COUNT);

                    mDbHelper.saveCategoryRecord(new Category(id,title,content,count));
            }
        } catch (JSONException e) {
            e.printStackTrace();
        }
    }

最后在您的数据库类中插入如下所示的值

public void saveCategoryRecord(Category category) {

        String query = "insert into"+ TABLE_NAME+ values( ?, ?, ?, ?, ?, ? )";
        SQLiteStatement stmt = mDb.compileStatement(query);
        stmt.bindString(1, category.getId());
        stmt.bindString(2, category.getTitle());
        stmt.bindString(3, category.getContent());
        stmt.bindString(4, category.getCount());
        stmt.execute();
    }

我尝试使用与您使用过的相同的东西。我认为这就是你现在有了做到这一点的概念的方式