org.jooq.impl.Pivot Maven / Gradle / Ivy
/**
* Copyright (c) 2009-2014, Data Geekery GmbH (http://www.datageekery.com)
* All rights reserved.
*
* This work is dual-licensed
* - under the Apache Software License 2.0 (the "ASL")
* - under the jOOQ License and Maintenance Agreement (the "jOOQ License")
* =============================================================================
* You may choose which license applies to you:
*
* - If you're using this work with Open Source databases, you may choose
* either ASL or jOOQ License.
* - If you're using this work with at least one commercial database, you must
* choose jOOQ License
*
* For more information, please visit http://www.jooq.org/licenses
*
* Apache Software License 2.0:
* -----------------------------------------------------------------------------
* 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.
*
* jOOQ License and Maintenance Agreement:
* -----------------------------------------------------------------------------
* Data Geekery grants the Customer the non-exclusive, timely limited and
* non-transferable license to install and use the Software under the terms of
* the jOOQ License and Maintenance Agreement.
*
* This library is distributed with a LIMITED WARRANTY. See the jOOQ License
* and Maintenance Agreement for more details: http://www.jooq.org/licensing
*/
package org.jooq.impl;
import static org.jooq.conf.ParamType.INLINED;
import static org.jooq.impl.DSL.trueCondition;
import static org.jooq.impl.DSL.using;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import org.jooq.Condition;
import org.jooq.Configuration;
import org.jooq.Context;
import org.jooq.Field;
import org.jooq.PivotForStep;
import org.jooq.PivotInStep;
import org.jooq.QueryPart;
import org.jooq.Record;
import org.jooq.Select;
import org.jooq.Table;
import org.jooq.conf.ParamType;
/**
* A pivot table implementation
*
* Future versions of jOOQ could simulate Oracle's PIVOT clause by
* rendering a subquery instead.
*
* @author Lukas Eder
*/
class Pivot
extends AbstractTable
implements
PivotForStep,
PivotInStep {
/**
* Generated UID
*/
private static final long serialVersionUID = -7918219502110473521L;
private final Table table;
private final SelectFieldList aggregateFunctions;
private Field on;
private SelectFieldList in;
Pivot(Table table, Field... aggregateFunctions) {
super("pivot");
this.table = table;
this.aggregateFunctions = new SelectFieldList(aggregateFunctions);
}
// ------------------------------------------------------------------------
// XXX: Table API
// ------------------------------------------------------------------------
@Override
public final Class getRecordType() {
return RecordImpl.class;
}
@Override
public final void accept(Context ctx) {
ctx.visit(pivot(ctx.configuration()));
}
private Table pivot(Configuration configuration) {
switch (configuration.dialect()) {
/* [pro] xx
xx xxxxxx xxx xxxxxx xxxxxxx xxx xxx xxxxx xxxxxx
xxxx xxxxxxx
xxxx xxxxxxxxxx
xxxx xxxxxxxxxx x
xxxxxx xxx xxxxxxxxxxxxxxxxxxx
x
xx [/pro] */
// Some other dialects can simulate it. This implementation is
// EXPERIMENTAL and not officially supported
default: {
return new DefaultPivotTable();
}
}
}
/**
* A simulation of Oracle's PIVOT table
*/
private class DefaultPivotTable extends DialectPivotTable {
/**
* Generated UID
*/
private static final long serialVersionUID = -5930286639571867314L;
@Override
public final void accept(Context ctx) {
ctx.declareTables(true)
.visit(select(ctx.configuration()))
.declareTables(false);
}
private Table select(Configuration configuration) {
List> groupingFields = new ArrayList>();
List> aliasedGroupingFields = new ArrayList>();
List> aggregatedFields = new ArrayList>();
Table pivot = table.as("pivot_outer");
// Clearly, the API should be improved to make this more object-
// oriented...
// This loop finds all fields that are used in aggregate
// functions. They're excluded from the GROUP BY clause
for (Field field : aggregateFunctions) {
if (field instanceof Function) {
for (QueryPart argument : ((Function) field).getArguments()) {
if (argument instanceof Field) {
aggregatedFields.add((Field) argument);
}
}
}
}
// This loop finds all fields qualify for GROUP BY clauses
for (Field field : table.fields()) {
if (!aggregatedFields.contains(field)) {
if (!on.equals(field)) {
aliasedGroupingFields.add(pivot.field(field));
groupingFields.add(field);
}
}
}
// The product {aggregateFunctions} x {in}
List> aggregationSelects = new ArrayList>();
for (Field inField : in) {
for (Field aggregateFunction : aggregateFunctions) {
Condition join = trueCondition();
for (Field field : groupingFields) {
join = join.and(condition(pivot, field));
}
@SuppressWarnings("unchecked")
Select aggregateSelect = using(configuration)
.select(aggregateFunction)
.from(table)
.where(on.equal((Field) inField))
.and(join);
aggregationSelects.add(aggregateSelect.asField(inField.getName() + "_" + aggregateFunction.getName()));
}
}
// This is the complete select
Table select =
using(configuration)
.select(aliasedGroupingFields)
.select(aggregationSelects)
.from(pivot)
.where(pivot.field(on).in(in.toArray(new Field[0])))
.groupBy(aliasedGroupingFields)
.asTable();
return select;
}
}
/* [pro] xx
xxx
x xxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxx
xx
xxxxxxx xxxxx xxxxxxxxxxxxxxxx xxxxxxx xxxxxxxxxxxxxxxxx x
xxx
x xxxxxxxxx xxx
xx
xxxxxxx xxxxxx xxxxx xxxx xxxxxxxxxxxxxxxx x xxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxx
xxxxxx xxxxx xxxx xxxxxxxxxxxxxxxxx xxxx x
xx xxxx xxxxxxxxx xxx xxx xxxxxxx xxxxxx xx xxxxx xxxxxx
xxxxxxxxx xxxxxxxxx x xxxxxxxxxxxxxxxx
xxxxxxx xxxxxxxxxxxxx x xxxxxxxxxxxxxxxxxxxx
xxxxxxx xxxxxxxxxxxxx x xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxx xxx
xxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxx xx
xxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx xxx
xxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxx
xxxxxxxxx
xxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxx
x
x
xx [/pro] */
/**
* A base class for dialect-specific implementations of the pivot table
*/
private abstract class DialectPivotTable extends AbstractTable {
/**
* Generated UID
*/
private static final long serialVersionUID = 2662639259338694177L;
DialectPivotTable() {
super("pivot");
}
@Override
public final Class getRecordType() {
return RecordImpl.class;
}
@Override
public final Table as(String as) {
return new TableAlias(this, as);
}
@Override
public final Table as(String as, String... fieldAliases) {
return new TableAlias(this, as, fieldAliases);
}
@Override
final Fields fields0() {
return Pivot.this.fields0();
}
}
/**
* Extracted method for type-safety
*/
private Condition condition(Table pivot, Field field) {
return field.equal(pivot.field(field));
}
@Override
public final boolean declaresTables() {
return true;
}
@Override
public final Table as(String alias) {
return new TableAlias(this, alias, true);
}
@Override
public final Table as(String alias, String... fieldAliases) {
return new TableAlias(this, alias, fieldAliases, true);
}
@Override
final Fields fields0() {
return new Fields();
}
// ------------------------------------------------------------------------
// XXX: Pivot API
// ------------------------------------------------------------------------
/* [pro] xx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxx
xxxxxx xxxxx xxx xxxxxxxx xxxxxxxxxxx xxxxxx x
xx xxx xxxxxxxx xxxxx xx xxx xx xxxxxxx xxx xxxxxx xxxxxxxxxx
xxxxxxx x xxxxxxxxxx xxxxxx
xxxxxx xxxxxxxxxx xxxxx
x
xxxxxxxxx
xxxxxx xxxxx xxxxxxxxxxxxx xxxxxxxxxxxx xxxxxxx x
xxxxxx xxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxx
x
xxxxxxxxx
xxxxxx xxxxx xxxxxxxxxxxxx xxxxxxxxxxxxxx xx x
xxxxxxx x xxx xxxxxxxxxxxxxxxxxxx
xxxxxx xxxxx
x
xxxxxxxxx
xxxxxx xxxxx xxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxx xxxxxxxxx xx x
xxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxx
x
xx [/pro] */
}