Factory create = new Factory(connection, dialect); SelectQuery query = create.selectQuery(); query.addFrom(AUTHOR); // Join books only under certain circumstances if (join) { query.addJoin(BOOK, BOOK.AUTHOR_ID.equal(AUTHOR.ID)); } Result<?> result = query.fetch();
protected void addJoins(SelectQuery<?> query, Map<Table<?>, Condition> joins) { if (joins == null) { return; } for (Map.Entry<Table<?>, Condition> entry : joins.entrySet()) { query.addJoin(entry.getKey(), JoinType.LEFT_OUTER_JOIN, entry.getValue()); } }
@Override public final SelectImpl join(TableLike<?> table, JoinType type) { switch (type) { case CROSS_JOIN: case NATURAL_JOIN: case NATURAL_LEFT_OUTER_JOIN: case NATURAL_RIGHT_OUTER_JOIN: case CROSS_APPLY: case OUTER_APPLY: { getQuery().addJoin(table, type); joinTable = null; joinPartitionBy = null; joinType = null; return this; } default: { conditionStep = ConditionStep.ON; joinTable = table; joinType = type; joinPartitionBy = null; joinConditions = null; return this; } } }
@Override public final SelectImpl join(TableLike<?> table, JoinType type) { switch (type) { case CROSS_JOIN: case NATURAL_JOIN: case NATURAL_LEFT_OUTER_JOIN: case NATURAL_RIGHT_OUTER_JOIN: case CROSS_APPLY: case OUTER_APPLY: { getQuery().addJoin(table, type); joinTable = null; joinPartitionBy = null; joinType = null; return this; } default: { conditionStep = ConditionStep.ON; joinTable = table; joinType = type; joinPartitionBy = null; joinConditions = null; return this; } } }
SelectQuery selectQuery = create.selectQuery(); selectQuery.addSelect(selectFields); selectQuery.addFrom(leftTable); selectQuery.addJoin(rightTable, joinCondition);
@Override public final SelectImpl on(Condition conditions) { conditionStep = ConditionStep.ON; joinConditions = new ConditionProviderImpl(); joinConditions.addConditions(conditions); getQuery().addJoin(joinTable, joinType, joinConditions); joinTable = null; joinPartitionBy = null; joinType = null; return this; }
@Override public final SelectImpl on(Condition... conditions) { conditionStep = ConditionStep.ON; joinConditions = new ConditionProviderImpl(); joinConditions.addConditions(conditions); getQuery().addJoin(joinTable, joinType, new Condition[] { joinConditions }); joinTable = null; joinPartitionBy = null; joinType = null; return this; }
@Override public final SelectImpl on(Condition... conditions) { conditionStep = ConditionStep.ON; joinConditions = new ConditionProviderImpl(); joinConditions.addConditions(conditions); getQuery().addJoin(joinTable, joinType, new Condition[] { joinConditions } /* [pro] xxx xxxxxxxxxxxxxxx xx [/pro] */); joinTable = null; joinPartitionBy = null; joinType = null; return this; }
query.addJoin(PROJECT_MEMBER, PROJECT_MEMBER.PROJECT_ID.equal(ACCOUNT.ID)); query.addConditions(allMembers); query.setDistinct(true);
protected void addJoin(SelectQuery<?> query, Table<?> toTable, SchemaFactory schemaFactory, String fromType, Table<?> from, String asName, Relationship rel) { TableField<?, Object> fieldFrom = null; TableField<?, Object> fieldTo = null; switch (rel.getRelationshipType()) { case REFERENCE: fieldFrom = JooqUtils.getTableField(getMetaDataManager(), fromType, rel.getPropertyName()); fieldTo = JooqUtils.getTableField(getMetaDataManager(), schemaFactory.getSchemaName(rel.getObjectType()), ObjectMetaDataManager.ID_FIELD); break; case CHILD: fieldFrom = JooqUtils.getTableField(getMetaDataManager(), fromType, ObjectMetaDataManager.ID_FIELD); fieldTo = JooqUtils.getTableField(getMetaDataManager(), schemaFactory.getSchemaName(rel.getObjectType()), rel.getPropertyName()); break; default: throw new IllegalArgumentException("Illegal Relationship type [" + rel.getRelationshipType() + "]"); } if (fieldFrom == null || fieldTo == null) { throw new IllegalStateException("Failed to construction join query for [" + fromType + "] [" + from + "] [" + rel + "]"); } query.addJoin(toTable, JoinType.LEFT_OUTER_JOIN, fieldFrom.eq(fieldTo.getTable().as(asName).field(fieldTo.getName()))); query.addOrderBy(fieldTo.getTable().as(asName).field(ObjectMetaDataManager.ID_FIELD).asc()); }
query.addJoin(USERS, JoinType.LEFT_OUTER_JOIN, USERS.USER_ID.eq(PROCESS_QUEUE.INITIATOR_ID)); query.addJoin(REPOSITORIES, JoinType.LEFT_OUTER_JOIN, REPOSITORIES.REPO_ID.eq(PROCESS_QUEUE.REPO_ID)); query.addJoin(Tables.PROJECTS, JoinType.LEFT_OUTER_JOIN, Tables.PROJECTS.PROJECT_ID.eq(PROCESS_QUEUE.PROJECT_ID));
protected void addMappingJoins(SelectQuery<?> query, Table<?> toTable, SchemaFactory schemaFactory, String fromType, Table<?> from, String asName, MapRelationship rel) { Table<?> mappingTable = JooqUtils.getTableFromRecordClass(rel.getMappingType()); /* * We don't required the mapping type to be visible external, that's why * we use the schemaFactory from the objectManager, because it is the * superset schemaFactory. */ String mappingType = getObjectManager().getSchemaFactory().getSchemaName(rel.getMappingType()); TableField<?, Object> fieldFrom = JooqUtils.getTableField(getMetaDataManager(), fromType, ObjectMetaDataManager.ID_FIELD); TableField<?, Object> fieldTo = JooqUtils.getTableField(getMetaDataManager(), mappingType, rel.getPropertyName()); TableField<?, Object> fieldRemoved = JooqUtils.getTableField(getMetaDataManager(), mappingType, ObjectMetaDataManager.REMOVED_FIELD); org.jooq.Condition cond = fieldFrom.eq(fieldTo.getTable().field(fieldTo.getName())).and( fieldRemoved == null ? DSL.trueCondition() : fieldRemoved.isNull()); query.addJoin(mappingTable, JoinType.LEFT_OUTER_JOIN, cond); fieldFrom = JooqUtils.getTableField(getMetaDataManager(), mappingType, rel.getOtherRelationship().getPropertyName()); fieldTo = JooqUtils.getTableField(getMetaDataManager(), schemaFactory.getSchemaName(rel.getObjectType()), ObjectMetaDataManager.ID_FIELD); cond = fieldFrom.eq(fieldTo.getTable().asTable(asName).field(fieldTo.getName())); query.addJoin(toTable, JoinType.LEFT_OUTER_JOIN, cond); query.addOrderBy(fieldTo.getTable().asTable(asName).field(fieldTo.getName()).asc()); }
@SuppressWarnings("unchecked") @Override protected <T> List<T> getListByRelationshipMap(Object obj, MapRelationship rel) { Class<UpdatableRecord<?>> typeClass = JooqUtils.getRecordClass(schemaFactory, rel.getObjectType()); String mappingType = schemaFactory.getSchemaName(rel.getMappingType()); String fromType = schemaFactory.getSchemaName(rel.getObjectType()); TableField<?, Object> fieldFrom = JooqUtils.getTableField(getMetaDataManager(), fromType, ObjectMetaDataManager.ID_FIELD); TableField<?, Object> mappingTo = JooqUtils.getTableField(getMetaDataManager(), mappingType, rel.getOtherRelationship().getPropertyName()); TableField<?, Object> mappingOther = JooqUtils.getTableField(getMetaDataManager(), mappingType, rel.getPropertyName()); TableField<?, Object> mappingRemoved = JooqUtils.getTableField(getMetaDataManager(), mappingType, ObjectMetaDataManager.REMOVED_FIELD); Table<?> table = JooqUtils.getTable(schemaFactory, typeClass); Table<?> mapTable = JooqUtils.getTable(schemaFactory, rel.getMappingType()); SelectQuery<?> query = create().selectQuery(); query.addFrom(table); query.addSelect(table.fields()); query.addJoin(mapTable, fieldFrom.eq(mappingTo).and(mappingRemoved == null ? DSL.trueCondition() : mappingRemoved.isNull()).and( mappingOther.eq(ObjectUtils.getId(obj)))); return (List<T>) query.fetchInto(typeClass); }