Sqlitedatabase turn back into json

Question

Good afternoon, I need to do a query but I can’t use the cursor.

Example I have an object that I created that is called Databasehelper, within it I created some methods (prepare,bindParam,getQuery,execute), but in execute I need the return to be a Jsonarray or an array as is done in PHP with mysql. Dry down an example of what I’m trying to do. I have this need because the query will be executed via Javascript in a Czech application, but I cannot use the cursor.

@JavascriptInterface
public JSONObject execute() throws JSONException{
    String sql = this.sql_prepare;
    JSONObject retorno = new JSONObject();
    try
    {
        SQLiteDatabase db = this.getReadableDatabase();
        db.execSQL(sql);

        retorno.put("ok", true);
        retorno.put("return", "Aqui deve conter um objeto array para que eu possa trabalhar com o javascript e esse objeto deve conter todo o retorno do execSQL");
        return retorno;
    } catch(Exception e){

        AlertDialog.Builder erro = new AlertDialog.Builder(contexto);
        erro.setTitle("Erro de SQL");
        erro.setMessage("Ocorreu um erro na execução de sua query.\nQuery: "+sql+"\nErro:"+e.getMessage());
        erro.setPositiveButton("OK", null);
        erro.show();

        retorno.put("ok", false);
        retorno.put("return", e.getMessage().toString());
        return retorno;
    }
}

Answer 1

Well, you can’t use it for that execSQL (String sql):

execSQL(String sql)
Run the single SQL statement that is NOT a SELECT or any other SQL statement that Returns data.

That translated:

execSQL(String sql)
Execute a single SQL statement that is not a SELECT statement or any other SQL statement that returns data.

---

arrayList from BD query

Generate a arrayList from a database query:

public ArrayList<String> GetAllValues(String aTable,String[] aColumn)
{
    ArrayList<String> list = new ArrayList<String>();
    Cursor cursor = sqlDB.query(aTable, aColumn, null, null, null, null, null);
    if (cursor.moveToFirst())
    {
        do
        {
            list.add(cursor.getString(0));
        }
        while (cursor.moveToNext());
    }
    if (cursor != null && !cursor.isClosed()) 
    {
        cursor.close();
    }

    return list;
}

Note: To get all the columns, pass null in the parameter aColumn.

^{Example taken from this reply by @Khawar on SOEN.}

---

JSON object from arrayList

To the question of JSON, you can make use of the class JSONObject to convert the data resulting from your query to JSON.

Example

// Novo objecto JSON
JSONObject jsonObject = new JSONObject();

// Consulta BD
ArrayList<DrugDetails> drugDetails = DataInterface
            .getSelectedDrugDetails(); //isto deve ser a consulta que devolve um arraylist

// Se temos dados
if (drugDetails != null && drugDetails.size() > 0) {

    // Novo array para guardar as entradas do JSON
    JSONArray array = new JSONArray();

    // Por cada entrada da BD, adiciona uma entrada no JSON
    for (DrugDetails selectedDrugDetails : drugDetails) {

        JSONObject json = new JSONObject();
        json.put(APPOINTMENT_ID, ""+"selectedDrugDetails.getAppoinmentID()");
        json.put(DOCUMENT_ID, ""+selectedDrugDetails.getId());
        array.put(json);
    }

    jsonObject.put(COLLATERAL_LIST, array);
}

^{Example taken from this reply by @srikanthgr on SOEN.}