SQL Course - Part 2. Environment

Today is the second installment in our series of posts on SQL. You may want to read Part 1. Introduction before you read this post.

How a database works

The basic architecture of a database is shown in the following diagram.

Database architecture

A database consists of two parts: a database server and data. The database server is the software part of a database. It is an independent, stand-alone program which stores, manages and maintains the data. How data are stored is up to the server and its configuration. A client program (one or many) connects to the database server and issues a request for certain data. In essence, the requests are SQL commands (as we remember from the Introduction) that create, retrieve, update or delete data. The servers processes the request, retrieves the requested data, and hands it to the client program.

Prepare database server

Before we have fun with SQL queries, we have to go through the boring process of setting up the database. In this course, we'll use a lightweight Java database server, called HSQLDB. The setup process of a real-world production database is longer and requires some informed decisions about database configuration. For our purposes, HSQLDB will do.

To set up HSQLDB follow these steps:

  1. Download hsqldb-2.2.8.zip file from http://hsqldb.org/. The current version might be different.
  2. Unzip the archive. Go to the hsqldb-2.2.8/hsqldb directory.
    cd hsqldb-2.2.8
    cd hsqldb
  3. Start the database server.
    java -classpath lib/hsqldb.jar \
    You should see output similar to this: Our database server is up and running, waiting for client connections!
  4. We'll use a HSQLDB client program to create tables and insert some initial data into our database. First start a client program:
    java -classpath lib/hsqldb.jar \ 
  5. A program window will show up. Select HSQL Database Server in the menu and click OK.

    Select HSQL Database Server
  6. Open the script file: create_tables.sql.

    Open SQL script
  7. The script is loaded into the script window. Click Execute SQL to run the script.

    Execute SQL script

    You should see the following result.

    Script output
  8. Open and run the script file insert_data.sql.

Our database is ready!


Prepare Java code

In this section, we'll write a simple database client which we'll use in the next parts of this tutorial to demonstrate how OneWebSQL classes can be used to access the database. First, we'll use OneWebSQL code generator to generate DAO classes. Then we'll use the generated classes to write the client program. OneWebSQL generates classes from a database model prepared in a database modeling tool. At the moment, OneWebSQL supports three modeling tools: PowerDesigner, ERwin and Oracle Data Modeler.  In this tutorial, we'll use OneWebSQL PD2Java tool and an already prepared PowerDesigner model. If you want to use a different modeling tool, download the appropriate OneWebSQL tool from our website.

  1. If you haven't already done so, download the newest version of OneWebSQL PD2Java from OneWebSQL Download page. The current version as of this writing is 1.0.2.
  2. Unzip the archive file. We'll use the name <OWSQL_HOME> to refer to the directory where you unzipped the archive.
  3. Download the PowerDesigner model file Model.pdm. If you want, you can open it in PowerDesigner. We'll investigate the model in more detail in the next episode of our series.
  4. Generate OneWebSQL classes.
    java -jar <OWSQL_HOME>/onewebsql-pd2java-1.0.2/onewebsql-pd2java-1.0.2.jar \
       -model_sets Model.pdm \
       -java_default_package or \
       -java_output_dir . \
       -dictionaries_dir .  \
       -classpath <OWSQL_HOME>/onewebsql-pd2java-1.0.2/onewebsql-runtime-1.0.2.jar \
  5. Open a text editor and create file Main.java:
    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();
            DBAdapter dbAdapter = new HSQLDB2Adapter();
            BookDAO bookDAO = new BookDAOImpl(ds, dbAdapter);
            List<Book> books = bookDAO.getBookList();
            for(Book b : books) {
  6. Compile the code adding <HSQLDB_HOME>/hsqldb/lib/hsqldb.jar and <OWSQL_HOME>/onewebsql-pd2java-1.0.2/onewebsql-runtime-1.0.2.jar into CLASSPATH.
    javac -cp .:<HSQLDB_HOME>/hsqldb/lib/hsqldb.jar:<OWSQL_HOME>/onewebsql-pd2java-1.0.2/onewebsql-runtime-1.0.2.jar Main.java
  7. To verify that the setup is correct run the code.
    java -cp .:<HSQLDB_HOME>/hsqldb/lib/hsqldb.jar:<OWSQL_HOME>/onewebsql-pd2java-1.0.2/onewebsql-runtime-1.0.2.jar Main

    You should see something like

We'll explain the code in the next part of our SQL course.

Can't wait for the next part? Read OneWebSQL documentation.