Functions and Operators

Arithmetic operators +, -, /, %, * are supported as well as generic expressions. Functions are not specified according to SQL keyword syntax but rather as a function identifier with parameters similar to programming languages. The format of functions is: function (param1, param2, ...).

Logical Operators

The logical operators of AND, OR, NOT, and XOR are available.

Comparison Operators

The following comparison operators are available:

Table 6.1. Comparison Operators

<less than
>greater than
<=less than or equal to
>=greater than or equal to
!=not equal
IS [NOT] NULLtests if value is NULL
IS [NOT] [TRUE | FALSE]tests if value is true or false

Arithmetic Functions and Operators

The following mathematical operators are supported:

Table 6.2. Mathematical Operators

+addition (and string concatenation for strings)
%modulus (remainder of integer division)

The following are a few of the mathematical functions supported. A complete list of functions is available on the web site.

Table 6.3. Mathematical Functions

FunctionReturn TypeExampleResultDescription
abs(x)Same as xabs(-17.4)17.4Absolute value
ceil(x)Same as inputceil(-42.8)-42Smallest integer not less than argument
exp(x)Same as inputexp(1.0)2.718exponential
floor(x)Same as inputln(2.0)0.69314natural logarithm
log(x)Same as inputlog(100.0)2base 10 logarithm
power(a, b)double precisionpower(9,3)729a raised to the power of b
random()double precisionrandom() random value between 0.0 and 1.0
sqrt(x)double precisionsqrt(2.0)1.4142square root

String Functions

The following are a few of the string functions supported. A complete list of functions is available on the web site.

Table 6.4. String Functions

<str> + <str>String'Unity' + 'JDBC'UnityJDBCString concatenation
ascii(string)intascii('xyz')120ASCII code of the first character of the input string
length(string)intlength('UnityJDBC')9Length of string in characters
lower(string)Stringlower('JDBC')jdbcConvert string to lower case
position(search, target)intposition('J','UnityJDBC')5Location of search in target (indexed from 1)
replace(source, search, replace)Stringreplace( 'abUnityabJDBC', 'ab', 'XX')XXUnityXXJDBCReplace all occurrences of search string in source string with replace string
substring(string, start)Stringsubstring('UnityJDBC',6)JDBCsubstring starting at position start
substring(string, start, count)Stringsubstring('UnityJDBC,6,2)JDsubstring starting at position start and taking count characters
trim(string)Stringtrim(' UnityJDBC ')UnityJDBCremove leading and trailing spaces from string
ltrim(string) OR trim(string, 'LEADING')Stringtrim(' UnityJDBC ')'UnityJDBC 'remove leading spaces from string
rtrim(string) OR trim(string, 'TRAILING')Stringtrim(' UnityJDBC ')' UnityJDBC'remove trailing spaces from string
trim(string, ['BOTH', 'LEADING', 'TRAILING'], [<chars>])Stringtrim('aaaUnityJDBCbbb', 'BOTH', 'ab')UnityJDBCremove leading, trailing or both from string where characters removed may be optionally specified in <chars>
upper(string)Stringupper('jdbc')JDBCConvert string to upper case

Pattern Matching Operators

Pattern matching is supported using the LIKE operator.

For example, 'abcdef' LIKE 'ab%' is true. The '%' is used to match one or more characters, and '_' is used to match a single character.

Data Type Conversion Functions

Data type conversions are performed using the CAST(x,y) function. The CAST function takes any object as the first parameter and takes a string literal representation of the type to cast to as the second parameter. Note that the type must be put in single quotes as a string literal. Example:

CAST(45, 'VARCHAR') creates '45'

Possible type names are: 'VARCHAR', 'CHAR', 'INT', 'FLOAT', 'DOUBLE', 'DATE', 'TIMESTAMP', 'TIME'.

Date/Time Functions and Operators

The following are a few of the date functions supported. A complete list is on the website.

Table 6.5. Date Functions

FunctionReturn TypeExampleResultDescription
CURRENT_TIMESTAMPTIMESTAMPCURRENT_TIMESTAMP2011-07-06 12:53:45Returns the current date. Format: "yyyy-MM-dd HH:mm:ss"
CURRENT_TIMETIMECURRENT_TIME12:53:45Returns the current time. Format: "HH:mm:ss"
CURRENT_DATEDATECURRENT_DATE2011-07-06Returns the current date. Format: "yyyy-MM-dd"
YEARINTYEAR('2011-07-06)2011Returns the year of the given date expression.
MONTHINTMONTH('2011-07-06)7Returns the month of the given date expression.
DAYINTDAY('2011-07-06)6Returns the day of the given date expression.
DATEADDTIMESTAMPDATEADD('2011-07-06', INTERVAL 3 days)2006-07-06 12:53:45Allows the addition of a given date field to a datetime expression. Intervals are supported and are translated as necessary for systems that do not support them.

Aggregate Functions

The following aggregate functions are supported:

Table 6.6. Aggregate Functions

FunctionArgument TypeReturn TypeDescription
avg(x)int, float, double precision typeint for integer types, double precision for float/double typesAverage of all input values
count(*)N/AintCount of number of input values
count(x)anyintCount of number of non-null input values
group_concat(x)anyvarcharReturns a comma-separated list of all input values.
max(x)any comparable typesame as inputMaximum of all input values
min(x)any comparable typesame as inputMinimum of all input values
sum(x)int, float, double precision typeint for integer types, double precision for float/double typesSum of all input values

User-Defined Functions and Support for Other Functions

For queries on a single database, UnityJDBC parses functions and passes them directly to the database engine for execution. Thus, all functions that can be executed at the source are available. UnityJDBC and user-defined functions are used only when applying functions to data after it is extracted from the sources. UnityJDBC will parse queries containing functions that it itself cannot process in its internal database engine. These functions are passed down to the database engine and executed locally. Only functions that require inputs from more than one database are processed in the UnityJDBC database engine. All other functions are passed down to the sources.

UnityJDBC supports user-defined functions (UDFs). Adding your own user-defined function is easy. There are two types of functions: row functions and aggregate functions. A row function operates on one row at a time for its data and includes functions like SUBSTRING() and ABS(). An aggregate function is used in GROUP BY queries and aggregates an expression (usually a column) across multiple rows in a group to produce a single value. Examples include MAX() and COUNT().

To create a row function, you must create a Java class that extends the Function class. A template example is in the file This class must implement a constructor, an evaluate() method, and provide information on the parameters it requires. Once completed, as long as this function is available in the CLASSPATH, UnityJDBC will search for it when called. A similar template is available for aggregate functions, Sample code is provided in the directory unity/functions.

Function Translation

UnityJDBC has a database of known functions. This database contains information on what functions are supported on each data source. This is how UnityJDBC processes functions:

  1. UnityJDBC does not support function - If a function is not in the UnityJDBC database, it is passed down as-is to the underlying source. If the source is able to execute it successfully, the query continues. If not, an error is thrown.

  2. UnityJDBC supports function, data source requires translation - If the function requested in the query is not directly supported by the data source (different name, different parameters, etc.), but UnityJDBC contains a mapping in its database, the function is translated to the correct form on the data source and executed on the data source.

  3. UnityJDBC supports function, data source does not support function - If UnityJDBC supports the function but not the data source, then the query is optimized to perform as much of the processing as possible on the source, but the function execution is performed internally in UnityJDBC. This way your query can execute on data sources with the help of UnityJDBC that do not support the required functions.

  4. UnityJDBC is running with local execution - If the local execution flag is set for the UnityStatement object executing the query, all functions except aggregate functions are executed by UnityJDBC. This setting may be useful to reduce load on the source or to guarantee absolute consistency of function execution across different sources.

The UnityJDBC function database is encrypted and stored in the unityjdbc.jar. To add user-defined functions to the function database, create a mapping.xml file in the JRE classpath (execution directory, etc.) that stores the information on the function. An example is included in the release and more information is available on the web site.

Non-parsed Functions

UnityJDBC attempts to support most of the SQL standard. If there is a function or feature not supported, it is possible to use the NP() function to pass the query string directly to the data source by-passing UnityJDBC validation. This may be used to support non-standard functions or SQL syntax. Below are several examples.

SELECT N1.n_nationkey, NP('OrderDB','n_name','varchar') 
FROM OrderDB.Nation N1 WHERE N1.n_nationkey = 1;

Result: (n_name is substituted directly into the query)
SELECT n_nationkey, n_name 
FROM Nation N1 WHERE N1.n_nationkey = 1

SELECT N1.n_nationkey,  NP('OrderDB','(select n_name from nation n2 
where N1.n_nationkey = N2.n_nationkey)','varchar') as name 
FROM OrderDB.Nation N1 WHERE N1.n_nationkey = 1

(select n_name from nation n2 where N1.n_nationkey = N2.n_nationkey) name

FROM NP('OrderDB',
 '(select n_name,n_nationkey from nation)','n_name,n_nationkey') N1,
 '(select n_name,n_nationkey from nation)','n_name,n_nationkey') as N2 
where N2.n_nationkey < 2 and N1.n_nationkey = N2.n_nationkey;

// Substitutes subquery for each of the two data sources (OrderDB and PartDB).  
// The result of the two subqueries is then joined at the UnityJDBC level.
// OrderDB: 
SELECT N2.n_name, N2.n_nationkey
FROM (select n_name,n_nationkey from nation) N2\n WHERE N2.n_nationkey < 2
// PartDB:
SELECT N1.n_nationkey FROM (select n_name,n_nationkey from nation) N1

More information on non-parsed functions is available on the web site.