Reputation: 187
Android SQLite using db.query() for JOIN instead of rawquery()
I have tableA, tableB, and tableC table A and tableB are joined by tableA.Id(PK) = tableB.tableAId(FK) table B and tableC are joined by tableB.Id(PK) = tableC.tableBId(FK)
I want to be able to do this:
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
I have found a lot of posts on the web which uses db.rawquery() to implement this query. However I have also heard that rawquery() is less secure than query(). So for the sake of seeking best practice as a beginner, my question is:
Is there a way to implement this query using db.query() instead of db.rawquery()?
thanks in advance.
Upvotes: 15
Views: 21213
Answers (5)
Reputation: 57063
As per SharpEdge's comment and after trying a more complex example based upon Nimrod Dayan's answer, here's a more complex example.
4 joins are used, a generated column is also used. It uses an expression (subtracts timestamps) and then uses that in the WHERE clause.
Basically, the method is to append the join clauses to the table name string (SQLite then moves this for you to after the columns).
DBConstants.SQL????? is resolved to the respective SQL e.g. DBConstants.SQLISNOTNULL resolves to IS NOT NULL
DBConstans.CALCULATED????? are names for calculated columns.
DB????TableConstants.????_COL resolves to column names (.._FULL resolves to table.column e.g. to avoid ambiguous _ID columns).
The method (getToolRules) is as follows :-
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[] whereargs = new String[] {
"0",
Integer.toString(minimumruleperiodindays),
Integer.toString(minimumbuycount)
};
return db.query(DBProductusageTableConstants.PRODUCTUSAGE_TABLE + joinclauses,
columns,whereclause,whereargs,null,null,null);
}
The base SQL, which was created in SQLite Manager, used as a guide to building the method (looks far nicer, IMHO, than the SQL extracted from the cursor in debug) is :-
Note! 0 AS _ID is used to enable the cursor to be used by a CursorAdapter (i.e. CursorAdapters require a column named _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
Upvotes: 0
Reputation: 976
Yes you can use query() instead of rawQuery(), given a single assumption - there are no two same column names in the tables you are joining.
If that criteria is fullfilled, then you can use this answer https://stackoverflow.com/a/34688420/3529903
Upvotes: 1
Reputation: 3100
This is kind of late, but I thought others who're looking for that might benefit from that:
db.query() method natively supports LEFT OUTER JOIN AND INNER JOIN via its table argument so you don't actually need to use SQLiteQueryBuilder to accomplish that. Also it's easier and and pretty much straight forward.
This method is widely used in Google I/O 2015 Schedule app's source code.
A Quick example (String constants left out for brevity):
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");
The key is in the first argument to db.query().
Currently, only LEFT OUTER JOIN and INNER JOIN are supported, which is quite sufficient for most apps.
I hope this answer helps others who're looking for this.
Upvotes: 21
Reputation: 41
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;
}
Upvotes: -2
Reputation: 33515
Is there a way to implement this query using db.query() instead of db.rawquery()?
So it's worth to say that rawQuery() makes a trick. But also exists another approach.
query() method is designed for performing queries over one table. But the best way how to JOIN tables in SQLite is to use SQLiteQueryBuilder and with setTables() method you are able to join.
Hence i recommend you to use mentioned SQLiteQueryBuilder. But it's little more complicated against rawQuery() method where you need to assign only raw statement.
If don't know how to start, check this example:
Note:
Is the fact that rawQuery() is less secure than query() because query() method uses precompiled statements which are safer than "raw" statements. But always you can(should) use placeholders which significantly increase safety of statement as main protection against SQL injections and statement becomes much more human-readable as well.
Upvotes: 21