package net.sourceforge.javautil.database.query.sql;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import net.sourceforge.javautil.database.query.IQueryFragmentCriteriaParameter;
import net.sourceforge.javautil.database.query.IQueryBuilder;
import net.sourceforge.javautil.database.query.QueryBuilderBase;
import net.sourceforge.javautil.database.query.QuerySnapshot;
import net.sourceforge.javautil.database.query.sql.JoinTableFragment.Type;
import net.sourceforge.javautil.database.query.sql.OrderByFragment.Direction;
/**
* A base for most SQL based or related builders.
*
* @author elponderador
* @author $Author: ponderator $
* @version $Id: QueryBuilderSelect.java 2286 2010-06-14 04:18:27Z ponderator $
*/
public abstract class QueryBuilderSelect<E, R extends List> extends QueryBuilderBase<E, R> {
protected Map<String, Object> parameters = new LinkedHashMap<String, Object>();
protected int firstRecord = -1;
protected int maxRecords = -1;
/**
* The tables that this query will select from
*/
protected CompositeTableFragment<TableFragment> from;
/**
* Table joins for this query
*/
protected CompositeJoinedTableFragment joins;
/**
* The fields that this query is selecting
*/
protected CompositeFieldsFragment<SelectedFieldFragment> selected;
/**
* The where clause of the query
*/
protected CompositeCriteria where;
/**
* The having clause of the query
*/
protected CompositeCriteria having;
/**
* The group by fields for the query
*/
protected CompositeFieldsFragment<FieldReferenceFragment> groupBy;
/**
* The order/sorting setup for this query
*/
protected CompositeFieldsFragment<OrderByFragment> orderBy;
/**
* @return The {@link #selected} fields
*/
public CompositeFieldsFragment<SelectedFieldFragment> getSelected() {
if (this.selected == null) this.selected = new CompositeFieldsFragment<SelectedFieldFragment>(this);
return selected;
}
public void setSelected(CompositeFieldsFragment<SelectedFieldFragment> selected) { this.selected = selected; }
public QueryBuilderSelect selectAliased (String field, String alias) {
this.getSelected().addSelected(field, alias); return this;
}
public QueryBuilderSelect select (List<String> fields) {
return this.select(fields.toArray(new String[fields.size()]));
}
public QueryBuilderSelect select (String... fields) {
for (String field : fields) {
this.getSelected().addSelected(field);
}
return this;
}
/**
* @return The {@link #from} tables
*/
public CompositeTableFragment<TableFragment> getFrom() {
if (this.from == null) this.from = new CompositeTableFragment<TableFragment>(this);
return from;
}
public void setFrom(CompositeTableFragment<TableFragment> from) { this.from = from; }
public QueryBuilderSelect from (String table) { return this.fromAliased(table, null); }
public QueryBuilderSelect fromAliased (String table, String alias) { this.getFrom().add(table, alias); return this; }
/**
* @return The {@link #joins}
*/
public CompositeJoinedTableFragment getJoins() {
if (this.joins == null) this.joins = new CompositeJoinedTableFragment(this);
return joins;
}
public void setJoins(CompositeJoinedTableFragment joins) { this.joins = joins; }
public QueryBuilderSelect join(String table, String alias) {
return this.join(table, alias, "INNER", null);
}
public QueryBuilderSelect join(String table, String alias, JoinTableConditionFragment condition) {
return this.join(table, alias, "INNER", condition);
}
public QueryBuilderSelect join(String table, String alias, String type) {
return this.join(table, alias, type, null);
}
public QueryBuilderSelect join(String table, String alias, String type, JoinTableConditionFragment condition) {
this.getJoins().addJoinedTable(table, alias, Type.valueOf(type.toUpperCase()), condition); return this;
}
/**
* @return The {@link #where} clause
*/
public CompositeCriteria getWhere() {
if (this.where == null) this.where = new CompositeCriteriaFragments(this);
return (CompositeCriteria) where;
}
public void setWhere(CompositeCriteria where) { this.where = where; }
/**
* @return The {@link #having} clause
*/
public CompositeCriteria getHaving() {
if (this.having == null) this.having = new CompositeCriteriaFragments(this);
return having;
}
public void setHaving(CompositeCriteria having) { this.having = having; }
/**
* @return The parameters for parameterized fragments
*/
public Map<String, Object> getParameters() { return parameters; }
public void setParameters(Map<String, Object> parameters) { this.parameters = parameters; }
/**
* @return The {@link #groupBy} clause
*/
public CompositeFieldsFragment<FieldReferenceFragment> getGroupBy() {
if (this.groupBy == null) this.groupBy = new CompositeFieldsFragment<FieldReferenceFragment>(this);
return groupBy;
}
public void setGroupBy(CompositeFieldsFragment<FieldReferenceFragment> groupBy) { this.groupBy = groupBy; }
public QueryBuilderSelect groupBy (String... fields) {
for (String field : fields)
this.getGroupBy().addReference(field);
return this;
}
/**
* @return The {@link #orderBy}
*/
public CompositeFieldsFragment<OrderByFragment> getOrderBy() {
if (this.orderBy == null) this.orderBy = new CompositeFieldsFragment<OrderByFragment>(this);
return orderBy;
}
public void setOrderBy(CompositeFieldsFragment<OrderByFragment> orderBy) { this.orderBy = orderBy; }
public QueryBuilderSelect orderBy (String field) { return this.orderBy(field, "ASC"); }
public QueryBuilderSelect orderBy (String field, String direction) {
this.getOrderBy().addOrderBy(field, Direction.valueOf(direction.toUpperCase())); return this;
}
public int getFirstRecord() { return firstRecord; }
public void setFirstRecord(int firstRecord) { this.firstRecord = firstRecord; }
public int getMaxRecords() { return maxRecords; }
public void setMaxRecords(int maxRecords) { this.maxRecords = maxRecords; }
public void setResultLimit(int offset, int maximum) {
this.firstRecord = offset;
this.maxRecords = maximum;
}
public QuerySnapshot build() {
List<IQueryFragmentCriteriaParameter> parameters = new ArrayList<IQueryFragmentCriteriaParameter>();
StringBuilder builder = this.buildQuery(parameters);
this.lastBuilt = System.currentTimeMillis();
return new QuerySnapshot(this, builder.toString(), parameters);
}
protected StringBuilder buildQuery (List<IQueryFragmentCriteriaParameter> parameters) {
StringBuilder builder = new StringBuilder();
builder.append("SELECT ");
selected.append(builder);
builder.append(" FROM ");
from.append(builder);
if (joins != null && joins.getFragmentCount() > 0)
joins.append(builder.append(" "));
if (where != null && where.getFragmentCount() > 0) {
where.append(builder.append(" WHERE "));
where.appendParameters(parameters);
}
if (groupBy != null && groupBy.getFragmentCount() > 0)
groupBy.append(builder.append(" GROUP BY "));
if (having != null && having.getFragmentCount() > 0) {
having.append(builder.append(" HAVING "));
having.appendParameters(parameters);
}
if (orderBy != null && orderBy.getFragmentCount() > 0) {
builder.append(" ORDER BY ");
orderBy.append(builder);
}
return builder;
}
}