1. Introduction

This document presents the rules of using generated code, which maps a database structure to Java classes. Based on the defined physical database schema, there are three classes generated for each database table:

  1. A class which maps a single record from a table. It is a simple Java Bean containing properties corresponding to the fields of the table together with getter and setter methods for them;

  2. A class implementing the methods defined in the DAO interface; and,

  3. A class containing constants corresponding to the table contents (only for dictionary tables).

In addition, a Data Access Object (DAO) interface is provided, which defines a set of operations that are used to create, read, update, and delete objects in/from a database.

The DAO Guide provides sample code that you can try out using the OneWebSQL-Demo application. The entire OneWebSQL-Demo application includes an application server, a database, a simple web interface, and sample code.

To use the sample code, first open the OneWebSQL-Demo project in Eclipse, and then open the class ExerciseServlet.

2. Creating a DAO object

To create a DAO object in your application, you use the following objects:

You can obtain the data source object using JNDI if you use an application server.

A database adapter is a class providing the handling of the features unique to the given database engine.

To create a DAO object, you can use the following syntax:

DataSource dataSource = ... ;
DBAdapter dbAdapter = new HSQLDB2Adapter();
BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);

2.1. List of supported databases and the corresponding database adapters

OneWebSQL officially supports these databases:

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

2.2. Support of database schemas

During the creation of a DAO object, you can supply a name of a database schema. All method calls using this object will apply to the tables in the given schema. For example:

DataSource dataSource = ... ;
DaoSchema schema = new DefaultDaoSchema("schema");
DBAdapter dbAdapter = new HSQLDB2Adapter();
BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter, schema);

Schemas are useful in a situation where you want to have just one database (e.g., because of licencing issues), but you need to share it. A good example of such a situation would be a popular practice of sharing one database among team members. In a business environment, one schema can be used for each application client.

If you don't provide the schema, the DAO object will use the schema specified in the DataSource.

3. Operations on data

This section describes the operations on data: creating, retrieving, updating, and deleting. For any of these operations, the DAO is required that corresponds to the table. For example, to delete from the table book, you need to have the BookDAO instance.

The code samples use a table, book, which has the following fields:

3.1. Adding records

3.1.1. A single record

The samples begin with adding a single record.

Create a POJO object.

Book book = new Book();

Fill it with data.

// here it is assumed that an author with the given id already exists in the database
book.setAuthorId(1); 

// title
book.setTitle("Foo bar");

// a unique symbol
book.setIsbn("123-" + System.currentTimeMillis());

// details in the next section
book.setEditionId(EditionDICT.PAPERBACK.getId());

You should leave the id field empty, as it is going to be set with a value from a sequence when the object is saved in the database.

Create a DAO object, using the data source and DBAdapter, as in Section 1.0, Introduction.

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);

Add a record to the database.

bookDao.insert(book);

Now calling book.getId() should give you the id of the newly added record.

writer.println("id: " + book.getId());

Copy the code above into the method action of the practice servlet and call it. A new entry should appear in the database.

3.1.2. Adding multiple rows at the same time

The insert method makes it possible to insert multiple rows of data at the same time. For example, assume that in one part of the application someone creates a list of books, as follows:

List<Book> books = new ArrayList<Book>();
for(int i = 1; i < 11; i++) {
   Book book = new Book();
   book.setAuthorId(1); 
   book.setTitle("Title " + i);
   book.setIsbn("123-"  + i + " " + System.currentTimeMillis());
   book.setEditionId(EditionDICT.PAPERBACK.getId());
   books.add(book);
}

If, in another part of the application, you want to add all these books to the database, there is no need to iterate over the list, adding books one at the time. You can do the following instead:

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);
bookDao.insert(books);

3.2. Retrieving records

You have inserted a couple of records, now it is time to retrieve them from the database.

3.2.1. An entire table

Database rows can be retrieved in many ways. To start with, read and display contents of an entire table:

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);
List<Book> all = bookDao.getBookList();

writer.println(all);

The order in which the records appear is determined by the database. In other words, you cannot rely on them being sorted in any particular way. If you want, you can force a particular order by sorting the records yourself:

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);
List<Book> sorted = bookDao.getBookList(BookDAO.ID, false);

writer.println(sorted);

The list has been sorted in a descending order by id (parameter false indicates a descending sort).

3.2.2. A single record

3.2.2.1. Using a primary key

You can retrieve a single record if you know its id:

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);

Integer currentBookId = 1;
Book book = bookDao.getByPK(currentBookId);

writer.println(book);

3.2.2.2. Using an alternative key

The book table has another unique identifier, ISBN, so you can use it to retrieve a record:

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);
String isbn = "Sample-ISBN-" + System.currentTimeMillis();

Book book = new Book();
book.setAuthorId(1);
book.setTitle("Title");
book.setIsbn(isbn);
book.setEditionId(EditionDICT.PAPERBACK.getId());
bookDao.insert(book);

Book bookFromDb = bookDao.getByIsbn(isbn);
writer.println(bookFromDb);

3.2.2.3. Using a foreign key

Generated DAO classes contain information about the relationships between tables. This means that you can retrieve a record using a foreign key.

The code below shows how to retrieve an author having a book. The foreign key, author_id, references the author object.

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);
		
Integer currentBookId = 1;
Book book = bookDao.getByPK(currentBookId);
		
AuthorDAO authorDao = new AuthorDAOImpl(dataSource, dbAdapter);
Author author = authorDao.getAuthor(book);
		
writer.println("author: " + author);

3.2.3. Query language

DAO methods are not the only way of retrieving records; a query language can be used for more complicated queries. The query language allows you to create queries, just as SQL does. The query language is a set of Java classes that represent SQL and SQL queries in Java. You can find a description of the query language in the Query Language Guide.

In the following example, you will retrieve a subset of records with an id less than 10:

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);

List<Book> books = bookDao.getBookList(BookDAO.ID.lt(10));
	
writer.println(books);

In this example only the WHERE part of an SQL query is being modified. Changing column names, joining tables, accessing aggregate functions are described in the Query Language Guide.

3.3. Updating records

You have just practiced inserting and retrieving records; now you will update records in a database using DAO. In the following example, you will modify a single record in a table:

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);

Integer currentBookId = 1;
Book book = bookDao.getByPK(currentBookId);
book.setTitle("Updated title " + System.currentTimeMillis());
bookDao.update(book);

3.3.1. A general mechanism

If you would like to modify multiple (thousands, millions) records in a one query, it is much better to use the query language, rather than using DAO's update method several times to update several different rows.

3.4. Deleting records

With the passing of time, some records become obsolete.

3.4.1. A single record

Here is an example of removing a record using its corresponding DAO object:

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);

Integer currentBookId = 1;
Book book = bookDao.getByPK(currentBookId);
		
bookDao.delete(book);

Or, you can use the primary key, which is even easier:

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);
Integer currentBookId = 2;
bookDao.delete(currentBookId);

3.4.2. Multiple records in one go

Just as you have seen in the example for retrieving database rows, it is possible to remove several records in one go. In the following example, you will delete all books with an identifier smaller than 20:

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);
bookDao.delete(BookDAO.ID.lt(20));

Or, as in this example, you can delete all books which have the word 'sample' in their description:

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);
bookDao.delete(BookDAO.ISBN.ilike("sample"));

You can also remove multiple chosen records if you have the corresponding POJO objects:

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);

List<Book> books = bookDao.getBookList(BookDAO.ID.lt(10));

bookDao.delete(books);

3.4.2.1. A general mechanism

Just as it was in the case of an update, you can use the query language to remove records. You can find a description of the query language in the Query Language Guide.

4. Aggregate functions

To retrieve a book record using a primary key, you would like to check the range of primary keys:

BookDAO bookDao = new BookDAOImpl(dataSource,dbAdapter);
Integer min = bookDao.aggregateFunction("MIN", BookDAO.ID);
Integer max = bookDao.aggregateFunction("MAX", BookDAO.ID);

writer.println("min: " + min + " max: " + max);

Make sure you notice that the name of the function is supplied as a string. By using the string name of the function, you are allowed to use either built-in or custom database functions.

5. Support for LOB fields

Fields of type BLOB or CLOB are nowhere to be found in POJO objects. They can be accessed using a DAO object. The book table that has been used in the previous examples contains two such fields:

5.1. A field of the type CLOB

CLOB fields are accessed as text fields, using objects of the types java.io.Reader and java.io.Writer or java.lang.String. It is recommended to use the stream types Reader/Writer as they do not require as much memory as the type String.

5.1.1. Inserting

Here is an example showing you how to add a book and its description to the database. First, you will add a record to the database, as follows:

Book book = new Book();
book.setAuthorId(1); 
book.setTitle("Title ");
book.setIsbn("1234567" + System.currentTimeMillis());
book.setEditionId(EditionDICT.PAPERBACK.getId());
		
BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);

bookDao.insert(book);

Next, you will populate the description field, as follows:

Reader content = new StringReader("very looooong description ....");
bookDao.setDescription(book, content);

To retrieve a book record using a primary key, you would use the following code:

bookDao.getDescription(book.getId(), writer);

5.1.2. Editing

Here is an example of how you can update the contents of a CLOB field if you have at your disposal its new value as an object of the type java.io.Reader:

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);
Integer currentBookId = 1;
Book book = bookDao.getByPK(currentBookId);
	
java.io.Reader content = new java.io.StringReader("new description " + System.currentTimeMillis());		
bookDao.setDescription(book, content);

And how you can retrieve it afterwards:

bookDao.getDescription(book,writer);

5.2. Field of the type BLOB

You can access fields of the type BLOB using objects of the types java.io.InputStream and java.io.OutputStream or byte[]. Just as it was in the case of fields of the type CLOB, using the stream types InputStream and OutputStream uses less operating memory.

5.2.1. Inserting

BLOB fields are treated in a similar way. An example of inserting and then retrieving of a field containing a book's cover:

Book book = new Book();
book.setAuthorId(1); 
book.setTitle("Title");
book.setIsbn("1234567" + System.currentTimeMillis());
book.setEditionId(EditionDICT.PAPERBACK.getId());
			
BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);
bookDao.insert(book);
		

FileInputStream fis = new FileInputStream("/path/to/image.jpg");
try {
   bookDao.setCoverImage(book, fis);
} finally {
   fis.close();
}		

Now reading:

FileOutputStream fos = new FileOutputStream("/path/to/new-image.jpg");
try {
   bookDao.getCoverImage(book.getId(), fos); 
} finally {
   fos.close();
}	

5.2.2. Editing

You can modify the contents of a field of the type BLOB as follows:

BookDAO bookDao = new BookDAOImpl(dataSource, dbAdapter);
Integer currentBookId = 1;

java.io.InputStream content = new ByteArrayInputStream(new byte[] {1,2,3,4}); 
bookDao.setCoverImage(book, content);

You can read its value:

ByteArrayOutputStream out = new ByteArrayOutputStream();
bookDao.getCoverImage(book, out);
		
byte[] bytes = out.toByteArray();
for(int i = 0; i < bytes.length; i++) {
	writer.println(" " + bytes[i]);
}	

6. Support for dictionary tables

Contents of the dictionary tables usually remain unchanged while a system is working. Accessing records in such tables is made easier by the class *DICT, which contains rows from the dictionary as constants. The contents of the dictionary are retrieved from a *.csv file that is supplied by an architect together with the data model. The generated class looks more or less like this:

...
    /**
     * Constant representing one of rows from a dictionary table.
     */
    public static final Edition PAPERBACK = new Edition(new Integer(1), "paperback", "Paperback");
    /**
     * Constant representing one of rows from a dictionary table.
     */
    public static final Edition HARDCOVER = new Edition(new Integer(2), "hardcover", "Hardcover");
...

For example, if you are looking for an id of a record representing a paperback edition, then you would use the following code:

EditionDAO dao = new EditionDAOImpl(dataSource, dbAdapter);
Integer paperbackId = dao.getByCode("paperback").getId();

You can simply write:

Integer paperbackId = EditionDICT.PAPERBACK.getId();

Using dictionary classes offers some important advantages. First of all, it frees you from the work of maintaining your own class with constants containing the codes of records from a dictionary table.

In addition, you can easily spot code changes that might need to be made as a result of changes in the dictionary table. If, for example, an architect removes a row from the dictionary, your code stops compiling, and the resulting compilation errors will help you to spotlight the location of the code that should be changed.

7. Summary

You've seen an overview of DAO methods. DAOs provide access to database tables. They give methods to perform basic operations on data: create, retrieve, update, and delete (CRUD). DAO methods give access to LOB columns in tables. In addition, they allow you to invoke aggregate functions; together with dictionary interfaces, they provide access to dictionary rows.