SQL Course - Part 6. Selecting Columns

If you need a reminder on how to set up the environment for this course, see the Environment and Basic Select posts.

As you know, SQL is a language to select data. It is a common situation that only some columns of a table are of interest, not all of them. For example, you want to know the titles of books in the database, and you don't care about publication year or number of pages. Run this query:

select title from book;

You should see the following result:

In previous SQL course posts an asterisk (*) was used in place of a list of columns. An asterisk means "select all columns"; a list of columns means "only select those columns and nothing else".

Of course, you can combine a column list with logical conditions. For example, if you want to know titles and number of pages of books published after year 2004, run this query:

select title, number_of_pages from book
where publication_year > 2004;

and you get the following result

OneWebSQL

Selecting only a couple of columns in OneWebSQL is a little more complicated than selecting whole rows. Generally, OneWebSQL thinks in terms of database entities: tables and views. The API is optimized to select entire rows. Selecting a different set of data requires some effort, but it can be done.

If you want to select a list of titles, run the following code.

SelectQuery select = new SelectQuery(BookDAO.TABLE_EXPRESSION, Arrays.asList(BookDAO.TITLE));
for(List<Object> elements : bookDAO.selectObjectList(select, new ListRowHandler<Object>()).getObjectList()) {
    System.out.println(elements.get(0));
}

There are some differences if you compare it with what we've seen earlier in the series.

First, we run the DAO method selectObjectList(SelectQuery,RowHandler) rather than the method getBookList(...). That is the method you would use when you want to select arbitrary objects and not entire rows.

The DAO method takes two arguments. Both are new kinds of objects we haven't yet seen in this course.

The first argument is of type com.onewebsql.query.SelectQuery. This is an object representation of an SQL query. The constructor takes two arguments: a table from which we want to select (BookDAO.TABLE_EXPRESSION) and a list of columns. If you omit the list of columns, you select all columns, just as an asterisk would do.

The second argument is a com.onewebsql.util.jdbc.RowHandler. We use a built-in RowHandler called com.onewebsql.util.jdbc.ListRowHandler. We'll learn about RowHandlers later in this series. For now, it's enough to know that the ListRowHandler makes sure that each result row is wrapped as a list of objects.

Finally, you use the method getObjectList() to extract the result values out of OneWebSQL result object.

If you want to add a WHERE condition to the query, use method where(LExp) in SelectQuery. Here is an example:

SelectQuery select = new SelectQuery(BookDAO.TABLE_EXPRESSION, Arrays.asList(BookDAO.TITLE, BookDAO.NUMBER_OF_PAGES));
select.where(BookDAO.PUBLICATION_YEAR.ge(2004));

for(List<Object> elements : bookDAO.selectObjectList(select, new ListRowHandler<Object>()).getObjectList()) {
    System.out.println(elements.get(0) + " " + System.out.println(elements.get(1));
}

 

Want more practice? Select titles and publication years of books with more than 100 pages.