SQLiteDatabase query method

Question

I am using the query method of SQLiteDatabase  How do I use the query method    I tried this   Cursor cursor   sqLiteDatabase query      tableName  tableColumns  whereClause  whereArgs  groupBy  having  orderBy     tableColumns - columns parameter is constructed as follows    String   columns   new String   KEY ID  KEY CONTENT     If we need to get all the fields  how should the column parameter to be constructed  Do we need to include all the Field Names in String array   How do I properly use the query method

User · Answer

Where clause and args work together to form the WHERE statement of the SQL query. So say you looking to express

WHERE Column1 = 'value1' AND Column2 = 'value2'

Then your whereClause and whereArgs will be as follows

String whereClause = "Column1 =? AND Column2 =?";
String[] whereArgs = new String[]{"value1", "value2"};

If you want to select all table columns, i believe a null string passed to tableColumns will suffice.

User · Answer

if your SQL query is like this  SELECT col-1  col-2 FROM tableName WHERE col-1 apple col-2 mango GROUPBY col-3 HAVING Count col-4   gt  5  ORDERBY col-2 DESC LIMIT 15    Then for query   method  we can do as -  String table    tableName   String   columns     col-1    col-2    String selection    col-1    AND col-2            String   selectionArgs     apple   mango    String groupBy  col-3  String having    COUNT col-4   gt  5   String orderBy    col-2 DESC   String limit    15    query tableName  columns  selection  selectionArgs  groupBy  having  orderBy  limit

User · Answer

This is a more general answer meant to be a quick reference for future viewers   Example  SQLiteDatabase db   helper getReadableDatabase     String table    table2   String   columns     column1    column3    String selection    column3      String   selectionArgs     apple    String groupBy   null  String having   null  String orderBy    column3 DESC   String limit    10    Cursor cursor   db query table  columns  selection  selectionArgs  groupBy  having  orderBy  limit     Explanation from the documentation        table     String  The table name to compile the query against    columns   String  A list of which columns to return  Passing null will return all columns  which is discouraged to prevent reading data   from storage that isn t going to be used    selection     String  A filter declaring which rows to return  formatted as an SQL WHERE clause  excluding the WHERE itself   Passing   null will return all rows for the given table    selectionArgs     String  You may include  s in selection  which will be replaced by the values from selectionArgs  in order that they   appear in the selection  The values will be bound as Strings    groupBy   String  A filter declaring how to group rows  formatted as an SQL GROUP BY clause  excluding the GROUP BY itself   Passing null   will cause the rows to not be grouped    having    String  A filter declare which row groups to include in the cursor  if row grouping is being used  formatted as an SQL HAVING   clause  excluding the HAVING itself   Passing null will cause all row   groups to be included  and is required when row grouping is not being   used    orderBy   String  How to order the rows  formatted as an SQL ORDER BY clause  excluding the ORDER BY itself   Passing null will use the   default sort order  which may be unordered    limit     String  Limits the number of rows returned by the query  formatted as LIMIT clause  Passing null denotes no LIMIT clause

User · Answer

tableColumns   null for all columns as in SELECT   FROM     new String      column1    column2         for specific columns as in SELECT column1  column2 FROM     - you can also put complex expressions here  new String       SELECT max column1  FROM table1  AS max    would give you a column named max holding the max value of column1   whereClause   the part you put after WHERE without that keyword  e g   column1  gt  5  should include   for things that are dynamic  e g   column1    -  see whereArgs   whereArgs   specify the content that fills each   in whereClause in the order they appear   the others   just like whereClause the statement after the keyword or null if you don t use it    Example  String   tableColumns   new String          column1         SELECT max column1  FROM table2  AS max     String whereClause    column1     OR column1       String   whereArgs   new String          value1        value2     String orderBy    column1   Cursor c   sqLiteDatabase query  table1   tableColumns  whereClause  whereArgs          null  null  orderBy       since we have a named column we can do int idx   c getColumnIndex  max      is equivalent to the following raw query  String queryString        SELECT column1   SELECT max column1  FROM table1  AS max FROM table1          WHERE column1     OR column1     ORDER BY column1   sqLiteDatabase rawQuery queryString  whereArgs       By using the Where Bind -Args version you get automatically escaped values and you don t have to worry if input-data contains     Unsafe  String whereClause    column1      value        Safe  String whereClause    column1      because if value contains a   your statement either breaks and you get exceptions or does unintended things  for example value    XYZ   DROP TABLE table1 --  might even drop your table since the statement would become two statements and a comment   SELECT   FROM table1 where column1  XYZ   DROP TABLE table1 --    using the args version XYZ   DROP TABLE table1 -- would be escaped to  XYZ    DROP TABLE table1 --  and would only be treated as a value  Even if the   is not intended to do bad things it is still quite common that people have it in their names or use it in texts  filenames  passwords etc  So always use the args version   It is okay to build int and other primitives directly into whereClause though

User · Answer

db query          TABLE NAME          new String     TABLE ROW ID  TABLE ROW ONE  TABLE ROW TWO            TABLE ROW ID         rowID          null  null  null  null  null      TABLE ROW ID         rowID  here   is the where clause  To select all values you will have to give all column names    or you can use a raw query like this  db rawQuery  SELECT   FROM permissions table WHERE name    Comics     null     and here is a good tutorial for database

[android] SQLiteDatabase.query method

Examples related to android

Examples related to sqlite