java - 如何从数据库中绘制与前一行具有相同ID但来自不同表的行？

Question

我有下面这个方法，它从 sqlite 数据库中，从名为 tblPitanja 的 tbale 中提取随机问题(数据库中有 3 个表)和四个可能的答案。用户回答问题后，我现在需要从同一个 sqlite 数据库但不同的表中提取具有相同 ID 的问题和答案。我需要问一些与上一个问题相关的附加问题。怎么做？现在对我来说最困难的部分是如何知道上一个问题中的 ID 是什么。

private void nextQuestion() {
        counter++;
        TestAdapter mDbHelper = new TestAdapter(this);
        mDbHelper.createDatabase();

        try{ 

            mDbHelper.open(); 

            Cursor c = mDbHelper.getTestData(generateWhereClause());

            mAnsweredQuestions.add(c.getLong(0));

            List<Answer> labels = new ArrayList<Answer>();

            labels.add(new Answer(c.getString(2), true));
            labels.add(new Answer(c.getString(3), false));
            labels.add(new Answer(c.getString(4), false));
            labels.add(new Answer(c.getString(5), false));

            tacanOdg = c.getString(2);


            Collections.shuffle(labels);

            byte[] image_bytes = c.getBlob(c.getColumnIndex("PITANJE"));
            ByteArrayInputStream inputStream = new ByteArrayInputStream(image_bytes); 
            Bitmap bp=BitmapFactory.decodeStream(inputStream);

            if(counter < 11){
            flag.setImageBitmap(bp);

            bOdgovor1.setText(labels.get(0).option);
            bOdgovor1.setTag(labels.get(0));
            bOdgovor1.setOnClickListener(clickListener);

            bOdgovor2.setText(labels.get(1).option);
            bOdgovor2.setTag(labels.get(1));
            bOdgovor2.setOnClickListener(clickListener);

            bOdgovor3.setText(labels.get(2).option);
            bOdgovor3.setTag(labels.get(2));
            bOdgovor3.setOnClickListener(clickListener);

            bOdgovor4.setText(labels.get(3).option);
            bOdgovor4.setTag(labels.get(3));
            bOdgovor4.setOnClickListener(clickListener);

            rezultat.setText(counter + "/10");
            score.setText("Score: " + brojacTacnihOdgovora);

            }else{
                brojacVremena.cancel();

            }
        }
        finally{ 
            mDbHelper.close();
        }

编辑:

数据库助手:

public class DataBaseHelper extends SQLiteOpenHelper
{
private static String TAG = "DataBaseHelper"; // Tag just for the LogCat window
//destination path (location) of our database on device
private static String DB_PATH = "/data/data/rs.androidaplikacije.flags/databases/";
private static String DB_NAME ="pitanja.sqlite";// Database name
private static SQLiteDatabase mDataBase; 
private final Context mContext;

public DataBaseHelper(Context mojContext) 
{
    super(mojContext, DB_NAME, null, 1);// 1? its Database Version
    DB_PATH = mojContext.getApplicationInfo().dataDir + "/databases/";
    this.mContext = mojContext;
}

public void createDataBase() throws IOException
{
    //If database not exists copy it from the assets


        this.getReadableDatabase();
        this.close();
        try 
        {
            //Copy the database from assests
            copyDataBase();
            Log.e(TAG, "createDatabase database created");
        } 
        catch (IOException mIOException) 
        {
            throw new Error("ErrorCopyingDataBase");
        }
    }
    /*Check that the database exists here: /data/data/your package/databases/Da Name
    private boolean checkDataBase()
    {
        File dbFile = new File(DB_PATH + DB_NAME);
        //Log.v("dbFile", dbFile + "   "+ dbFile.exists());
        return dbFile.exists();
    }
    */

    //Copy the database from assets
    private void copyDataBase() throws IOException
    {
        InputStream mInput = mContext.getAssets().open(DB_NAME);
        String outFileName = DB_PATH + DB_NAME;
        OutputStream mOutput = new FileOutputStream(outFileName);
        byte[] mBuffer = new byte[1024];
        int mLength;
        while ((mLength = mInput.read(mBuffer))>0)
        {
            mOutput.write(mBuffer, 0, mLength);
        }
        mOutput.flush();
        mOutput.close();
        mInput.close();
    }

    //Open the database, so we can query it
    public boolean openDataBase() throws SQLException
    {
        String mPath = DB_PATH + DB_NAME;
        //Log.v("mPath", mPath);
        mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.CREATE_IF_NECESSARY);
        //mDataBase = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
        return mDataBase != null;
    }
    @Override
    public void close() 
    {
        if(mDataBase != null)
            mDataBase.close();
        super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase arg0) {
        }

    @Override
    public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
        Log.w("DataBaseHelper", "Upgrading database!!!!!");
          onCreate(arg0);

    }


}

适配器:

public class TestAdapter 
{
    protected static final String TAG = "DataAdapter";

    private final Context mContext;
    private SQLiteDatabase mDb;
    private DataBaseHelper mDbHelper;

    public TestAdapter(Context context) 
    {
        this.mContext = context;
        mDbHelper = new DataBaseHelper(mContext);
    }

    public TestAdapter createDatabase() throws SQLException 
    {
        try 
        {
            mDbHelper.createDataBase();
        } 
        catch (IOException mIOException) 
        {
            Log.e(TAG, mIOException.toString() + "  UnableToCreateDatabase");
            throw new Error("UnableToCreateDatabase");
        }
        return this;
    }

    public TestAdapter open() throws SQLException 
    {
        try 
        {
            mDbHelper.openDataBase();
            mDbHelper.close();
            mDb = mDbHelper.getReadableDatabase();
        } 
        catch (SQLException mSQLException) 
        {
            Log.e(TAG, "open >>"+ mSQLException.toString());
            throw mSQLException;
        }
        return this;
    }

    public void close() 
    {
        mDbHelper.close();
    }

     public Cursor getTestData(String whereClause)
     {;
         try
         {
             String sql ="SELECT * FROM tblPitanja WHERE 1 = 1 " + whereClause + " ORDER BY RANDOM() LIMIT 1";

             Cursor mCur = mDb.rawQuery(sql, null);
             if (mCur!=null)
             {
                mCur.moveToNext();
             }
             return mCur;
         }
         catch (SQLException mSQLException) 
         {
             Log.e(TAG, "getTestData >>"+ mSQLException.toString());
             throw mSQLException;
         }
     }
     public Cursor getTestDataGradovi()
     {;
         try
         {
             String sql ="SELECT * FROM tblGradovi LIMIT 1";

             Cursor mCur = mDb.rawQuery(sql, null);
             if (mCur!=null)
             {
                mCur.moveToNext();
             }
             return mCur;
         }
         catch (SQLException mSQLException) 
         {
             Log.e(TAG, "getTestData >>"+ mSQLException.toString());
             throw mSQLException;
         }
     }
     public Cursor getTestDataValute()
     {;
         try
         {
             String sql ="SELECT * FROM tblValute LIMIT 1";

             Cursor mCur = mDb.rawQuery(sql, null);
             if (mCur!=null)
             {
                mCur.moveToNext();
             }
             return mCur;
         }
         catch (SQLException mSQLException) 
         {
             Log.e(TAG, "getTestData >>"+ mSQLException.toString());
             throw mSQLException;
         }
     }
}

以及我针对其他问题的新方法(尚未完成，它只是复制了我的第一个方法，并进行了一些更改以使用数据库适配器类中的 .getTestDataGradovi() :

private void nextQuestionGrad() {
         flag.setVisibility(View.GONE);
         dodatnoPitanje.setVisibility(View.VISIBLE);

            TestAdapter mDbHelper = new TestAdapter(this);
            mDbHelper.createDatabase();

            try{ 

                mDbHelper.open(); 

                Cursor c = mDbHelper.getTestDataGradovi();

                mAnsweredQuestions.add(c.getLong(0));

                List<Answer> labels = new ArrayList<Answer>();

                labels.add(new Answer(c.getString(2), true));
                labels.add(new Answer(c.getString(3), false));
                labels.add(new Answer(c.getString(4), false));
                labels.add(new Answer(c.getString(5), false));

                tacanOdg = c.getString(2);

                Collections.shuffle(labels);


                dodatnoPitanje.setText(c.getString(1));

                bOdgovor1.setText(labels.get(0).option);
                bOdgovor1.setTag(labels.get(0));
                bOdgovor1.setOnClickListener(clickListenerGrad);

                bOdgovor2.setText(labels.get(1).option);
                bOdgovor2.setTag(labels.get(1));
                bOdgovor2.setOnClickListener(clickListenerGrad);

                bOdgovor3.setText(labels.get(2).option);
                bOdgovor3.setTag(labels.get(2));
                bOdgovor3.setOnClickListener(clickListenerGrad);

                bOdgovor4.setText(labels.get(3).option);
                bOdgovor4.setTag(labels.get(3));
                bOdgovor4.setOnClickListener(clickListenerGrad);

                score.setText("Score: " + brojacTacnihOdgovora);


                }
            }
            finally{
                mDbHelper.close();
            }
     }

Answer 1

您需要更改 getTestDataGradovi 以加载一条由其 ID 标识的特定记录:

public Cursor getTestDataGradovi(long id) {
    String sql = "SELECT * FROM tblGradovi WHERE _ID = " + id;
    Cursor c = mDb.rawQuery(sql, null);
    c.moveToNext();
    return c;
}

在nextQuestion内，您需要将当前问题的ID保存在某处:

mCurrentID = c.GetLong(0);

在nextQuestionGrad中，您可以使用保存的ID来获取相应的其他问题:

Cursor c = mDbHelper.getTestDataGradovi(mCurrentID);