OneWebSQL tips: seeing is believing

This is the first post in a new category called "OneWebSQL tips". In this category, we are going to show you some nice features of our product. Just some tricks and tips that we hope that you enjoy.

Every database accessing layer is doing the same thing. It converts some sequence of method calls to SQL query. If you are familiar with SQL, then you know that there is need to view the queries. You may enable dumbing quieries in the database. However, sometimes you don't have an access to the database, or you share the same engine with other team members. In this case, it is better to enable dumping queries in database access layers. Every layer has its own method. I am going to show the OneWebSQL way.

Let's do some Java. As you probably know, for every table in the database there are two kinds of classes are generated by OneWebSQL. The first one is a simple POJO that represents contents of one row of the table. The second one is the DAO class, in which there are methods to manipulate the table (SELECT, INSERT, etc.). In most cases, the DAO object is created in this way:

BookDAO bookDAO = new BookDAOImpl(dataSource, dbAdapter);

These parameters are obligatory. You can read more about in our DAO Guide. You can pass an extra parameter to the constructor. It is an implementation of the DaoMonitor interface. For every database operation, there is a separate method in the DAO monitor interface. The method is called while the SQL query is executed.

Rudimentary logging

Here is the simplest example. In this case, the implementation dumps queries to the standard error stream:

BookDAO bookDAO = new BookDAOImpl(dataSource, dbAdapter, new PrintStreamDaoMonitor(System.err));

The output looks like this:

query string: SELECT book.book_id, book.category_id, book.edition_id, book.isbn, book.number_of_pages, book.publication_year, book.title FROM book WHERE book.publication_year >= ?
parameter #1: "2000" (BIGINT)

Sophisticated logging

There are other implementations that depend on more sophisticated logging frameworks:

Log4j

Here is example for Log4j 1.2.x. Start with the Java code:

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

Here is a minimal Log4j configuration. Save it as log4j.properties file:

log4j.rootLogger=OFF
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%-5p [%t] %c{1} %x - %m%n

log4j.logger.com.onewebsql.demo.or=DEBUG,console

Add the JVM parameter -Dlog4j.configuration=file:log4j.properties while running the example code. The output looks like that:

DEBUG [main] BookDAOImpl  - query string: SELECT book.book_id, book.category_id, book.edition_id, book.isbn, book.number_of_pages, book.publication_year, book.title FROM book WHERE book.publication_year >= ?
DEBUG [main] BookDAOImpl  - parameter #1: "2000" (BIGINT)

You need to add log4j.jar to the classpath.

JDK logging

JDK has a built-in logging framework, java.util.logging. It's based on Log4j, so the configuration is very similar. Here is the Java code:

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

Save the configuration as logging.properties file:

.level=SEVERE

handlers=java.util.logging.ConsoleHandler

java.util.logging.ConsoleHandler.level=ALL
java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter

com.onewebsql.demo.or.level=FINE

Add the JVM parameter
-Djava.util.logging.config.file=file:loggin.properties while running the example code. The output looks like this:

2012-09-20 11:21:57 com.onewebsql.dao.monitors.JULDaoMonitor a
FINE: query string: SELECT book.book_id, book.category_id, book.edition_id, book.isbn, book.number_of_pages, book.publication_year, book.title FROM book WHERE book.publication_year >= ?
2012-09-20 11:21:57 com.onewebsql.dao.monitors.JULDaoMonitor a
FINE: parameter #1: "2000" (BIGINT)

SL4J and Logback

Now it's time to show you the modern framework SLF4J. It is a facade for various logging. In this example, we'll be using Logback backend. Here is the Java code:

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

This is the configration of Logback; save it as a logback.xml file:

<configuration>

  <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
    <encoder>
      <pattern>%d{HH:mm:ss.SSS} %-5level %logger{36} - %msg%n</pattern>
    </encoder>
  </appender>

  <root level="debug">
    <appender-ref ref="STDOUT" />
  </root>
</configuration>

Add -Dlogback.configurationFile=logback.xml parameter to the JVM. The output is:

11:47:36.587 [main] DEBUG com.onewebsql.demo.or.BookDAOImpl - query string: SELECT book.book_id, book.category_id, book.edition_id, book.isbn, book.number_of_pages, book.publication_year, book.title FROM book WHERE book.publication_year >= ?
11:47:36.594 [main] DEBUG com.onewebsql.demo.or.BookDAOImpl - parameter #1: "2000" (BIGINT)

Add following libraries to the classpath: slf4j-api-1.61.jar, logback-simple-0.9.29.jar, logback-classic-0.9.29.jar.

Apache Commons Logging

Apache Commons Logging is not supported. It discovers the underlying logging framework at runtime. That behavior is considered harmful.

Custom logging

You may implement the interface by yourself, in place, like this:

BookDAO bookDAO = new BookDAOImpl(dataSource, dbAdapter, new DaoMonitor() {
 // Eclipse will generate nessesery methods :)
});

It is fast if you're debugging only a SELECT in one place.

Hope this helps you to get familiar with OneWebSQL.