In the previous post, we've learned how to select all rows from a table. However, most of the time you want to really select data in an SQL query. This means you have to be able to specify some criteria.
I assume you already have a handle on running SQL queries via HSQLDB client and via OneWebSQL. From now on I will skip the instructions how to run them. If you need a reminder, see the Environment and Basic Select posts.
The WHERE clause
If you want to get the list of books published after the year 2005, write this:
SELECT * FROM book WHERE publication_year > 2005;
You specify the select conditions using the WHERE clause in SQL query.
Which conditions can you specify?
First, there are arithmetic conditions. SQL uses arithmetic operators similar to other programming languages.
|>=||greater than or equal to|
|<=||less than or equal to|
|<>, !=||not equal to|
There are also string operators. Obviously, you can use the equality sign '=' to compare two strings:
SELECT * FROM book WHERE title = 'Java Puzzlers';
Use single quotes (the ' sign) to mark the beginning and end of a string in SQL.
There is one more string operator, LIKE, which is used to search for a specific pattern in a string. The "%" sign is used to define wildcards (missing letters in the pattern) both before and after the pattern.
Let's say that you want to get books with titles beginning with 'A'. Well, here you go:
SELECT * FROM book WHERE title LIKE 'A%';
Of course, you can combine various conditions to get more complex conditions.
SELECT * FROM book WHERE title LIKE 'A%' AND publication_year > 2005;
SELECT * FROM book WHERE NOT title LIKE 'A%';
You have AND, OR and NOT operators with their obvious meanings. Use parentheses to form even more complex expressions.
SELECT * FROM book WHERE (title LIKE 'A%' OR title LIKE 'a%') AND publication_year > 2005;
Hint: If you want to see the query OneWebSQL sends to the database, do this:
BookDAO bookDAO = new BookDAOImpl(ds, dbAdapter, new PrintStreamDaoMonitor(System.out));
In the console you'll see something like this:
query string: SELECT book.book_id, book.category_code, book.number_of_pages, book.publication_year, book.title FROM book
We'll repeat the SQL queries we've already seen. To get a list of books published after 2005, invoke this method:
List<Book> books = bookDAO.getBookList( BookDAO.PUBLICATION_YEAR.gt(2005));
BookDAO.PUBLICATION_YEAR is a constant representing the column
publication_year in the table
book. OneWebSQL generates a constant for every column in every table. The column constants have methods to create various conditions.
The methods take values (7, 3.14, "A%") or columns (
BookDAO.PUBLICATION_YEAR) as arguments.
To get a list of books with titles beginning with 'A', run this query:
List<Book> books = bookDAO.getBookList(BookDAO.TITLE.like("A%"));
To combine two conditions, you can use
List<Book> books = bookDAO.getBookList( BookDAO.TITLE.like("A%").and(BookDAO.PUBLICATION_YEAR.gt(2005)));
Use the static
LExp.not method to negate a condition:
List<Book> books = bookDAO.getBookList( LExp.not(BookDAO.TITLE.like("A%")));
If you want to put an expression in parentheses, use static methods
List<Book> books = bookDAO.getBookList( LExp.and( BookDAO.TITLE.like("A%").or(BookDAO.TITLE.like("a%")), BookDAO.PUBLICATION_YEAR.gt(2005)));
Read more about creating conditions in the documentation for the LExp class.
Want more exercises? Get a list of short (less than 100 pages) Java books.