SQL Course - Part 3. Selecting All Rows

Finally, after getting to know the history of SQL language and setting up the environment, we'll write our first SQL query.

Structure of the database

In the previous episode, we've created tables in our database.

Database structure

The database we've created is a very simple book library. There are four tables:

  • book, which stores information about books. It has columns book_id, title, publication_year, number_of_pages, and category_code.
  • author, which stores information about authors. It has columns author_id and name.
  • category, which stores information about book categories. It has columns category_code and category_name.
  • book_author, which stores the relationship between the tables book and author.

Select all rows from a table

First, make sure that your environment is correctly set up. Start the HSQLDB server and the HSQLDB client.

The SQL query which retrieves all rows from the table book looks like this:

SELECT * FROM book;

Type the query into the SQL field and click Execute SQL.

SELECT * FROM book;

You should see something like this:

Query results

Congratulations! You've successfully run your first SQL query!

How to do it in OneWebSQL

Now we'll invoke the same query in OneWebSQL. In fact, we already wrote this code in the previous post. Recall the Main class:

import java.util.List;

import or.Book;
import or.BookDAO;
import or.BookDAOImpl;

import org.hsqldb.jdbc.JDBCDataSource;

import com.onewebsql.query.DBAdapter;
import com.onewebsql.query.adapter.HSQLDB2Adapter;

public class Main {
    public static void main(String[] args) {
        JDBCDataSource ds = new JDBCDataSource();
        ds.setUrl("jdbc:hsqldb:hsql://localhost/");
        DBAdapter dbAdapter = new HSQLDB2Adapter();
        
        BookDAO bookDAO = new BookDAOImpl(ds, dbAdapter);
        List<Book> books = bookDAO.getBookList();
        
        for(Book b : books) {
            System.out.println(b);
        }
    }
}

All we have to do is understand the code!

For every table, OneWebSQL generates three Java classes:

  1. DAO interface providing set of operations on this table
  2. DAO object implementing the DAO interface, and
  3. Java bean corresponding to a row in a table.

For the table book, the DAO interface is called BookDAO, its implementation is called BookDAOImpl, and the Java bean is simply called Book.

Before you run the query, you have to create an appropriate DAO object. You need two things to do this:

  1. A data source, and
  2. A database adapter.

This is what happens in these lines of code:

JDBCDataSource ds = new JDBCDataSource();
ds.setUrl("jdbc:hsqldb:hsql://localhost/");
DBAdapter dbAdapter = new HSQLDB2Adapter();

A data source is an instance of java.sql.DataSource interface which represents your database. How you set it up is beyond this series of posts. It depends on a database you use and the general setup of your application. Common ways of getting the data source include creating is manually, like we've done it, and using JNDI to retrieve an already configured data source.

Remember when I talked about different SQL dialects in different databases? Well, a database adapter is an answer to that problem in OneWebSQL. A database adapter is an object which ensures OneWebSQL chooses the right SQL dialect when it creates a query. In this course, we use HSQLDB2Adapter, which emits queries using HSQLDB dialect. Other database adapters are implementations of the DBAdapter interface. The list of currently supported databases and their database adapter is given in the table.

Database Database adapter
Oracle OracleDBAdapter
IBM DB2 DB2DBAdapter
MS SQL Server MSSQLDBAdapter
MySQL MySQLDBAdapter
PostgreSQL PostgresDBAdapter
HSQLDB HSQLDB2DBAdapter

 

Once you create the DAO object, you can use it to invoke the query. The getBookList() method does exactly that; it retrieves all Book objects from the table book.

List<Book> books = bookDAO.getBookList();

 

and then we write it in a simple loop:

for(Book b : books) {
    System.out.println(b);
}

 

Want more practice? Try to retrieve all rows from the table authors using both HSQLDB client and OneWebSQL.