Another Java-Database Mapping Utility

Another Java-Database Mapping Utility



In one of my previous blog I discussed about one ResultSet Mapping utility. After using that utility in couple of my projects, I came across some of the shotfalls of this utility. Like:

• There is no support for primitive data types. If you want to retrieve only one column from database also you need to create an Object.
  
• Need to use sql classes like Connection, Statement and Resultset objects explicitly.
  

After looking into Groovy, I was amazed by its dynamic nature and Meta Programming model. Meta Programming model can be used as an alternative to remove lot of boiler plate java reflcetion code. So I decided to go with Groovy for my new Utility. There is another reason to choose groovy, once groovy classes are compiled it is compiled to java byte code. So you can use it in your java applications also.

@Log4j
 class DBUtil<T> {
  
  def static final primitives = [Integer.class, String.class, Long.class, Double.class, Float.class]
  
  /**
   * Will execute given query and convert result to a collection of specified class.
   * 
   * @param dataSource - JNDI Name of the Datasource 
   * @param query - Select query
   * @param params - List of parameters in query
   * @param clazz - Return type
   * @return List of objects of clazz argument type
   */
  static def executeQuery(String dataSource, String query, List params, Class clazz) {
   log.debug "Executing query ${query} with parameters ${params} using datasource ${dataSource}"
   
   def objList = [], obj, colName = "", field, sql
   try {
    sql = Sql.newInstance(lookup(dataSource))
    if(primitives.find {it == clazz}) {
     sql.eachRow(query, params) {row ->
      colName = row.getMetaData().getColumnName(1)
      objList << row."$colName"
     }
    } else {
     def fieldMap = mapFieldNames(clazz)
     sql.eachRow(query, params) {row -> 
      obj = clazz.metaClass.invokeConstructor()
      (0..row.getMetaData().columnCount-1).each {
       colName = row.getMetaData().getColumnName(it+1)
       field = fieldMap.find {it.key.compareToIgnoreCase(colName) == 0}
       if(field) obj."$field.value" = row."$colName"
      }
      objList << obj
     }
    }
   } catch(Exception e) {
    log.error e.message, e
    throw new DAOException(e)
   } finally {
    sql?.close()
   }
   objList
  } 
  
  private static Map mapFieldNames(Class<?> clazz) {
   def flds = [], fld, fldMap = [:]
   def metaProps = clazz.metaClass.properties
   def fields = clazz.getDeclaredFields()
   metaProps.each {metaProp -> 
    fld = fields.find {metaProp.name == it.name}
    if(fld) flds << fld
   }
   flds.each{it-> 
    def fldName = it.name
    if(fldName != 'metaClass') {
     if(it.isAnnotationPresent(Column.class)) {
      def colAnn = it.getAnnotation(Column.class)
      fldMap[colAnn.name()] = fldName
     } else fldMap[fldName] = fldName
    }
   }
   fldMap
  }
  
  /**
   * Will lookup for a datasource with given JNDI Name in the JNDI Context
   */
  private static def lookup = {jndiName ->
   def ctx, ds
   try {
    log.debug "Looking up for a datasource with JNDI name ${jndiName}"
    ctx = new InitialContext()
    ds = ctx.lookup(jndiName)
   } finally {
    ctx?.close()
   }
   return ds
  }.memoize()
 }
 

Now let me go through the code.

• mapFieldNames() method process the Class which we need to map. This method will find all fields in the Class which needs to be mapped and will find whether any name overriding is needed. I reusing the same Column annotation declared in my old ResultSet Mapper blog to resolve the colum and variable name difference.
• executeQuery() method will lookup the datasource and execute the given query. After that it will try to map field names returned by mapFieldNames() with resultset.

Please refer to Groovy SQL documentation to use it in a stand alone environment where a datasource is not available.

Usage:

In Java:

DBUtil.executeQuery("jdbc/MyDBJNDI", 
         "select empno, lname, fname, birth, deptno from employees where upper(lname) like ?", 
         Arrays.asList(new String[]{"AND"}), Employee.class);
 

In Groovy:

DBUtil.executeQuery("jdbc/MyDBJNDI", 
         "select empno, lname, fname, birth, deptno from employees where upper(lname) like ?", 
         ['AND'], Employee.class);
 

Suppose if your query returns only one String, then you could use this utility as

def emps = DBUtil.executeQuery("jdbc/MyDBJNDI", 
         "select lname from employees where empno = ?", 
         [120L], String.class);
 println "Last Name of Employee with empno 120 is ${emps[0]}"
 

Suppose if your query returns multiple String values, then you could use this utility as

def emps = DBUtil.executeQuery("jdbc/MyDBJNDI", 
         "select lname from employees where deptno = ?", 
         [100], String.class);
 println "Employees working in department 100 are ${emps}"
 

Since executeQuery() return type is Object you need an explicit cast to java.util.List when using from Java. To avoid this you can add one more function in DBUtil as below

static def <T> List<T> toList(String dataSource, String query, List<?> params, Class clazz) {
  log.debug "Executing query ${query} with parameters ${params} using datasource ${dataSource}"
  executeQuery(dataSource, query, params, clazz)
 }
 

Now it is really easy to use this Utility from Java also.

List<Employee> emps = DBUtil.toList("jdbc/MyDBJNDI", 
         "select empno, lname, fname, birth, deptno from employees where upper(lname) like ?", 
         Arrays.asList(new String[]{"AND"}), Employee.class);
 
 List<String> empLNames = DBUtil.toList("jdbc/MyDBJNDI", 
         "select lname from employees where empno = ?", 
         Arrays.asList(new Long[]{120L}), String.class);
 System.out.println(String.format("Last Name of Employee with empno 120 is %s", empLNames.get(0)));
 

To compile this groovy class, First you need to download Groovy. Then you can use either groovy eclipse plugin or ANT to build it. If you are using ANT, here is the snippet which you need to add to your build.xml for compiling groovy scripts.