Defining Query Helper Functions

To use the query helper functions in your application, use Application Composer to define the following set of global functions in the order that they appear.

The order is important because later functions depend on calling the earlier ones in the list.

Global Function: Map mapForRow(Object row, List selectList)

def ret = null
if (row != null) {
  ret = [:]
  for (attrName in row.getAttributeNames()) {
    if (attrName in selectList) {
       ret[attrName] = row.getAttribute(attrName)
    }
  }
}
return ret as Map

Global Function: void error(String message)

throw new oracle.jbo.JboException(message)

Global Function: Object query(Map options)

// The options Map might be null if caller passes no named parameters
// so first two checks use the safe-navigation operator to gracefully
// handle the case when options == null
if (!options?.select) {
 adf.util.error("Must specify list of field names in 'select' parameter")
}
if (!options?.from) {
 adf.util.error("Must specify object name in 'from' parameter")
}
// From here, we know that some options were supplied, so we do not
// need to continue using the "?." operator when using options.someName
def sysBindVars = [
     [name:"SysUser", type:"Text",
      defaultValue: {adf.context.getSecurityContext().getUserName()?:'anonymous'}],
     [name:"SysToday", type:"Date", defaultValue: {today()}],
     [name:"SysNow", type:"Datetime", defaultValue: {now()}]]
options.selectList = []
for (fieldNameWithWhitespace in options.select.split(',')) {
   def fieldName = fieldNameWithWhitespace.trim()
   options.selectList << fieldName
}
def vo = newView(options.from)
if (options.orderBy) {
 vo.setSortBy(options.orderBy)
}
if (options.where) {
 if (options.binds) {
   for (varName in options.binds.keySet()) {
     if (sysBindVars.find{ it.name == varName}) {
       adf.util.error("Cannot give a value for system bind variable ${varName}")
     }
     if (varName.startsWith('Sys')) {
       adf.util.error("${varName} is a bind variable reserved for future system use")
     }
     def value = options.binds[varName]
     def varType = 'Text'
     if (value) {
       if (value instanceof java.sql.Timestamp ||
           value instanceof oracle.jbo.domain.Timestamp ) {
           varType = 'Datetime'
       }
       else if (value instanceof java.util.Date ||
                value instanceof oracle.jbo.domain.Date) {
           varType = 'Date'
       }
       else if ( value instanceof java.lang.Number ||
                 value instanceof oracle.jbo.domain.Number) {
           varType = 'Number'
       }
     }         
     addBindVariable(vo,varName,varType)
   }
 }
 for (var in sysBindVars) {
   if (options.where.contains(":${var.name}")) {
      addBindVariable(vo, var.name, var.type)
   }
 }
 if (options.ignoreNullBinds) {
   vo.appendViewCriteria(options.where, (Boolean)options.ignoreNullBinds)
 }
 else {
   vo.appendViewCriteria(options.where)
 }      
}
selectAttributesBeforeQuery(vo,options.selectList)
if (options.limit) {
 vo.setMaxFetchSize(options.limit as short)
}
if (options.where) {
  if (options.binds) {
     for (varName in options.binds.keySet()) {               
       setBindVariable(vo,varName,options.binds[varName])
     }
  }
  for (var in sysBindVars) {
    if (options.where.contains(":${var.name}")) {
      def defaultValueClosure = var.defaultValue
      def value = defaultValueClosure()
      setBindVariable(vo, var.name, value)
    }
  }
}
return vo

Global Function: List queryRows(Map options)

def ret = []
def vo = adf.util.query(options)
vo.executeQuery()
while (vo.hasNext()) {
  ret << vo.next()
}
return ret

Global Function: Object queryRow(Map options)

def ret = []
def vo = adf.util.query(options)
vo.executeQuery()
while (vo.hasNext()) {
  ret << vo.next()
}
return ret

Global Function: Map queryMap(Map options)

def vo = adf.util.query(options)
vo.executeQuery()
return adf.util.mapForRow(vo.first(),options.selectList)

Global Function: List queryMaps(Map options)

def ret = []
def vo = adf.util.query(options)
vo.executeQuery()
while (vo.hasNext()) {
 ret << adf.util.mapForRow(vo.next(),options.selectList)
}
return ret

Global Function: Long queryCount(Map options)

def vo = adf.util.query(options)
vo.executeQuery()
return vo.getEstimatedRowCount()