Examples of org.datanucleus.store.rdbms.sql.SQLStatement

org.datanucleus.store.rdbms.sql.SQLStatement
Class providing an API for generating SQL statements. Caller should create the SQLStatement object and (optionally) call setClassLoaderResolver() to set any class loading restriction. Then the caller builds up the statement using the various methods, and accesses the SQL statement using getStatement(). Generated statement is of the form :-
```
 SELECT {expr}, {expr}, ... FROM {tblExpr} [joinInfo {tblExpr} ON ...] ... WHERE {boolExpr} [AND|OR] {boolExpr} ... GROUP BY {expr}, {expr} HAVING {boolExpr} ORDER BY {expr} [ASC|DESC], {expr} [ASC|DESC], ... 
```
and also supports UNIONs between SQLStatements, and having sub-queries of other SQLStatements. Alternatively, for an UPDATE,
```
 UPDATE {tbl} SET {expr}={val}, {expr}={val}, ... WHERE {boolExpr} [AND|OR] {boolExpr} ... 
```
The generated SQL is cached. Any use of a mutating method, changing the composition of the statement will clear the cached SQL, and it will be regenerated when
```
getStatement
```
is called next.
Table Groups
When tables are registered in the statement they are split into "table groups". A table group is, in simple terms, an object in the query. If a table has a super-table and a field of the object is selected that is in the super-table then the super-table is added to the table group. If there is a join to a related object then the table of this object will be put in a new table group. So the same datastore table can appear multiple times in the statement, each time for a different object.
Table Aliases
All methods that cause a new SQLTable to be created also allow specification of the table alias in the statement. Where the alias is not provided then we use a table "namer" (definable on the plugin-point "org.datanucleus.store.rdbms.sql_tablenamer"). The table namer can define names simply based on the table number, or based on table group and the number of tables in the group etc etc. To select a particular table "namer", set the extension "datanucleus.sqlTableNamingStrategy" to the key of the namer plugin. The default is "alpha-scheme" which bases table names on the group and number in that group.

        if (iteratorStmtLocked == null)
        {
            synchronized (this) // Make sure this completes in case another thread needs the same info
            {
                // Generate the statement, and statement mapping/parameter information
                SQLStatement sqlStmt = getSQLStatementForIterator(ownerSM);
                iteratorStmtUnlocked = sqlStmt.getSelectStatement().toSQL();
                sqlStmt.addExtension("lock-for-update", true);
                iteratorStmtLocked = sqlStmt.getSelectStatement().toSQL();
            }
        }


        Transaction tx = ec.getTransaction();
        String stmt = (tx.lockReadObjects() ? iteratorStmtLocked : iteratorStmtUnlocked);

View Full Code Here

     */
    protected SQLStatement getSQLStatementForIterator(ObjectProvider ownerSM)
    {
        RDBMSStoreManager storeMgr = (RDBMSStoreManager)this.storeMgr;


        SQLStatement sqlStmt = new SQLStatement(storeMgr, mapTable, null, null);
        sqlStmt.setClassLoaderResolver(clr);
        iteratorKeyResultCols = sqlStmt.select(sqlStmt.getPrimaryTable(), keyMapping, null);
        iteratorValueResultCols = sqlStmt.select(sqlStmt.getPrimaryTable(), valueMapping, null);


        // Apply condition on owner field to filter by owner
        SQLExpressionFactory exprFactory = storeMgr.getSQLExpressionFactory();
        SQLTable ownerSqlTbl =
            SQLStatementHelper.getSQLTableForMappingOfTable(sqlStmt, sqlStmt.getPrimaryTable(), ownerMapping);
        SQLExpression ownerExpr = exprFactory.newExpression(sqlStmt, ownerSqlTbl, ownerMapping);
        SQLExpression ownerVal = exprFactory.newLiteralParameter(sqlStmt, ownerMapping, null, "OWNER");
        sqlStmt.whereAnd(ownerExpr.eq(ownerVal), true);


        // Apply condition that key is not null
        SQLExpression keyExpr = exprFactory.newExpression(sqlStmt, sqlStmt.getPrimaryTable(), keyMapping);
        SQLExpression nullExpr = exprFactory.newLiteral(sqlStmt, null, null);
        sqlStmt.whereAnd(keyExpr.ne(nullExpr), true);


        // Input parameter(s) - the owner
        int inputParamNum = 1;
        StatementMappingIndex ownerIdx = new StatementMappingIndex(ownerMapping);
        if (sqlStmt.getNumberOfUnions() > 0)
        {
            // Add parameter occurrence for each union of statement
            for (int j=0;j<sqlStmt.getNumberOfUnions()+1;j++)
            {
                int[] paramPositions = new int[ownerMapping.getNumberOfDatastoreMappings()];
                for (int k=0;k<ownerMapping.getNumberOfDatastoreMappings();k++)
                {
                    paramPositions[k] = inputParamNum++;

View Full Code Here

        {
            datastoreCompilation.setResultDefinitionForClass(new StatementClassMapping());
        }


        // Generate statement for candidate(s)
        SQLStatement stmt = null;
        try
        {
            stmt = RDBMSQueryUtils.getStatementForCandidates(null, candidateCmd,
                datastoreCompilation.getResultDefinitionForClass(), ec, candidateClass, subclasses, result, null, 
                null);
        }
        catch (NucleusException ne)
        {
            // Statement would result in no results, so just catch it and avoid generating the statement
            NucleusLogger.QUERY.warn("Query for candidates of " + candidateClass.getName() +
                (subclasses ? " and subclasses" : "") + " resulted in no possible candidates", ne);
            statementReturnsEmpty = true;
            return;
        }


        // Update the SQLStatement with filter, ordering, result etc
        Set<String> options = new HashSet<String>();
        options.add(QueryToSQLMapper.OPTION_BULK_UPDATE_VERSION);
        QueryToSQLMapper sqlMapper = new QueryToSQLMapper(stmt, compilation, parameters,
            datastoreCompilation.getResultDefinitionForClass(), datastoreCompilation.getResultDefinition(),
            candidateCmd, getFetchPlan(), ec, getParsedImports(), options, extensions);
        sqlMapper.compile();
        datastoreCompilation.setParameterNameByPosition(sqlMapper.getParameterNameByPosition());
        datastoreCompilation.setPrecompilable(sqlMapper.isPrecompilable());
        if (!getResultDistinct() && stmt.isDistinct())
        {
            setResultDistinct(true);
            compilation.setResultDistinct();
        }


        if (candidateCollection != null)
        {
            // Restrict to the supplied candidate ids
            BooleanExpression candidateExpr = null;
            Iterator iter = candidateCollection.iterator();
            JavaTypeMapping idMapping = stmt.getPrimaryTable().getTable().getIdMapping();
            while (iter.hasNext())
            {
                Object candidate = iter.next();
                SQLExpression idExpr = stmt.getSQLExpressionFactory().newExpression(stmt, stmt.getPrimaryTable(),
                    idMapping);
                SQLExpression idVal = stmt.getSQLExpressionFactory().newLiteral(stmt, idMapping, candidate);
                if (candidateExpr == null)
                {
                    candidateExpr = idExpr.eq(idVal);
                }
                else
                {
                    candidateExpr = candidateExpr.ior(idExpr.eq(idVal));
                }
            }
            stmt.whereAnd(candidateExpr, true);
        }


        // Apply any range
        if (range != null)
        {
            long lower = fromInclNo;
            long upper = toExclNo;
            if (fromInclParam != null)
            {
                if (parameters.containsKey(fromInclParam))
                {
                    lower = ((Number)parameters.get(fromInclParam)).longValue();
                }
                else
                {
                    // Must be numbered input so take penultimate
                    int pos = parameters.size()-2;
                    lower = ((Number)parameters.get(Integer.valueOf(pos))).longValue();
                }
            }
            if (toExclParam != null)
            {
                if (parameters.containsKey(fromInclParam))
                {
                    upper = ((Number)parameters.get(toExclParam)).longValue();
                }
                else
                {
                    // Must be numbered input so take ultimate
                    int pos = parameters.size()-1;
                    upper = ((Number)parameters.get(Integer.valueOf(pos))).longValue();
                }
            }
            stmt.setRange(lower, upper-lower);
        }


        // Set any extensions
        boolean useUpdateLock = RDBMSQueryUtils.useUpdateLockForQuery(this);
        stmt.addExtension("lock-for-update", Boolean.valueOf(useUpdateLock));


        datastoreCompilation.setSQL(stmt.getSelectStatement().toString());
        datastoreCompilation.setStatementParameters(stmt.getSelectStatement().getParametersForStatement());


        if (NucleusLogger.QUERY.isDebugEnabled())
        {
            NucleusLogger.QUERY.debug(LOCALISER.msg("021084", getLanguage(), System.currentTimeMillis()-startTime));
        }

View Full Code Here


        StatementClassMapping resultsDef = new StatementClassMapping();
        datastoreCompilation.setResultDefinitionForClass(resultsDef);


        // Generate statement for candidate(s)
        SQLStatement stmt = null;
        try
        {
            stmt = RDBMSQueryUtils.getStatementForCandidates(null, candidateCmd,
                datastoreCompilation.getResultDefinitionForClass(), ec, candidateClass, subclasses, result, null,
                null);
        }
        catch (NucleusException ne)
        {
            // Statement would result in no results, so just catch it and avoid generating the statement
            NucleusLogger.QUERY.warn("Query for candidates of " + candidateClass.getName() +
                (subclasses ? " and subclasses" : "") + " resulted in no possible candidates", ne);
            statementReturnsEmpty = true;
            return;
        }


        if (stmt.allUnionsForSamePrimaryTable())
        {
            // Select fetch-plan fields of candidate class
            SQLStatementHelper.selectFetchPlanOfCandidateInStatement(stmt,
                datastoreCompilation.getResultDefinitionForClass(), candidateCmd, getFetchPlan(), 1);
        }
        else
        {
            // Select id only since tables don't have same mappings or column names
            // TODO complete-table will come through here but maybe ought to be treated differently
            SQLStatementHelper.selectIdentityOfCandidateInStatement(stmt,
                datastoreCompilation.getResultDefinitionForClass(), candidateCmd);
        }


        datastoreCompilation.setSQL(stmt.getSelectStatement().toString());
        datastoreCompilation.setStatementParameters(stmt.getSelectStatement().getParametersForStatement());
    }

View Full Code Here

            return;
        }


        // Generate statement for candidate
        StatementClassMapping resultsDef = new StatementClassMapping();
        SQLStatement stmt = RDBMSQueryUtils.getStatementForCandidates(null, candidateCmd,
            resultsDef, ec, candidateClass, subclasses, result, null, null);


        Set<String> options = new HashSet<String>();
        options.add(QueryToSQLMapper.OPTION_BULK_UPDATE_VERSION);
        QueryToSQLMapper sqlMapper = new QueryToSQLMapper(stmt, compilation, parameterValues,
            resultsDef, null, candidateCmd, getFetchPlan(), ec, getParsedImports(), options, extensions);
        sqlMapper.compile();


        // The assumption here is that the SQL UPDATE statement generated will not need to use multiple
        // tables. This means that the SET clause only refers to the table of the candidate, and that
        // the WHERE clause only refers to the table of the candidate.
        // It is possible with some RDBMS e.g MySQL that support multiple table update syntax
        // that these 2 conditions don't need to be enforced.
        DatastoreAdapter dba = ((RDBMSStoreManager)ec.getStoreManager()).getDatastoreAdapter();
        if (stmt.getNumberOfTables() > 0 && !dba.supportsOption(RDBMSAdapter.UPDATE_MULTITABLE))
        {
            throw new NucleusDataStoreException("Bulk update requires use of multiple tables yet datastore doesnt allow multiple table syntax");
        }
        datastoreCompilation.setSQL(stmt.getUpdateStatement().toString());
        datastoreCompilation.setStatementParameters(stmt.getSelectStatement().getParametersForStatement());
    }

View Full Code Here

            }
        }


        // Generate statement for candidate
        StatementClassMapping resultsDef = new StatementClassMapping();
        SQLStatement stmt = RDBMSQueryUtils.getStatementForCandidates(null, candidateCmd,
            resultsDef, ec, candidateClass, subclasses, result, null, null);


        QueryToSQLMapper sqlMapper = new QueryToSQLMapper(stmt, compilation, parameterValues,
            resultsDef, null, candidateCmd, getFetchPlan(), ec, getParsedImports(), null, extensions);
        sqlMapper.compile();


        // This currently assumes that we only need to delete from one table.
        // TODO Delete entity from all populated tables
        datastoreCompilation.setSQL(stmt.getDeleteStatement().toString());
        datastoreCompilation.setStatementParameters(stmt.getSelectStatement().getParametersForStatement());
    }

View Full Code Here

        if (getStmtLocked == null)
        {
            synchronized (this) // Make sure this completes in case another thread needs the same info
            {
                // Generate the statement, and statement mapping/parameter information
                SQLStatement sqlStmt = getSQLStatementForGet(ownerSM);
                getStmtUnlocked = sqlStmt.getSelectStatement().toSQL();
                sqlStmt.addExtension("lock-for-update", true);
                getStmtLocked = sqlStmt.getSelectStatement().toSQL();
            }
        }


        Transaction tx = ec.getTransaction();
        String stmt = (tx.lockReadObjects() ? getStmtLocked : getStmtUnlocked);

View Full Code Here

     * @param ownerSM StateManager for the owning object
     * @return The SQLStatement
     */
    protected SQLStatement getSQLStatementForGet(ObjectProvider ownerSM)
    {
        SQLStatement sqlStmt = null;


        final ClassLoaderResolver clr = ownerSM.getExecutionContext().getClassLoaderResolver();
        final Class valueCls = clr.classForName(this.valueType);
        RDBMSStoreManager storeMgr = (RDBMSStoreManager)this.storeMgr;
        if (valuesAreEmbedded || valuesAreSerialised)
        {
            // Value is stored in join table
            sqlStmt = new SQLStatement(storeMgr, mapTable, null, null);
            sqlStmt.setClassLoaderResolver(clr);
            sqlStmt.select(sqlStmt.getPrimaryTable(), valueMapping, null);
        }
        else
        {
            // Value is stored in own table
            getMappingDef = new StatementClassMapping();
            UnionStatementGenerator stmtGen =
                new UnionStatementGenerator(storeMgr, clr, valueCls, true, null, null, mapTable, null, valueMapping);
            stmtGen.setOption(StatementGenerator.OPTION_SELECT_NUCLEUS_TYPE);
            getMappingDef.setNucleusTypeColumnName(UnionStatementGenerator.NUC_TYPE_COLUMN);
            sqlStmt = stmtGen.getStatement();


            // Select the value field(s)
            SQLTable valueSqlTbl = sqlStmt.getTable(valueTable, sqlStmt.getPrimaryTable().getGroupName());
            SQLStatementHelper.selectFetchPlanOfSourceClassInStatement(sqlStmt, getMappingDef,
                ownerSM.getExecutionContext().getFetchPlan(), valueSqlTbl, vmd, 0);
        }


        // Apply condition on owner field to filter by owner
        SQLExpressionFactory exprFactory = storeMgr.getSQLExpressionFactory();
        SQLTable ownerSqlTbl =
            SQLStatementHelper.getSQLTableForMappingOfTable(sqlStmt, sqlStmt.getPrimaryTable(), ownerMapping);
        SQLExpression ownerExpr = exprFactory.newExpression(sqlStmt, ownerSqlTbl, ownerMapping);
        SQLExpression ownerVal = exprFactory.newLiteralParameter(sqlStmt, ownerMapping, null, "OWNER");
        sqlStmt.whereAnd(ownerExpr.eq(ownerVal), true);


        // Apply condition on key
        if (keyMapping instanceof SerialisedMapping)
        {
            // if the keyMapping contains a BLOB column (or any other column not supported by the database
            // as primary key), uses like instead of the operator OP_EQ (=)
            // in future do not check if the keyMapping is of ObjectMapping, but use the database 
            // adapter to check the data types not supported as primary key
            // if object mapping (BLOB) use like
            SQLExpression keyExpr = exprFactory.newExpression(sqlStmt, sqlStmt.getPrimaryTable(), keyMapping);
            SQLExpression keyVal = exprFactory.newLiteralParameter(sqlStmt, keyMapping, null, "KEY");
            sqlStmt.whereAnd(new org.datanucleus.store.rdbms.sql.expression.BooleanExpression(keyExpr,
                Expression.OP_LIKE, keyVal), true);
        }
        else
        {
            SQLExpression keyExpr = exprFactory.newExpression(sqlStmt, sqlStmt.getPrimaryTable(), keyMapping);
            SQLExpression keyVal = exprFactory.newLiteralParameter(sqlStmt, keyMapping, null, "KEY");
            sqlStmt.whereAnd(keyExpr.eq(keyVal), true);
        }


        // Input parameter(s) - owner, key
        int inputParamNum = 1;
        StatementMappingIndex ownerIdx = new StatementMappingIndex(ownerMapping);
        StatementMappingIndex keyIdx = new StatementMappingIndex(keyMapping);
        if (sqlStmt.getNumberOfUnions() > 0)
        {
            // Add parameter occurrence for each union of statement
            for (int j=0;j<sqlStmt.getNumberOfUnions()+1;j++)
            {
                int[] ownerPositions = new int[ownerMapping.getNumberOfDatastoreMappings()];
                for (int k=0;k<ownerPositions.length;k++)
                {
                    ownerPositions[k] = inputParamNum++;

View Full Code Here

        // Form the query to find which one of these classes has the instance with this id
        DiscriminatorStatementGenerator stmtGen =
            new DiscriminatorStatementGenerator(storeMgr, clr, clr.classForName(cmd.getFullClassName()), true,
                null, null);
        stmtGen.setOption(StatementGenerator.OPTION_RESTRICT_DISCRIM);
        SQLStatement sqlStmt = stmtGen.getStatement();


        // Select the discriminator
        JavaTypeMapping discrimMapping = primaryTable.getDiscriminatorMapping(true);
        SQLTable discrimSqlTbl = SQLStatementHelper.getSQLTableForMappingOfTable(sqlStmt, sqlStmt.getPrimaryTable(), discrimMapping);
        sqlStmt.select(discrimSqlTbl, discrimMapping, null);


        // Restrict to this id
        JavaTypeMapping idMapping = primaryTable.getIdMapping();
        JavaTypeMapping idParamMapping = new PersistableIdMapping((PersistableMapping) idMapping);
        SQLExpression sqlFldExpr = exprFactory.newExpression(sqlStmt, sqlStmt.getPrimaryTable(), idMapping);
        SQLExpression sqlFldVal = exprFactory.newLiteralParameter(sqlStmt, idParamMapping, id, "ID");
        sqlStmt.whereAnd(sqlFldExpr.eq(sqlFldVal), true);


        // Perform the query
        try
        {
            ManagedConnection mconn = storeMgr.getConnection(ec);
            SQLController sqlControl = storeMgr.getSQLController();
            if (ec.getSerializeReadForClass(cmd.getFullClassName()))
            {
                sqlStmt.addExtension("lock-for-update", true);
            }


            try
            {
                PreparedStatement ps =
                    SQLStatementHelper.getPreparedStatementForSQLStatement(sqlStmt, ec, mconn, null, null);
                String statement = sqlStmt.getSelectStatement().toSQL();
                try
                {
                    ResultSet rs = sqlControl.executeStatementQuery(mconn, statement, ps);
                    try
                    {

View Full Code Here

        AbstractClassMetaData rootCmd = rootCmdIter.next();
        UnionStatementGenerator stmtGen =
            new UnionStatementGenerator(storeMgr, clr, clr.classForName(rootCmd.getFullClassName()), true,
                null, null);
        stmtGen.setOption(StatementGenerator.OPTION_SELECT_NUCLEUS_TYPE);
        SQLStatement sqlStmtMain = stmtGen.getStatement();


        while (rootCmdIter.hasNext())
        {
            // UNION to any other roots
            AbstractClassMetaData cmd = rootCmdIter.next();
            stmtGen = new UnionStatementGenerator(storeMgr, clr, clr.classForName(cmd.getFullClassName()), true,
                null, null);
            stmtGen.setOption(StatementGenerator.OPTION_SELECT_NUCLEUS_TYPE);
            SQLStatement sqlStmt = stmtGen.getStatement();


            sqlStmtMain.union(sqlStmt);
        }


        // WHERE (object id) = ?

View Full Code Here

0 1 2 3 4 5

TOP

Related Classes of org.datanucleus.store.rdbms.sql.SQLStatement

org.datanucleus.store.mapped.DatastoreIdentifier

org.datanucleus.store.mapped.mapping.JavaTypeMapping

org.datanucleus.store.rdbms.adapter.DatabaseAdapter

org.datanucleus.store.rdbms.adapter.RDBMSAdapter

org.datanucleus.store.rdbms.mapping.oracle.OracleBlobRDBMSMapping

org.datanucleus.store.rdbms.mapping.oracle.OracleClobRDBMSMapping

org.datanucleus.store.rdbms.query.JDOQLQuery

org.datanucleus.store.rdbms.query.JPQLQuery

org.datanucleus.store.rdbms.query.QueryToSQLMapper

org.datanucleus.store.rdbms.query.RDBMSQueryUtils

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.

Examples of org.datanucleus.store.rdbms.sql.SQLStatement

Table Groups

Table Aliases

Related Classes of org.datanucleus.store.rdbms.sql.SQLStatement