org.datanucleus.store.rdbms.query.SQLQuery Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of datanucleus-rdbms Show documentation
Show all versions of datanucleus-rdbms Show documentation
Plugin for DataNucleus providing persistence to RDBMS datastores.
/**********************************************************************
Copyright (c) 2004 Erik Bengtson and others. All rights reserved.
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.
Contributors:
2004 Andy Jefferson - commented and localised
2004 Andy Jefferson - changed to allow for null candidate class
2005 Andy Jefferson - changed to use JDBC parameters
2005 Andy Jefferson - added timeout support
2005 Andy Jefferson - changed to use persistable candidate class
2005 Andy Jefferson - added checks on missing columns to compile step
2006 Andy Jefferson - implemented executeWithMap taking Integer args
...
**********************************************************************/
package org.datanucleus.store.rdbms.query;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.datanucleus.ClassLoaderResolver;
import org.datanucleus.ExecutionContext;
import org.datanucleus.PropertyNames;
import org.datanucleus.exceptions.ClassNotPersistableException;
import org.datanucleus.exceptions.NucleusDataStoreException;
import org.datanucleus.exceptions.NucleusUserException;
import org.datanucleus.metadata.AbstractClassMetaData;
import org.datanucleus.metadata.AbstractMemberMetaData;
import org.datanucleus.metadata.FieldPersistenceModifier;
import org.datanucleus.metadata.FieldRole;
import org.datanucleus.metadata.IdentityType;
import org.datanucleus.metadata.QueryResultMetaData;
import org.datanucleus.store.Extent;
import org.datanucleus.store.StoreManager;
import org.datanucleus.store.connection.ManagedConnection;
import org.datanucleus.store.connection.ManagedConnectionResourceListener;
import org.datanucleus.store.rdbms.adapter.DatastoreAdapter;
import org.datanucleus.store.rdbms.RDBMSStoreManager;
import org.datanucleus.store.rdbms.mapping.StatementClassMapping;
import org.datanucleus.store.rdbms.mapping.StatementMappingIndex;
import org.datanucleus.store.rdbms.mapping.java.JavaTypeMapping;
import org.datanucleus.store.rdbms.mapping.java.PersistableMapping;
import org.datanucleus.store.rdbms.table.DatastoreClass;
import org.datanucleus.store.query.Query;
import org.datanucleus.store.query.QueryInterruptedException;
import org.datanucleus.store.query.QueryResult;
import org.datanucleus.store.rdbms.SQLController;
import org.datanucleus.util.Localiser;
import org.datanucleus.util.NucleusLogger;
import org.datanucleus.util.StringUtils;
/**
* A Query using SQL.
* The majority of this has to be specified in the query filter itself.
* There are no variables/imports. Ordering/grouping is explicit in the query.
* Results
* Results from this type of query will be :-
*
* - resultClass - each row of the ResultSet is converted into an instance of the result class
* - candidateClass - each row of the ResultSet is converted into an instance of the candidate
* - Object[] - when no candidate or result class specified
* - Long - when the query is an INSERT/UPDATE/DELETE/MERGE
*
* Parameters
* Parameters to this type of query can be :-
*
* - Positional : The SQL statement includes "?" and the parameters are positional starting at 1 (just like in JDBC).
* - Numbered : The SQL statement includes "?1", "?2" etc (with numbers starting at 1) and the users input of parameters at execute is of the numbered parameters.
* - Named : The SQL statement includes ":myParam1", ":myOtherParam" etc and the users input of parameters via "executeWithMap" includes values for all specified names.
*
*/
public final class SQLQuery extends Query
{
private static final long serialVersionUID = -6820729188666657398L;
/** The statement that the user specified to the Query. */
protected transient final String inputSQL;
/** The actual SQL issued at execution time. */
protected transient String compiledSQL = null;
/** MetaData defining the results of the query. */
protected QueryResultMetaData resultMetaData = null;
/** State variable for the compilation state */
protected transient boolean isCompiled = false;
/** Mappings for the result of the query. */
protected transient StatementMappingIndex[] stmtMappings;
/**
* Constructor for a new query using the existing query as start point.
* @param storeMgr StoreManager for this query
* @param ec execution context
* @param query The existing query
*/
public SQLQuery(StoreManager storeMgr, ExecutionContext ec, SQLQuery query)
{
this(storeMgr, ec, query.inputSQL);
}
/**
* Constructs a new query instance.
* @param storeMgr StoreManager for this query
* @param ec execution context
*/
public SQLQuery(StoreManager storeMgr, ExecutionContext ec)
{
this(storeMgr, ec, (String)null);
}
/**
* Constructs a new query instance for the provided SQL query.
* @param storeMgr StoreManager for this query
* @param ec execution context
* @param queryString The SQL query string
*/
public SQLQuery(StoreManager storeMgr, ExecutionContext ec, String queryString)
{
super(storeMgr, ec);
candidateClass = null;
filter = null;
imports = null;
explicitVariables = null;
explicitParameters = null;
ordering = null;
if (queryString == null)
{
throw new NucleusUserException(Localiser.msg("059001"));
}
// Remove any end-of-line/tab chars for when user dumped the query in a text file with one word per line!
this.inputSQL = queryString.replace('\r', ' ').replace('\n', ' ').replace('\t', ' ').trim();
// Detect type of SQL statement
String firstToken = inputSQL.trim().substring(0,6).toUpperCase();
if (firstToken.equals("SELECT"))
{
type = SELECT;
}
else if (firstToken.equals("DELETE"))
{
type = BULK_DELETE;
unique = true;
}
else if (firstToken.equals("UPDATE") || firstToken.equals("INSERT") || firstToken.startsWith("MERGE"))
{
type = BULK_UPDATE;
unique = true;
}
else
{
// Stored procedures, others
type = OTHER;
unique = true;
}
if (ec.getApiAdapter().getName().equalsIgnoreCase("JDO"))
{
// Check for strict SQL where the API restricts the usage
boolean allowAllSyntax = ec.getNucleusContext().getConfiguration().getBooleanProperty(PropertyNames.PROPERTY_QUERY_SQL_ALLOWALL);
if (ec.getProperty(PropertyNames.PROPERTY_QUERY_SQL_ALLOWALL) != null)
{
allowAllSyntax = ec.getBooleanProperty(PropertyNames.PROPERTY_QUERY_SQL_ALLOWALL);
}
if (!allowAllSyntax)
{
// JDO spec [14.7] : SQL queries must start with SELECT
if (!firstToken.equals("SELECT"))
{
throw new NucleusUserException(Localiser.msg("059002", inputSQL));
}
}
}
}
public String getLanguage()
{
return "SQL";
}
/**
* Set the candidate Extent to query.
* This implementation always throws a NucleusUserException since this concept doesn't apply to SQL queries.
* @param pcs the Candidate Extent.
* @throws NucleusUserException Always thrown since method not applicable
*/
public void setCandidates(Extent pcs)
{
throw new NucleusUserException(Localiser.msg("059004"));
}
/**
* Set the candidate Collection to query.
* This implementation always throws a NucleusUserException since this concept doesn't apply to SQL queries.
* @param pcs the Candidate collection.
* @throws NucleusUserException Always thrown since method not applicable
*/
public void setCandidates(Collection pcs)
{
throw new NucleusUserException(Localiser.msg("059005"));
}
/**
* Set the result for the results. The application might want to get results
* from a query that are not instances of the candidate class. The results
* might be fields of persistent instances, instances of classes other than
* the candidate class, or aggregates of fields.
* This implementation always throws a NucleusUserException since this concept doesn't apply to SQL queries.
* @param result The result parameter consists of the optional keyword
* distinct followed by a commaseparated list of named result expressions or
* a result class specification.
* @throws NucleusUserException Always thrown.
*/
public void setResult(String result)
{
throw new NucleusUserException(Localiser.msg("059006"));
}
/**
* Method to set the MetaData defining the result.
* Setting this will unset the resultClass.
* @param qrmd Query Result MetaData
*/
public void setResultMetaData(QueryResultMetaData qrmd)
{
this.resultMetaData = qrmd;
super.setResultClass(null);
}
/**
* Set the result class for the results.
* Setting this will unset the resultMetaData.
* @param result_cls The result class
*/
public void setResultClass(Class result_cls)
{
super.setResultClass(result_cls);
this.resultMetaData = null;
}
/**
* Set the range of the results. Not applicable for SQL.
* This implementation always throws a NucleusUserException since this concept doesn't apply to SQL queries.
* @param fromIncl From element no (inclusive) to return
* @param toExcl To element no (exclusive) to return
* @throws NucleusUserException Always thrown.
*/
public void setRange(int fromIncl, int toExcl)
{
throw new NucleusUserException(Localiser.msg("059007"));
}
/**
* Method to set whether to use subclasses. Not applicable for SQL.
* This implementation always throws a NucleusUserException since this concept doesn't apply to SQL queries.
* @param subclasses Whether to use subclasses
* @throws NucleusUserException Always thrown.
*/
public void setSubclasses(boolean subclasses)
{
throw new NucleusUserException(Localiser.msg("059004"));
}
/**
* Set the filter for the query. Not applicable for SQL.
* This implementation always throws a NucleusUserException since this concept doesn't apply to SQL queries.
* @param filter the query filter.
* @throws NucleusUserException Always thrown since method not applicable
*/
public void setFilter(String filter)
{
throw new NucleusUserException(Localiser.msg("059008"));
}
/**
* Declare the unbound variables to be used in the query.
* This implementation always throws a NucleusUserException since this concept doesn't apply to SQL queries.
* @param variables the variables separated by semicolons.
* @throws NucleusUserException Always thrown since method not applicable
*/
public void declareExplicitVariables(String variables)
{
throw new NucleusUserException(Localiser.msg("059009"));
}
/**
* Declare the explicit parameters to be used in the query.
* This implementation always throws a NucleusUserException since this concept doesn't apply to SQL queries.
* @param parameters the parameters separated by semicolons.
* @exception NucleusUserException Always thrown.
*/
public void declareExplicitParameters(String parameters)
{
throw new NucleusUserException(Localiser.msg("059016"));
}
/**
* Set the import statements to be used to identify the fully qualified name of variables or parameters.
* This implementation always throws a NucleusUserException since this concept doesn't apply to SQL queries.
* @param imports import statements separated by semicolons.
* @exception NucleusUserException Always thrown.
*/
public void declareImports(String imports)
{
throw new NucleusUserException(Localiser.msg("059026"));
}
/**
* Set the grouping specification for the result Collection.
* This implementation always throws a NucleusUserException since this concept doesn't apply to SQL queries.
* @param grouping the grouping specification.
* @throws NucleusUserException Always thrown.
*/
public void setGrouping(String grouping)
{
throw new NucleusUserException(Localiser.msg("059010"));
}
/**
* Set the ordering specification for the result Collection.
* This implementation always throws a NucleusUserException since this concept doesn't apply to SQL queries.
* @param ordering the ordering specification.
* @throws NucleusUserException Always thrown.
*/
public void setOrdering(String ordering)
{
throw new NucleusUserException(Localiser.msg("059011"));
}
/**
* Execute the query to delete persistent objects.
* @param parameters the Map containing all of the parameters.
* @return the filtered QueryResult of the deleted objects.
*/
protected long performDeletePersistentAll(Map parameters)
{
throw new NucleusUserException(Localiser.msg("059000"));
}
/**
* Convenience method to return whether the query should return a single row.
* @return Whether it represents a unique row
*/
protected boolean shouldReturnSingleRow()
{
if (unique)
{
return true;
}
// An SQL query returns what it returns
return false;
}
/* (non-Javadoc)
* @see org.datanucleus.store.query.Query#processesRangeInDatastoreQuery()
*/
@Override
public boolean processesRangeInDatastoreQuery()
{
return true;
}
public boolean equals(Object obj)
{
if (obj == this)
{
return true;
}
if (!(obj instanceof SQLQuery) || !super.equals(obj))
{
return false;
}
return inputSQL.equals(((SQLQuery)obj).inputSQL);
}
public int hashCode()
{
return super.hashCode() ^ inputSQL.hashCode();
}
/**
* Utility to remove any previous compilation of this Query.
*/
protected void discardCompiled()
{
super.discardCompiled();
compiledSQL = null;
isCompiled = false;
stmtMappings = null;
}
/**
* Method to return if the query is compiled.
* @return Whether it is compiled
*/
protected boolean isCompiled()
{
return isCompiled;
}
/**
* Verify the elements of the query and provide a hint to the query to prepare and optimize an execution plan.
*/
public void compileInternal(Map parameterValues)
{
if (isCompiled)
{
return;
}
// Default to using the users SQL direct with no substitution of params etc
compiledSQL = inputSQL;
if (candidateClass != null && getType() == Query.SELECT)
{
// Perform any sanity checking of input for SELECT queries
RDBMSStoreManager storeMgr = (RDBMSStoreManager)getStoreManager();
ClassLoaderResolver clr = ec.getClassLoaderResolver();
AbstractClassMetaData cmd = ec.getMetaDataManager().getMetaDataForClass(candidateClass, clr);
if (cmd == null)
{
throw new ClassNotPersistableException(candidateClass.getName());
}
if (cmd.getPersistableSuperclass() != null)
{
// throw new PersistentSuperclassNotAllowedException(candidateClass.getName());
}
if (getResultClass() == null)
{
// Check the presence of the required columns (id, version, discriminator) in the candidate class
String selections = stripComments(compiledSQL.trim()).substring(7); // Skip "SELECT "
int fromStart = selections.indexOf("FROM");
if (fromStart == -1)
{
fromStart = selections.indexOf("from");
}
selections = selections.substring(0, fromStart).trim();
String[] selectedColumns = StringUtils.split(selections, ",");
if (selectedColumns == null || selectedColumns.length == 0)
{
throw new NucleusUserException(Localiser.msg("059003", compiledSQL));
}
if (selectedColumns.length == 1 && selectedColumns[0].trim().equals("*"))
{
// SQL Query using * so just end the checking since all possible columns will be selected
}
else
{
// Generate id column field information for later checking the id is present
DatastoreClass table = storeMgr.getDatastoreClass(candidateClass.getName(), clr);
PersistableMapping idMapping = (PersistableMapping)table.getIdMapping();
String[] idColNames = new String[idMapping.getNumberOfDatastoreMappings()];
boolean[] idColMissing = new boolean[idMapping.getNumberOfDatastoreMappings()];
for (int i=0;i 0)
{
// Allow for user specification of "XX.YY AS ZZ"
colName = colName.substring(colName.indexOf(" AS ")+4).trim();
}
else if (colName.indexOf(" as ") > 0)
{
// Allow for user specification of "XX.YY as ZZ"
colName = colName.substring(colName.indexOf(" as ")+4).trim();
}
if (candidateCmd.getIdentityType() == IdentityType.DATASTORE)
{
// Check for existence of id column, allowing for any RDBMS using quoted identifiers
if (SQLQuery.columnNamesAreTheSame(dba, idColNames[0], colName))
{
idColMissing[0] = false;
}
}
else if (candidateCmd.getIdentityType() == IdentityType.APPLICATION)
{
for (int j=0; j 0)
{
char prev = statement[charPos-1];
if (Character.isLetterOrDigit(prev))
{
// Some valid SQL can include colon, so ignore if the part just before is alphanumeric
}
else
{
colonParam = true;
paramPos++;
paramName = new StringBuilder();
}
}
else
{
colonParam = true;
paramPos++;
paramName = new StringBuilder();
}
}
else
{
if (paramName != null)
{
if (Character.isLetterOrDigit(c))
{
// Allow param names to include alphnumeric
paramName.append(c);
}
else
{
endOfParam = true;
}
}
}
if (paramName != null)
{
if (endOfParam)
{
// Replace the param by "?" in the runtime SQL
runtimeJdbcText.append('?');
runtimeJdbcText.append(c);
}
}
else
{
runtimeJdbcText.append(c);
}
charPos++;
complete = (charPos == compiledSQL.length());
if (complete && paramName != null && !endOfParam)
{
runtimeJdbcText.append('?');
}
if (paramName != null && (complete || endOfParam))
{
// Process the parameter
if (paramName.length() > 0)
{
// Named/Numbered parameter
if (colonParam)
{
expectedParams.add(paramName.toString());
}
else
{
try
{
Integer num = Integer.valueOf(paramName.toString());
expectedParams.add(num);
}
catch (NumberFormatException nfe)
{
throw new NucleusUserException("SQL query " + inputSQL + " contains an invalid parameter specification " + paramName.toString());
}
}
}
else
{
if (!colonParam)
{
// Positional parameter
expectedParams.add(Integer.valueOf(paramPos));
}
else
{
// Just a colon so ignore it
}
}
paramName = null;
}
}
compiledSQL = runtimeJdbcText.toString(); // Update the SQL that JDBC will receive to just have ? for a parameter
if (expectedParams.size() > 0 && params.isEmpty())
{
// We expect some parameters yet the user gives us none!
throw new NucleusUserException(Localiser.msg("059028", inputSQL, "" + expectedParams.size()));
}
// Build a Map of params with keys 1, 2, 3, etc representing the position in the runtime JDBC SQL
Map executeMap = new HashMap();
// Cycle through the expected params
paramPos = 1;
Iterator expectedParamIter = expectedParams.iterator();
while (expectedParamIter.hasNext())
{
Object key = expectedParamIter.next();
if (!params.containsKey(key))
{
// Expected parameter is not provided
throw new NucleusUserException(Localiser.msg("059030", inputSQL, "" + key));
}
executeMap.put(Integer.valueOf(paramPos), params.get(key));
paramNames.add("" + paramPos);
paramPos++;
}
parameterNames = (String[])paramNames.toArray(new String[paramNames.size()]);
return executeMap;
}
/**
* Method that will throw an {@link UnsupportedOperationException} if the query implementation doesn't
* support cancelling queries.
*/
protected void assertSupportsCancel()
{
// We support cancel via JDBC PreparedStatement.cancel();
}
/**
* Method to generate a ResultObjectFactory for converting rows of the provided ResultSet into
* instances of the candidate class. Populates stmtMappings.
* @param rs The ResultSet
* @return The ResultObjectFactory
* @throws SQLException Thrown if an error occurs processing the ResultSet
*/
protected ResultObjectFactory getResultObjectFactoryForCandidateClass(ResultSet rs)
throws SQLException
{
ClassLoaderResolver clr = ec.getClassLoaderResolver();
RDBMSStoreManager storeMgr = (RDBMSStoreManager) getStoreManager();
DatastoreAdapter dba = storeMgr.getDatastoreAdapter();
// Create an index listing for ALL (fetchable) fields in the result class.
final AbstractClassMetaData candidateCmd = ec.getMetaDataManager().getMetaDataForClass(candidateClass, clr);
int fieldCount = candidateCmd.getNoOfManagedMembers() + candidateCmd.getNoOfInheritedManagedMembers();
Map columnFieldNumberMap = new HashMap(); // Map of field numbers keyed by the column name
stmtMappings = new StatementMappingIndex[fieldCount];
DatastoreClass tbl = storeMgr.getDatastoreClass(candidateClass.getName(), clr);
for (int fieldNumber = 0; fieldNumber < fieldCount; ++fieldNumber)
{
AbstractMemberMetaData mmd = candidateCmd.getMetaDataForManagedMemberAtAbsolutePosition(fieldNumber);
String fieldName = mmd.getName();
Class fieldType = mmd.getType();
JavaTypeMapping m = null;
if (mmd.getPersistenceModifier() != FieldPersistenceModifier.NONE)
{
if (tbl != null)
{
// Get the field mapping from the candidate table
m = tbl.getMemberMapping(mmd);
}
else
{
// Fall back to generating a mapping for this type - does this ever happen?
m = storeMgr.getMappingManager().getMappingWithDatastoreMapping(fieldType, false, false, clr);
}
if (m.includeInFetchStatement())
{
// Set mapping for this field since it can potentially be returned from a fetch
String columnName = null;
if (mmd.getColumnMetaData() != null && mmd.getColumnMetaData().length > 0)
{
for (int colNum = 0;colNum= 0)
{
int[] exprIndices = null;
if (stmtMappings[fieldNumber].getColumnPositions() != null)
{
exprIndices = new int[stmtMappings[fieldNumber].getColumnPositions().length+1];
for (int i=0;i