Examples with SelectStatement org.datanucleus.store.rdbms.sql.SelectStatement used on opensource projects

Example 21 with SelectStatement

use of org.datanucleus.store.rdbms.sql.SelectStatement in project datanucleus-rdbms by datanucleus.

the class ArraySizeMethod method getExpression.

/* (non-Javadoc)
     * @see org.datanucleus.store.rdbms.sql.method.SQLMethod#getExpression(org.datanucleus.store.rdbms.sql.expression.SQLExpression, java.util.List)
     */
public SQLExpression getExpression(SQLStatement stmt, SQLExpression expr, List<SQLExpression> args) {
    if (args != null && args.size() > 0) {
        throw new NucleusException(Localiser.msg("060015", "size/length", "ArrayExpression"));
    }
    SQLExpressionFactory exprFactory = stmt.getSQLExpressionFactory();
    if (expr instanceof ArrayLiteral) {
        // Just return the array length since we have the value
        return exprFactory.newLiteral(stmt, exprFactory.getMappingForType(int.class, false), Integer.valueOf(Array.getLength(((ArrayLiteral) expr).getValue())));
    }
    AbstractMemberMetaData ownerMmd = expr.getJavaTypeMapping().getMemberMetaData();
    String elementType = ownerMmd.getArray().getElementType();
    RDBMSStoreManager storeMgr = stmt.getRDBMSManager();
    ClassLoaderResolver clr = stmt.getQueryGenerator().getClassLoaderResolver();
    // TODO Allow for interface elements, etc
    JavaTypeMapping ownerMapping = null;
    Table arrayTbl = null;
    if (ownerMmd.getMappedBy() != null) {
        // Bidirectional
        AbstractMemberMetaData elementMmd = ownerMmd.getRelatedMemberMetaData(clr)[0];
        if (ownerMmd.getJoinMetaData() != null || elementMmd.getJoinMetaData() != null) {
            // JoinTable
            arrayTbl = storeMgr.getTable(ownerMmd);
            ownerMapping = ((JoinTable) arrayTbl).getOwnerMapping();
        } else {
            // ForeignKey
            arrayTbl = storeMgr.getDatastoreClass(elementType, clr);
            ownerMapping = arrayTbl.getMemberMapping(elementMmd);
        }
    } else {
        // Unidirectional
        if (ownerMmd.getJoinMetaData() != null) {
            // JoinTable
            arrayTbl = storeMgr.getTable(ownerMmd);
            ownerMapping = ((JoinTable) arrayTbl).getOwnerMapping();
        } else {
            // ForeignKey
            arrayTbl = storeMgr.getDatastoreClass(elementType, clr);
            ownerMapping = ((DatastoreClass) arrayTbl).getExternalMapping(ownerMmd, MappingType.EXTERNAL_FK);
        }
    }
    SelectStatement subStmt = new SelectStatement(stmt, storeMgr, arrayTbl, null, null);
    subStmt.setClassLoaderResolver(clr);
    JavaTypeMapping mapping = storeMgr.getMappingManager().getMappingWithDatastoreMapping(String.class, false, false, clr);
    SQLExpression countExpr = exprFactory.newLiteral(subStmt, mapping, "COUNT(*)");
    ((StringLiteral) countExpr).generateStatementWithoutQuotes();
    subStmt.select(countExpr, null);
    SQLExpression elementOwnerExpr = exprFactory.newExpression(subStmt, subStmt.getPrimaryTable(), ownerMapping);
    SQLExpression ownerIdExpr = exprFactory.newExpression(stmt, expr.getSQLTable(), expr.getSQLTable().getTable().getIdMapping());
    subStmt.whereAnd(elementOwnerExpr.eq(ownerIdExpr), true);
    JavaTypeMapping subqMapping = exprFactory.getMappingForType(Integer.class, false);
    SQLExpression subqExpr = new NumericSubqueryExpression(stmt, subStmt);
    subqExpr.setJavaTypeMapping(subqMapping);
    return subqExpr;
}

Example 22 with SelectStatement

use of org.datanucleus.store.rdbms.sql.SelectStatement in project datanucleus-rdbms by datanucleus.

the class ListGetMethod method getAsSubquery.

/**
 * Implementation of Collection.get() using a subquery on the table representing the collection, adding a condition on the index and returning the element.
 * @param stmt SQLStatement
 * @param listExpr The list expression
 * @param idxExpr The index expression
 * @return The element expression
 */
protected SQLExpression getAsSubquery(SQLStatement stmt, CollectionExpression listExpr, SQLExpression idxExpr) {
    AbstractMemberMetaData mmd = listExpr.getJavaTypeMapping().getMemberMetaData();
    RDBMSStoreManager storeMgr = stmt.getRDBMSManager();
    ClassLoaderResolver clr = stmt.getQueryGenerator().getClassLoaderResolver();
    JavaTypeMapping ownerMapping = null;
    JavaTypeMapping indexMapping = null;
    JavaTypeMapping elemMapping = null;
    Table listTbl = null;
    if (mmd != null) {
        AbstractMemberMetaData[] relatedMmds = mmd.getRelatedMemberMetaData(clr);
        if (mmd.getJoinMetaData() != null || relatedMmds != null && relatedMmds[0].getJoinMetaData() != null) {
            // Join table List
            listTbl = storeMgr.getTable(mmd);
            ownerMapping = ((CollectionTable) listTbl).getOwnerMapping();
            indexMapping = ((CollectionTable) listTbl).getOrderMapping();
            elemMapping = ((CollectionTable) listTbl).getElementMapping();
        } else {
            // FK List
            DatastoreClass elemTbl = storeMgr.getDatastoreClass(mmd.getCollection().getElementType(), clr);
            listTbl = elemTbl;
            if (relatedMmds != null) {
                ownerMapping = elemTbl.getMemberMapping(relatedMmds[0]);
                indexMapping = elemTbl.getExternalMapping(mmd, MappingType.EXTERNAL_INDEX);
                elemMapping = elemTbl.getIdMapping();
            } else {
                ownerMapping = elemTbl.getExternalMapping(mmd, MappingType.EXTERNAL_FK);
                indexMapping = elemTbl.getExternalMapping(mmd, MappingType.EXTERNAL_INDEX);
                elemMapping = elemTbl.getIdMapping();
            }
        }
    }
    SQLExpressionFactory exprFactory = stmt.getSQLExpressionFactory();
    SelectStatement subStmt = new SelectStatement(stmt, storeMgr, listTbl, null, null);
    subStmt.setClassLoaderResolver(clr);
    SQLExpression valExpr = exprFactory.newExpression(subStmt, subStmt.getPrimaryTable(), elemMapping);
    subStmt.select(valExpr, null);
    // Link to primary statement
    SQLExpression elementOwnerExpr = exprFactory.newExpression(subStmt, subStmt.getPrimaryTable(), ownerMapping);
    SQLExpression ownerIdExpr = exprFactory.newExpression(stmt, listExpr.getSQLTable(), listExpr.getSQLTable().getTable().getIdMapping());
    subStmt.whereAnd(elementOwnerExpr.eq(ownerIdExpr), true);
    // Condition on key
    SQLExpression keyExpr = exprFactory.newExpression(subStmt, subStmt.getPrimaryTable(), indexMapping);
    subStmt.whereAnd(keyExpr.eq(idxExpr), true);
    // Create subquery, with mapping being of the element type (since "get" returns an element)
    SubqueryExpression subExpr = new SubqueryExpression(stmt, subStmt);
    subExpr.setJavaTypeMapping(elemMapping);
    return subExpr;
}

Example 23 with SelectStatement

use of org.datanucleus.store.rdbms.sql.SelectStatement in project datanucleus-rdbms by datanucleus.

the class CollectionContainsMethod method containsAsSubquery.

/**
 * Method to return an expression for Collection.contains using a subquery "EXISTS".
 * This is for use when there are "!contains" or "OR" operations in the filter.
 * Creates the following SQL,
 * <ul>
 * <li><b>Collection of NonPC using join table</b>
 * <pre>
 * SELECT 1 FROM JOIN_TBL A0_SUB
 * WHERE A0_SUB.JOIN_OWN_ID = A0.ID AND A0_SUB.JOIN_ELEM_ID = {elemExpr}
 * </pre>
 * </li>
 * <li><b>Collection of PC using join table</b>
 * <pre>
 * SELECT 1 FROM ELEM_TABLE A0_SUB INNER JOIN JOIN_TBL B0 ON ...
 * WHERE B0.JOIN_OWN_ID = A0.ID AND A0_SUB.ID = {elemExpr}
 * </pre>
 * </li>
 * <li><b>Collection of PC using FK</b>
 * <pre>
 * SELECT 1 FROM ELEM_TABLE A0_SUB
 * WHERE A0_SUB.OWN_ID = A0.ID AND A0_SUB.ID = {elemExpr}
 * </pre>
 * </li>
 * </ul>
 * and returns a BooleanSubqueryExpression ("EXISTS (subquery)")
 * @param stmt SQLStatement
 * @param collExpr Collection expression
 * @param elemExpr Expression for the element
 * @return Contains expression
 */
protected SQLExpression containsAsSubquery(SQLStatement stmt, CollectionExpression collExpr, SQLExpression elemExpr) {
    boolean elemIsUnbound = (elemExpr instanceof UnboundExpression);
    String varName = null;
    if (elemIsUnbound) {
        varName = ((UnboundExpression) elemExpr).getVariableName();
        NucleusLogger.QUERY.debug("collection.contains(" + elemExpr + ") binding unbound variable " + varName + " using SUBQUERY");
    }
    RDBMSStoreManager storeMgr = stmt.getRDBMSManager();
    SQLExpressionFactory exprFactory = stmt.getSQLExpressionFactory();
    ClassLoaderResolver clr = stmt.getQueryGenerator().getClassLoaderResolver();
    AbstractMemberMetaData mmd = collExpr.getJavaTypeMapping().getMemberMetaData();
    AbstractClassMetaData elemCmd = mmd.getCollection().getElementClassMetaData(clr);
    CollectionTable joinTbl = (CollectionTable) storeMgr.getTable(mmd);
    String elemType = mmd.getCollection().getElementType();
    if (elemIsUnbound) {
        Class varType = stmt.getQueryGenerator().getTypeOfVariable(varName);
        if (varType != null) {
            elemType = varType.getName();
            elemCmd = storeMgr.getMetaDataManager().getMetaDataForClass(elemType, clr);
        }
    }
    SelectStatement subStmt = null;
    if (joinTbl != null) {
        // JoinTable Collection
        if (elemCmd == null) {
            // Collection<Non-PC>
            subStmt = new SelectStatement(stmt, storeMgr, joinTbl, null, null);
            subStmt.setClassLoaderResolver(clr);
            JavaTypeMapping oneMapping = storeMgr.getMappingManager().getMapping(Integer.class);
            subStmt.select(exprFactory.newLiteral(subStmt, oneMapping, 1), null);
            // Restrict to collection owner
            JavaTypeMapping ownerMapping = ((JoinTable) joinTbl).getOwnerMapping();
            SQLExpression ownerExpr = exprFactory.newExpression(subStmt, subStmt.getPrimaryTable(), ownerMapping);
            SQLExpression ownerIdExpr = exprFactory.newExpression(stmt, collExpr.getSQLTable(), collExpr.getSQLTable().getTable().getIdMapping());
            subStmt.whereAnd(ownerExpr.eq(ownerIdExpr), true);
            SQLExpression elemIdExpr = exprFactory.newExpression(subStmt, subStmt.getPrimaryTable(), joinTbl.getElementMapping());
            if (elemIsUnbound) {
                // Bind the variable in the QueryGenerator
                stmt.getQueryGenerator().bindVariable(varName, null, elemIdExpr.getSQLTable(), elemIdExpr.getJavaTypeMapping());
            } else {
                // Add restrict to element
                addRestrictionOnElement(subStmt, elemIdExpr, elemExpr);
            }
        } else {
            // Collection<PC>
            DatastoreClass elemTbl = storeMgr.getDatastoreClass(elemType, clr);
            subStmt = new SelectStatement(stmt, storeMgr, elemTbl, null, null);
            subStmt.setClassLoaderResolver(clr);
            JavaTypeMapping oneMapping = storeMgr.getMappingManager().getMapping(Integer.class);
            subStmt.select(exprFactory.newLiteral(subStmt, oneMapping, 1), null);
            // Join to join table
            SQLTable joinSqlTbl = subStmt.join(JoinType.INNER_JOIN, subStmt.getPrimaryTable(), elemTbl.getIdMapping(), joinTbl, null, joinTbl.getElementMapping(), null, null, true, null);
            // Restrict to collection owner
            JavaTypeMapping ownerMapping = ((JoinTable) joinTbl).getOwnerMapping();
            SQLExpression ownerExpr = exprFactory.newExpression(subStmt, joinSqlTbl, ownerMapping);
            SQLExpression ownerIdExpr = exprFactory.newExpression(stmt, collExpr.getSQLTable(), collExpr.getSQLTable().getTable().getIdMapping());
            subStmt.whereAnd(ownerExpr.eq(ownerIdExpr), true);
            SQLExpression elemIdExpr = exprFactory.newExpression(subStmt, subStmt.getPrimaryTable(), elemTbl.getIdMapping());
            if (elemIsUnbound) {
                // Bind the variable in the QueryGenerator
                stmt.getQueryGenerator().bindVariable(varName, elemCmd, elemIdExpr.getSQLTable(), elemIdExpr.getJavaTypeMapping());
            } else {
                // Add restrict to element
                addRestrictionOnElement(subStmt, elemIdExpr, elemExpr);
            }
        }
    } else {
        // FK Collection
        DatastoreClass elemTbl = storeMgr.getDatastoreClass(mmd.getCollection().getElementType(), clr);
        subStmt = new SelectStatement(stmt, storeMgr, elemTbl, null, null);
        subStmt.setClassLoaderResolver(clr);
        JavaTypeMapping oneMapping = storeMgr.getMappingManager().getMapping(Integer.class);
        subStmt.select(exprFactory.newLiteral(subStmt, oneMapping, 1), null);
        // Restrict to collection owner
        JavaTypeMapping ownerMapping = null;
        if (mmd.getMappedBy() != null) {
            ownerMapping = elemTbl.getMemberMapping(mmd.getRelatedMemberMetaData(clr)[0]);
        } else {
            ownerMapping = elemTbl.getExternalMapping(mmd, MappingType.EXTERNAL_FK);
        }
        SQLExpression ownerExpr = exprFactory.newExpression(subStmt, subStmt.getPrimaryTable(), ownerMapping);
        SQLExpression ownerIdExpr = exprFactory.newExpression(stmt, collExpr.getSQLTable(), collExpr.getSQLTable().getTable().getIdMapping());
        subStmt.whereAnd(ownerExpr.eq(ownerIdExpr), true);
        if (elemIsUnbound) {
            SQLExpression elemIdExpr = null;
            if (!elemType.equals(mmd.getCollection().getElementType())) {
                // Variable is defined as a subclass of the declared type so add extra join to variable type
                DatastoreClass varTbl = storeMgr.getDatastoreClass(elemType, clr);
                SQLTable varSqlTbl = subStmt.join(JoinType.INNER_JOIN, subStmt.getPrimaryTable(), elemTbl.getIdMapping(), null, varTbl, null, varTbl.getIdMapping(), null, null, null, true, null);
                elemIdExpr = exprFactory.newExpression(subStmt, varSqlTbl, varTbl.getIdMapping());
            } else {
                elemIdExpr = exprFactory.newExpression(subStmt, subStmt.getPrimaryTable(), elemTbl.getIdMapping());
            }
            // Bind the variable in the QueryGenerator
            stmt.getQueryGenerator().bindVariable(varName, elemCmd, elemIdExpr.getSQLTable(), elemIdExpr.getJavaTypeMapping());
        } else {
            // Add restrict to element
            SQLExpression elemIdExpr = exprFactory.newExpression(subStmt, subStmt.getPrimaryTable(), elemTbl.getIdMapping());
            addRestrictionOnElement(subStmt, elemIdExpr, elemExpr);
        }
    }
    return new BooleanSubqueryExpression(stmt, "EXISTS", subStmt);
}

Example 24 with SelectStatement

use of org.datanucleus.store.rdbms.sql.SelectStatement in project datanucleus-rdbms by datanucleus.

the class SimpleNumericAggregateMethod method getExpression.

/* (non-Javadoc)
     * @see org.datanucleus.store.rdbms.sql.method.SQLMethod#getExpression(org.datanucleus.store.rdbms.sql.expression.SQLExpression, java.util.List)
     */
public SQLExpression getExpression(SQLStatement stmt, SQLExpression expr, List<SQLExpression> args) {
    if (expr != null) {
        throw new NucleusException(Localiser.msg("060002", getFunctionName(), expr));
    }
    if (args == null || args.size() != 1) {
        throw new NucleusException(getFunctionName() + " is only supported with a single argument");
    }
    if (stmt.getQueryGenerator().getCompilationComponent() == CompilationComponent.RESULT || stmt.getQueryGenerator().getCompilationComponent() == CompilationComponent.HAVING) {
        // FUNC(argExpr)
        // Use same java type as the argument
        SQLExpression argExpr = args.get(0);
        JavaTypeMapping m = argExpr.getJavaTypeMapping();
        return new AggregateNumericExpression(stmt, m, getFunctionName(), args);
    }
    ClassLoaderResolver clr = stmt.getQueryGenerator().getClassLoaderResolver();
    // Handle as Subquery "SELECT AVG(expr) FROM tbl"
    SQLExpression argExpr = args.get(0);
    SelectStatement subStmt = new SelectStatement(stmt, stmt.getRDBMSManager(), argExpr.getSQLTable().getTable(), argExpr.getSQLTable().getAlias(), null);
    subStmt.setClassLoaderResolver(clr);
    SQLExpressionFactory exprFactory = stmt.getSQLExpressionFactory();
    JavaTypeMapping mapping = stmt.getRDBMSManager().getMappingManager().getMappingWithDatastoreMapping(String.class, false, false, clr);
    String aggregateString = getFunctionName() + "(" + argExpr.toSQLText() + ")";
    SQLExpression aggExpr = exprFactory.newLiteral(subStmt, mapping, aggregateString);
    ((StringLiteral) aggExpr).generateStatementWithoutQuotes();
    subStmt.select(aggExpr, null);
    JavaTypeMapping subqMapping = exprFactory.getMappingForType(Integer.class, false);
    SQLExpression subqExpr = new NumericSubqueryExpression(stmt, subStmt);
    subqExpr.setJavaTypeMapping(subqMapping);
    return subqExpr;
}

Example 25 with SelectStatement

use of org.datanucleus.store.rdbms.sql.SelectStatement in project datanucleus-rdbms by datanucleus.

the class RDBMSStoreHelper method getClassNameForIdUsingDiscriminator.

/**
 * Utility that does a discriminator candidate query for the specified candidate and subclasses
 * and returns the class name of the instance that has the specified identity (if any).
 * @param storeMgr RDBMS StoreManager
 * @param ec execution context
 * @param id The id
 * @param cmd Metadata for the root candidate class
 * @return Name of the class with this identity (or null if none found)
 */
public static String getClassNameForIdUsingDiscriminator(RDBMSStoreManager storeMgr, ExecutionContext ec, Object id, AbstractClassMetaData cmd) {
    // Check for input error
    if (cmd == null || id == null) {
        return null;
    }
    SQLExpressionFactory exprFactory = storeMgr.getSQLExpressionFactory();
    ClassLoaderResolver clr = ec.getClassLoaderResolver();
    DatastoreClass primaryTable = storeMgr.getDatastoreClass(cmd.getFullClassName(), clr);
    // 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(SelectStatementGenerator.OPTION_RESTRICT_DISCRIM);
    SelectStatement sqlStmt = stmtGen.getStatement(ec);
    // Select the discriminator
    JavaTypeMapping discrimMapping = primaryTable.getSurrogateMapping(SurrogateColumnType.DISCRIMINATOR, 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.getConnectionManager().getConnection(ec);
        SQLController sqlControl = storeMgr.getSQLController();
        if (ec.getSerializeReadForClass(cmd.getFullClassName())) {
            sqlStmt.addExtension(SQLStatement.EXTENSION_LOCK_FOR_UPDATE, true);
        }
        try {
            PreparedStatement ps = SQLStatementHelper.getPreparedStatementForSQLStatement(sqlStmt, ec, mconn, null, null);
            String statement = sqlStmt.getSQLText().toSQL();
            try {
                ResultSet rs = sqlControl.executeStatementQuery(ec, mconn, statement, ps);
                try {
                    while (rs.next()) {
                        DiscriminatorMetaData dismd = discrimMapping.getTable().getDiscriminatorMetaData();
                        return RDBMSQueryUtils.getClassNameFromDiscriminatorResultSetRow(discrimMapping, dismd, rs, ec);
                    }
                } finally {
                    rs.close();
                }
            } finally {
                sqlControl.closeStatement(mconn, ps);
            }
        } finally {
            mconn.release();
        }
    } catch (SQLException sqe) {
        NucleusLogger.DATASTORE.error("Exception thrown on querying of discriminator for id", sqe);
        throw new NucleusDataStoreException(sqe.toString(), sqe);
    }
    return null;
}