Voruta

Voruta is a data access framework for embedding SQL statements in Java methods using custom javadoc tags and dynamic code generation at runtime.

QDox is used to parse metadata and CGLib to generate implementation at runtime.

Data Access Classes and Metadata

Data Access Classes are JAVA interfaces, interface method declaration and custom java doc tags are used to generate Data Access Object. Generated Service is stateless and thread safe.

   public interface UserManager {
    /**
     *@update  INSERT INTO _USER_ (username,password)VALUES($1,$2)
     */
    public void addUser(String userName,String password);
    /**
     *@query  SELECT COUNT(1) > 0 
     *        FROM _USER_ WHERE userName=$1 AND password=$2
     */
    public boolean authenticate(String userName,String password);
  }
  
The main class in voruta API is Db class. Static Db methods are used to generate service implementation, commit and close conections.
 
  public static UserManager getUserManager(){
  
     UserManager manager = (UserManager)Db.
                        getProcedures(UserManager.class);
                        
  }  
  
  public static void main(String [] args){
  
   try{
   
     getUserManager().addUser(args[0], args[1]);
     Db.commit();
     
    }finally{
    
       Db.close();
       
    }
  
  }
                        
                        
 
JAVA source file is DAO declaration and metadata file at the same time, it must be in classpath at runtime, Voruta will find it and generate implementation on the fly. if voruta.reload property is set then metadata is not cached, it can be usefull at development time. Metadata parser validates SQL and logs query plan in "autotrace" mode too. Any static or parameteresed query can be validated on application startup without executing it on database. It is RBMS specific feature, default configuration uses PostgreSQL style "EXPLAIN" command.

Method parameters are mapped to JDBC prepared statement parameters and are indexed from one. Data access method can read ThreadLocal constants, Beans can be used as parameters too.

   public interface UserManager {
    public static final ThreadLocal CALLER = new ThreadLocal();
    /**
     *@update  INSERT INTO _USER_ ( username, password,    created_by )
     *                      VALUES( $1.id,    $1.password, $CALLER    )
     */
    public void addUser(User bean);
    
  }
  

Dynamic SQL

We have used static SQL and JDBC parameters in examples, but parameters, final fields or voruta properties can be used to generate dynamic SQL too.

    /**
     *@query  SELECT COUNT(1) FROM ${1} 
     */
     public int count(String tableName);
  
escape ${1} will be replaced with the first method parameter before to execute query. JAVA constants and Db properties (properties have priority) can be used for dynamic SQL too:
    static public String FIELD_LIST = "id,name,value";
    /**
     *@query  SELECT ${FIELD_LIST} FROM MY_TABLE WHERE ID=$1
     */
     public Object[] row(int id);
     
     /**
     *@query  SELECT ${FIELD_LIST} FROM MY_TABLE;
     */
     public Collection rows();
     
  
we use ${FIELD_LIST} in query to reduce copy/paste operations. Unmapped parameters are used by custom handler implementations.

Stored Procedures

PostgreSQL style procedures can be used with Voruta, INOUT and OUT parameters are not supported at this time. Voruta uses PreparedStatement and qexecuteQuery for both SQL and Stored Procedures. @execute tag is used to create/replace procedures before to call methods. This is simple plpgsql example:

    
   /**
    * This file is used for metadata and must be in classpath at runtime,
    *  set "voruta.execute" property to "on" at development time to 
    *  execute SQL statements on startup.
    *  Static "Db.init(Properties props)" method is used to set properties
    *
    * @execute CREATE OR REPLACE FUNCTION concat_text (TEXT, TEXT) RETURNS TEXT AS '
    *    BEGIN
    *        RETURN $1 || $2;
    *    END;
    * ' LANGUAGE 'plpgsql';
    * 
    */
 
   public interface Demo {
     /**
      *@query SELECT concat_text($1,$2)
      */
      String concatText(String s1, String s2);
  }
  
@execute tag values are not parsed by Voruta.

Cache

Cache is optional feature, voruta will try to find result in memory first before to execute query if @useCache tag is present and will not execute query if method parameter values match the same query executed before, it will return cached value. Cache is implemented using soft references and cached results can be cleared by JVM if references become unreachable. @flushCache tag is used for updates, to clear results and parameter keys from memory, Db.clearCache() doe's the same programmicaly. Chahe implementation is transactional, isolation level is uset the same as in current connection, all JDBC isolation levels are supported.

Default is no cache and no flush

Result Set Handlers

There are a few predefined ResultSetHandler implementations, they are used for data processing. @handler tag is used to register handler for method. For scalar queries like

  SELECT COUNT(1) > 0 FROM _USER_ WHERE userName=$1 AND password=$2 
 
default ScalarHanler implementation is used. The most of handler implementations are very trivial and reusable:
  public Object handle(DbResultSet rs, Class returnType, Object[] params) 
                 throws Exception{
                 
                if (rs.next()) {
                    Object result = rs.get(0);
                    if(rs.next()){
                      throw new DbException("too many results");
                    }
                    return result;
                }
               throw new DbException("empty result set");
            }
  }
 
Object[] params is array of method parameters. See samples and tests for more use cases. You can exdend framework and use custon handlers, but there are predefined handlers for the most of use cases, voruta handles Maps, Collections, Arrays, Beans.

Beans

Predefined @handler tag value "bean" used to declare return value as Bean.

    /**
     *@query SELECT  FLD_ID ad id, FLD_NAME as name 
     *       FROM    TBL_TEST 
     *       WHERE   FLD_ID=$1 
     *@handler bean
     */
     public DemoBean getRowAsBean( int id );
    
SQL alias is used to map DB field names Bean property names.

Map

@handler map No tag value needs to be specified, it will be detected.

    /**
     *@query SELECT  FLD_ID ad id, FLD_NAME as name 
     *       FROM    TBL_TEST 
     *       WHERE   FLD_ID=$1 
     *
     */
     public Map getRowAsMap( int id );
    

Array of Objects

@handler vector
    /**
     *@query SELECT  FLD_ID ad id, FLD_NAME as name 
     *       FROM    TBL_TEST 
     *       WHERE   FLD_ID=$1 
     *
     */
     public Object[] getRowAsArray( int id );
    
@handler beans ( <Class Name> )

Collection of beans

Voruta returns collections of beans and arrays too. @handler beans(DemoBean) is used to declare collection type.

    /**
     *@query SELECT  FLD_ID ad id, FLD_NAME as name 
     *       FROM    TBL_TEST 
     *
     *@handler beans(DemoBean)      
     */
     public Collection getBeans();
    

Collection of arrays

@handler collection Array type is "detected" if no handler type is specified.

    /**
     *@query SELECT  FLD_ID as id, FLD_NAME as name 
     *       FROM    TBL_TEST 
     *
     */
     public Collection getArrays();
    

Column

@handler column list of objects in the firs colmn.

    /**
     *@query SELECT  FLD_ID as id
     *       FROM    TBL_TEST 
     *
     */
     public List getColumn();
    

All predefined handlers are optional, use @handler MyClassName to define custom handler for any procedure, custom handlers have priority. DbUtils class can be used as helper for custom handler implementation. Objects returned by handlers must be thread safe, if cache is used.

Properties

Voruta properties can be used for global settings. It can be usefull for use cases like this:

   /**  
    * @pgsql-execute CREATE OR REPLACE FUNCTION concat_text (TEXT, TEXT) RETURNS TEXT AS '
    *    BEGIN
    *        RETURN $1 || $2;
    *    END;
    * ' LANGUAGE 'plpgsql';
    *
    * @somedb-execute CREATE OR REPLACE FUNCTION concat_text(s1 IN varchar, s2 IN varchar)
    *     RETURN varchar IS
    *     BEGIN
    *        RETURN s1 || s2;
    *     END;
    *
    */
  
Name Description Default
voruta.execute uses "execute" tag if set to "on" Not set
voruta.reload reloads medata (source file) if set to "on" Not set
voruta.debug Enables default log implementation ( prints trace to STDOUT) Not set
voruta.autotrace Enables query validation Not set
voruta.tag.update Tag name, for JDBC updates update
voruta.tag.query Tag name, for JDBC queries query
voruta.tag.useCache Tag name, used to enable cache useCache
voruta.tag.flushCache Tag name,used to flush cache flushCache
voruta.tag.handler Tag name, defines handler for procedure ("predefined" or class name) handler
voruta.tag.execute Tag name, executes value as SQL on startup execute

Tags

Custom javadoc tags are used as metadata.

Name Scope Description Value
@execute Class executes value as SQL on startup if enabled SQL string
@reload Class reloads medata (source file) if set to "on" "on" or undefined
@update Method declares SQL update statement SQL update,delete or insert statement
@query Method declares SQL query SELECT statement
@useCache Method used to enable cache for method cache region
@flushCache Method clears cache after method execution List of cache regions
@handler Method defines handler for procedure ("predefined" or class name) ResultSetHandler implementation class name
@language Method query language name registred language, sql if undefined

Custom Interceptors

Custom interceptors decorate interceptor implemented by Voruta to transform return value, parameters or to throw exceptions. One of the most trivial use cases is synchronization:

  
  public class SynchronizedInterceptor implements ProcedureInterceptor {
    Object invoke(Method method, Object args[], Invokation invocation,Properties tags )throws Throwable{
     synchronized(this){
       return invocation.invoke(method, args);
       }
    }
   }
 
It doe's nothing, all methods are thread safe.

Abstract Classes

Abstract class can be used as DAO too. It must declare "default" constructor, must not be final and declare abstract methods. It can implement one or more data access interfaces. Abstract class source code is ignored and is not used to parse javadoc comments.

It is used to implement helper methods, class methods are not intercepted.

  public abstract class AbstractUserManager implements UserManager{
    
    public AbstractDemo() {
    }
    
    public void check( String user,String role )throws UnAuthorizedException{
    
        if(!isUserInRole(user,role)){ // calls DA method
           throw new UnAuthorizedException();
        }
    }
    
    public void addUsers(Collection users){
    
      for( Iterator i = users.iterator(); i.hasNext(); ){
        User user = (User)i.next();
        addUser(user); //method implemented by voruta
      }
    
    }
    
}
 

Logging

Voruta uses internal Log to print debug and log information, use DbUtils.setLog(Log) to set custom implementations, null value dissables logging. Default implementation doe's nothing.

      
   Voruta debug output:
   
     [java] main  INFO public abstract boolean net.sf.voruta.AbstractDemo.exists(int)
     [java] EXPLAIN SELECT COUNT(1) > 0 FROM TBL WHERE ? = ID
     [java] QUERY PLAN	
     [java] Aggregate  (cost=22.50..22.51 rows=1 width=0)	
     [java]   ->  Result  (cost=0.00..20.00 rows=1000 width=0)	
     [java]         One-Time Filter: NULL::boolean	
     [java]         ->  Seq Scan on tbl  (cost=0.00..20.00 rows=1000 width=0)	

 
    

See API documentation for more information.

Exception handling

Voruta rethrows all runtime or declared exceptions, undeclared exeptions are wrapped using DbException, it exdends RuntimeException and getCause() method is used to return cause. DbException overrides printStackTrace() methods and generates readable stack traces.

Thread Local Connection

Voruta uses ThreadLocal to manage connections, transactions and cache. Interface implementations generated by voruta are thread safe. Db.close() must be used to close current connection.

Application must call Voruta.init( ConnectionFactory, Properties ) on startup to init singletons, see samples for ConnectionFactory implementations.

Class and resource loading

Handler implementations are loaded by Data Access Class loader and are defined in the same runtime package. Voruta uses the same class loader to find resources (source file) too.

Samples

UserManager sample is in source distribution, it is trivial CLI application. See "readme" file for more information.

Integration sample with J2EE is in samples.ejb package.

samples.web.ConnectionManager is sample Filter implementation, it can be used to manage connections and transactions in "plain" web applications.

Integration

Voruta doe's not provide connection pool, use your favorite implementation. DBCP is popular and well tested jakarta commons component.

Voruta doe's not provide custom development tools and needs no integration with ide. Standard javadoc features help to use IDE features like Code Completion.

Develpment with Voruta and Netbeans IDE:

Monitoring UI:

$Id: index.xml,v 1.33 2004/03/07 14:33:57 baliuka Exp $