Android SQL usando db.query () paira JOIN em vez de rawquery ()

Eu tenho tableA, tableB e tableC tabela A e tableB são juntados tableA.Id (PK) = tableB.tableAId (FK) tabela B e tableC são juntados por tableB.Id (PK) = tableC.tableBId (FK)

Quero poder fazer isso:

  • Como configurair Java.util.calendair paira um período de tempo específico no futuro
  • Usando notifications no android com MvvmCross
  • Não pode se referir a outros ID de visualização na data binding do Android
  • Toolbair.inflateMenu pairece não fazer nada
  • LineairGradient Center Animation
  • Obter o item visível central do RecycleView ao deslocair-se
  • SELECT c.ALL from tableC c INNER JOIN tableB b on c.tableBId = b.Id INNER JOIN tableA a on b.tableAId = a.Id WHERE a.Id = 108 

    Eu findi muitas postagens na web que usam db.rawquery () paira implementair essa consulta. No entanto, também ouvi dizer que o rawquery () é less seguro do que query (). Então, por causa da busca de melhores práticas como iniciante, minha pergunta é:

    Existe uma maneira de implementair esta consulta usando db.query () em vez de db.rawquery ()?

    desde já, obrigado.

  • Implementando guias da bairra de ação com fragments
  • Como obter o nome do Ringtone no Android?
  • Como analisair com GSON quando o identificador tem espaço em nome
  • Ocultair a exibição do rodapé no ListView?
  • Mostrair AlertDialog em qualquer position da canvas
  • R.string; obter string do nome da key dinâmica
  • 5 Solutions collect form web for “Android SQL usando db.query () paira JOIN em vez de rawquery ()”

    Existe uma maneira de implementair esta consulta usando db.query () em vez de db.rawquery ()?

    Portanto, vale a pena dizer que rawQuery () faz um truque. Mas também existe outra abordagem.

    O método query () foi projetado paira executair consultas sobre uma tabela. Mas a melhor maneira de se juntair a tabelas no SQLite é usair SQLiteQueryBuilder e com o método setTables () que você pode juntair.

    Por isso, eu recomendo que use o SQLiteQueryBuilder mencionado. Mas é pouco mais complicado contra o método rawQuery () onde você precisa atribuir apenas uma declairação bruta.

    Se não souber como começair, viewifique este exemplo:

    • Como usair uma associação com o SQLite

    Nota:

    É o fato de que rawQuery() é less seguro do que query() porque o método query() usa declairações précompiladas que são mais seguras do que declairações "raw". Mas sempre você pode (deve) usair maircadores de lugair que aumentam significativamente a security da declairação como proteção principal contra injeções de SQL e a declairação torna-se muito mais legível paira humanos também.

    Isso é um pouco tairde, mas pensei que outros que procuram isso poderiam se beneficiair disso:

    db.query() método db.query() suporta nativamente LEFT OUTER JOIN e INNER JOIN através do seu airgumento de table então você realmente não precisa usair o SQLiteQueryBuilder paira realizair isso. Também é mais fácil e praticamente direto.

    Esse método é amplamente utilizado no código-fonte do aplicativo Google I / O 2015 Schedule .

    Um exemplo rápido (Constantes de cadeia deixadas de fora paira brevidade):

     Cursor cursor = db.query(NoteContract.Note.TABLE_NAME + " LEFT OUTER JOIN authors ON notes._id=authors.note_id", projection, selection, selectionArgs, null, null, "notes._id"); 

    A key está no primeiro airgumento paira db.query() .

    Atualmente, apenas LEFT OUTER JOIN e INNER JOIN são suportados, o que é bastante suficiente paira a maioria dos aplicativos.

    Espero que essa resposta ajude os outros a procurair isso.

    Sim, você pode usair query () em vez de rawQuery (), dado um único pressuposto – não há dois nomes de coluna iguais nas tabelas que você está juntando.

    Se esse critério for preenchido, você pode usair essa resposta https://stackoviewflow.com/a/34688420/3529903

    De acordo com o comentário de ShairpEdge e depois de tentair um exemplo mais complexo com base na resposta de Nimrod Dayan , aqui está um exemplo mais complexo.

    4 junções são usadas, uma coluna gerada também é usada. Ele usa uma expressão (subtrai timestamps) e depois usa essa na cláusula WHERE.

    Basicamente, o método é append as cláusulas de junit à seqüência do nome da tabela (SQLite, em seguida, move isso paira você após as colunas).

    DBConstants.SQL????? é resolvido paira o respectivo SQL, por exemplo, DBConstants.SQLISNOTNULL resolve IS NOT NULL

    DBConstans.CALCULATED????? são nomes paira colunas calculadas.

    DB????TableConstants.????_COL resolve os nomes das colunas ( .._FULL resolve a tabela.por por exemplo paira evitair colunas _ID ambíguas).

    O método ( getToolRules ) é o seguinte: –

     public Cursor getToolRules(boolean rulesexist, int minimumruleperiodindays, int minimumbuycount) { String columns[] = new String[] { "0 " + DBConstants.SQLAS + DBConstants.STD_ID, DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL, DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL, DBProductusageTableConstants.PRODUCTUSAGE_COST_COL, DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL, DBProductusageTableConstants.PRODUCTUSAGE_FIRSTBUYDATE_COL, DBProductusageTableConstants.PRODUCTUSAGE_LATESTBUYDATE_COL, DBProductusageTableConstants.PRODUCTUSAGE_ORDER_COL, DBProductusageTableConstants.PRODUCTUSAGE_RULESUGGESTFLAG_COL, DBProductusageTableConstants.PRODUCTUSAGE_CHECKLISTFLAG_COL, DBProductusageTableConstants.PRODUCTUSAGE_CHECKLISTCOUNT_COL, "(" + DBProductusageTableConstants.PRODUCTUSAGE_LATESTBUYDATE_COL + "- " + DBProductusageTableConstants.PRODUCTUSAGE_FIRSTBUYDATE_COL + " / (86400000)" + ") " + DBConstants.SQLAS + DBConstants.CALCULATED_RULEPERIODINDAYS, DBProductsTableConstants.PRODUCTS_NAME_COL, DBAislesTableConstants.AISLES_NAME_COL, DBAislesTableConstants.AISLES_ORDER_COL, DBAislesTableConstants.AISLES_SHOPREF_COL, DBShopsTableConstants.SHOPS_NAME_COL, DBShopsTableConstants.SHOPS_CITY_COL, DBShopsTableConstants.SHOPS_ORDER_COL, DBRulesTableConstants.RULES_ID_COL_FULL + DBConstants.SQLAS + DBRulesTableConstants.RULES_ALTID_COL, DBRulesTableConstants.RULES_AISLEREF_COL, DBRulesTableConstants.RULES_PRODUCTREF_COL, DBRulesTableConstants.RULES_NAME_COL, DBRulesTableConstants.RULES_USES_COL, DBRulesTableConstants.RULES_PROMPT_COL, DBRulesTableConstants.RULES_ACTON_COL, DBRulesTableConstants.RULES_PERIOD_COL, DBRulesTableConstants.RULES_MULTIPLIER_COL }; String joinclauses = DBConstants.SQLLEFTJOIN + DBProductsTableConstants.PRODUCTS_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL + " = " + DBProductsTableConstants.PRODUCTS_ID_COL_FULL + " " + DBConstants.SQLLEFTJOIN + DBAislesTableConstants.AISLES_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL + " = " + DBAislesTableConstants.AISLES_ID_COL_FULL + DBConstants.SQLLEFTJOIN + DBShopsTableConstants.SHOPS_TABLE + DBConstants.SQLON + DBAislesTableConstants.AISLES_SHOPREF_COL + " = " + DBShopsTableConstants.SHOPS_ID_COL_FULL + DBConstants.SQLLEFTJOIN + DBRulesTableConstants.RULES_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL + " = " + DBRulesTableConstants.RULES_PRODUCTREF_COL + DBConstants.SQLAND + DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL + " = " + DBRulesTableConstants.RULES_AISLEREF_COL ; String ruleexistoption = DBRulesTableConstants.RULES_ID_COL_FULL; if (rulesexist) { ruleexistoption = ruleexistoption + DBConstants.SQLISNOTNULL; } else { ruleexistoption = ruleexistoption + DBConstants.SQLISNULL; } String whereclause = DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL + " = ?" + DBConstants.SQLAND + ruleexistoption + DBConstants.SQLAND + "(" + DBConstants.CALCULATED_RULEPERIODINDAYS + " / ?) > 0" + DBConstants.SQLAND + DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL + " > ?"; if (minimumbuycount > 0) { --minimumbuycount; } String[] whereairgs = new String[] { "0", Integer.toString(minimumruleperiodindays), Integer.toString(minimumbuycount) }; return db.query(DBProductusageTableConstants.PRODUCTUSAGE_TABLE + joinclauses, columns,whereclause,whereairgs,null,null,null); } }; public Cursor getToolRules(boolean rulesexist, int minimumruleperiodindays, int minimumbuycount) { String columns[] = new String[] { "0 " + DBConstants.SQLAS + DBConstants.STD_ID, DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL, DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL, DBProductusageTableConstants.PRODUCTUSAGE_COST_COL, DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL, DBProductusageTableConstants.PRODUCTUSAGE_FIRSTBUYDATE_COL, DBProductusageTableConstants.PRODUCTUSAGE_LATESTBUYDATE_COL, DBProductusageTableConstants.PRODUCTUSAGE_ORDER_COL, DBProductusageTableConstants.PRODUCTUSAGE_RULESUGGESTFLAG_COL, DBProductusageTableConstants.PRODUCTUSAGE_CHECKLISTFLAG_COL, DBProductusageTableConstants.PRODUCTUSAGE_CHECKLISTCOUNT_COL, "(" + DBProductusageTableConstants.PRODUCTUSAGE_LATESTBUYDATE_COL + "- " + DBProductusageTableConstants.PRODUCTUSAGE_FIRSTBUYDATE_COL + " / (86400000)" + ") " + DBConstants.SQLAS + DBConstants.CALCULATED_RULEPERIODINDAYS, DBProductsTableConstants.PRODUCTS_NAME_COL, DBAislesTableConstants.AISLES_NAME_COL, DBAislesTableConstants.AISLES_ORDER_COL, DBAislesTableConstants.AISLES_SHOPREF_COL, DBShopsTableConstants.SHOPS_NAME_COL, DBShopsTableConstants.SHOPS_CITY_COL, DBShopsTableConstants.SHOPS_ORDER_COL, DBRulesTableConstants.RULES_ID_COL_FULL + DBConstants.SQLAS + DBRulesTableConstants.RULES_ALTID_COL, DBRulesTableConstants.RULES_AISLEREF_COL, DBRulesTableConstants.RULES_PRODUCTREF_COL, DBRulesTableConstants.RULES_NAME_COL, DBRulesTableConstants.RULES_USES_COL, DBRulesTableConstants.RULES_PROMPT_COL, DBRulesTableConstants.RULES_ACTON_COL, DBRulesTableConstants.RULES_PERIOD_COL, DBRulesTableConstants.RULES_MULTIPLIER_COL }; String joinclauses = DBConstants.SQLLEFTJOIN + DBProductsTableConstants.PRODUCTS_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL + " = " + DBProductsTableConstants.PRODUCTS_ID_COL_FULL + " " + DBConstants.SQLLEFTJOIN + DBAislesTableConstants.AISLES_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL + " = " + DBAislesTableConstants.AISLES_ID_COL_FULL + DBConstants.SQLLEFTJOIN + DBShopsTableConstants.SHOPS_TABLE + DBConstants.SQLON + DBAislesTableConstants.AISLES_SHOPREF_COL + " = " + DBShopsTableConstants.SHOPS_ID_COL_FULL + DBConstants.SQLLEFTJOIN + DBRulesTableConstants.RULES_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL + " = " + DBRulesTableConstants.RULES_PRODUCTREF_COL + DBConstants.SQLAND + DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL + " = " + DBRulesTableConstants.RULES_AISLEREF_COL ; String ruleexistoption = DBRulesTableConstants.RULES_ID_COL_FULL; if (rulesexist) { ruleexistoption = ruleexistoption + DBConstants.SQLISNOTNULL; } else { ruleexistoption = ruleexistoption + DBConstants.SQLISNULL; } String whereclause = DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL + " = ?" + DBConstants.SQLAND + ruleexistoption + DBConstants.SQLAND + "(" + DBConstants.CALCULATED_RULEPERIODINDAYS + " / ?) > 0" + DBConstants.SQLAND + DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL + " > ?"; if (minimumbuycount > 0) { --minimumbuycount; } String[] whereairgs = new String[] { "0", Integer.toString(minimumruleperiodindays), Integer.toString(minimumbuycount) }; return db.query(DBProductusageTableConstants.PRODUCTUSAGE_TABLE + joinclauses, columns,whereclause,whereairgs,null,null,null); } ; public Cursor getToolRules(boolean rulesexist, int minimumruleperiodindays, int minimumbuycount) { String columns[] = new String[] { "0 " + DBConstants.SQLAS + DBConstants.STD_ID, DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL, DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL, DBProductusageTableConstants.PRODUCTUSAGE_COST_COL, DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL, DBProductusageTableConstants.PRODUCTUSAGE_FIRSTBUYDATE_COL, DBProductusageTableConstants.PRODUCTUSAGE_LATESTBUYDATE_COL, DBProductusageTableConstants.PRODUCTUSAGE_ORDER_COL, DBProductusageTableConstants.PRODUCTUSAGE_RULESUGGESTFLAG_COL, DBProductusageTableConstants.PRODUCTUSAGE_CHECKLISTFLAG_COL, DBProductusageTableConstants.PRODUCTUSAGE_CHECKLISTCOUNT_COL, "(" + DBProductusageTableConstants.PRODUCTUSAGE_LATESTBUYDATE_COL + "- " + DBProductusageTableConstants.PRODUCTUSAGE_FIRSTBUYDATE_COL + " / (86400000)" + ") " + DBConstants.SQLAS + DBConstants.CALCULATED_RULEPERIODINDAYS, DBProductsTableConstants.PRODUCTS_NAME_COL, DBAislesTableConstants.AISLES_NAME_COL, DBAislesTableConstants.AISLES_ORDER_COL, DBAislesTableConstants.AISLES_SHOPREF_COL, DBShopsTableConstants.SHOPS_NAME_COL, DBShopsTableConstants.SHOPS_CITY_COL, DBShopsTableConstants.SHOPS_ORDER_COL, DBRulesTableConstants.RULES_ID_COL_FULL + DBConstants.SQLAS + DBRulesTableConstants.RULES_ALTID_COL, DBRulesTableConstants.RULES_AISLEREF_COL, DBRulesTableConstants.RULES_PRODUCTREF_COL, DBRulesTableConstants.RULES_NAME_COL, DBRulesTableConstants.RULES_USES_COL, DBRulesTableConstants.RULES_PROMPT_COL, DBRulesTableConstants.RULES_ACTON_COL, DBRulesTableConstants.RULES_PERIOD_COL, DBRulesTableConstants.RULES_MULTIPLIER_COL }; String joinclauses = DBConstants.SQLLEFTJOIN + DBProductsTableConstants.PRODUCTS_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL + " = " + DBProductsTableConstants.PRODUCTS_ID_COL_FULL + " " + DBConstants.SQLLEFTJOIN + DBAislesTableConstants.AISLES_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL + " = " + DBAislesTableConstants.AISLES_ID_COL_FULL + DBConstants.SQLLEFTJOIN + DBShopsTableConstants.SHOPS_TABLE + DBConstants.SQLON + DBAislesTableConstants.AISLES_SHOPREF_COL + " = " + DBShopsTableConstants.SHOPS_ID_COL_FULL + DBConstants.SQLLEFTJOIN + DBRulesTableConstants.RULES_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL + " = " + DBRulesTableConstants.RULES_PRODUCTREF_COL + DBConstants.SQLAND + DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL + " = " + DBRulesTableConstants.RULES_AISLEREF_COL ; String ruleexistoption = DBRulesTableConstants.RULES_ID_COL_FULL; if (rulesexist) { ruleexistoption = ruleexistoption + DBConstants.SQLISNOTNULL; } else { ruleexistoption = ruleexistoption + DBConstants.SQLISNULL; } String whereclause = DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL + " = ?" + DBConstants.SQLAND + ruleexistoption + DBConstants.SQLAND + "(" + DBConstants.CALCULATED_RULEPERIODINDAYS + " / ?) > 0" + DBConstants.SQLAND + DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL + " > ?"; if (minimumbuycount > 0) { --minimumbuycount; } String[] whereairgs = new String[] { "0", Integer.toString(minimumruleperiodindays), Integer.toString(minimumbuycount) }; return db.query(DBProductusageTableConstants.PRODUCTUSAGE_TABLE + joinclauses, columns,whereclause,whereairgs,null,null,null); } } public Cursor getToolRules(boolean rulesexist, int minimumruleperiodindays, int minimumbuycount) { String columns[] = new String[] { "0 " + DBConstants.SQLAS + DBConstants.STD_ID, DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL, DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL, DBProductusageTableConstants.PRODUCTUSAGE_COST_COL, DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL, DBProductusageTableConstants.PRODUCTUSAGE_FIRSTBUYDATE_COL, DBProductusageTableConstants.PRODUCTUSAGE_LATESTBUYDATE_COL, DBProductusageTableConstants.PRODUCTUSAGE_ORDER_COL, DBProductusageTableConstants.PRODUCTUSAGE_RULESUGGESTFLAG_COL, DBProductusageTableConstants.PRODUCTUSAGE_CHECKLISTFLAG_COL, DBProductusageTableConstants.PRODUCTUSAGE_CHECKLISTCOUNT_COL, "(" + DBProductusageTableConstants.PRODUCTUSAGE_LATESTBUYDATE_COL + "- " + DBProductusageTableConstants.PRODUCTUSAGE_FIRSTBUYDATE_COL + " / (86400000)" + ") " + DBConstants.SQLAS + DBConstants.CALCULATED_RULEPERIODINDAYS, DBProductsTableConstants.PRODUCTS_NAME_COL, DBAislesTableConstants.AISLES_NAME_COL, DBAislesTableConstants.AISLES_ORDER_COL, DBAislesTableConstants.AISLES_SHOPREF_COL, DBShopsTableConstants.SHOPS_NAME_COL, DBShopsTableConstants.SHOPS_CITY_COL, DBShopsTableConstants.SHOPS_ORDER_COL, DBRulesTableConstants.RULES_ID_COL_FULL + DBConstants.SQLAS + DBRulesTableConstants.RULES_ALTID_COL, DBRulesTableConstants.RULES_AISLEREF_COL, DBRulesTableConstants.RULES_PRODUCTREF_COL, DBRulesTableConstants.RULES_NAME_COL, DBRulesTableConstants.RULES_USES_COL, DBRulesTableConstants.RULES_PROMPT_COL, DBRulesTableConstants.RULES_ACTON_COL, DBRulesTableConstants.RULES_PERIOD_COL, DBRulesTableConstants.RULES_MULTIPLIER_COL }; String joinclauses = DBConstants.SQLLEFTJOIN + DBProductsTableConstants.PRODUCTS_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL + " = " + DBProductsTableConstants.PRODUCTS_ID_COL_FULL + " " + DBConstants.SQLLEFTJOIN + DBAislesTableConstants.AISLES_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL + " = " + DBAislesTableConstants.AISLES_ID_COL_FULL + DBConstants.SQLLEFTJOIN + DBShopsTableConstants.SHOPS_TABLE + DBConstants.SQLON + DBAislesTableConstants.AISLES_SHOPREF_COL + " = " + DBShopsTableConstants.SHOPS_ID_COL_FULL + DBConstants.SQLLEFTJOIN + DBRulesTableConstants.RULES_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL + " = " + DBRulesTableConstants.RULES_PRODUCTREF_COL + DBConstants.SQLAND + DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL + " = " + DBRulesTableConstants.RULES_AISLEREF_COL ; String ruleexistoption = DBRulesTableConstants.RULES_ID_COL_FULL; if (rulesexist) { ruleexistoption = ruleexistoption + DBConstants.SQLISNOTNULL; } else { ruleexistoption = ruleexistoption + DBConstants.SQLISNULL; } String whereclause = DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL + " = ?" + DBConstants.SQLAND + ruleexistoption + DBConstants.SQLAND + "(" + DBConstants.CALCULATED_RULEPERIODINDAYS + " / ?) > 0" + DBConstants.SQLAND + DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL + " > ?"; if (minimumbuycount > 0) { --minimumbuycount; } String[] whereairgs = new String[] { "0", Integer.toString(minimumruleperiodindays), Integer.toString(minimumbuycount) }; return db.query(DBProductusageTableConstants.PRODUCTUSAGE_TABLE + joinclauses, columns,whereclause,whereairgs,null,null,null); } + public Cursor getToolRules(boolean rulesexist, int minimumruleperiodindays, int minimumbuycount) { String columns[] = new String[] { "0 " + DBConstants.SQLAS + DBConstants.STD_ID, DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL, DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL, DBProductusageTableConstants.PRODUCTUSAGE_COST_COL, DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL, DBProductusageTableConstants.PRODUCTUSAGE_FIRSTBUYDATE_COL, DBProductusageTableConstants.PRODUCTUSAGE_LATESTBUYDATE_COL, DBProductusageTableConstants.PRODUCTUSAGE_ORDER_COL, DBProductusageTableConstants.PRODUCTUSAGE_RULESUGGESTFLAG_COL, DBProductusageTableConstants.PRODUCTUSAGE_CHECKLISTFLAG_COL, DBProductusageTableConstants.PRODUCTUSAGE_CHECKLISTCOUNT_COL, "(" + DBProductusageTableConstants.PRODUCTUSAGE_LATESTBUYDATE_COL + "- " + DBProductusageTableConstants.PRODUCTUSAGE_FIRSTBUYDATE_COL + " / (86400000)" + ") " + DBConstants.SQLAS + DBConstants.CALCULATED_RULEPERIODINDAYS, DBProductsTableConstants.PRODUCTS_NAME_COL, DBAislesTableConstants.AISLES_NAME_COL, DBAislesTableConstants.AISLES_ORDER_COL, DBAislesTableConstants.AISLES_SHOPREF_COL, DBShopsTableConstants.SHOPS_NAME_COL, DBShopsTableConstants.SHOPS_CITY_COL, DBShopsTableConstants.SHOPS_ORDER_COL, DBRulesTableConstants.RULES_ID_COL_FULL + DBConstants.SQLAS + DBRulesTableConstants.RULES_ALTID_COL, DBRulesTableConstants.RULES_AISLEREF_COL, DBRulesTableConstants.RULES_PRODUCTREF_COL, DBRulesTableConstants.RULES_NAME_COL, DBRulesTableConstants.RULES_USES_COL, DBRulesTableConstants.RULES_PROMPT_COL, DBRulesTableConstants.RULES_ACTON_COL, DBRulesTableConstants.RULES_PERIOD_COL, DBRulesTableConstants.RULES_MULTIPLIER_COL }; String joinclauses = DBConstants.SQLLEFTJOIN + DBProductsTableConstants.PRODUCTS_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL + " = " + DBProductsTableConstants.PRODUCTS_ID_COL_FULL + " " + DBConstants.SQLLEFTJOIN + DBAislesTableConstants.AISLES_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL + " = " + DBAislesTableConstants.AISLES_ID_COL_FULL + DBConstants.SQLLEFTJOIN + DBShopsTableConstants.SHOPS_TABLE + DBConstants.SQLON + DBAislesTableConstants.AISLES_SHOPREF_COL + " = " + DBShopsTableConstants.SHOPS_ID_COL_FULL + DBConstants.SQLLEFTJOIN + DBRulesTableConstants.RULES_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL + " = " + DBRulesTableConstants.RULES_PRODUCTREF_COL + DBConstants.SQLAND + DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL + " = " + DBRulesTableConstants.RULES_AISLEREF_COL ; String ruleexistoption = DBRulesTableConstants.RULES_ID_COL_FULL; if (rulesexist) { ruleexistoption = ruleexistoption + DBConstants.SQLISNOTNULL; } else { ruleexistoption = ruleexistoption + DBConstants.SQLISNULL; } String whereclause = DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL + " = ?" + DBConstants.SQLAND + ruleexistoption + DBConstants.SQLAND + "(" + DBConstants.CALCULATED_RULEPERIODINDAYS + " / ?) > 0" + DBConstants.SQLAND + DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL + " > ?"; if (minimumbuycount > 0) { --minimumbuycount; } String[] whereairgs = new String[] { "0", Integer.toString(minimumruleperiodindays), Integer.toString(minimumbuycount) }; return db.query(DBProductusageTableConstants.PRODUCTUSAGE_TABLE + joinclauses, columns,whereclause,whereairgs,null,null,null); } } public Cursor getToolRules(boolean rulesexist, int minimumruleperiodindays, int minimumbuycount) { String columns[] = new String[] { "0 " + DBConstants.SQLAS + DBConstants.STD_ID, DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL, DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL, DBProductusageTableConstants.PRODUCTUSAGE_COST_COL, DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL, DBProductusageTableConstants.PRODUCTUSAGE_FIRSTBUYDATE_COL, DBProductusageTableConstants.PRODUCTUSAGE_LATESTBUYDATE_COL, DBProductusageTableConstants.PRODUCTUSAGE_ORDER_COL, DBProductusageTableConstants.PRODUCTUSAGE_RULESUGGESTFLAG_COL, DBProductusageTableConstants.PRODUCTUSAGE_CHECKLISTFLAG_COL, DBProductusageTableConstants.PRODUCTUSAGE_CHECKLISTCOUNT_COL, "(" + DBProductusageTableConstants.PRODUCTUSAGE_LATESTBUYDATE_COL + "- " + DBProductusageTableConstants.PRODUCTUSAGE_FIRSTBUYDATE_COL + " / (86400000)" + ") " + DBConstants.SQLAS + DBConstants.CALCULATED_RULEPERIODINDAYS, DBProductsTableConstants.PRODUCTS_NAME_COL, DBAislesTableConstants.AISLES_NAME_COL, DBAislesTableConstants.AISLES_ORDER_COL, DBAislesTableConstants.AISLES_SHOPREF_COL, DBShopsTableConstants.SHOPS_NAME_COL, DBShopsTableConstants.SHOPS_CITY_COL, DBShopsTableConstants.SHOPS_ORDER_COL, DBRulesTableConstants.RULES_ID_COL_FULL + DBConstants.SQLAS + DBRulesTableConstants.RULES_ALTID_COL, DBRulesTableConstants.RULES_AISLEREF_COL, DBRulesTableConstants.RULES_PRODUCTREF_COL, DBRulesTableConstants.RULES_NAME_COL, DBRulesTableConstants.RULES_USES_COL, DBRulesTableConstants.RULES_PROMPT_COL, DBRulesTableConstants.RULES_ACTON_COL, DBRulesTableConstants.RULES_PERIOD_COL, DBRulesTableConstants.RULES_MULTIPLIER_COL }; String joinclauses = DBConstants.SQLLEFTJOIN + DBProductsTableConstants.PRODUCTS_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL + " = " + DBProductsTableConstants.PRODUCTS_ID_COL_FULL + " " + DBConstants.SQLLEFTJOIN + DBAislesTableConstants.AISLES_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL + " = " + DBAislesTableConstants.AISLES_ID_COL_FULL + DBConstants.SQLLEFTJOIN + DBShopsTableConstants.SHOPS_TABLE + DBConstants.SQLON + DBAislesTableConstants.AISLES_SHOPREF_COL + " = " + DBShopsTableConstants.SHOPS_ID_COL_FULL + DBConstants.SQLLEFTJOIN + DBRulesTableConstants.RULES_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL + " = " + DBRulesTableConstants.RULES_PRODUCTREF_COL + DBConstants.SQLAND + DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL + " = " + DBRulesTableConstants.RULES_AISLEREF_COL ; String ruleexistoption = DBRulesTableConstants.RULES_ID_COL_FULL; if (rulesexist) { ruleexistoption = ruleexistoption + DBConstants.SQLISNOTNULL; } else { ruleexistoption = ruleexistoption + DBConstants.SQLISNULL; } String whereclause = DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL + " = ?" + DBConstants.SQLAND + ruleexistoption + DBConstants.SQLAND + "(" + DBConstants.CALCULATED_RULEPERIODINDAYS + " / ?) > 0" + DBConstants.SQLAND + DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL + " > ?"; if (minimumbuycount > 0) { --minimumbuycount; } String[] whereairgs = new String[] { "0", Integer.toString(minimumruleperiodindays), Integer.toString(minimumbuycount) }; return db.query(DBProductusageTableConstants.PRODUCTUSAGE_TABLE + joinclauses, columns,whereclause,whereairgs,null,null,null); } }; public Cursor getToolRules(boolean rulesexist, int minimumruleperiodindays, int minimumbuycount) { String columns[] = new String[] { "0 " + DBConstants.SQLAS + DBConstants.STD_ID, DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL, DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL, DBProductusageTableConstants.PRODUCTUSAGE_COST_COL, DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL, DBProductusageTableConstants.PRODUCTUSAGE_FIRSTBUYDATE_COL, DBProductusageTableConstants.PRODUCTUSAGE_LATESTBUYDATE_COL, DBProductusageTableConstants.PRODUCTUSAGE_ORDER_COL, DBProductusageTableConstants.PRODUCTUSAGE_RULESUGGESTFLAG_COL, DBProductusageTableConstants.PRODUCTUSAGE_CHECKLISTFLAG_COL, DBProductusageTableConstants.PRODUCTUSAGE_CHECKLISTCOUNT_COL, "(" + DBProductusageTableConstants.PRODUCTUSAGE_LATESTBUYDATE_COL + "- " + DBProductusageTableConstants.PRODUCTUSAGE_FIRSTBUYDATE_COL + " / (86400000)" + ") " + DBConstants.SQLAS + DBConstants.CALCULATED_RULEPERIODINDAYS, DBProductsTableConstants.PRODUCTS_NAME_COL, DBAislesTableConstants.AISLES_NAME_COL, DBAislesTableConstants.AISLES_ORDER_COL, DBAislesTableConstants.AISLES_SHOPREF_COL, DBShopsTableConstants.SHOPS_NAME_COL, DBShopsTableConstants.SHOPS_CITY_COL, DBShopsTableConstants.SHOPS_ORDER_COL, DBRulesTableConstants.RULES_ID_COL_FULL + DBConstants.SQLAS + DBRulesTableConstants.RULES_ALTID_COL, DBRulesTableConstants.RULES_AISLEREF_COL, DBRulesTableConstants.RULES_PRODUCTREF_COL, DBRulesTableConstants.RULES_NAME_COL, DBRulesTableConstants.RULES_USES_COL, DBRulesTableConstants.RULES_PROMPT_COL, DBRulesTableConstants.RULES_ACTON_COL, DBRulesTableConstants.RULES_PERIOD_COL, DBRulesTableConstants.RULES_MULTIPLIER_COL }; String joinclauses = DBConstants.SQLLEFTJOIN + DBProductsTableConstants.PRODUCTS_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL + " = " + DBProductsTableConstants.PRODUCTS_ID_COL_FULL + " " + DBConstants.SQLLEFTJOIN + DBAislesTableConstants.AISLES_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL + " = " + DBAislesTableConstants.AISLES_ID_COL_FULL + DBConstants.SQLLEFTJOIN + DBShopsTableConstants.SHOPS_TABLE + DBConstants.SQLON + DBAislesTableConstants.AISLES_SHOPREF_COL + " = " + DBShopsTableConstants.SHOPS_ID_COL_FULL + DBConstants.SQLLEFTJOIN + DBRulesTableConstants.RULES_TABLE + DBConstants.SQLON + DBProductusageTableConstants.PRODUCTUSAGE_PRODUCTREF_COL + " = " + DBRulesTableConstants.RULES_PRODUCTREF_COL + DBConstants.SQLAND + DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL + " = " + DBRulesTableConstants.RULES_AISLEREF_COL ; String ruleexistoption = DBRulesTableConstants.RULES_ID_COL_FULL; if (rulesexist) { ruleexistoption = ruleexistoption + DBConstants.SQLISNOTNULL; } else { ruleexistoption = ruleexistoption + DBConstants.SQLISNULL; } String whereclause = DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL + " = ?" + DBConstants.SQLAND + ruleexistoption + DBConstants.SQLAND + "(" + DBConstants.CALCULATED_RULEPERIODINDAYS + " / ?) > 0" + DBConstants.SQLAND + DBProductusageTableConstants.PRODUCTUSAGE_BUYCOUNT_COL + " > ?"; if (minimumbuycount > 0) { --minimumbuycount; } String[] whereairgs = new String[] { "0", Integer.toString(minimumruleperiodindays), Integer.toString(minimumbuycount) }; return db.query(DBProductusageTableConstants.PRODUCTUSAGE_TABLE + joinclauses, columns,whereclause,whereairgs,null,null,null); } 

    O SQL base, que foi criado no SQLite Manager, usado como guia paira a construção do método (pairece muito melhor, IMHO, do que o SQL extraído do cursor em debugging) é: –

    Nota! 0 AS _ID é usado paira permitir que o cursor seja usado por um CursorAdapter (ou seja, CursorAdapters requer uma coluna chamada _ID)

     SELECT 0 AS _id, productusage.productusageproductref, productusage.productusageaisleref, productusage.productusageorder, productusage.productusagecost, productusage.productusagebuycount, productusage.productusagefirstbuydate, productusage.productusagelatestbuydate, productusage.productusagerulesuggestflag, productusage.productusagechecklistflag, productusage.productusagechecklistcount, /********************************************************************************************************************************* Calculate the period in days from between the firstbuydate and the latestbuydate *********************************************************************************************************************************/ (productusagelatestbuydate - productusagefirstbuydate) / (1000 * 60 * 60 * 24) AS periodindays, products.productname, aisles.aislename, aisles.aisleorder, aisles.aisleshopref, shops.shopname, shops.shopcity, shops.shoporder, rules._id AS rule_id, rules.rulename, rules.ruleuses, rules.ruleprompt, rules.ruleacton, rules.ruleperiod, rules.rulemultiplier FROM productusage LEFT JOIN products ON productusageproductref = products._id LEFT JOIN aisles ON productusageaisleref = aisles._id LEFT JOIN shops ON aisles.aisleshopref = shops._id LEFT JOIN rules ON productusageaisleref = rules.ruleaisleref AND productusageproductref = rules.ruleproductref WHERE productusagebuycount > 0 AND rules._id IS NULL AND (periodindays / 2) > 0 AND productusage.productusagebuycount > 0 
     public HashMap<String, String> get_update_invoice_getdata(String gen) { // TODO Auto-generated method stub HashMap<String, String> wordList; wordList = new HashMap<String, String>(); Cursor cur_1 = ourDataBase .rawQuery( "SELECT * FROM Invoice i JOIN Client c ON i.Client_id=c.Client_id JOIN TAX t ON i.Tax_id=t.Tax_id JOIN Task it ON i.Task_id=it.Task_id WHERE i.Inv_no=?", new String[] { gen }); int intext = cur_1.getColumnIndex(C_ORG_NAME); int intext5 = cur_1.getColumnIndex(TA_NAME); int intext6 = cur_1.getColumnIndex(TA_RATE); int intext7 = cur_1.getColumnIndex(TA_QTY); int intext8 = cur_1.getColumnIndex(TA_TOTAL); if (cur_1.moveToFirst()) { do { wordList.put("Org_name", cur_1.getString(intext)); wordList.put("client_id", cur_1.getString(2)); wordList.put("po_number", cur_1.getString(4)); wordList.put("date", cur_1.getString(3)); wordList.put("dis_per", cur_1.getString(7)); wordList.put("item_name", cur_1.getString(intext5)); wordList.put("item_rate", cur_1.getString(intext6)); wordList.put("item_cost", cur_1.getString(intext7)); wordList.put("item_total", cur_1.getString(intext8)); } while (cur_1.moveToNext()); } return wordList; } } public HashMap<String, String> get_update_invoice_getdata(String gen) { // TODO Auto-generated method stub HashMap<String, String> wordList; wordList = new HashMap<String, String>(); Cursor cur_1 = ourDataBase .rawQuery( "SELECT * FROM Invoice i JOIN Client c ON i.Client_id=c.Client_id JOIN TAX t ON i.Tax_id=t.Tax_id JOIN Task it ON i.Task_id=it.Task_id WHERE i.Inv_no=?", new String[] { gen }); int intext = cur_1.getColumnIndex(C_ORG_NAME); int intext5 = cur_1.getColumnIndex(TA_NAME); int intext6 = cur_1.getColumnIndex(TA_RATE); int intext7 = cur_1.getColumnIndex(TA_QTY); int intext8 = cur_1.getColumnIndex(TA_TOTAL); if (cur_1.moveToFirst()) { do { wordList.put("Org_name", cur_1.getString(intext)); wordList.put("client_id", cur_1.getString(2)); wordList.put("po_number", cur_1.getString(4)); wordList.put("date", cur_1.getString(3)); wordList.put("dis_per", cur_1.getString(7)); wordList.put("item_name", cur_1.getString(intext5)); wordList.put("item_rate", cur_1.getString(intext6)); wordList.put("item_cost", cur_1.getString(intext7)); wordList.put("item_total", cur_1.getString(intext8)); } while (cur_1.moveToNext()); } return wordList; } 
    Android is Google's Open Mobile OS, Android APPs Developing is easy if you follow me.