plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/SQLQueryHandler.java - gerrit/cdo/cdo - Git at Google

 /*
  * Copyright (c) 2009-2013, 2015 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();
   }
 }
	/*
	* Copyright (c) 2009-2013, 2015 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();
	}
	}