SQLJEP - Java SQL Expression Parser

SQLJEP is a Java API for parsing and evaluating SQL-like expressions. It supports some SQL predicase (is null, like, in, between) and almost all Oracle's and MaxDB's functions. See BaseJEP class in JavaDoc.

SQLJEP provides abstract class BaseJEP which doesn't know how to get source data. There are two types of variables in an expression. Columns of a abstarct table data and external variables. Example of external variables are timestamp, date, time, sysdate variables.

Also SQLJEP provides two examples of classes how to use BaseJEP. ResultSetJEP illustrates how apply SQLJEP to java.sql.ResultSet data with one external variable sysdate and simple class RowJEP for illustrating how to apply SQLJEP to one row (Comparable[] array).

Example of usage ResultSetJEP.

ResultSet rs = statement.excute("SELECT ID,SUM,SALE_DATE from test");
ResultSetJEP sqljep = new ResultSetJEP("ID in (1,2,3) and SUM>100 and SALE_DATE>trunc(sysdate)-7");
try {
   sqljep.addConstant("sysdate", new java.util.Date());
   sqljep.parseExpression(rs);
   whille (rs.next()) {
      System.out.println(sqljep.getValue());
   }
}
catch (ParseException e) {
   e.printStackTrace();
}

Example of usage RowJEP.

Comparable[] row = {1,100.00,new java.util.Date()};
HashMap<String,Integer> columnMapping = new HashMap<String,Integer>();
columnMapping.put("ID",0);
columnMapping.put("SUM",1);
columnMapping.put("SALE_DATE",2);
RowJEP sqljep = new RowJEP("ID in (1,2,3) and SUM>100 and SALE_DATE>to_date('2006-01-01','yyyy-mm-dd')");
try {
   sqljep.parseExpression(columnMapping);
   System.out.println(sqljep.getValue(row));
}
catch (ParseException e) {
   e.printStackTrace();
}

Operators

All SQL arithmetic operators are supported. Boolean operators are also fully supported.

An indicates that the operator can be used with the specific type of variable. Refer to the grammar for detailed information about operator precedence.

		Number	String	Boolean	Timestamp Date Time
Unary Plus, Unary Minus	+x, -x
Division	/
Multiplication	*
Addition, Subtraction	+, -		(only \|\|)
Less or Equal, More or Equal	<=, >=		(using compareTo)
Less Than, Greater Than	<, >		(using compareTo)
Not Equal, Equal	!=, ==		(using compareTo)
Boolean And	And
Boolean Or	Or
Boolean Not	Not

Predicates

	Number	String	Boolean	Timestamp Date Time
in
between		(using compareTo)
like
not like
is null
is not null

Functions

Note that you can always add new functions. Each of the following functions can be applied to objects of the types indicated.

Functions added by default:

	RDBS like	Number	String	Boolean	Timestamp Date Time
abs	Oracle
power	Oracle
mod	Oracle
substr	Oracle
sign	Oracle
ceil	Oracle
floor	Oracle
trunc	Oracle
round	Oracle
length	Oracle
concat	Oracle
instr	Oracle
trim	Oracle
rtrim	Oracle
ltrim	Oracle
rpad	Oracle
lpad	Oracle
lower	Oracle
upper	Oracle
translate	Oracle
replace	Oracle
initcap	Oracle
value	MaxDB (nvl in Oracle)
decode	Oracle
to_char	Oracle
to_number	Oracle
imatch	replacement for of soundex
months_between	Oracle
add_months	Oracle
last_day	Oracle
next_day	Oracle
to_date	Oracle
case	replacement for CASE WHEN
index	MaxDB
num	MaxDB
chr	MaxDB
dayname	MaxDB
adddate	MaxDB
subdate	MaxDB
addtime	MaxDB
subtime	MaxDB
year	MaxDB
month	MaxDB
day	MaxDB
dayofmonth	MaxDB
hour	MaxDB
minute	MaxDB
second	MaxDB
microsecond	MaxDB
datediff	MaxDB
dayofweek	MaxDB
weekofyear	MaxDB
dayname	MaxDB
monthname	MaxDB
makedate	MaxDB
maketime	MaxDB

Requirements

JDK 5.0, JavaCC 3.2 for changing grammar.

Download

You can download the software from Sourceforge.

License

SQLJEP is distributed under the GNU General Public License (GPL)

::	© 2006 Medfoster.ru