Using our Students schema from Chapter 2, Using a SQLite Database, let's begin with a glimpse at what our Students table looks like at this point:

In this way, for each query that we do, we'll know exactly what results we should expect and, thus, we can validate our queries. Before we dive right, in here's a list of what we'll cover in this section:

It'll be a lot to take in at once, especially for those with no prior SQL experience, but once you learn these basic building blocks, you'll be well on your way to building longer, more complex queries. And so, let's begin with the most basic SELECT query:

public class BasicQueryActivity extends Activity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        /*
         * SELECT Query
         */


        System.out.println("METHOD 1");

        // METHOD #1 - SQLITEDATABASE RAWQUERY()
        Cursor c = sqdb.rawQuery("SELECT * from " + StudentTable.TABLE_NAME, null);
        while (c.moveToNext()) {
            int colid = c.getColumnIndex(StudentTable.NAME);
            String name = c.getString(colid);
            System.out.println("GOT STUDENT " + name);
        }

        System.out.println("METHOD 2");

        // METHOD #2 - SQLITEDATABASE QUERY()
        c = sqdb.query(StudentTable.TABLE_NAME, null, null, null, null, null, null);
        while (c.moveToNext()) {
            int colid = c.getColumnIndex(StudentTable.NAME);
            String name = c.getString(colid);
            System.out.println("GOT STUDENT " + name);
        }

        System.out.println("METHOD 3");

        // METHOD #3 - SQLITEQUERYBUILDER
        String query = SQLiteQueryBuilder.buildQueryString(false, StudentTable.TABLE_NAME, null, null, null, null, null, null);
        System.out.println(query);
        c = sqdb.rawQuery(query, null);
        while (c.moveToNext()) {
            int colid = c.getColumnIndex(StudentTable.NAME);
            String name = c.getString(colid);
            System.out.println("GOT STUDENT " + name);
        }


    }
}

Here, we see that in the first method, we're simply passing in the standard SQL query, while in the second method we are breaking down the query into its different parameters (that is, its table name, its selection filters, and so on). Finally, in the last method, which we notice looks very similar to the second method (for now), we again break down the query into its different parameters, but instead of returning a Cursor, our method returns the query as a String, which we can then execute as a raw query. The reasoning behind this is that one of SQLiteQueryBuilder's strengths is that you can specify multiple queries and submit them all at the same time and effectively perform a UNION SQL query – but again we will play with this functionality late.

Now, let's take a look at the results from those queries and see if we can validate the results:

Looks pretty good to me! We see that each method was able to return all rows of our table as expected. Under the third method, we can also see the query that was constructed using our SQLiteQueryBuilder class and indeed verify that the SQL query we submitted in the first method matches that built-in the third method.

Now, say you have a large table with thousands of rows of data and with tens of columns – for the sake of both efficiency and memory, it's often suggested in practice that you don't return the entire table with your queries but, instead, refine your queries to only return those columns of data of interest! And so, let's take a look at how we can specify which columns to return in our SELECT queries:

/*
* SELECT COLUMNS Query
*/

System.out.println("METHOD 1");

// METHOD #1 - SQLITEDATABASE RAWQUERY()
c = sqdb.rawQuery(
  "SELECT " + StudentTable.NAME + "," + StudentTable.STATE + " from "+ StudentTable.TABLE_NAME, null);
    while (c.moveToNext()) {
      int colid = c.getColumnIndex(StudentTable.NAME);
      int colid2 = c.getColumnIndex(StudentTable.STATE);
            
    }

System.out.println("METHOD 2");

// METHOD #2 - SQLITEDATABASE QUERY()
String[] cols = new String[] { StudentTable.NAME, StudentTable.STATE };
c = sqdb.query(StudentTable.TABLE_NAME, cols, null, null, null, null, null);
while (c.moveToNext()) {
  int colid = c.getColumnIndex(StudentTable.NAME);
  int colid2 = c.getColumnIndex(StudentTable.STATE);
            
}

System.out.println("METHOD 3");

// METHOD #3 - SQLITEQUERYBUILDER
query = SQLiteQueryBuilder.buildQueryString(false, StudentTable.TABLE_NAME, cols, null, null, null, null, null);
System.out.println(query);
c = sqdb.rawQuery(query, null);
while (c.moveToNext()) {
  int colid = c.getColumnIndex(StudentTable.NAME);
  int colid2 = c.getColumnIndex(StudentTable.STATE);
            
}

And so, we see that the overall structure of the query is the same for all three methods, but in methods two and three, we pass in a String[] containing the columns of data that we want. Again, just to verify that our queries are behaving the way we want them to, here's the output of those queries:

And so we see that indeed we are able to return each student, along with their respective states. Finally again, notice the query that is constructed in the third method and compare it to the raw SQL query that was passed to the first method – they should match exactly and they do.