Android sqlite order by com case

Question

I need to sort a query like the example below, where, the searched text is 'un', it first brings the items that start with 'un'. This is exactly what I need, to order the output of the query, so that the items that start with the text are the first of the result, but without eliminating the others.

Using this answer from Stackoverflow: https://stackoverflow.com/a/1588817/1997073

I created a query in Sqlite like this:

SELECT ProDescricao FROM ProdutosDB
where ProDescricao like '%un%'
ORDER BY CASE WHEN ProDescricao like 'un %'    THEN 0
                 WHEN ProDescricao like 'un%'     THEN 1
                 WHEN ProDescricao like '%un%' THEN 2
                 ELSE 3
END, ProDescricao;

Output example:

28 |UNIAO SOLD 40-- 
29 |UNIAO SOLD 50-- 
30 |UNIAO SOLD 60-- 
35 |ADAPT UNIVERSAL 
44 |ADESIVO JUNTA 3M
48 |ALIC MINI 4,5 UN
34 |ACESS WC JUNIOR 

My question is, how to do this, with parameter? Calling the query by Android using Sqlitedatabase Query?

Note that the parameter would always be the same:

SELECT ProDescricao FROM ProdutosDB
where ProDescricao like '%'+@busca+'%'
ORDER BY CASE WHEN ProDescricao like ''+@busca+' %'    THEN 0
                 WHEN ProDescricao like ''+@busca+'%'     THEN 1
                 WHEN ProDescricao like '%'+@busca+'%' THEN 2
                 ELSE 3
END, ProDescricao;

Is it something like that? Is it correct to use the Sqlitedatabase Query or should I use some other way?

Thanks in advance

Answer 1

I’m not sure I quite understand your question because you can use the method rawQuery() thus:

String query = "SELECT ProDescricao FROM ProdutosDB " +
               "where ProDescricao like %?% " +
               "ORDER BY CASE WHEN ProDescricao like ?% THEN 0 " +
                             "WHEN ProDescricao like ?% THEN 1 " +
                             "WHEN ProDescricao like %?% THEN 2 " +
                             "ELSE 3 " + 
                        "END, ProDescricao";

String[] parametros = new String[] {"valor","valor","valor","valor"}

Cursor cursor = db.rawQuery(query, parametros);  

The parameters are given in query through the symbol ?
In the second parameter of rawQuery() must be passed a String[] with the values to be assigned to each of the parameters.
_{Note: I assume that the query is correct.}

Answer 2

The @ramaral response is almost correct, works with the parameters but the class is not compiled in the posted way, below as it was:

String query = "SELECT PD.ProCodigo, PD.ProDescricao, PD.ProP_Venda, ET.EstoqueQtdade, PD.ProQtdLote," +
                " TRIM(PD.ProCodigo||PD.ProDescricao) as ProCodDesc, ET.PermiteNegativo" +
                " FROM ProdutosDB PD, EstoqueDB ET " +
                " WHERE ET.EstoqueCodigo = PD.ProCodigo AND PD.ProDescricao LIKE ? " +
                " ORDER BY CASE WHEN PD.ProDescricao like ? THEN 0 " +
                              " WHEN PD.ProDescricao like ? THEN 1 " +
                              " WHEN PD.ProDescricao like ? THEN 2" +
                         " ELSE 3 END, "+ ( ordenarPorCodigo ?  "PD.ProCodigo;" : "PD.ProDescricao;");

String[] parametros = new String[]{"%"+busca+"%", busca+"%", busca+" %", "%"+busca+"%"};
Cursor c = getReadableDatabase().rawQuery(query, parametros);

The symbols '%' added directly to the queries caused errors, when append in the parameters worked.

Source: https://stackoverflow.com/a/5934890/1997073