Android sqlite order by com case

Question

Android sqlite order by com case

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

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

Using this StackOverflow response in English: link

I created a SQLite query 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;

Would it be something like this? Is it correct to use SQLiteDatabase Query or should I use some other form?

Thanks in advance

java android sqlite

asked by anonymous 27.06.2015 / 15:25

2 answers

How to use ListActivity with ActionBar? JQuery - Tie inside a DIV

score 2 · Answer 1

I do not know if I understand your question well because you can use the rawQuery() method like this:

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);

Parameters are indicated in query by the symbol ?
In the second parameter of rawQuery() a String[] must be passed with the values to be assigned to each of the parameters.
Note: principle that the query is correct.

score 0 · Answer 2

The answer from @ramaral is almost correct, it works with the parameters but the class is not compiled in the form posted, as below:

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 done append in the parameters worked out.

Source: link