/**********************************************************************
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 PersistenceCapable candidate class
2005 Andy Jefferson - added checks on missing columns to compile step
2006 Andy Jefferson - implemented executeWithMap taking Integer args
...
**********************************************************************/
package org.jpox.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.Map;
import java.util.StringTokenizer;
import org.jpox.ClassLoaderResolver;
import org.jpox.ManagedConnection;
import org.jpox.ManagedConnectionResourceListener;
import org.jpox.ObjectManager;
import org.jpox.exceptions.JPOXDataStoreException;
import org.jpox.exceptions.JPOXUserException;
import org.jpox.metadata.AbstractClassMetaData;
import org.jpox.metadata.AbstractMemberMetaData;
import org.jpox.metadata.FieldPersistenceModifier;
import org.jpox.metadata.FieldRole;
import org.jpox.metadata.IdentityType;
import org.jpox.store.mapped.DatastoreAdapter;
import org.jpox.store.mapped.DatastoreClass;
import org.jpox.store.mapped.MappedStoreManager;
import org.jpox.store.mapped.StatementExpressionIndex;
import org.jpox.store.mapped.expression.QueryExpression;
import org.jpox.store.mapped.mapping.DatastoreMapping;
import org.jpox.store.mapped.mapping.JavaTypeMapping;
import org.jpox.store.mapped.mapping.PersistenceCapableMapping;
import org.jpox.store.mapped.query.Evaluator;
import org.jpox.store.query.AbstractSQLQuery;
import org.jpox.store.query.Query;
import org.jpox.store.query.QueryCompiler;
import org.jpox.store.query.QueryResult;
import org.jpox.store.query.ResultObjectFactory;
import org.jpox.store.rdbms.RDBMSManager;
import org.jpox.store.rdbms.SQLController;
import org.jpox.util.JPOXLogger;
import org.jpox.util.Localiser;
/**
* A Query using SQL, and keeping to the JDO2 definition of a SQL query.
* 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.
* <h3>Results</h3>
* Results from this type of query will be :-
* <ul>
* <li><b>resultClass</b> - each row of the ResultSet is converted into an instance of the result class</li>
* <li><b>candidateClass</b> - each row of the ResultSet is converted into an instance of the candidate</li>
* <li><b>Object[]</b> - when no candidate or result class specified</li>
* <li><b>Long</b> - when the query is an INSERT/UPDATE/DELETE/MERGE</li>
* </ul>
* <h3>Parameters</h3>
* Parameters to this type of query can be :-
* <ul>
* <li><b>Positional</b> : The SQL statement includes "?" and the parameters are positional starting at 1
* (just like in JDBC).</li>
* <li><b>Numbered</b> : 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.</li>
* <li><b>Named</b> : The SQL statement includes ":myParam1", ":myOtherParam" etc and the users input of parameters
* via "executeWithMap" includes values for all specified names.</li>
* </ul>
*/
public final class SQLQuery extends AbstractSQLQuery
{
/** Localiser of messages. */
protected static final Localiser LOCALISER_RDBMS = Localiser.getInstance("org.jpox.store.rdbms.Localisation",
RDBMSManager.class.getClassLoader());
/**
* Constructor for a new query using the existing query.
* @param om ObjectManager
* @param query The existing query
*/
public SQLQuery(ObjectManager om, SQLQuery query)
{
super(om, query);
}
/**
* Constructs a new query instance having the same criteria as the given query.
* @param om The ObjectManager
*/
public SQLQuery(ObjectManager om)
{
super(om, (String)null);
}
/**
* Constructs a new query instance having the same criteria as the given query.
* @param om The ObjectManager
* @param sql_text The SQL query string
*/
public SQLQuery(ObjectManager om, String sql_text)
{
super(om, sql_text);
String firstToken = new StringTokenizer(sql_text, " ").nextToken();
if (firstToken.equalsIgnoreCase("INSERT") ||
firstToken.equalsIgnoreCase("UPDATE") ||
firstToken.equalsIgnoreCase("DELETE") ||
firstToken.equalsIgnoreCase("MERGE"))
{
type = BULK_UPDATE; // Could be BULK_DELETE but dont distinguish for SQL
unique = true;
}
if (!om.getOMFContext().getPersistenceConfiguration().getBooleanProperty("org.jpox.rdbms.sql.allowAllSQLStatements"))
{
// JDO spec [14.7]. SQL queries must start with SELECT/select
if (!firstToken.equals("SELECT") && !firstToken.startsWith("select"))
{
throw new JPOXUserException(LOCALISER.msg("059002", inputSQL));
}
}
}
/**
* Equality operator.
* @param obj The object to compare against
* @return Whether they are equal
**/
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);
}
/**
* Verify the elements of the query and provide a hint to the query to prepare and optimize an execution plan.
*/
public void compileInternal(boolean forExecute, Map parameterValues)
{
if (isCompiled)
{
return;
}
if (forExecute)
{
SQLQueryCompiler c = new SQLQueryCompiler(this, getParsedImports(), parameterValues);
compiledSQL = (String)c.compile(QueryCompiler.COMPILE_EXECUTION);
if (JPOXLogger.QUERY.isDebugEnabled())
{
JPOXLogger.QUERY.debug(LOCALISER.msg("059012", compiledSQL));
}
}
else
{
SQLQueryCompiler c = new SQLQueryCompiler(this, getParsedImports(), parameterValues);
compiledSQL = (String)c.compile(QueryCompiler.COMPILE_SYNTAX);
if (JPOXLogger.QUERY.isDebugEnabled())
{
JPOXLogger.QUERY.debug(LOCALISER.msg("059012", compiledSQL));
}
}
isCompiled = true;
}
/**
* Execute the query and return the result.
* For a SELECT query this will be the QueryResult. For an UPDATE/DELETE it will be the row count for
* the update statement.
* @param parameters the Map containing all of the parameters (positional parameters)
* @return the result of the query
*/
protected Object performExecute(Map parameters)
{
if (parameters.size() != (parameterNames != null ? parameterNames.length : 0))
{
throw new JPOXUserException(LOCALISER_RDBMS.msg("059019",
"" + parameterNames.length,"" + parameters.size()));
}
if (type != SELECT)
{
// Update statement (INSERT/UPDATE/DELETE/MERGE)
Collection qr = null;
try
{
RDBMSManager storeMgr = (RDBMSManager)om.getStoreManager();
ManagedConnection mconn = storeMgr.getConnection(om);
SQLController sqlControl = storeMgr.getSQLController();
try
{
PreparedStatement ps = sqlControl.getStatementForUpdate(mconn, compiledSQL, false);
try
{
// Set the values of any parameters
if (parameters != null)
{
for (int i=0;i<parameters.size();i++)
{
Object obj = parameters.get(new Integer(i+1));
ps.setObject((i+1), obj);
}
}
// Execute the update statement
int[] rcs = sqlControl.executeStatementUpdate(mconn, compiledSQL, ps, true);
qr = new ArrayList();
qr.add(new Long(rcs[0])); // Return a single Long with the number of records updated
}
finally
{
sqlControl.closeStatement(mconn, ps);
}
}
finally
{
mconn.release();
}
}
catch (SQLException e)
{
throw new JPOXDataStoreException(LOCALISER.msg("059025", compiledSQL), e);
}
return qr;
}
else
{
// Query statement (SELECT, stored-procedure)
Evaluator eval = new SQLQueryEvaluator(this, parameters);
QueryResult qr = (QueryResult)eval.evaluate(null);
queryResults.add(qr);
return qr;
}
}
class SQLQueryEvaluator extends SQLEvaluator
{
Map parameters;
StatementExpressionIndex[] statementExpressionIndex;
public SQLQueryEvaluator(Query query, Map parameters)
{
super(query, false, null, null);
this.parameters = parameters;
}
public Object evaluate(QueryExpression queryStmt)
{
QueryResult qr = null;
try
{
RDBMSManager storeMgr = (RDBMSManager)om.getStoreManager();
ManagedConnection mconn = storeMgr.getConnection(om);
SQLController sqlControl = storeMgr.getSQLController();
try
{
PreparedStatement ps = getStatement(mconn, compiledSQL);
try
{
// Set the values of any parameters
if (parameters != null)
{
for (int i=0;i<parameters.size();i++)
{
Object obj = parameters.get(new Integer(i+1));
ps.setObject((i+1), obj);
}
}
// Apply any user-specified constraints over timeouts and ResultSet
prepareStatementForExecution(ps);
// Execute the query
ResultSet rs = sqlControl.executeStatementQuery(mconn, compiledSQL, ps);
try
{
// Generate a ResultObjectFactory
if (resultMetaData != null)
{
// Each row of the ResultSet is defined by MetaData
rof = new ResultMetaDataROF(resultMetaData);
}
else if (resultClass != null || candidateClass == null)
{
// Each row of the ResultSet is either an instance of resultClass, or Object[]
rof = getResultObjectFactoryForNoCandidateClass(rs, resultClass);
}
else
{
// Each row of the ResultSet is an instance of the candidate class
rof = getResultObjectFactoryForCandidateClass(rs);
}
// Return the associated type of results depending on whether scrollable or not
if (getResultSetType().equals("scroll-insensitive") ||
getResultSetType().equals("scroll-sensitive"))
{
qr = new ScrollableQueryResult(null, query, rof, rs, null);
}
else
{
qr = new ForwardQueryResult(null, query, rof, rs, null);
}
final QueryResult qr1 = qr;
final ManagedConnection mconn1 = mconn;
mconn.addListener(new ManagedConnectionResourceListener()
{
public void managedConnectionFlushed()
{
qr1.disconnect();
}
public void managedConnectionPreClose(){}
public void managedConnectionPostClose(){}
public void resourcePostClose()
{
mconn1.removeListener(this);
}
});
}
finally
{
if (qr == null)
{
rs.close();
}
}
}
finally
{
if (qr == null)
{
sqlControl.closeStatement(mconn, ps);
}
}
}
finally
{
mconn.release();
}
}
catch (SQLException e)
{
throw new JPOXDataStoreException(LOCALISER.msg("059025", compiledSQL), e);
}
return qr;
}
/**
* Method to generate a ResultObjectFactory for converting rows of the provided ResultSet into
* instances of the candidate class.
* @param rs The ResultSet
* @return The ResultObjectFactory
* @throws SQLException Thrown if an error occurs processing the ResultSet
*/
private ResultObjectFactory getResultObjectFactoryForCandidateClass(ResultSet rs)
throws SQLException
{
ClassLoaderResolver clr = om.getClassLoaderResolver();
MappedStoreManager storeMgr = (MappedStoreManager)om.getStoreManager();
DatastoreAdapter dba = storeMgr.getDatastoreAdapter();
// Create an index listing for ALL (fetchable) fields in the result class.
final AbstractClassMetaData candidateCmd = om.getMetaDataManager().getMetaDataForClass(candidateClass, clr);
int fieldCount = candidateCmd.getNoOfManagedMembers() + candidateCmd.getNoOfInheritedManagedMembers();
Map columnFieldNumberMap = new HashMap(); // Map of field numbers keyed by the column name
statementExpressionIndex = new StatementExpressionIndex[fieldCount];
DatastoreClass tbl = storeMgr.getDatastoreClass(candidateClass.getName(), clr);
for (int fieldNumber = 0; fieldNumber < fieldCount; ++fieldNumber)
{
statementExpressionIndex[fieldNumber] = new StatementExpressionIndex();
AbstractMemberMetaData fmd = candidateCmd.getMetaDataForManagedMemberAtAbsolutePosition(fieldNumber);
String fieldName = fmd.getName();
Class fieldType = fmd.getType();
if (fmd.getPersistenceModifier() != FieldPersistenceModifier.NONE)
{
JavaTypeMapping m = null;
if (tbl != null)
{
// Get the field mapping from the candidate table
m = tbl.getFieldMapping(fmd);
}
else
{
// Fall back to generating a mapping for this type - does this ever happen?
m = dba.getMapping(fieldType, storeMgr, clr);
}
if (m.includeInFetchStatement())
{
// Set mapping for this field since it can potentially be returned from a fetch
statementExpressionIndex[fieldNumber].setMapping(m);
String columnName = null;
if (fmd.getColumnMetaData() != null && fmd.getColumnMetaData().length > 0)
{
for (int colNum = 0;colNum<fmd.getColumnMetaData().length;colNum++)
{
columnName = fmd.getColumnMetaData()[colNum].getName();
columnFieldNumberMap.put(columnName, new Integer(fieldNumber));
}
}
else
{
columnName = storeMgr.getIdentifierFactory().newDatastoreFieldIdentifier(
fieldName, om.getOMFContext().getTypeManager().isDefaultEmbeddedType(fieldType),
FieldRole.ROLE_NONE).getIdentifier();
columnFieldNumberMap.put(columnName, new Integer(fieldNumber));
}
}
else
{
// Dont put anything in this position (field has no col in the result set)
}
}
else
{
// Dont put anything in this position (field has no col in the result set)
}
}
if (columnFieldNumberMap.size() == 0)
{
// None of the fields in the class have columns in the datastore table!
// TODO Localise the message
throw new JPOXUserException("SQL query class has no persistent fields in the SELECT : " +
candidateClass.getName()).setFatal();
}
// Generate id column field information for later checking the id is present
DatastoreClass table = storeMgr.getDatastoreClass(candidateClass.getName(), clr);
PersistenceCapableMapping idMapping = (PersistenceCapableMapping)table.getIDMapping();
String[] idColNames = new String[idMapping.getNumberOfDatastoreFields()];
boolean[] idColMissing = new boolean[idMapping.getNumberOfDatastoreFields()];
for (int i=0;i<idMapping.getNumberOfDatastoreFields();i++)
{
DatastoreMapping m = idMapping.getDataStoreMapping(i);
idColNames[i] = m.getDatastoreField().getIdentifier().toString();
idColMissing[i] = true;
}
// Generate discriminator/version information for later checking they are present
String discriminatorColName = table.getDiscriminatorMapping(false) != null ?
table.getDiscriminatorMapping(false).getDataStoreMapping(0).getDatastoreField().getIdentifier().toString() : null;
String versionColName = table.getVersionMapping(false) != null ?
table.getVersionMapping(false).getDataStoreMapping(0).getDatastoreField().getIdentifier().toString() : null;
boolean discrimMissing = (discriminatorColName != null);
boolean versionMissing = true;
if (versionColName == null)
{
versionMissing = false;
}
// Go through the fields of the ResultSet and map to the required fields in the candidate
// Note that we check the existence of the columns again here even though they were checked at compilation
// TODO This could be removed from here since its now done at compile time
ResultSetMetaData rsmd = rs.getMetaData();
HashSet remainingColumnNames = new HashSet(columnFieldNumberMap.size());
int colCount = rsmd.getColumnCount();
int[] datastoreIndex = null;
int[] versionIndex = null;
int[] matchedFieldNumbers = new int[colCount];
int fieldNumberPosition = 0;
for (int colNum=1; colNum<=colCount; ++colNum)
{
String colName = rsmd.getColumnName(colNum);
int fieldNumber = -1;
Integer fieldNum = (Integer)columnFieldNumberMap.get(colName);
if (fieldNum != null)
{
fieldNumber = fieldNum.intValue();
}
if (fieldNumber >= 0)
{
int[] exprIndices = null;
if (statementExpressionIndex[fieldNumber].getExpressionIndex() != null)
{
exprIndices = new int[statementExpressionIndex[fieldNumber].getExpressionIndex().length+1];
for (int i=0;i<statementExpressionIndex[fieldNumber].getExpressionIndex().length;i++)
{
exprIndices[i] = statementExpressionIndex[fieldNumber].getExpressionIndex()[i];
}
exprIndices[exprIndices.length-1] = colNum;
}
else
{
exprIndices = new int[] {colNum};
}
statementExpressionIndex[fieldNumber].setExpressionIndex(exprIndices);
remainingColumnNames.remove(colName);
matchedFieldNumbers[fieldNumberPosition++] = fieldNumber;
}
if (versionColName != null && colName.equals(versionColName))
{
// Identify the location of the version column
versionIndex = new int[1];
versionIndex[0] = colNum;
versionMissing = false;
}
if (candidateCmd.getIdentityType() == IdentityType.DATASTORE)
{
// Check for existence of id column, allowing for any RDBMS using quoted identifiers
if (columnNamesAreTheSame(dba, idColNames[0], colName))
{
datastoreIndex = new int[1];
datastoreIndex[0] = colNum;
idColMissing[0] = false;
}
}
else if (candidateCmd.getIdentityType() == IdentityType.APPLICATION)
{
for (int j=0;j<idColNames.length;j++)
{
// Check for existence of id column, allowing for any RDBMS using quoted identifiers
if (columnNamesAreTheSame(dba, idColNames[j], colName))
{
idColMissing[j] = false;
}
}
}
if (discrimMissing && columnNamesAreTheSame(dba, discriminatorColName, colName))
{
discrimMissing = false;
}
else if (versionMissing && columnNamesAreTheSame(dba, versionColName, colName))
{
versionMissing = false;
}
}
// Set the field numbers found to match what we really have
int[] fieldNumbers = new int[fieldNumberPosition];
for (int i=0;i<fieldNumberPosition;i++)
{
fieldNumbers[i] = matchedFieldNumbers[i];
}
if (discrimMissing)
{
throw new JPOXUserException(LOCALISER_RDBMS.msg("059014",
inputSQL, candidateClass.getName(), discriminatorColName));
}
if (versionMissing)
{
throw new JPOXUserException(LOCALISER_RDBMS.msg("059015",
inputSQL, candidateClass.getName(), versionColName));
}
for (int i=0;i<idColMissing.length;i++)
{
if (idColMissing[i])
{
throw new JPOXUserException(LOCALISER_RDBMS.msg("059013",
inputSQL, candidateClass.getName(), idColNames[i]));
}
}
return new PersistentIDROF(table, fieldNumbers, candidateCmd,
statementExpressionIndex, datastoreIndex, versionIndex, ignoreCache,
false, false, getFetchPlan(), query.getCandidateClass());
}
}
/**
* Convenience method to compare two column names.
* Allows for case sensitivity issues, and for database added quoting.
* @param dba Datastore adapter
* @param name1 The first name (from the datastore)
* @param name2 The second name (from the user SQL statement)
* @return Whether they are the same
*/
public static boolean columnNamesAreTheSame(DatastoreAdapter dba, String name1, String name2)
{
if (name1.equalsIgnoreCase(name2) ||
name1.equalsIgnoreCase(dba.getIdentifierQuoteString() + name2 + dba.getIdentifierQuoteString()))
{
return true;
}
return false;
}
}