Search in sources :

Example 1 with QuerySpecificationBuilder

use of org.sql.generation.api.grammar.builders.query.QuerySpecificationBuilder in project qi4j-sdk by Qi4j.

the class AbstractSQLQuerying method selectAllEntitiesOfCorrectType.

protected QuerySpecificationBuilder selectAllEntitiesOfCorrectType(SQLVendor vendor, org.sql.generation.api.grammar.booleans.BooleanExpression entityTypeCondition) {
    TableReferenceFactory t = vendor.getTableReferenceFactory();
    String tableAlias = TABLE_NAME_PREFIX + "0";
    TableReferenceBuilder from = t.tableBuilder(t.table(t.tableName(this._state.schemaName().get(), DBNames.ENTITY_TABLE_NAME), t.tableAlias(tableAlias)));
    this.addTypeJoin(vendor, from, 0);
    QuerySpecificationBuilder query = this.getBuilderForPredicate(vendor, tableAlias);
    query.getFrom().addTableReferences(from);
    query.getWhere().reset(entityTypeCondition);
    return query;
}
Also used : TableReferenceFactory(org.sql.generation.api.grammar.factories.TableReferenceFactory) QuerySpecificationBuilder(org.sql.generation.api.grammar.builders.query.QuerySpecificationBuilder) TableReferenceBuilder(org.sql.generation.api.grammar.builders.query.TableReferenceBuilder)

Example 2 with QuerySpecificationBuilder

use of org.sql.generation.api.grammar.builders.query.QuerySpecificationBuilder in project qi4j-sdk by Qi4j.

the class GenericDatabaseExplorer method visitDatabaseTables.

public static void visitDatabaseTables(Connection connection, String catalogName, String schemaNamePattern, String tableNamePattern, DatabaseProcessor processor, SQLVendor sqlSyntaxVendor) throws SQLException {
    DatabaseMetaData metaData = connection.getMetaData();
    connection.setReadOnly(true);
    ResultSet rs = metaData.getTables(catalogName, schemaNamePattern, tableNamePattern, new String[] { "TABLE" });
    try {
        while (rs.next()) {
            String schemaName = rs.getString(2);
            try {
                processor.beginProcessSchemaInfo(schemaName);
                String tableName = rs.getString(3);
                String tableRemarks = rs.getString(5);
                try {
                    processor.beginProcessTableInfo(schemaName, tableName, tableRemarks);
                    List<ColumnInfo> colInfos = new ArrayList<>();
                    ResultSet rsCols = metaData.getColumns(null, schemaName, tableName, null);
                    try {
                        while (rsCols.next()) {
                            String nullable = rsCols.getString(18);
                            colInfos.add(new ColumnInfo(rsCols.getString(4), rsCols.getInt(5), rsCols.getString(6), rsCols.getInt(7), rsCols.getInt(9), nullable.length() > 0 ? Boolean.toString(nullable.equals("YES")) : "unknown", rsCols.getString(13), rsCols.getString(12)));
                        }
                    } finally {
                        rsCols.close();
                    }
                    rsCols = metaData.getImportedKeys(null, schemaName, tableName);
                    Map<String, ForeignKeyInfo> fkInfos = new HashMap<>();
                    try {
                        while (rsCols.next()) {
                            fkInfos.put(//
                            rsCols.getString(8), new ForeignKeyInfo(rsCols.getString(2), rsCols.getString(3), rsCols.getString(4), rsCols.getShort(10), rsCols.getShort(11), rsCols.getShort(14)));
                        }
                    } finally {
                        rsCols.close();
                    }
                    try {
                        processor.beginProcessColumns(schemaName, tableName, tableRemarks);
                        for (ColumnInfo colInfo : colInfos) {
                            try {
                                processor.beginProcessColumnInfo(schemaName, tableName, colInfo, fkInfos.get(colInfo._name));
                            } finally {
                                processor.endProcessColumnInfo(schemaName, tableName, colInfo, fkInfos.get(colInfo._name));
                            }
                        }
                    } finally {
                        processor.endProcessColumns(schemaName, tableName, tableRemarks);
                    }
                    QueryFactory q = sqlSyntaxVendor.getQueryFactory();
                    TableReferenceFactory t = sqlSyntaxVendor.getTableReferenceFactory();
                    QuerySpecificationBuilder builda = q.querySpecificationBuilder();
                    builda.getSelect().selectAll();
                    builda.getFrom().addTableReferences(t.tableBuilder(t.table(t.tableName(schemaName, tableName))));
                    String sql = sqlSyntaxVendor.toString(q.createQuery(builda.createExpression()));
                    Statement stmt = connection.createStatement();
                    ResultSet rowsRs = null;
                    try {
                        rowsRs = stmt.executeQuery(sql);
                        processor.beginProcessRows(schemaName, tableName, tableRemarks);
                        while (rowsRs.next()) {
                            Object[] rowContents = new Object[colInfos.size()];
                            for (Integer x = 0; x < rowContents.length; ++x) {
                                rowContents[x] = rowsRs.getObject(x + 1);
                            }
                            try {
                                processor.beginProcessRowInfo(schemaName, tableName, rowContents);
                            } finally {
                                processor.endProcessRowInfo(schemaName, tableName, rowContents);
                            }
                        }
                    } finally {
                        processor.endProcessRows(schemaName, tableName, tableRemarks);
                        if (rowsRs != null) {
                            rowsRs.close();
                        }
                        stmt.close();
                    }
                } finally {
                    processor.endProcessTableInfo(schemaName, tableName, tableRemarks);
                }
            } finally {
                processor.endProcessSchemaInfo(schemaName);
            }
        }
    } finally {
        SQLUtil.closeQuietly(rs);
    }
}
Also used : QueryFactory(org.sql.generation.api.grammar.factories.QueryFactory) HashMap(java.util.HashMap) Statement(java.sql.Statement) ArrayList(java.util.ArrayList) QuerySpecificationBuilder(org.sql.generation.api.grammar.builders.query.QuerySpecificationBuilder) DatabaseMetaData(java.sql.DatabaseMetaData) TableReferenceFactory(org.sql.generation.api.grammar.factories.TableReferenceFactory) ResultSet(java.sql.ResultSet)

Example 3 with QuerySpecificationBuilder

use of org.sql.generation.api.grammar.builders.query.QuerySpecificationBuilder in project qi4j-sdk by Qi4j.

the class AbstractSQLIndexing method createQueryEntityPkByIdentityStatement.

protected QueryExpression createQueryEntityPkByIdentityStatement(String schemaName, SQLVendor vendor) {
    BooleanFactory b = vendor.getBooleanFactory();
    LiteralFactory l = vendor.getLiteralFactory();
    ColumnsFactory c = vendor.getColumnsFactory();
    TableReferenceFactory t = vendor.getTableReferenceFactory();
    QueryFactory q = vendor.getQueryFactory();
    // "SELECT " + ENTITY_TABLE_PK_COLUMN_NAME + "\n" + //
    // "FROM " + "%s" + "." + ENTITY_TABLE_NAME + "\n" + //
    // "WHERE " + ENTITY_TABLE_IDENTITY_COLUMN_NAME + " = ?" + "\n" + //
    // ";" //
    QuerySpecificationBuilder query = q.querySpecificationBuilder();
    query.getSelect().addUnnamedColumns(c.colName(DBNames.ENTITY_TABLE_PK_COLUMN_NAME));
    query.getFrom().addTableReferences(t.tableBuilder(t.table(t.tableName(schemaName, DBNames.ENTITY_TABLE_NAME))));
    query.getWhere().reset(b.eq(c.colName(DBNames.ENTITY_TABLE_IDENTITY_COLUMN_NAME), l.param()));
    return q.createQuery(query.createExpression());
}
Also used : TableReferenceFactory(org.sql.generation.api.grammar.factories.TableReferenceFactory) QueryFactory(org.sql.generation.api.grammar.factories.QueryFactory) ColumnsFactory(org.sql.generation.api.grammar.factories.ColumnsFactory) QuerySpecificationBuilder(org.sql.generation.api.grammar.builders.query.QuerySpecificationBuilder) BooleanFactory(org.sql.generation.api.grammar.factories.BooleanFactory) LiteralFactory(org.sql.generation.api.grammar.factories.LiteralFactory)

Example 4 with QuerySpecificationBuilder

use of org.sql.generation.api.grammar.builders.query.QuerySpecificationBuilder in project qi4j-sdk by Qi4j.

the class AbstractSQLIndexing method createAssoInsert.

protected InsertStatement createAssoInsert(QNameInfo qNameInfo, SQLVendor vendor, Integer amountOfParams) {
    ModificationFactory m = vendor.getModificationFactory();
    LiteralFactory l = vendor.getLiteralFactory();
    ColumnsFactory c = vendor.getColumnsFactory();
    QueryFactory q = vendor.getQueryFactory();
    TableReferenceFactory t = vendor.getTableReferenceFactory();
    BooleanFactory b = vendor.getBooleanFactory();
    String schemaName = this._state.schemaName().get();
    // "INSERT INTO " + "%s" + "." + "%s" + "\n" + //
    // "SELECT " + "?, " + "?, " + ENTITY_TABLE_PK_COLUMN_NAME + "\n" + // <-- here is 4 params
    // when many-asso
    // "FROM " + "%s" + "." + ENTITY_TABLE_NAME + "\n" + //
    // "WHERE " + ENTITY_TABLE_IDENTITY_COLUMN_NAME + " = " + "?";
    QuerySpecificationBuilder qBuilder = q.querySpecificationBuilder();
    for (Integer x = 0; x < amountOfParams; ++x) {
        qBuilder.getSelect().addUnnamedColumns(c.colExp(l.param()));
    }
    qBuilder.getSelect().addUnnamedColumns(c.colName(DBNames.ENTITY_TABLE_PK_COLUMN_NAME));
    qBuilder.getFrom().addTableReferences(t.tableBuilder(t.table(t.tableName(schemaName, DBNames.ENTITY_TABLE_NAME))));
    qBuilder.getWhere().reset(b.eq(c.colName(DBNames.ENTITY_TABLE_IDENTITY_COLUMN_NAME), l.param()));
    return m.insert().setTableName(t.tableName(schemaName, qNameInfo.getTableName())).setColumnSource(m.columnSourceByQuery(q.createQuery(qBuilder.createExpression()))).createExpression();
}
Also used : ModificationFactory(org.sql.generation.api.grammar.factories.ModificationFactory) TableReferenceFactory(org.sql.generation.api.grammar.factories.TableReferenceFactory) QueryFactory(org.sql.generation.api.grammar.factories.QueryFactory) ColumnsFactory(org.sql.generation.api.grammar.factories.ColumnsFactory) QuerySpecificationBuilder(org.sql.generation.api.grammar.builders.query.QuerySpecificationBuilder) BooleanFactory(org.sql.generation.api.grammar.factories.BooleanFactory) LiteralFactory(org.sql.generation.api.grammar.factories.LiteralFactory)

Example 5 with QuerySpecificationBuilder

use of org.sql.generation.api.grammar.builders.query.QuerySpecificationBuilder in project qi4j-sdk by Qi4j.

the class AbstractSQLQuerying method constructQueryForPredicate.

protected QuerySpecification constructQueryForPredicate(//
Specification<Composite> predicate, //
PropertyFunction<?> propRef, //
TraversedAssoOrManyAssoRef assoRef, //
Boolean includeLastAssoPathTable, //
Boolean negationActive, //
SQLVendor vendor, //
org.sql.generation.api.grammar.booleans.BooleanExpression entityTypeCondition, //
WhereClauseProcessor whereClauseGenerator) {
    Integer startingIndex = 0;
    TableReferenceFactory t = vendor.getTableReferenceFactory();
    QuerySpecificationBuilder builder = this.getBuilderForPredicate(vendor, TABLE_NAME_PREFIX + startingIndex);
    TableReferenceBuilder from = t.tableBuilder(t.table(t.tableName(this._state.schemaName().get(), DBNames.ENTITY_TABLE_NAME), t.tableAlias(TABLE_NAME_PREFIX + startingIndex)));
    this.addTypeJoin(vendor, from, startingIndex);
    Integer lastTableIndex = null;
    JoinType joinStyle = this.getTableJoinStyle(predicate, negationActive);
    if (propRef == null && assoRef != null && assoRef._hasRefs) {
        lastTableIndex = this.traverseAssociationPath(assoRef, startingIndex, startingIndex + 1, vendor, from, joinStyle, includeLastAssoPathTable);
    } else if (assoRef == null || !assoRef._hasRefs) {
        lastTableIndex = this.traversePropertyPath(propRef, startingIndex, startingIndex + 1, vendor, from, joinStyle);
    } else {
        throw new InternalError("Can not have both property reference and association reference (non-)nulls [propRef=" + propRef + ", assoRef=" + assoRef + ", predicate=" + predicate + "].");
    }
    builder.getFrom().addTableReferences(from);
    BooleanBuilder afterWhere = vendor.getBooleanFactory().booleanBuilder();
    whereClauseGenerator.processWhereClause(builder, afterWhere, joinStyle, startingIndex, lastTableIndex);
    BooleanBuilder where = builder.getWhere();
    if (negationActive) {
        where.not();
    }
    where.and(afterWhere.createExpression());
    where.and(entityTypeCondition);
    builder.trimGroupBy();
    return builder.createExpression();
}
Also used : TableReferenceFactory(org.sql.generation.api.grammar.factories.TableReferenceFactory) BooleanBuilder(org.sql.generation.api.grammar.builders.booleans.BooleanBuilder) QuerySpecificationBuilder(org.sql.generation.api.grammar.builders.query.QuerySpecificationBuilder) JoinType(org.sql.generation.api.grammar.query.joins.JoinType) TableReferenceBuilder(org.sql.generation.api.grammar.builders.query.TableReferenceBuilder)

Aggregations

QuerySpecificationBuilder (org.sql.generation.api.grammar.builders.query.QuerySpecificationBuilder)7 TableReferenceFactory (org.sql.generation.api.grammar.factories.TableReferenceFactory)6 QueryFactory (org.sql.generation.api.grammar.factories.QueryFactory)5 ColumnsFactory (org.sql.generation.api.grammar.factories.ColumnsFactory)4 LiteralFactory (org.sql.generation.api.grammar.factories.LiteralFactory)3 TableReferenceBuilder (org.sql.generation.api.grammar.builders.query.TableReferenceBuilder)2 BooleanFactory (org.sql.generation.api.grammar.factories.BooleanFactory)2 DatabaseMetaData (java.sql.DatabaseMetaData)1 ResultSet (java.sql.ResultSet)1 Statement (java.sql.Statement)1 ArrayList (java.util.ArrayList)1 HashMap (java.util.HashMap)1 BooleanBuilder (org.sql.generation.api.grammar.builders.booleans.BooleanBuilder)1 QueryBuilder (org.sql.generation.api.grammar.builders.query.QueryBuilder)1 ModificationFactory (org.sql.generation.api.grammar.factories.ModificationFactory)1 ColumnReference (org.sql.generation.api.grammar.query.ColumnReference)1 QueryExpression (org.sql.generation.api.grammar.query.QueryExpression)1 JoinType (org.sql.generation.api.grammar.query.joins.JoinType)1 SQLVendor (org.sql.generation.api.vendor.SQLVendor)1