/*
* Copyright (c) 2009-2013, 2015, 2016 Eike Stepper (Berlin, Germany) and others.
* All rights reserved. This program and the accompanying materials
* are made available under the terms of the Eclipse Public License v1.0
* which accompanies this distribution, and is available at
* http://www.eclipse.org/legal/epl-v10.html
*
* Contributors:
* Kai Schlamp - initial API and implementation
* Eike Stepper - maintenance
* Kai Schlamp - Bug 284812: [DB] Query non CDO object fails
* Erdal Karaca - added cdoObjectResultAsMap parameter to return Map<String,Object> in result
*/
package org.eclipse.emf.cdo.server.internal.db;
import org.eclipse.emf.cdo.common.id.CDOID;
import org.eclipse.emf.cdo.common.util.CDOQueryInfo;
import org.eclipse.emf.cdo.server.IQueryContext;
import org.eclipse.emf.cdo.server.IQueryHandler;
import org.eclipse.emf.cdo.server.db.IIDHandler;
import org.eclipse.net4j.db.DBException;
import org.eclipse.net4j.db.DBUtil;
import org.eclipse.net4j.db.IDBPreparedStatement;
import org.eclipse.net4j.db.IDBPreparedStatement.ReuseProbability;
import java.sql.Clob;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* Implements server side SQL query execution.
*
* @author Kai Schlamp
*/
public class SQLQueryHandler implements IQueryHandler
{
public static final String QUERY_LANGUAGE = "sql";
public static final String FIRST_RESULT = "firstResult";
public static final String CDO_OBJECT_QUERY = "cdoObjectQuery";
public static final String MAP_QUERY = "mapQuery";
public static final String QUERY_STATEMENT = "queryStatement";
private DBStoreAccessor accessor;
public SQLQueryHandler(DBStoreAccessor storeAccessor)
{
accessor = storeAccessor;
}
public DBStoreAccessor getStoreAccessor()
{
return accessor;
}
/**
* Executes SQL queries. Gets the connection from {@link DBStoreAccessor}, creates a SQL query and sets the parameters
* taken from the {@link CDOQueryInfo#getParameters()}.
* <p>
* Takes into account the {@link CDOQueryInfo#getMaxResults()} and the {@link SQLQueryHandler#FIRST_RESULT} (numbered
* from 0) values for paging.
* <p>
* By default (parameter {@link SQLQueryHandler#CDO_OBJECT_QUERY} == true) a query for CDO Objects is exectued. The
* SQL query must return the CDO ID in the first column for this to work. If you set
* {@link SQLQueryHandler#CDO_OBJECT_QUERY} parameter to false, the value of the first column of a row itself is
* returned.
* <p>
* By default (parameter {@link SQLQueryHandler#QUERY_STATEMENT} == true) query statements are executed. Set this
* parameter to false for update/DDL statements.
* <p>
* It is possible to use variables inside the SQL string with ":" as prefix. E.g.
* "SELECT cdo_id FROM Company WHERE name LIKE :name". The value must then be set by using a parameter. E.g.
* query.setParameter(":name", "Foo%");
*
* @param info
* the object containing the query and parameters
* @param context
* the query results are placed in the context
* @see IQueryHandler#executeQuery(CDOQueryInfo, IQueryContext)
*/
public void executeQuery(CDOQueryInfo info, IQueryContext context)
{
String language = info.getQueryLanguage();
if (!QUERY_LANGUAGE.equals(language))
{
throw new IllegalArgumentException("Unsupported query language: " + language);
}
HashMap<String, List<Integer>> paramMap = new HashMap<String, List<Integer>>();
String query = parse(info.getQueryString(), paramMap);
int firstResult = -1;
boolean queryStatement = true;
boolean objectQuery = true;
boolean mapQuery = false;
IIDHandler idHandler = accessor.getStore().getIDHandler();
IDBPreparedStatement stmt = accessor.getDBConnection().prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY,
ReuseProbability.MEDIUM);
ResultSet resultSet = null;
try
{
for (String key : info.getParameters().keySet())
{
if (FIRST_RESULT.equalsIgnoreCase(key))
{
final Object o = info.getParameters().get(key);
if (o != null)
{
try
{
firstResult = (Integer)o;
}
catch (ClassCastException ex)
{
throw new IllegalArgumentException("Parameter " + FIRST_RESULT + " must be an integer but it is a " + o + " class " + o.getClass().getName(), ex);
}
}
}
else if (QUERY_STATEMENT.equalsIgnoreCase(key))
{
final Object o = info.getParameters().get(key);
if (o != null)
{
try
{
queryStatement = (Boolean)o;
}
catch (ClassCastException ex)
{
throw new IllegalArgumentException("Parameter " + QUERY_STATEMENT + " must be an boolean but it is a " + o + " class " + o.getClass().getName(),
ex);
}
}
}
else if (CDO_OBJECT_QUERY.equalsIgnoreCase(key))
{
final Object o = info.getParameters().get(key);
if (o != null)
{
try
{
objectQuery = (Boolean)o;
}
catch (ClassCastException ex)
{
throw new IllegalArgumentException("Parameter " + CDO_OBJECT_QUERY + " must be a boolean but it is a " + o + " class " + o.getClass().getName(),
ex);
}
}
}
else if (MAP_QUERY.equalsIgnoreCase(key))
{
final Object o = info.getParameters().get(key);
if (o != null)
{
try
{
mapQuery = (Boolean)o;
}
catch (ClassCastException ex)
{
throw new IllegalArgumentException("Parameter " + MAP_QUERY + " must be a boolean but it is a " + o + " class " + o.getClass().getName(), ex);
}
}
}
else
{
if (!paramMap.containsKey(key) || paramMap.get(key) == null)
{
throw new IllegalArgumentException("No parameter value found for named parameter " + key);
}
Integer[] indexes = paramMap.get(key).toArray(new Integer[0]);
for (int i = 0; i < indexes.length; i++)
{
Object parameter = info.getParameters().get(key);
// parameter = convertToSQL(parameter);
stmt.setObject(indexes[i], parameter);
}
}
}
if (queryStatement)
{
resultSet = stmt.executeQuery();
if (firstResult > -1)
{
resultSet.absolute(1 + firstResult);
}
String[] columnNames = null;
if (mapQuery)
{
columnNames = new String[resultSet.getMetaData().getColumnCount()];
for (int i = 1; i <= columnNames.length; i++)
{
columnNames[i - 1] = resultSet.getMetaData().getColumnName(i);
}
}
int maxResults = info.getMaxResults();
int counter = 0;
while (firstResult > -1 || resultSet.next())
{
if (maxResults != CDOQueryInfo.UNLIMITED_RESULTS && counter++ >= maxResults)
{
break;
}
if (objectQuery)
{
CDOID result = idHandler.getCDOID(resultSet, 1);
context.addResult(result);
}
else
{
int columnCount = resultSet.getMetaData().getColumnCount();
if (columnCount == 1)
{
Object result = convertFromSQL(resultSet.getObject(1));
context.addResult(mapQuery ? toMap(columnNames, new Object[] { result }) : result);
}
else
{
Object[] results = new Object[columnCount];
for (int i = 0; i < columnCount; i++)
{
results[i] = convertFromSQL(resultSet.getObject(i + 1));
}
context.addResult(mapQuery ? toMap(columnNames, results) : results);
}
}
}
}
else
{
int result = stmt.executeUpdate();
context.addResult(result);
}
}
catch (SQLException ex)
{
throw new DBException("Problem while executing SQL query: " + query, ex);
}
finally
{
DBUtil.close(resultSet);
DBUtil.close(stmt);
}
}
@SuppressWarnings("unused")
private Object convertToSQL(Object value)
{
if (value instanceof java.util.Date)
{
java.util.Date date = (java.util.Date)value;
value = new java.sql.Date(date.getTime());
}
return value;
}
private Object convertFromSQL(Object value)
{
// Conversion of java.sql.Date not needed in this direction
if (value instanceof Clob)
{
Clob clob = (Clob)value;
try
{
value = clob.getSubString(1, (int)clob.length());
}
catch (SQLException ex)
{
throw new DBException("Could not extract CLOB value", ex);
}
}
return value;
}
private Map<String, Object> toMap(String[] columnNames, Object[] results)
{
Map<String, Object> ret = new HashMap<String, Object>();
for (int i = 0; i < columnNames.length; i++)
{
String columnName = columnNames[i];
ret.put(columnName, results[i]);
}
return ret;
}
private String parse(String query, Map<String, List<Integer>> paramMap)
{
int length = query.length();
StringBuilder builder = new StringBuilder(length);
boolean inSingleQuote = false;
boolean inDoubleQuote = false;
int index = 1;
for (int i = 0; i < length; i++)
{
char c = query.charAt(i);
if (inSingleQuote)
{
if (c == '\'')
{
inSingleQuote = false;
}
}
else if (inDoubleQuote)
{
if (c == '"')
{
inDoubleQuote = false;
}
}
else
{
if (c == '\'')
{
inSingleQuote = true;
}
else if (c == '"')
{
inDoubleQuote = true;
}
else if (c == ':' && i + 1 < length && Character.isJavaIdentifierStart(query.charAt(i + 1)))
{
int j = i + 2;
while (j < length && Character.isJavaIdentifierPart(query.charAt(j)))
{
j++;
}
String name = query.substring(i + 1, j);
c = '?';
i += name.length();
List<Integer> indexList = paramMap.get(name);
if (indexList == null)
{
indexList = new ArrayList<Integer>();
paramMap.put(name, indexList);
}
indexList.add(new Integer(index));
index++;
}
}
builder.append(c);
}
return builder.toString();
}
}
