android - 如何将数据库查询放在一个类中

Question

这是我的问题，我不知道如何将使用 SQLiteStatement 的插入查询放在一个类中而不影响绑定(bind)和迭代器？这是我到目前为止取得的进展。数据库位于 MainActivity 中，但我希望所有查询都放在一个类中。

     btnSave = (Button) findViewById(R.id.btnSaveAlbum);
            btnSave.setOnClickListener(new View.OnClickListener() {
                @TargetApi(Build.VERSION_CODES.JELLY_BEAN)
                @Override
                public void onClick(View v) {
                    txtTitle = (EditText) findViewById(R.id.txtAlbumTitle);
                    String title = txtTitle.getText().toString();

                    if (requestCode == 200 && resultCode == Activity.RESULT_OK) {
                        String[] apath = data.getStringArrayExtra("all_path");
                        ArrayList<String> strings = new ArrayList<String>();
                        Iterator iterate = strings.iterator();
                        for (String string : apath) {
                            strings.add(string);
                        }


                        String query = "INSERT INTO " + TBNAME + " VALUES (?,?);";
                        SQLiteStatement statement = timedb.compileStatement(query);
                        timedb.beginTransaction();

                        while (iterate.hasNext()) {
                            statement.clearBindings();
                            statement.bindString(1, title);
                            statement.bindString(2, iterate.next().toString());
                            statement.execute();
                        }

                        timedb.setTransactionSuccessful();
                        timedb.endTransaction();
                        timedb.close();
 Toast.makeText(MainActivity.this, "Successful", Toast.LENGTH_LONG).show();

                    }
                }
            });

Answer 1

这样做

import java.text.ParseException;
import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbHelper extends SQLiteOpenHelper
{
    private final String TAG = "Database";

    public static final int DATABASE_VERSION = 13;
    public static final String DATABASE_NAME = "assessment.db";



    public static final String FEED_AUDIO_TABLE = "audiTable";
    public static final String FEED_AUDIO_COLUMN_ID = "audioId";
    public static final String FEED_AUDIO_AUDIO = "audio";
    public static final String FEED_AUDIO_ISDOWNLODED = "audioIsdownloaded";


    Context context;    

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

        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {
    //table
        db.execSQL("create table "+FEED_AUDIO_TABLE+
                "("+FEED_AUDIO_COLUMN_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+FEED_AUDIO_AUDIO+" text NOT NULL UNIQUE, "+FEED_AUDIO_ISDOWNLODED+" text);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {

        db.execSQL("DROP TABLE IF EXISTS "+FEED_AUDIO_TABLE);
        onCreate(db);
    }




    //insert method
    public boolean insertAudioForDownload(String audioUrl, String isDownloaded)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();

        contentValues.put(FEED_AUDIO_AUDIO, audioUrl);
        contentValues.put(FEED_AUDIO_ISDOWNLODED, isDownloaded);

        long i = db.insertWithOnConflict(FEED_AUDIO_TABLE, null, contentValues, SQLiteDatabase.CONFLICT_REPLACE);

        Log.i(TAG, "insertForBackground Value inserted: "+i +" for audioUrl :"+ audioUrl+ " isDownloaded is : "+ isDownloaded);


        return true;
    }

    //Read method

    public ArrayList<String> getAudioForDownload(String isDownloaded)
    {
        ArrayList<String> arrayList = new ArrayList<String>();
        String gridSelected = "";
        SQLiteDatabase db = getReadableDatabase();

        String where = FEED_AUDIO_ISDOWNLODED+"=?";
        String[] whereArgs = {isDownloaded};

        Cursor cursor = db.query(FEED_AUDIO_TABLE, null, where, whereArgs, null, null, null);
        if(cursor != null && cursor.getCount() > 0)
        {
            Log.d(TAG, "getAudioForDownload cursor count  is: "+cursor.getCount());

            while(cursor.moveToNext())
            {
                gridSelected = cursor.getString(cursor.getColumnIndex(FEED_AUDIO_AUDIO));

                arrayList.add(gridSelected);
            }
        }

        db.close();

        Log.d(TAG, "getAudioForDownload arrayList size  is: "+arrayList.size());

        return arrayList;
    }
}