com.novartis.opensource.yada.adaptor.OracleAdaptor Maven / Gradle / Ivy

Go to download
/**
 * 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.PreparedStatement;
import java.sql.SQLException;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.log4j.Logger;
import org.json.JSONObject;

import com.novartis.opensource.yada.YADARequest;

/**
 * JDBCAdaptor class for execution of Oracle SQL queries.  This is a subclass of {@link JDBCAdaptor} but only 
 * overrides the constructors.  {@link JDBCAdaptor}, thought abstract, was initially created as an
 * Oracle adaptor, and by and large, contains generic code.
 * 
 * @author David Varon
 *
 */
public class OracleAdaptor extends JDBCAdaptor {
	/**
   * Local logger handle
   */
	private static Logger l = Logger.getLogger(OracleAdaptor.class);
	/**
	 * Default constructor
	 */
	public OracleAdaptor() {
		super();
		l.debug("Initializing");
	}
	
	/**
	 * Preferred "YADARequest" constructor
	 * @param yadaReq YADA request configuration
	 */
	public OracleAdaptor(YADARequest yadaReq)
	{
		super(yadaReq);
	}
	
	/**
	 * Create a YADA SELECT statement from {@code core} wrapping it in two subqueries to account for pagination, sorting, and 
	 * filtering with a where clause. 
	 * @param core the code to wrap
	 * @param sortKey the column on which to sort
	 * @param sortOrder the sort order 'asc' or 'desc'
	 * @param firstRow the lower limiter of the result set
	 * @param pageSize the number of rows to return
	 * @param filters a JSON object containing the WHERE criteria 
	 * @return StringBuffer containing newly wrapped core sql, with pagination, filtering, and sorting, as desired
	 * @throws YADAAdaptorException when the query filters can't be converted into a WHERE clause
	 */
	@Override
	public StringBuffer buildSelect(String core, String sortKey, String sortOrder, int firstRow, int pageSize, JSONObject filters) throws YADAAdaptorException 
	{
		StringBuffer sql = new StringBuffer(SQL_SELECT_ALL);
		sql.append(SQL_FROM);
		sql.append(OPEN_PAREN);
		sql.append(NEWLINE+SPACE+SPACE);
		sql.append(SQL_SELECT);
		sql.append(SQL_CORE_ALIAS+"."+SQL_ALL);
		sql.append(COMMA+SPACE);
		sql.append(ROWNUM+SPACE+ROWNUM_ALIAS); /*  THIS IS AN ORACLE SPECIFIC LINE */
		sql.append(NEWLINE+SPACE+SPACE);
		sql.append(SQL_FROM);
		sql.append(SPACE+OPEN_PAREN+NEWLINE);
		sql.append(core);
		if (null != sortKey && !sortKey.equals(EMPTY))
		{
			if (core.toUpperCase().indexOf(SQL_ORDER_BY) == -1)
			{
				sql.append(NEWLINE);
				sql.append(SQL_ORDER_BY);
			}
			else
			{
				sql.append(COMMA+SPACE);
			}
			sql.append(sortKey);
			if (null != sortOrder && !sortOrder.equals(EMPTY))
			{
				 sql.append(SPACE + sortOrder);
			}
		}
		sql.append(NEWLINE+"       "+CLOSE_PAREN+SPACE);
		sql.append(SQL_CORE_ALIAS);
		sql.append(NEWLINE+SPACE+SPACE);
		sql.append(SQL_WHERE);
		sql.append(ROWNUM+SPACE+"< " + String.valueOf(firstRow+pageSize)); /*  THIS IS AN ORACLE SPECIFIC LINE */
		if (filters != null)
		{
			sql.append(NEWLINE+"    ");
			sql.append(SQL_AND);
			sql.append(NEWLINE+"    "+OPEN_PAREN+NEWLINE);
			sql.append(getQueryFilters(false));
			sql.append(NEWLINE+CLOSE_PAREN+NEWLINE);
		}
		sql.append(CLOSE_PAREN+SPACE+SQL_WRAPPER_ALIAS);
		sql.append(NEWLINE);
		sql.append(SQL_WHERE);
		sql.append(ROWNUM_ALIAS+SPACE+">="+SPACE+firstRow); /*  THIS IS AN ORACLE SPECIFIC LINE */
		return sql;
	}
	
	/**
	 * Returns a StringBuffer with core sql + filters, wrapped in an outer sql COUNT(*) query.  
	 * This is typically used in pagination scenarios to return the total number of records 
	 * returnable by a query when only a subset are requested by the application.
	 * 
	 * @param core the SQL to wrap in an outer count(*) query
	 * @param filters a JSON object containing the WHERE criteria
	 * @return StringBuffer of wrapped core sql including filters
	 * @throws YADAAdaptorException when the query filters can't be converted into a WHERE clause
	 */
	@Override
	public StringBuffer buildSelectCount(String core, JSONObject filters) throws YADAAdaptorException
	{
		boolean hasFilter = false;
		StringBuffer sql = new StringBuffer(SQL_SELECT);
		sql.append(SQL_COUNT_ALL);
		sql.append(SQL_COUNT);
		sql.append(NEWLINE);
		sql.append(SQL_FROM);
		sql.append(SPACE+SPACE+OPEN_PAREN+NEWLINE);
		sql.append(core);
		sql.append(NEWLINE+"       "+CLOSE_PAREN+SPACE);
		sql.append(SQL_CORE_ALIAS);
		if (filters != null)
		{
			sql.append(NEWLINE+SPACE+SPACE);
			sql.append(SQL_WHERE);
			sql.append(getQueryFilters(false));
			hasFilter = true;
		}
		if (this.yadaReq.getViewLimit() > -1)
		{
			if (hasFilter) 
			{ 
				sql.append(NEWLINE+SPACE+SPACE);
				sql.append(SQL_AND); 
			} 
			else 
			{ 
				sql.append(NEWLINE);
				sql.append(SQL_WHERE); 
			}
			
			sql.append("ROWNUM <=" + this.yadaReq.getViewLimit()); /*  THIS IS AN ORACLE SPECIFIC LINE */
		}
		return sql;
	}
	
	/**
   * Enables checking for {@link JDBCAdaptor#ORACLE_TIMESTAMP_FMT} if {@code val} does not conform to {@link JDBCAdaptor#STANDARD_TIMESTAMP_FMT}
   * @since 0.5.1.1
   */
  @Override
  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.DATE);
    }
    else
    {
      SimpleDateFormat sdf     = new SimpleDateFormat(STANDARD_TIMESTAMP_FMT);
      ParsePosition    pp      = new ParsePosition(0);
      Date             dateVal = sdf.parse(val,pp);
      if(dateVal == null)
      {
        sdf     = new SimpleDateFormat(ORACLE_TIMESTAMP_FMT);
        pp      = new ParsePosition(0);
        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);
      }
    }
  }
	
	/**
	 * Enables checking for {@link JDBCAdaptor#ORACLE_DATE_FMT} if {@code val} does not conform to {@link JDBCAdaptor#STANDARD_DATE_FMT}
	 * @since 0.5.1.1
	 */
	@Override
	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)
      {
        sdf     = new SimpleDateFormat(ORACLE_DATE_FMT);
        pp      = new ParsePosition(0);
        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);
      }
    }
  }
}