1. Introduction

Throughout this tutorial, we’ll walk you through the creation of a simple book management application. The tutorial will show you two things:

After completing the tutorial, you will have a good grasp of what programming with OneWebSQL™ looks like.

Where to get help:
Full OneWebSQL™ documentation is available online at http://onewebsql.com/documentation. If you're having trouble with the tutorial or OneWebSQL™ just send email to contact@OneWebSQL.com.

What you need:

2. Setup

We will use OneWebSQL Demo as a basis for the tutorial application.

We recommend that you use your favorite IDE. The Demo comes with Eclipse project descriptors that work out the box, but you can use any other IDE.

  1. Unzip the archive
  2. Point JAVA_HOME environment variable to your JDK directory
  3. Import onewebsql-pd2java-1.0.3 directory as a project in your IDE.

3. Quick Start

OneWebSQL Demo comes pre-compiled and pre-configured. It should work out of the box. Run the runquick.bat script and check its output:

C:\onewebsql-demo-1.0.3>runquick.bat
query string: SELECT book.book_id, book.title, book.isbn, book.publication_year, [...]
book_id         : 100
title           : Clean Code - A handbook of Agile Software Craftsmanship
isbn            : 978-99999000100
publication_year: 2005
number_of_pages : 226
--------------------------------------------------
book_id         : 101
title           : Coders at Work
isbn            : 978-99999000101
publication_year: 2003
number_of_pages : 126
--------------------------------------------------
book_id         : 102
title           : Effective Java
isbn            : 978-99999000102
publication_year: 2003
number_of_pages : 88
--------------------------------------------------
book_id         : 103

[...]

Working in Linux:
All command line examples in the tutorial use Windows *.bat scripts. If you work in Linux just use *.sh scripts.

Make sure that JAVA_HOME variable points to JDK!
OneWebSQL requires JDK (not JRE!) for source code generation. It's best to put the path to JDK in setenv.bat script. Uncomment the line that looks like this:

set "JAVA_HOME=C:\Program Files\Java\jdk1.7.0_07"

Change the path to point to your local JDK installation.

When you run runquick.bat the code from OneWebSQLQuickstart.java is executed.

Open the src/com/onewebsql/demo/OneWebSQLQuickstart.java and get yourself familiar with the code. It displays list of all books in the database. Let's see how it does this:

Step 1: Obtain javax.sql.DataSource.

DataSource dataSource = getDataSource();

Where is the database?
In the tutorial, we will use the embedded HSQLDB2 database, bundled with the OneWebSQL Demo.

In your application, you have to configure the DataSource yourself. If you're programming a standalone application, you'll probably need to create a new DataSource instance and configure its connection properties. If you're programming a JavaEE application you'll have to get your DataSource from the JNDI context.

Step 2: Define SQL dialect of your database.

DBAdapter dbAdapter = new HSQLDB2Adapter();

Use a database adapter that matches the database SQL dialect.

What database dialects are supported?
OneWebSQL officially supports these databases:

All database adapters are in the com.onewebsql.query.adapter package.

Step 3 (optional): Create DAO monitor.

DaoMonitor systemErrDaoMonitor = new PrintStreamDaoMonitor(System.err);

This DAO monitor logs everything to System.err.

Step 4: Create DAO instance.

BookDAO bookDAO = new BookDAOImpl(dataSource, dbAdapter, systemErrDaoMonitor);

Pass the data source, database adapter, and DAO monitor to BookDAO constructor. If you don't use the DAO monitor, use a two-argument DAO constructor.

Step 5: Retrieve and update data in the database.

OneWebSQL is ready. It's time to have some fun! :)

Edit the OneWebSQLQuickstart.java and put if statement around the book-displaying code (lines 101-109) to make it run only when the first command line argument is list-books:

if (args.length > 0 && "list-books".equals(args[0])) {
	List<Book> bookList = bookDAO.getBookList();
	for (Book book : bookList) {
		System.out.println("book_id         : " + BookId());
		System.out.println("title           : " + Title());
		System.out.println("isbn            : " + book.getIsbn());
		System.out.println("publication_year: " + PublicationYear());
		System.out.println("number_of_pages : " + NumberOfPages());
		System.out.println("--------------------------------------------------");
	}
}

You've created your first command for our application! Just call runquick.bat with list-books argument:

C:\onewebsql-demo-1.0.3>runquick.bat list-books
query string: SELECT book.book_id, book.title, book.isbn, book.publication_year, [...]
book_id         : 100
title           : Clean Code - A handbook of Agile Software Craftsmanship

[...]

4. Database Schema

OneWebSQL Demo database has the following schema:

There are two main tables in the database:

There are two reference tables that describe book records:

There is a single relation table:

There are two views:

The database is ready to use, and has some initial data. Before you proceed, explore the database and play with some live data. Use HSQL Database Manager for this:

C:\onewebsql-demo-1.0.3>viewdb.bat

Can I change the database?
You can add, delete and modify rows as you wish. It's best to have at least a few rows in each table to make sure that code you write works as expected.

Don't change the schema of the database (add/remove columns or tables). You will see how OneWebSQL handles schema changes later in the tutorial.

If something goes awry during your experiments, you can always restore database to its original state by running:

C:\onewebsql-demo-1.0.3>cleandb.bat

Remember to close HSQL Database Manager!
HSQL Database Manager locks access to the database. Your application won't be able to use it while HSQL Database Manager is running. If that happens your application will throw this exception:

[...]
Caused by: org.hsqldb.HsqlException: Database lock acquisition failure: lockFile: [...]
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.error.Error.error(Unknown Source)
	at org.hsqldb.persist.LockFile.newLockFileLock(Unknown Source)
	at org.hsqldb.persist.Logger.acquireLock(Unknown Source)
	at org.hsqldb.persist.Logger.openPersistence(Unknown Source)
	at org.hsqldb.Database.reopen(Unknown Source)
	at org.hsqldb.Database.open(Unknown Source)
	at org.hsqldb.DatabaseManager.getDatabase(Unknown Source)
	at org.hsqldb.DatabaseManager.newSession(Unknown Source)
	... 8 more

5. Select Rows From the Database

Now that you're familiar with the code and the schema, let's add another command to list all authors in the database.

Where are the DAO classes?
The code generated by OneWebSQL is placed in the gensrc directory. All classes are in the com.onewebsql.demo.or package. We use short class names for brevity. As you go through the tutorial add imports as needed.

Create AuthorDAO object:

AuthorDAO authorDAO = new AuthorDAOImpl(dataSource, dbAdapter, systemErrDaoMonitor);

Call its getAuthorList() method and iterate over all rows:

List<Author> authorList = authorDAO.getAuthorList();
for (Author author : authorList) {
    // ...
}

Write each row to the console:

for (Author author : authorList) {
	System.out.println("author_id: " + author.getAuthorId());
	System.out.println("name     : " + author.getName());
	System.out.println("--------------------------------------------------");
}

Wrap everything with if and make it list-authors command (as you did for list-books). The code should look like this:

if (args.length > 0 & "list-authors".equals(args[0])) {
	AuthorDAO authorDAO = new AuthorDAOImpl(dataSource, dbAdapter, systemErrDaoMonitor);
	List<Author> authorList = authorDAO.getAuthorList();
	for (Author author : authorList) {
		System.out.println("author_id: " + author.getAuthorId());
		System.out.println("name     : " + author.getName());
		System.out.println("--------------------------------------------------");
	}
}

Now test your changes:

C:\onewebsql-demo-1.0.3>runquick.bat list-authors
query string: SELECT author.author_id, author.name FROM author
author_id: 200
name     : Robert C. Martin
--------------------------------------------------
author_id: 201
name     : Peter Seibel
--------------------------------------------------
author_id: 202

[...]

6. Modify the Database

6.1. Insert

Let's insert some rows into the author table.

Create an Author object to represent author row. Pass the second command line argument to setName(String):

Author author = new Author();
author.setName(args[1]);

Create DAO and call its insert(Author) method:

AuthorDAO authorDAO = new AuthorDAOImpl(dataSource, dbAdapter, systemErrDaoMonitor);
authorDAO.insert(author);

Write a row to the console:

System.out.println("Inserted row into author table: " + author);

Make it add-author command. The code should look like this:

if (args.length > 1 && "add-author".equals(args[0])) {
	Author author = new Author();
	author.setName(args[1]);
	AuthorDAO authorDAO = new AuthorDAOImpl(dataSource, dbAdapter, systemErrDaoMonitor);
	authorDAO.insert(author);
	System.out.println("Inserted row into author table: " + author);
}

Test your changes:

C:\onewebsql-demo-1.0.3>runquick.bat add-author "Donald Knuth"
insert statement: INSERT INTO author(author_id, name) VALUES (?, ?)
parameter #1: "250" (INTEGER)
parameter #2: "Donald Knuth" (VARCHAR)
Inserted row into author table: com.onewebsql.demo.or.Author@b3f4c[authorId=250,
name=Donald Knuth]

C:\onewebsql-demo-1.0.3>runquick.bat list-authors
query string: SELECT author.author_id, author.name FROM author
[...]
--------------------------------------------------
author_id: 250
name     : Donald Knuth
--------------------------------------------------

Where did id come from?
Note that you don't have to provide the id column when calling insert(Author). The primary key value is taken from the appropriate database sequence.

6.2. Update

Now let's update some author rows.

Parse the row identifier using the second command line argument:

Integer authorId = Integer.valueOf(Integer.parseInt(args[1]));

Create AuthorDAO and fetch row to update using primary key:

AuthorDAO authorDAO = new AuthorDAOImpl(dataSource, dbAdapter, systemErrDaoMonitor);
Author author = authorDAO.getByPK(authorId);

If row exists, change its value and invoke AuthorDAO#update(Author) method:

if (author != null) {
	author.setName(args[2]);
	authorDAO.update(author);
	System.out.println("Updated author row: " + author);
} else {
	System.out.println("No author row with id: " + authorId);
}

Make the code into update-author command. It should look like this:

if (args.length > 2 && "update-author".equals(args[0])) {
	Integer authorId = Integer.valueOf(Integer.parseInt(args[1]));
	AuthorDAO authorDAO = new AuthorDAOImpl(dataSource, dbAdapter, systemErrDaoMonitor);
	Author author = authorDAO.getByPK(authorId);
	if (author != null) {
		author.setName(args[2]);
		authorDAO.update(author);
		System.out.println("Updated author row: " + author);
	} else {
		System.out.println("No author row with id: " + authorId);
	}
}

Test your changes:

C:\onewebsql-demo-1.0.3>runquick.bat update-author 250 "Donald Knuth 2"
query string: SELECT author.author_id, author.name FROM author WHERE author.author_id = ?
parameter #1: "250" (INTEGER)
update statement: UPDATE author SET name = ? WHERE author.author_id = ?
parameter #1: "Donald Knuth 2" (VARCHAR)
parameter #2: "250" (INTEGER)
Updated author row: com.onewebsql.demo.or.Author@167e8f2[authorId=250,name=Donald Knuth 2]

C:\onewebsql-demo-1.0.3>runquick.bat list-authors
query string: SELECT author.author_id, author.name FROM author
[...]
--------------------------------------------------
author_id: 250
name     : Donald Knuth 2
--------------------------------------------------

6.3. Delete

Finally you'll learn how to delete a row.

Parse the row identifier using the second command line argument:

Integer authorId = Integer.valueOf(Integer.parseInt(args[1]));

Create AuthorDAO and fetch the row to delete using the primary key:

AuthorDAO authorDAO = new AuthorDAOImpl(dataSource, dbAdapter, systemErrDaoMonitor);
Author author = authorDAO.getByPK(authorId);

If the row exists, invoke the AuthorDAO#delete(Author) method to remove the row from the database:

if (author != null) {
	authorDAO.delete(author);
	System.out.println("Deleted author row: " + author);
} else {
	System.out.println("No author row with id: " + Authorid);
}

Finally, wrap the code and make it delete-author command. The code should look like this:

if (args.length > 1 && "delete-author".equals(args[0])) {
	Integer authorId = Integer.valueOf(Integer.parseInt(args[1]));
	AuthorDAO authorDAO = new AuthorDAOImpl(dataSource, dbAdapter, systemErrDaoMonitor);
	Author author = authorDAO.getByPK(authorId);
	if (author != null) {
		authorDAO.delete(author);
		System.out.println("Deleted author row: " + author);
	} else {
		System.out.println("No author row with id: " + authorId);
	}
}

Test your changes:

C:\onewebsql-demo-1.0.3>runquick.bat delete-author 250
query string: SELECT author.author_id, author.name FROM author WHERE author.author_id = ?
parameter #1: "250" (INTEGER)
delete statement: DELETE FROM author WHERE author.author_id = ?
parameter #1: "250" (INTEGER)
Deleted author row: com.onewebsql.demo.or.Author@c9c2d7[authorId=250,name=Donald Knuth 2]

Do I need to retrieve the row before the update or delete?
No. In this tutorial, we fetch the rows for demonstration purposes only. If you have the primary key of a row. you can delete it using AuthorDAO#delete(Integer). If you want to delete or update multiple rows in one go, you need more advanced features of OneWebSQL. For additional information see DAO Guide.

Congratulations! Now that you know how to SELECT, INSERT, UPDATE and DELETE rows from the database, let's move on to more advanced topics.

7. JOINs

Now let's do something more interesting, and display books with their authors and number of pages for each book. To do so, you need to join three tables: book, book_author and author. See ER diagram for details.

Define the Join object for first pair of joined tables (book and book_author):

Join j1 = Join.join(BookDAO.TABLE_EXPRESSION, BookAuthorDAO.TABLE_EXPRESSION,
		BookDAO.BOOK_ID.eq(BookAuthorDAO.BOOK_ID));

Join#join(...) method takes three arguments. The first two arguments are TableExpressions that represent joined tables. The third argument defines the join condition.

How is the join condition defined?
The join condition is written using an object-oriented representation of SQL in Java (OneWebSQL Query DSL). The DSL (Domain-specific language) is based on Fluent Interface pattern. You use it to write type-safe, syntactically-correct SQL. IDE autocomplete helps you quickly type names of tables and columns.

The expression BookDAO.BOOK_ID.eq(BookAuthorDAO.BOOK_ID) is the same as book.book_id = book_author.book_id in SQL. You'll learn more about OneWebSQL Query DSL in Query Language Guide

Now define the join for the second pair of tables, but this time for the first table expression, use Join from the first pair:

Join j2 = Join.join(j1, AuthorDAO.TABLE_EXPRESSION,
		BookAuthorDAO.AUTHOR_ID.eq(AuthorDAO.AUTHOR_ID));

As before, the join condition is defined using OneWebSQL Query DSL.

What if I need to join more than three tables?
If you want to join more that three tables, just create more Join objects, passing the join created in the previous step in first argument (e.g., create j3 and pass j2 as its first argument, and so on). Remember to define an appropriate condition for each join. Use constants generated by OneWebSQL.

Create SelectQuery:

SelectQuery query = new SelectQuery(j2,
		Arrays.asList(AuthorDAO.NAME, BookDAO.TITLE, BookDAO.NUMBER_OF_PAGES));

SelectQuery is used to define SQL queries. You can use it to build arbitrarily complex SELECT statements. SelectQuery constructor takes two arguments. First is TableExpression that describes what tables you select from. Pass j2 as the first argument. The second argument is a list of columns to fetch. Pass a list of constants from the DAO interfaces that were generated by OneWebSQL.

Can I add WHERE to SelectQuery?
Yes. SelectQuery is part of OneWebSQL Query DSL. You can specify additional constraints on the queried data that correspond to different SELECT clauses (e.g. WHERE, ORDER BY, GROUP BY). For more information, see Query Language Guide.

Create RowHandler. RowHandler is used to create object from each row returned from the database. You'll learn how to create your own RowHandler later in the tutorial. For now, we'll use the generic ListRowHandler shipped with OneWebSQL:

RowHandler<List<Object>> rowHandler = new ListRowHandler<Object>();

Create AuthorDAO and call its selectObjectList(SelectQuery, RowHandler) method:

AuthorDAO authorDAO = new AuthorDAOImpl(dataSource, dbAdapter, systemErrDaoMonitor);
SelectObjectListResult<List<Object>> result = authorDAO.selectObjectList(query, rowHandler);

Why AuthorDAO and not BookDAO or BookAuthorDAO?
It does not matter. You could as easily create and call selectObjectList(SelectQuery, RowHandler) on BookDAO or BookAuthorDAO. The only thing that matters is DataSource and DBAdapter of DAO.

Unpack the query result and iterate over all rows returned from the database:

for (List<Object> row : result.getObjectList()) {
	System.out.println("author         : " + row.get(0));
	System.out.println("title          : " + row.get(1));
	System.out.println("number_of_pages: " + row.get(2));
	System.out.println("--------------------------------------------------");
}

Finally, wrap everything make it list-books-with-authors command. The code should look like this:

if (args.length > 0 && "list-books-with-authors".equals(args[0])) {
	Join j1 = Join.join(BookDAO.TABLE_EXPRESSION, BookAuthorDAO.TABLE_EXPRESSION,
			BookDAO.BOOK_ID.eq(BookAuthorDAO.BOOK_ID));
	Join j2 = Join.join(j1, AuthorDAO.TABLE_EXPRESSION,
			BookAuthorDAO.AUTHOR_ID.eq(AuthorDAO.AUTHOR_ID));
	
	SelectQuery query = new SelectQuery(j2,
			Arrays.asList(AuthorDAO.NAME, BookDAO.TITLE, BookDAO.NUMBER_OF_PAGES));
	
	RowHandler<List<Object>> rowHandler = new ListRowHandler<Object>();
	
	AuthorDAO authorDAO = new AuthorDAOImpl(dataSource, dbAdapter, systemErrDaoMonitor);
	SelectObjectListResult<List<Object>> result = authorDAO.selectObjectList(query, rowHandler);
	
	for (List<Object> row : result.getObjectList()) {
		System.out.println("author         : " + row.get(0));
		System.out.println("title          : " + row.get(1));
		System.out.println("number_of_pages: " + row.get(2));
		System.out.println("--------------------------------------------------");
	}
}

Test your changes:

C:\onewebsql-demo-1.0.3>runquick.bat list-books-with-authors
query string: SELECT author.name, book.title, book.number_of_pages FROM book JOIN book_author ON
 book.book_id = book_author.book_id JOIN author ON book_author.author_id = author.author_id
author         : Robert C. Martin
title          : Clean Code - A handbook of Agile Software Craftsmanship
number_of_pages: 226
--------------------------------------------------
author         : Peter Seibel
title          : Coders at Work
number_of_pages: 126
--------------------------------------------------
author         : J. Bloch
title          : Effective Java
number_of_pages: 88
--------------------------------------------------
author         : Darrell Huff
title          : How To Lie with Statistics

[...]

Congratulations! You've created your first JOIN.

7.1. Type-safe RowHandler

In the previous section, the ListRowHandler returned rows data as list of lists of objects. Yes, it was quick to code, but such an approach is not very robust against changes in the database. You need to specify the list of columns in SelectQuery constructor (line 173 in list-books-with-authors command) , and then match their order during retrieval (lines 181-183). Also, if the column types change in the future, you'll get different objects returned. Let's fix it.

First, write BookWithAuthor Java Bean to hold data for each row returned by the join:

package com.onewebsql.demo;

public class BookWithAuthor {
	private String title;
	private String author;
	private Integer numberOfPages;
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public Integer getNumberOfPages() {
		return numberOfPages;
	}
	public void setNumberOfPages(Integer numberOfPages) {
		this.numberOfPages = numberOfPages;
	}
}

Now write a specialized RowHandler that for each row returned from the database creates the BookWithAuthor object and stores ResultSet data in it:

package com.onewebsql.demo;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

import com.onewebsql.demo.or.AuthorDAO;
import com.onewebsql.demo.or.BookDAO;
import com.onewebsql.query.Column;
import com.onewebsql.util.jdbc.RowHandler;

public class BookWithAuthorRowHandler implements RowHandler<BookWithAuthor> {
	public static final List<Column> COLUMNS =
			Collections.unmodifiableList(Arrays
					.asList(AuthorDAO.NAME, BookDAO.TITLE, BookDAO.NUMBER_OF_PAGES));

	@Override
	public BookWithAuthor getObject(ResultSet rs) throws SQLException {
		BookWithAuthor b = new BookWithAuthor();
		b.setAuthor(rs.getString(1));
		b.setTitle(rs.getString(2));
		b.setNumberOfPages(Integer.valueOf(rs.getInt(3)));
		return b;
	}
}

How to manage columns returned by the JOIN?
It's a good practice to store a list of columns as COLUMNS constant inside the RowHandler definition. The constant is passed to SelectQuery constructor. This way the code related to the number and order of columns in a query is modified in a single place.

Change the SelectQuery constructor, and pass the COLUMNS constant instead of the in-place list:

SelectQuery query = new SelectQuery(j2, BookWithAuthorRowHandler.COLUMNS);

Use BookWithAuthorRowHandler handler:

RowHandler<BookWithAuthor> rowHandler = new BookWithAuthorRowHandler();

Change SelectObjectListResult generic type:

SelectObjectListResult<BookWithAuthor> result = authorDAO.selectObjectList(query, rowHandler);

Replace display code:

for (BookWithAuthor bookWithAuthor : result.getObjectList()) {
	System.out.println("author         : " + bookWithAuthor.getAuthor());
	System.out.println("title          : " + bookWithAuthor.getTitle());
	System.out.println("number_of_pages: " + bookWithAuthor.getNumberOfPages());
	System.out.println("--------------------------------------------------");
}

The final code should look like this:

if (args.length > 0 && "list-books-with-authors".equals(args[0])) {
	Join j1 = Join.join(BookDAO.TABLE_EXPRESSION, BookAuthorDAO.TABLE_EXPRESSION,
			BookDAO.BOOK_ID.eq(BookAuthorDAO.BOOK_ID));
	Join j2 = Join.join(j1, AuthorDAO.TABLE_EXPRESSION,
			BookAuthorDAO.AUTHOR_ID.eq(AuthorDAO.AUTHOR_ID));
	
	SelectQuery query = new SelectQuery(j2, BookWithAuthorRowHandler.COLUMNS);
	
	RowHandler<BookWithAuthor> rowHandler = new BookWithAuthorRowHandler();
	
	AuthorDAO authorDAO = new AuthorDAOImpl(dataSource, dbAdapter, systemErrDaoMonitor);
	SelectObjectListResult<BookWithAuthor> result = authorDAO.selectObjectList(query, rowHandler);
	
	for (BookWithAuthor bookWithAuthor : result.getObjectList()) {
		System.out.println("author         : " + bookWithAuthor.getAuthor());
		System.out.println("title          : " + bookWithAuthor.getTitle());
		System.out.println("number_of_pages: " + bookWithAuthor.getNumberOfPages());
		System.out.println("--------------------------------------------------");
	}
}

As usual, test your changes:

C:\onewebsql-demo-1.0.3>runquick.bat list-books-with-authors
query string: SELECT author.name, book.title, book.number_of_pages FROM book JOIN book_author ON
 book.book_id = book_author.book_id JOIN author ON book_author.a
uthor_id = author.author_id
author         : Robert C. Martin
title          : Clean Code - A handbook of Agile Software Craftsmanship
number_of_pages: 226
--------------------------------------------------
author         : Peter Seibel
title          : Coders at Work
number_of_pages: 126
--------------------------------------------------
author         : J. Bloch
title          : Effective Java
number_of_pages: 88
--------------------------------------------------
author         : Darrell Huff

[...]

Congratulations! You've created your first RowHandler. Now your code is robust against database changes.

7.2. Complex JOINs and Database Views

As you write more and more complex JOINs you need to write more and more RowHandlers and row beans to make sure that your code is type-safe and robust against changes in the database. It may quickly become quite a burden. Don't worry, OneWebSQL is here to help!

Use database views for complex JOINs
If you ever find yourself joining more than two or three tables, or writing a lot of specialized RowHandlers, consider creating equivalent database view. OneWebSQL handles database views just as it handles plain tables. For each a DAO is generated that handles all common view operations, without you having to write a single line of supporting code.

Let's see how OneWebSQL handles database views. Our database contains book_with_author_view, that has following definition:

SELECT
    book.book_id AS book_id,
    group_concat(author.name separator ', ') AS authors,
    book.title,
    book.isbn,
    book.publication_year,
    book.number_of_pages,
    category.name AS category,
    edition.name AS edition
FROM
    book
    LEFT JOIN book_author ON book.book_id = book_author.book_id
    LEFT JOIN author ON author.author_id = book_author.author_id
    JOIN category ON category.category_id = book.category_id
    JOIN edition ON edition.edition_id = book.edition_id
GROUP BY
    book.book_id,
    book.title,
    book.isbn,
    book.publication_year,
    book.number_of_pages,
    category,
    edition
;

The view is quite complex. It joins five tables, uses GROUP BY clause and group_concat database function. It's possible to program exactly the same SELECT in OneWebSQL but the code will be much more complicated and you'll have to manually fix it for all future changes in the database. It's much simpler to just use the DAO that OneWebSQL generated for the book_with_author_view.

Replace the whole list-books-with-authors command with the following code:

if (args.length > 0 && "list-books-with-authors".equals(args[0])) {
	BookWithAuthorViewDAO bookWithAuthorViewDAO = new BookWithAuthorViewDAOImpl(dataSource, dbAdapter, systemErrDaoMonitor);
	for (BookWithAuthorView bookWithAuthor : bookWithAuthorViewDAO.getBookWithAuthorViewList()) {
		System.out.println("author         : " + bookWithAuthor.getAuthors());
		System.out.println("title          : " + bookWithAuthor.getTitle());
		System.out.println("number_of_pages: " + bookWithAuthor.getNumberOfPages());
		System.out.println("--------------------------------------------------");
	}
}

And test your changes:

C:\onewebsql-demo-1.0.3>runquick.bat list-books-with-authors
query string: SELECT book_with_author_view.book_id, book_with_author_view.authors,
 book_with_author_view.title, book_with_author_view.isbn, book_with_author_view.publication_year,
 book_with_author_view.number_of_pages, book_with_author_view.category,
 book_with_author_view.edition FROM book_with_author_view
author         : Robert C. Martin
title          : Clean Code - A handbook of Agile Software Craftsmanship
number_of_pages: 226
--------------------------------------------------
author         : Peter Seibel
title          : Coders at Work
number_of_pages: 126
--------------------------------------------------
author         : J. Bloch
title          : Effective Java
number_of_pages: 88
--------------------------------------------------
author         : Darrell Huff

[...]

Much, much simpler, isn't it?

8. Schema Change

In every project, there comes a time when new requirements emerge. Frequently that means that the database and your application require modifications. Now you'll see how OneWebSQL helps you find and fix the code when the database schema changes.

OneWebSQL assumes that the database is changed first. Usually the DBA will make the necessary database schema modifications and write the database migration scripts. OneWebSQL takes the new database model and generates the code that matches the new schema.

How was the database changed?
The number_of_pages column was removed from the book table.

"Migrate" the database:

  1. Copy recursively all files and directories from \dbsrc\version2 to \dbsrc\current directory. Overwrite all files.
  2. Restore database to initial state:
    C:\onewebsql-demo-1.0.3>cleandb.bat
    

Generate new OneWebSQL code:

C:\onewebsql-demo-1.0.3>gensrc.bat
Warning: you are using OneWeb SQL evaluation license. Your license is valid till Tue Dec 25 23:59:59
 CET 2012. Table limit: 20
postprocessing serialVersionUIDs...
compiling generated classes...
generated serialVersionUIDs...
updating serialVersionUIDs...
OneWebSQL code generation done.

OneWebSQL throws PD2JavaException. What happened?
Running gensrc.bat can throw PD2JavaException: Couldn't find Java compiler. Make sure that you're using JDK and not JRE. The stack trace looks something like this:

============================================================
 OneWebSQL WARNING: JAVA_HOME environment variable not set.
============================================================
Warning: you are using OneWeb SQL evaluation license. Your license is valid till
 Tue Dec 25 23:59:59 CET 2012. Table limit: 20
Exception in thread "main" com.onewebsql.pd2java.PD2JavaException: com.onewebsql
.pd2java.z.V: Couldn't find Java compiler. Make sure that you're using JDK and n
ot JRE.
        at com.onewebsql.pd2java.PD2Java.run(:327)
        at com.onewebsql.pd2java.PD2Java.main(:145)
Caused by: com.onewebsql.pd2java.z.V: Couldn't find Java compiler. Make sure tha
t you're using JDK and not JRE.
        at com.onewebsql.pd2java.z.W.bh(:34)
        at com.onewebsql.pd2java.z.W.bg(:22)
        at com.onewebsql.pd2java.z.L.a(:56)
        at com.onewebsql.pd2java.PD2Java.run(:325)
        ... 1 more

This means that gensrc.bat script couldn't find JDK. OneWebSQL requires JDK (not JRE!). Without the JDK source code, generation will fail. It's best to put the path to JDK in the setenv.bat script. Uncomment the line that looks like this:

set "JAVA_HOME=C:\Program Files\Java\jdk1.7.0_07"

Change the path to point to your local JDK installation. From now on, the code generation should work without errors.

Compile the demo against newly generated code:

C:\onewebsql-demo-1.0.3>compile.bat
C:\onewebsql-demo-1.0.3\src\com\onewebsql\demo\BookWithAuthorRowHandler.java:17: error: cannot find symbol
                                .asList(AuthorDAO.NAME, BookDAO.TITLE, BookDAO.NUMBER_OF_PAGES));
                                                                              ^
  symbol:   variable NUMBER_OF_PAGES
  location: interface BookDAO

[...]

  symbol:   method getNumberOfPages()
  location: variable book of type Book
C:\onewebsql-demo-1.0.3\src\com\onewebsql\demo\OneWebSQLQuickstart.java:114: error: cannot find symbol
                        System.out.println("number_of_pages : " + book.getNumberOfPages());
                                                                      ^
  symbol:   method getNumberOfPages()
  location: variable book of type Book
C:\onewebsql-demo-1.0.3\src\com\onewebsql\demo\OneWebSQLQuickstart.java:167: error: cannot find symbol
                        System.out.println("number_of_pages: " + bookWithAuthor.getNumberOfPages());
                                                                               ^
  symbol:   method getNumberOfPages()
  location: variable bookWithAuthor of type BookWithAuthorView
16 errors
Code compiled

Boom! There are compilation errors. And that's a good thing. Now it's simply a matter of jumping in you IDE to every problem and fixing the code. Use IDE code completion for better results. You will love how OneWebSQL™ helps you program the so-called "little" changes in the system.

But what do I do if my IDE compiles everything from me?
After running gensrc.bat just refresh the sources in your IDE and rebuild the project.

It's just tutorial, do I really have to fix these errors?
No, of course you don't have to :). If you want to restore previous database schema, just copy recursively everything from \dbsrc\version1 to \dbsrc\current, than run cleandb.bat and compile.bat.

Congratulations! Now you're ready to tackle writing real-world applications using OneWebSQL.

9. OneWebSQL Overview

OneWebSQL consists of two core elements:

  1. Code Generator
  2. Runtime Library

Code Generator takes database model and generates Java code ready to use in your application. Generated code depends on Runtime Library. You have to put OneWebSQL Runtime Library in your application classpath to use generated DAOs, but you can also use it for your low level JDBC needs.

OneWebSQL generates the Java code from the database model that was created using one of the supported modeling tools:

If you don't have any of these tools, but you have an access to the existing database you can use OneWebSQL JDBC Reverse Engineering to generate the code via JDBC.

This tutorial uses PowerDesigner model files from the OneWebSQL Demo to generate the code, but you don't have to download and install PowerDesigner to finish this tutorial. OneWebSQL Demo contains everything you need.

If you want to change the database model on your own, however, you can download the PowerDesigner evaluation version from the vendor website, and tinker with it yourself. PowerDesigner is available only for Windows.

Initial database model is stored in the \dbsrc\version1\bookshop.pdm file. Updated model (with number_of_pages column removed) is stored in the \dbsrc\version1\bookshop.pdm file.

Install HSQLDB2 database definition for PowerDesigner
PowerDesigner does not support HSQLDB2 out of the box. If you open the *.pdm models bundled with this tutorial PowerDesigner will complain that it can't load "HyperSQL 2" DBMS definition. Change the DBMS definition to the \dbsrc\hsqldb2.xdb file while opening the model. See PowerDesigner documentation for more information on how DBMS definitions are managed.

For more information see Application Development Guide

10. What next?

Congratulations! You've completed the OneWebSQL Tutorial. If you want to play with more features on your own, dive into OneWebSQLDemo.java. There you'll find there more examples of advanced OneWebSQL features.

If you want learn more go to OneWebSQL documentation.

Questions/Feedback
Having trouble? We'd like to help! Just send email to contact@OneWebSQL.com.