com.novartis.opensource.yada.adaptor.JDBCAdaptor Maven / Gradle / Ivy
/**
* Copyright 2016 Novartis Institutes for BioMedical Research Inc.
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
/**
*
*/
package com.novartis.opensource.yada.adaptor;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.log4j.Logger;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import com.novartis.opensource.yada.Parser;
import com.novartis.opensource.yada.YADAQuery;
import com.novartis.opensource.yada.YADAQueryResult;
import com.novartis.opensource.yada.YADARequest;
import com.novartis.opensource.yada.YADASQLException;
/**
* Abstract subclass defining many methods and fields for JDBC functionality including
* the construction of SQL statements incorporating pagination, filtering, sorting, and
* total row counts.
* The code in this class is based on Oracle functionality. Other vendor JDBC Adaptor
* subclasses should extend this class and override it's methods, particalarly
* {@link #buildSelect(String, String, String, int, int, JSONObject)}
* and {@link #buildSelectCount(String, JSONObject)}
*
* @author David Varon
*
*/
public abstract class JDBCAdaptor extends Adaptor{
/**
* Local logger handle
*/
private static Logger l = Logger.getLogger(JDBCAdaptor.class);
/**
* Constant equal to: {@code d}
*/
protected static final char DATE = 'd';
/**
* Constant equal to: {@code t}
* @since 0.5.1.0
*/
protected static final char TIMESTAMP = 't';
/**
* Constant equal to: {@code i}
*/
protected static final char INTEGER = 'i';
/**
* Constant equal to: {@code n}
*/
protected static final char NUMBER = 'n';
/**
* Constant equal to: {@code v}
*/
protected static final char VARCHAR = 'v';
// protected static final char AUTH_USER = 'u';
/**
* Constant equal to: {@code D}
*/
protected static final char OUTPARAM_DATE = 'D';
/**
* Constant equal to: {@code I}
*/
protected static final char OUTPARAM_INTEGER = 'I';
/**
* Constant equal to: {@code N}
*/
protected static final char OUTPARAM_NUMBER = 'N';
/**
* Constant equal to: {@code V}
*/
protected static final char OUTPARAM_VARCHAR = 'V';
/**
* Constant equal to: {@value}
*/
protected static final String NEWLINE = "\n";
/**
* Constant equal to: {@value}
*/
protected static final String SPACE = " ";
/**
* Constant equal to: {@value}
*/
protected static final String OPEN_PAREN = "(";
/**
* Constant equal to: {@value}
*/
protected static final String CLOSE_PAREN = ")";
/**
* Constant equal to: {@value}
*/
protected static final String OPEN_CURLY = "{";
/**
* Constant equal to: {@value}
*/
protected static final String CLOSE_CURLY = "}";
/**
* Constant equal to: {@value}
*/
protected static final String COMMA = ",";
/**
* Constant equal to: {@value}
*/
protected static final String SQL_NULL = "NULL";
/**
* Constant equal to: {@value}
*/
protected static final String SQL_NOT = "NOT ";
/**
* Constant equal to: {@value}
*/
protected static final String SQL_IS = "IS ";
/**
* Constant equal to: {@value}
*/
protected static final String SQL_IN = "IN ";
/**
* Constant equal to: {@value}
*/
protected static final String SQL_SELECT = "SELECT ";
/**
* Constant equal to: {@value}
*/
protected static final String SQL_LIKE = "LIKE ";
/**
* Constant equal to: {@value}
*/
protected static final String SQL_ALL = "*";
/**
* Constant equal to: {@value}
*/
protected static final String SQL_SELECT_ALL = SQL_SELECT + SQL_ALL + SPACE;
/**
* Constant equal to: {@value}
*/
protected static final String SQL_FROM = "FROM ";
/**
* Constant equal to: {@value}
*/
protected static final String SQL_ORDER_BY = "ORDER BY ";
/**
* Constant equal to: {@value}
*/
protected static final String SQL_CORE_ALIAS = "yadacore";
/**
* Constant equal to: {@value}
*/
protected static final String SQL_WRAPPER_ALIAS = "wrapper";
/**
* Constant equal to: {@value}
*/
protected static final String SQL_WHERE = "WHERE ";
/**
* Constant equal to: {@value}
*/
protected static final String SQL_AND = "AND ";
/**
* Constant equal to: {@value}
*/
protected static final String ROWNUM = "ROWNUM";
/**
* Constant equal to: {@value}
*/
public static final String ROWNUM_ALIAS = "yada_rnum";
/**
* An alias for the sql COUNT(*) function, e.g., SELECT COUNT(*) COUNT FROM TAB
*/
public static final String SQL_COUNT = "COUNT";
/**
* Constant equal to: {@value}
*/
protected static final String SQL_COUNT_ALL = SQL_COUNT+OPEN_PAREN+SQL_ALL+CLOSE_PAREN+SPACE;
/**
* Constant equal to: {@value}
*/
protected static final String ORACLE_DATE_FMT = "dd-MMM-yy";
/**
* Constant equal to: {@value}
*/
protected static final String ORACLE_TIMESTAMP_FMT = "dd-MMM-yy hh:mm:ss";
/**
* Constant equal to: {@value}
*/
protected static final String STANDARD_DATE_FMT = "yyyy-MM-dd";
/**
* Constant equal to: {@value}
*/
protected static final String STANDARD_TIMESTAMP_FMT = "yyyy-MM-dd HH:mm:ss";
/**
* Constant equal to: {@value}
*/
protected static final String FILTERKEY_FIELD = "field";
/**
* Constant equal to: {@value}
*/
protected static final String FILTERKEY_OP = "op";
/**
* Constant equal to: {@value}
*/
protected static final String FILTERKEY_TYPE = "type";
/**
* Constant equal to: {@value}
*/
protected static final String FILTERKEY_DATA = "data";
/**
* Constant equal to: {@value}
*/
protected static final String FILTERKEY_RULES = "rules";
/**
* Constant equal to: {@value}
*/
protected static final String FILTERKEY_GROUPS = "groups";
/**
* Constant equal to: {@value}
*/
protected static final String FILTERKEY_GROUPOP = "groupOp";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_EQUAL = "eq";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_NOTEQUAL = "ne";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_LESSTHAN = "lt";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_LESSEQUAL = "le";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_GREATERTHAN = "gt";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_GREATEREQUAL = "ge";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_IN = "in";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_NOTIN = "ni";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_NULL = "nu";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_NOTNULL = "nn";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_BEGINSWITH = "bw";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_NOTBEGINSWITH = "bn";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_ENDSWITH = "ew";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_NOTENDSWITH = "en";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_CONTAINS = "cn";
/**
* Constant equal to: {@value}
*/
protected static final String FILTER_NOTCONTAINS = "nc";
/**
* Constant equal to: {@value}
*/
protected static final String TYPE_NUMBER = "number";
/**
* Constant equal to: {@value}
*/
protected static final String TYPE_TEXT = "text";
/**
* Constant equal to: {@value}
*/
protected static final String TYPE_EXACTTEXT = "etxt";
/**
* Constant equal to: {@value}
*/
protected static final String RX_ALT = "|";
/**
* Constant equal to: {@value}
*/
protected static final String RX_CALLABLE = "^call .+\\(.*\\)\\s*$";
/**
* Constant equal to: {@value}
*/
protected static final String EMPTY = "";
/**
* A constant equal to: {@value}
*/
protected static final String LIMIT = "LIMIT ";
/**
* A constant equal to: {@value}
* @since 0.4.1.0
*/
protected static final String OFFSET = "OFFSET";
/**
* A convenient variable to hold an empty string by default. This is potentially configurable in future versions.
*/
public String NULL_REPLACEMENT = EMPTY; // this will need to be a settable property, maybe NULL_REPLACEMENT_STRING
/**
* Default constructor for adaptors, includes debug-level logging, but nothing else.
*/
public JDBCAdaptor() {
l.debug("Initializing");
}
/**
* @since 0.4.0.0
* @param yadaReq YADA request configuration
*/
public JDBCAdaptor(YADARequest yadaReq)
{
super(yadaReq);
}
/**
* Executes the statemetns stored in the query object. Results are
* stored in a data structure inside a {@link YADAQueryResult} object
* contained by the query object.
* @since 0.4.0.0
* @param yq {@link YADAQuery} object containing code to be executed
* @throws YADAAdaptorExecutionException when the adaptor can't execute the statement or statements stored in the query
*/
@Override
public void execute(YADAQuery yq) throws YADAAdaptorExecutionException
{
l.debug("Executing query ["+yq.getQname()+"]");
boolean count = Boolean.valueOf(yq.getYADAQueryParamValue(YADARequest.PS_COUNT)[0]).booleanValue();
boolean countOnly = Boolean.valueOf(yq.getYADAQueryParamValue(YADARequest.PS_COUNTONLY)[0]).booleanValue();
int countResult = -1;
int dataSize = yq.getData().size() > 0 ? yq.getData().size() : 1;
for(int row=0;row -1)
{
sql.append(NEWLINE);
sql.append(LIMIT + this.yadaReq.getViewLimit()); /* THIS IS A MYSQL SPECIFIC LINE */
}
return sql;
}
/**
* Calls {@link #getQueryFilters(boolean, StringBuffer, JSONObject)} with {@code append} = {@code false} as
* well as a {@code null} {@link java.lang.StringBuffer} and filters from {@link #yadaReq}
* @param append currently ignored, always reset to {@code false}
* @return an SQL representation of the json filter string
* @throws YADAAdaptorException when the query filters can't be converted into a WHERE clause
*/
protected String getQueryFilters(boolean append) throws YADAAdaptorException
{
return getQueryFilters(false,null,this.yadaReq.getFilters());
}
/**
* Converts the filters stored in the local {@link #yadaReq} config as json into an SQL fragment for appendage
* to a {@code WHERE} clause
* @since 0.4.0.0
* @param append currently ignored
* @param sql the current SQL query to which to append the WHERE clause
* @param filters a JSON object containing the WHERE criteria
* @return {@link String} SQL translation of {@link org.json.JSONObject#toString()} filters
* @throws YADAAdaptorException when the filters cannot be converted into a WHERE clause
*/
protected String getQueryFilters(boolean append, StringBuffer sql, JSONObject filters) throws YADAAdaptorException
{
JSONArray rules = null;
JSONArray groups = null;
StringBuffer lSql = sql;
/*
* {"groupOp":"OR",
* "rules":[{"field":"SAMPLE","op":"cn","data":"hly","type":"text"}],
* "groups":[{"groupOp":"AND",
* "rules":[{"field":"RPKM","op":"ge","data":"20"},
* {"field":"RPKM","op":"lt","data":"30"}],
* "groups":[]},
* {"groupOp":"AND",
* "rules":[{"field":"RPKM","op":"ge","data":"100"},
* {"field":"RPKM","op":"lt","data":"1000"}],
* "groups":[]}]}
*
*
* {"groupOp":"AND",
* "rules":[],
* "groups":[{"groupOp":"AND",
* "rules":[{"field":"SAMPLE","op":"eq","data":""},
* {"field":"SAMPLE","op":"eq","data":""}],
* "groups":[]},
* {"groupOp":"AND",
* "rules":[{"field":"SAMPLE","op":"eq","data":""},
* {"field":"SAMPLE","op":"eq","data":""}],
* "groups":[]}]}
*/
if (null == lSql)
{
lSql = new StringBuffer();
}
try
{
String groupOp = filters.getString(FILTERKEY_GROUPOP);
// init groups and rules
try
{
rules = filters.has(FILTERKEY_RULES) ? filters.getJSONArray(FILTERKEY_RULES) : null;
groups = filters.has(FILTERKEY_GROUPS) ? filters.getJSONArray(FILTERKEY_GROUPS) : null;
}
catch (JSONException e)
{
String msg = "Could not extract filter rules and groups.";
throw new YADAAdaptorException(msg,e);
}
if (null != rules && rules.length() > 0)
{
for (int i=0;i 0)
{
lSql.append(" " + groupOp + " ");
}
// nulls, not nulls
if(op.matches(FILTER_NULL + RX_ALT + FILTER_NOTNULL))
{
lSql.append(SQL_CORE_ALIAS+"." + field + " ");
lSql.append(SQL_IS);
if (FILTER_NOTNULL.equals(op)) { lSql.append(SQL_NOT); }
lSql.append(SQL_NULL);
}
// numbers
else
{
// strings: eq, ne, cn, nc, bw, bn, ew, en, in, ni
// numbers: eq, ne, lt, le, gt, ge, in, ni
if (TYPE_NUMBER.equals(type))
{
lSql.append(SQL_CORE_ALIAS+"." + field + " ");
if(FILTER_NOTEQUAL.equals(op)) { lSql.append("<> "); }
else if(op.matches(FILTER_LESSTHAN + RX_ALT + FILTER_LESSEQUAL))
{ lSql.append("< "); }
else if(op.matches(FILTER_GREATERTHAN + RX_ALT + FILTER_GREATEREQUAL))
{ lSql.append("> "); }
if(op.matches(FILTER_EQUAL + RX_ALT
+FILTER_LESSEQUAL + RX_ALT
+FILTER_GREATEREQUAL))
{ lSql.append("= "); }
if(FILTER_NOTIN.matches(op)) { lSql.append(SQL_NOT); }
if(op.matches(FILTER_IN + RX_ALT + FILTER_NOTIN))
{ lSql.append(SQL_IN+"("+value+") "); }
else { lSql.append(value); }
}
// varchars
else if (TYPE_TEXT.equals(type) || (TYPE_EXACTTEXT.equals(type)))
{
// case sensitive
if (TYPE_EXACTTEXT.equals(type))
{
lSql.append(SQL_CORE_ALIAS+"." + field + " ");
}
// case insensitive
else
{
lSql.append("LOWER("+SQL_CORE_ALIAS+"." + field + ") ");
}
if(FILTER_EQUAL.equals(op)) { lSql.append(" = "); }
else if(FILTER_NOTEQUAL.equals(op)) { lSql.append(" <> ");}
// not contains, not begins with, not ends with
if(op.matches(FILTER_NOTCONTAINS + RX_ALT
+FILTER_NOTBEGINSWITH + RX_ALT
+FILTER_NOTENDSWITH + RX_ALT
+FILTER_NOTIN)) { lSql.append(SQL_NOT);}
if (op.matches(FILTER_CONTAINS + RX_ALT
+FILTER_BEGINSWITH + RX_ALT
+FILTER_ENDSWITH)) { lSql.append(SQL_LIKE); }
// equals, not equals, contains,
if (op.matches(FILTER_EQUAL + RX_ALT
+FILTER_NOTEQUAL + RX_ALT
+FILTER_CONTAINS + RX_ALT
+FILTER_NOTCONTAINS + RX_ALT
+FILTER_BEGINSWITH + RX_ALT
+FILTER_NOTBEGINSWITH + RX_ALT
+FILTER_ENDSWITH + RX_ALT
+FILTER_NOTENDSWITH)) { lSql.append("'"); }
// contains, ends with, not ends with
if (op.matches(FILTER_CONTAINS + RX_ALT
+FILTER_ENDSWITH + RX_ALT
+FILTER_NOTENDSWITH)) { lSql.append("%"); }
if (op.matches(FILTER_EQUAL + RX_ALT
+FILTER_NOTEQUAL + RX_ALT
+FILTER_CONTAINS + RX_ALT
+FILTER_NOTCONTAINS + RX_ALT
+FILTER_BEGINSWITH + RX_ALT
+FILTER_NOTBEGINSWITH + RX_ALT
+FILTER_ENDSWITH + RX_ALT
+FILTER_NOTENDSWITH)) { lSql.append(value); }
// in, ni
else
{
String[] split = value.split(",");
lSql.append(SQL_IN+"(");
for (int j=0;j 0)
{
for(int i=0;i 0 || (rules != null && rules.length() > 0))
{
lSql.append("\n " + groupOp + NEWLINE);
}
JSONObject filter = groups.getJSONObject(i);
lSql.append("(");
// recursive call to getQueryFilters to process nested groups
this.getQueryFilters(false,lSql,filter);
lSql.append(")");
}
}
} // end try
catch(JSONException e)
{
String msg = "Filter to SQL translation failed";
throw new YADAAdaptorException(msg,e);
}
return lSql.toString();
}
/**
* Sets the parameter value based on the data type designated in the YADA Markup, mapped to the correct JDBC setter.
* @param pstmt the statement in which to set the parameter values
* @param index the current parameter
* @param type the data type of the parameter
* @param val the value to set
* @throws YADASQLException when a parameter cannot be set, for instance if the data type is wrong or unsupported
*/
protected void setQueryParameter(PreparedStatement pstmt, int index, char type, String val) throws YADASQLException
{
String idx = (index < 10) ? " "+String.valueOf(index) : String.valueOf(index);
l.debug("Setting param [" + idx + "] of type [" + String.valueOf(type) + "] to: " + val);
try
{
switch(type)
{
case TIMESTAMP:
setTimestampParameter(pstmt,index,type,val);
break;
case DATE:
setDateParameter(pstmt,index,type,val);
break;
case INTEGER:
setIntegerParameter(pstmt,index,type,val);
break;
case NUMBER:
setNumberParameter(pstmt,index,type,val);
break;
case OUTPARAM_DATE:
((CallableStatement) pstmt).registerOutParameter(index,java.sql.Types.DATE);
break;
case OUTPARAM_INTEGER:
((CallableStatement) pstmt).registerOutParameter(index,java.sql.Types.INTEGER);
break;
case OUTPARAM_NUMBER:
((CallableStatement) pstmt).registerOutParameter(index,java.sql.Types.FLOAT);
break;
case OUTPARAM_VARCHAR:
((CallableStatement) pstmt).registerOutParameter(index,java.sql.Types.VARCHAR);
break;
default: //VARCHAR2
pstmt.setString(index, val);
break;
}
}
catch(SQLException e)
{
String msg = "Failed to set parameter.";
throw new YADASQLException(msg,e);
}
}
/**
* Sets a {@code ?t} parameter value mapped to the correct {@link java.sql.Types#TIMESTAMP} JDBC setter.
* @param pstmt the statement in which to set the parameter values
* @param index the current parameter
* @param type the data type of the parameter (retained here for logging)
* @param val the value to set
* @throws SQLException when a parameter cannot be set, for instance if the data type is wrong or unsupported
* @since 0.5.1.0
*/
protected void setTimestampParameter(PreparedStatement pstmt, int index, char type, String val) throws SQLException
{
if (EMPTY.equals(val) || val == null)
{
pstmt.setNull(index, java.sql.Types.TIMESTAMP);
}
else
{
SimpleDateFormat sdf = new SimpleDateFormat(STANDARD_TIMESTAMP_FMT);
ParsePosition pp = new ParsePosition(0);
Date dateVal = sdf.parse(val,pp);
if (dateVal != null)
{
long t = dateVal.getTime();
java.sql.Timestamp sqlDateVal = new java.sql.Timestamp(t);
pstmt.setTimestamp(index, sqlDateVal);
}
}
}
/**
* Sets a {@code ?d} parameter value mapped to the correct {@link java.sql.Types#DATE} JDBC setter.
* @param pstmt the statement in which to set the parameter values
* @param index the current parameter
* @param type the data type of the parameter (retained here for logging)
* @param val the value to set
* @throws SQLException when a parameter cannot be set, for instance if the data type is wrong or unsupported
* @since 0.5.1.0
*/
protected void setDateParameter(PreparedStatement pstmt, int index, char type, String val) throws SQLException
{
if (EMPTY.equals(val) || val == null)
{
pstmt.setNull(index, java.sql.Types.DATE);
}
else
{
SimpleDateFormat sdf = new SimpleDateFormat(STANDARD_DATE_FMT);
ParsePosition pp = new ParsePosition(0);
Date dateVal = sdf.parse(val,pp);
if (dateVal != null)
{
long t = dateVal.getTime();
java.sql.Date sqlDateVal = new java.sql.Date(t);
pstmt.setDate(index, sqlDateVal);
}
}
}
/**
* Sets a {@code ?i} parameter value mapped to the correct {@link java.sql.Types#INTEGER} JDBC setter.
* @param pstmt the statement in which to set the parameter values
* @param index the current parameter
* @param type the data type of the parameter (retained here for logging)
* @param val the value to set
* @throws SQLException when a parameter cannot be set, for instance if the data type is wrong or unsupported
* @since 0.5.1.0
*/
protected void setIntegerParameter(PreparedStatement pstmt, int index, char type, String val) throws SQLException
{
try
{
int ival = Integer.parseInt(val);
pstmt.setInt(index, ival);
}
catch(NumberFormatException e)
{
l.warn("Error: " + e.getMessage() + " caused by " + e.getClass());
l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type) + "] to: null");
pstmt.setNull(index, java.sql.Types.INTEGER);
}
catch(NullPointerException e)
{
l.warn("Error: " + e.getMessage() + " caused by " + e.getClass());
l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type) + "] to: null");
pstmt.setNull(index, java.sql.Types.INTEGER);
}
}
/**
* Sets a {@code ?n} parameter value mapped to the correct {@link java.sql.Types#FLOAT} JDBC setter.
* @param pstmt the statement in which to set the parameter values
* @param index the current parameter
* @param type the data type of the parameter (retained here for logging)
* @param val the value to set
* @throws SQLException when a parameter cannot be set, for instance if the data type is wrong or unsupported
* @since 0.5.1.0
*/
protected void setNumberParameter(PreparedStatement pstmt, int index, char type, String val) throws SQLException
{
try
{
float fval = Float.parseFloat(val);
pstmt.setFloat(index, fval);
}
catch(NumberFormatException | NullPointerException e)
{
l.warn("Error: " + e.getMessage() + " caused by " + e.getClass());
l.debug("Setting param [" + String.valueOf(index) + "] of type [" + String.valueOf(type) + "] to: null");
pstmt.setNull(index, java.sql.Types.INTEGER);
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy