SQLiteDatabase transform return into json

Question

SQLiteDatabase transform return into json

Navigation

#1 by (2 votes)

2

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

Example I have an object that I created that is called DatabaseHelper, inside it I have created some methods (prepare, bindParam, getQuery, execute), however in executing I need the return to be a JSONArray or an array as it is done in PHP with or 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 an ApacheCordova application, but I can not 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;
    }
}

javascript android sqlite apache-cordova

asked by anonymous 20.12.2014 / 20:00

1 answer

Application icon in Visual Studio 2012 Express Get position of wxTextCtrl

score 2 · Accepted Answer

Well, you can not use execSQL (String sql) :

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

What 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 query to DB

Generate a arrayList from a query to the database:

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 aColumn parameter.

Example taken from this response from @Khawar in SOEN.

JSON object from arrayList

For the JSON issue, you can make use of JSONObject to convert the resulting data 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 @srikanthgr response in SOEN.