My Top Five OneWebSQL Patterns

When I start using a new language or library, I'm a beginner, again. Most of the time it is an adventure, but sometimes I feel lost. I've got documentation, but I don't know what should I read first. I'm looking for a cookbook. Some vendors provide a cookbook; some do not, in which case hopefully we've got the Interent -- especially Google and StackOverFlow.com.

Besides working on OneWebSQL I'm involved in another project. I really regret I can't write more about it. In that project I'm using OneWebSQL. I'm a a typical web application developer, and, yes, I'm eating my own dog food here.

Here are my most frequent/typical/known/favorite/liked usage patterns of OneWebSQL.

#1 Getting a single row from the table

Yes. Getting a specified row from a table and pouring data to something else is the most popular usage in my code. Here is an example:

Book b = this.bookDao.getById(selectedBookId);
titleField.setValue(b.getTitle());

 

#2 Getting a list of rows

The second most popular query is getting a subset of table rows. In this example, I'm using a dictionary table. Dictionary tables are treated in a special way, as they becomes an interface that contains all of the data. There is no need to bother the database to get an id "hardcover" row.

LExp where = BookDAO.EDITION_ID.eq(EditionDICT.HARDCOVER.getEditionId());
List<Book> hardcovers = bookDAO.getBookList(where);
for(Book b : hardcovers) {
    titles.add(b.getTitle());
}

 

#3 Creating new rows

The third query is about adding new rows. After processing a submitted form, pour data to a POJO object.
Book b = new Book();

b.setTitle(formData.get("title"));
..

bookDao.insert(b);

#4 Updating the specified row

Some data may need to be altered. This is the fourth most popular query. It's very similar to the previous one, and works just like "edit" and "add" screens in the application.
Book b = this.bookDao.getById(selectedBookId);

b.setTitle(newTitle);
this.bookDao.update(b);

#5 Getting data from two tables

The last one is the most advanced. It requires some thinking. It's about joining tables. In this example, I'm getting a list of books in which the category name matches the user query.

Join join = Join.join(BookDAO.TABLE_EXPRESSION, CategoryDAO.TABLE_EXPRESSION,
                       BookDAO.CATEGORY_ID.eq(CategoryDAO.CATEGORY_ID));
SelectQuery select = new SelectQuery(join);
select.setWhere(CategoryDAO.CODE.like("%" + searchQuery + "%"));
List<Book> books = bookDAO.getBookList(select);

 

That's all. Knowing these five patterns allows you implement over 80% or even 90% of the typical web application code. As you can see the web application mostly reads from the database. Sometimes there are some inserts and updates of rows. Removing rows is not popular. "The Internet never forgets" and you should know about that :)

Using OneWebSQL is quite easy for me. What about for you?