Usage | Grammar | Version History


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 Check 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 Check    
Division / Check  

Multiplication * Check  

Addition, Subtraction +,
-
Check Check (only ||)   Check
Less or Equal, More or Equal <=, >= Check  Check
(using compareTo)
Check
Check
Less Than, Greater Than <,
>
Check  Check
(using compareTo)
 Check Check
Not Equal, Equal !=, == Check Check
(using compareTo)
Check Check
Boolean And And

  Check
Boolean Or Or

  Check
Boolean Not Not


Check


Predicates



Number String Boolean
Timestamp
Date
Time
in
Check Check Check Check
between
Check Check
(using compareTo)
Check Check
like

Check

not like

Check

is null
Check Check Check Check
is not null
Check Check Check Check



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
Check
   
power Oracle Check
   
mod Oracle Check
   
substr Oracle
Check    
sign Oracle Check
   
ceil Oracle Check
   
floor Oracle Check
 
trunc Oracle Check
   Check
round Oracle Check
  Check
length Oracle
Check    
concat Oracle
Check    
instr Oracle
Check    
trim Oracle
Check    
rtrim Oracle
Check    
ltrim Oracle
Check
   
rpad Oracle
Check    
lpad Oracle    Check    
lower Oracle
Check
   
upper Oracle
Check    
translate Oracle
Check
   
replace
Oracle
Check

initcap
Oracle
Check

value
MaxDB (nvl in Oracle)
Check Check Check Check
decode
Oracle Check Check Check Check
to_char
Oracle Check
Check Check
to_number
Oracle
Check

imatch
replacement for of  soundex

Check

months_between
Oracle


Check
add_months
Oracle


Check
last_day
Oracle


Check
next_day
Oracle


Check
to_date
Oracle


Check
case
replacement for CASE WHEN


Check
index
MaxDB

Check

num
MaxDB

Check

chr
MaxDB
Check
Check Check
dayname
MaxDB



Check
adddate
MaxDB



Check
subdate
MaxDB



Check
addtime
MaxDB



Check
subtime
MaxDB



Check
year
MaxDB



Check
month
MaxDB



Check
day
MaxDB



Check
dayofmonth
MaxDB



Check
hour
MaxDB



Check
minute
MaxDB



Check
second
MaxDB



Check
microsecond
MaxDB



Check
datediff
MaxDB



Check
dayofweek
MaxDB



Check
weekofyear
MaxDB



Check
dayname
MaxDB



Check
monthname
MaxDB



Check
makedate
MaxDB



Check
maketime
MaxDB



Check


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)
 


SourceForge.net Logo
:: © 2006 Medfoster.ru