org.efaps.db.wrapper.SQLSelect Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of efaps-kernel Show documentation
Show all versions of efaps-kernel Show documentation
eFaps is a framework used to map objects with or without attached files to
a relational database and optional file systems (only for attaches files). Configurable access control can be provided down to object and attribute level depending on implementation and use case. Depending on requirements, events (like triggers) allow to implement business logic and to separate business logic from user interface.
The framework includes integrations (e.g. webdav, full text search) and a web application as 'simple' configurable user interface. Some best practises, example web application modules (e.g. team work module) support administrators and implementers using this framework.
/*
* Copyright 2003 - 2012 The eFaps Team
*
* 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.
*
* Revision: $Rev: 7483 $
* Last Changed: $Date: 2012-05-11 11:57:38 -0500 (Fri, 11 May 2012) $
* Last Changed By: $Author: [email protected] $
*/
package org.efaps.db.wrapper;
import java.util.ArrayList;
import java.util.List;
import org.efaps.db.Context;
import org.efaps.db.search.section.AbstractQSection;
import org.efaps.util.EFapsException;
/**
* An easy wrapper for a SQL select statement.
*
* @author The eFaps Team
* @version $Id: SQLSelect.java 7483 2012-05-11 16:57:38Z [email protected] $
*/
public class SQLSelect
{
/**
* Parts that will be added to the created SQL Statement.
*/
private final List parts = new ArrayList();
/**
* Selected columns.
*
* @see #column(String)
*/
private final List columns = new ArrayList();
/**
* Selected tables.
*
* @see #from(String)
*/
private final List fromTables = new ArrayList();
/**
* Must the select be distinct.
*
*/
private boolean distinct = false;
/**
* Appends a selected column.
*
* @param _name name of the column
* @return this SQL select statement
* @see #columns
*/
public SQLSelect column(final String _name)
{
this.columns.add(new Column(null, _name));
return this;
}
/**
* Appends a selected column _name for given
* _tableIndex.
*
* @param _tableIndex index of the table
* @param _columnName name of the column
* @return this SQL select statement
* @see #columns
*/
public SQLSelect column(final int _tableIndex,
final String _columnName)
{
this.columns.add(new Column(_tableIndex, _columnName));
return this;
}
/**
* Getter method for the instance variable {@link #columns}.
*
* @return value of instance variable {@link #columns}
*/
public List getColumns()
{
return this.columns;
}
/**
* Appends a table as from selected table.
*
* @param _name name of the table
* @return this SQL select statement
* @see #fromTables
*/
public SQLSelect from(final String _name)
{
this.fromTables.add(new FromTable(_name, null));
return this;
}
/**
* Appends a table as from selected table.
*
* @param _tableName name of the SQL table
* @param _tableIndex index of the table within the SQL statement
* @return this SQL select statement
* @see #fromTables
*/
public SQLSelect from(final String _tableName,
final int _tableIndex)
{
this.fromTables.add(new FromTable(_tableName, _tableIndex));
return this;
}
/**
* Getter method for the instance variable {@link #fromTables}.
*
* @return value of instance variable {@link #fromTables}
*/
public List getFromTables()
{
return this.fromTables;
}
/**
*
* @param _tableName name of the SQL table
* @param _tableIndex index of the table used within the SQL select
* statement
* @param _columnName name of the column of table _tableName
* used for "left join"
* @param _joinTableIndex index of the table from which is joined
* @param _joinColumnName name of the column of the table from which is
* joined
* @return this SQL select statement instance
*/
public SQLSelect leftJoin(final String _tableName,
final int _tableIndex,
final String _columnName,
final int _joinTableIndex,
final String _joinColumnName)
{
this.fromTables.add(new FromTableLeftJoin(_tableName, _tableIndex, _columnName,
_joinTableIndex, _joinColumnName));
return this;
}
/**
*
* @param _tableName name of the SQL table
* @param _tableIndex index of the table used within the SQL select
* statement
* @param _columnNames names of the columns of table _tableName
* used for "left join"
* @param _joinTableIndex index of the table from which is joined
* @param _joinColumnNames names of the column of the table from which is
* joined
* @return this SQL select statement instance
*/
public SQLSelect leftJoin(final String _tableName,
final int _tableIndex,
final String[] _columnNames,
final int _joinTableIndex,
final String[] _joinColumnNames)
{
this.fromTables.add(new FromTableLeftJoin(_tableName, _tableIndex, _columnNames,
_joinTableIndex, _joinColumnNames));
return this;
}
/**
*
* @param _tableName name of the SQL table
* @param _tableIndex index of the table used within the SQL select
* statement
* @param _columnName name of the column of table _tableName
* used for "left join"
* @param _joinTableIndex index of the table from which is joined
* @param _joinColumnName name of the column of the table from which is
* joined
* @return this SQL select statement instance
*/
public SQLSelect innerJoin(final String _tableName,
final int _tableIndex,
final String _columnName,
final int _joinTableIndex,
final String _joinColumnName)
{
this.fromTables.add(new FromTableInnerJoin(_tableName, _tableIndex, _columnName,
_joinTableIndex, _joinColumnName));
return this;
}
/**
*
* @param _tableName name of the SQL table
* @param _tableIndex index of the table used within the SQL select
* statement
* @param _columnNames names of the columns of table _tableName
* used for "left join"
* @param _joinTableIndex index of the table from which is joined
* @param _joinColumnNames names of the columns of the table from which is
* joined
* @return this SQL select statement instance
*/
public SQLSelect innerJoin(final String _tableName,
final int _tableIndex,
final String[] _columnNames,
final int _joinTableIndex,
final String[] _joinColumnNames)
{
this.fromTables.add(new FromTableInnerJoin(_tableName, _tableIndex, _columnNames,
_joinTableIndex, _joinColumnNames));
return this;
}
/**
* Returns the depending SQL statement.
*
* @return SQL statement
*/
public String getSQL()
{
final StringBuilder cmd = new StringBuilder().append(" ")
.append(Context.getDbType().getSQLPart(SQLPart.SELECT)).append(" ");
if (this.distinct) {
cmd.append(Context.getDbType().getSQLPart(SQLPart.DISTINCT)).append(" ");
}
boolean first = true;
for (final Column column : this.columns) {
if (first) {
first = false;
} else {
cmd.append(Context.getDbType().getSQLPart(SQLPart.COMMA));
}
column.appendSQL(cmd);
}
cmd.append(" ").append(Context.getDbType().getSQLPart(SQLPart.FROM)).append(" ");
first = true;
for (final FromTable fromTable : this.fromTables) {
fromTable.appendSQL(first, cmd);
if (first) {
first = false;
}
}
cmd.append(" ");
for (final SQLSelectPart part : this.parts) {
part.appendSQL(cmd);
cmd.append(" ");
}
return cmd.toString();
}
/**
* Must this SQLSelect be distinct.
*
* @param _distinct distinct
* @return this
*/
public SQLSelect distinct(final boolean _distinct)
{
this.distinct = _distinct;
return this;
}
/**
* @param _section Sectin o to be added
* @throws EFapsException on error
* @return this
*/
public SQLSelect addSection(final AbstractQSection _section)
throws EFapsException
{
if (_section != null) {
_section.appendSQL(this);
}
return this;
}
/**
* @param _part Part to be added
* @return this
*/
public SQLSelect addPart(final SQLPart _part)
{
this.parts.add(new SQLSelectPart(_part));
return this;
}
/**
* Add a column as part.
* @param _tableIndex index of the table
* @param _columnName name of the column
* @return this
*/
public SQLSelect addColumnPart(final Integer _tableIndex,
final String _columnName)
{
this.parts.add(new Column(_tableIndex, _columnName));
return this;
}
/**
* Add a table as part.
* @param _tableName name of the table
* @param _tableIndex index of the table
* @return this
*/
public SQLSelect addTablePart(final String _tableName,
final Integer _tableIndex)
{
this.parts.add(new FromTable(_tableName, _tableIndex));
return this;
}
/**
* @param _char val;ue to be added as nested Select part
* @return this
*/
public SQLSelect addNestedSelectPart(final CharSequence _char)
{
this.parts.add(new NestedSelect(_char));
return this;
}
/**
* @param _value value to be added as part
* @return this
*/
public SQLSelect addValuePart(final Object _value)
{
this.parts.add(new Value(_value));
return this;
}
/**
* @param _value add the value that must be escaped
* @return this
*/
public SQLSelect addEscapedValuePart(final String _value)
{
this.parts.add(new EscapedValue(_value));
return this;
}
/**
* Add a timestamp value to the select.
* @param __timestampString String to be casted to a timestamp
* @return this
*/
public SQLSelect addTimestampValue(final String _isoDateTime)
{
this.parts.add(new Value(Context.getDbType().getTimestampValue(_isoDateTime)));
return this;
}
/**
* Add a timestamp value to the select.
* @param __timestampString String to be casted to a timestamp
* @return this
*/
public SQLSelect addBooleanValue(final Boolean _value)
{
this.parts.add(new BooleanValue(_value));
return this;
}
/**
* @return get a new instance of this SQLSelect
*/
public SQLSelect getCopy()
{
final SQLSelect select = new SQLSelect();
select.columns.addAll(this.columns);
select.parts.addAll(this.parts);
select.fromTables.addAll(this.fromTables);
select.distinct = this.distinct;
return select;
}
@Override
public String toString()
{
return getSQL();
}
/**
*
*/
protected static class FromTable
extends SQLSelectPart
{
/** SQL name of the table. */
private final String tableName;
/** Index of the table within the SQL select statement. */
private final Integer tableIndex;
/**
* Default constructor.
*
* @param _tableName name of the SQL table
* @param _tableIndex index of the table
*/
protected FromTable(final String _tableName,
final Integer _tableIndex)
{
this.tableName = _tableName;
this.tableIndex = _tableIndex;
}
/**
* Returns the related {@link #tableName SQL table name} which is
* represented by this class.
*
* @return name of the SQL table
* @see #tableName
*/
public String getTableName()
{
return this.tableName;
}
/**
* Returns the related {@link #tableIndex table index} in the SQL select
* statement.
*
* @return table index
* @see #tableIndex
*/
public Integer getTableIndex()
{
return this.tableIndex;
}
@Override
public void appendSQL(final StringBuilder _cmd)
{
appendSQL(true, _cmd);
}
/**
* Appends the {@link #tableName name} of this table depending on a
* given {@link #tableIndex index} to the SQL select statement in
* _cmd. If _first is true a comma ','
* is defined in the front.
*
* @param _first true if first statement and a comma must be
* prefixed; otherwise false
* @param _cmd string builder used to append SQL statement for this
* table
*/
public void appendSQL(final boolean _first,
final StringBuilder _cmd)
{
if (!_first) {
_cmd.append(Context.getDbType().getSQLPart(SQLPart.COMMA));
}
_cmd.append(Context.getDbType().getTableQuote())
.append(this.tableName)
.append(Context.getDbType().getTableQuote());
if (this.tableIndex != null) {
_cmd.append(" T").append(this.tableIndex);
}
}
}
/**
*
*/
protected static class FromTableLeftJoin
extends SQLSelect.FromTable
{
/**
* Name of the columns used for the "left join".
*/
private final String[] columnNames;
/**
* Index of the table from which is joined.
*/
private final int joinTableIndex;
/**
* Name of the columns of the table from which is joined.
*/
private final String[] joinColumnNames;
/**
*
* @param _tableName name of the SQL table
* @param _tableIndex index of the table used within the SQL select
* statement
* @param _columnName name of the column of table
* _tableName used for "left join"
* @param _joinTableIndex index of the table from which is joined
* @param _joinColumnName name of the column of the table from which is
* joined
*/
protected FromTableLeftJoin(final String _tableName,
final Integer _tableIndex,
final String _columnName,
final int _joinTableIndex,
final String _joinColumnName)
{
super(_tableName, _tableIndex);
this.columnNames = new String[] {_columnName};
this.joinTableIndex = _joinTableIndex;
this.joinColumnNames = new String[] {_joinColumnName};
}
/**
* Constructor used to join on more than one column.
*
* @param _tableName name of the SQL table
* @param _tableIndex index of the table used within the
* SQL select statement
* @param _columnNames names of the columns of table _tableName
* used for "left join"
* @param _joinTableIndex index of the table from which is joined
* @param _joinColumnNames names of the columns of the table from
* which is joined
*/
private FromTableLeftJoin(final String _tableName,
final Integer _tableIndex,
final String[] _columnNames,
final int _joinTableIndex,
final String[] _joinColumnNames)
{
super(_tableName, _tableIndex);
this.columnNames = _columnNames;
this.joinTableIndex = _joinTableIndex;
this.joinColumnNames = _joinColumnNames;
}
/**
* Appends the SQL statement for this left join.
*
* @param _first true if first statement and a space must be
* prefixed; otherwise false
* @param _cmd string builder used to append SQL statement for this left
* join
*/
@Override
public void appendSQL(final boolean _first,
final StringBuilder _cmd)
{
if (!_first) {
_cmd.append(' ');
}
for (int i = 0; i < this.columnNames.length; i++) {
if (i == 0) {
_cmd.append(Context.getDbType().getSQLPart(getJoin()))
.append(" ").append(Context.getDbType().getSQLPart(SQLPart.JOIN)).append(" ")
.append(Context.getDbType().getTableQuote())
.append(getTableName())
.append(Context.getDbType().getTableQuote())
.append(" T").append(getTableIndex()).append(" ")
.append(Context.getDbType().getSQLPart(SQLPart.ON));
} else {
_cmd.append(" ").append(Context.getDbType().getSQLPart(SQLPart.AND)).append(" ");
}
_cmd.append(" T").append(this.joinTableIndex).append('.')
.append(Context.getDbType().getColumnQuote())
.append(this.joinColumnNames[i])
.append(Context.getDbType().getColumnQuote())
.append(Context.getDbType().getSQLPart(SQLPart.EQUAL))
.append("T").append(getTableIndex()).append('.')
.append(Context.getDbType().getColumnQuote())
.append(this.columnNames[i])
.append(Context.getDbType().getColumnQuote());
}
}
/**
* @return the join for this class
*/
protected SQLPart getJoin()
{
return SQLPart.LEFT;
}
}
/**
* Render an inner join.
*/
protected static class FromTableInnerJoin
extends SQLSelect.FromTableLeftJoin
{
/**
*
* @param _tableName name of the SQL table
* @param _tableIndex index of the table used within the SQL select
* statement
* @param _columnName name of the column of table
* _tableName used for "left join"
* @param _joinTableIndex index of the table from which is joined
* @param _joinColumnName name of the column of the table from which is
* joined
*/
protected FromTableInnerJoin(final String _tableName,
final Integer _tableIndex,
final String _columnName,
final int _joinTableIndex,
final String _joinColumnName)
{
super(_tableName, _tableIndex, _columnName, _joinTableIndex, _joinColumnName);
}
/**
*
* @param _tableName name of the SQL table
* @param _tableIndex index of the table used within the SQL select statement
* @param _columnNames name of the column of table _tableName used for "left join"
* @param _joinTableIndex index of the table from which is joined
* @param _joinColumnNames name of the column of the table from which is joined
*/
private FromTableInnerJoin(final String _tableName,
final Integer _tableIndex,
final String[] _columnNames,
final int _joinTableIndex,
final String[] _joinColumnNames)
{
super(_tableName, _tableIndex, _columnNames, _joinTableIndex, _joinColumnNames);
}
/**
* {@inheritDoc}
*/
@Override
protected SQLPart getJoin()
{
return SQLPart.INNER;
}
}
/**
* Nested Select.
*/
protected static class NestedSelect
extends SQLSelectPart
{
/**
* Value.
*/
private final CharSequence value;
/**
* @param _value Value
*/
public NestedSelect(final CharSequence _value)
{
this.value = _value;
}
@Override
public void appendSQL(final StringBuilder _cmd)
{
_cmd.append(this.value);
}
}
/**
* Value .
*/
protected static class Value
extends SQLSelectPart
{
/**
* Value.
*/
private final Object value;
/**
* @param _value Value
*/
public Value(final Object _value)
{
this.value = _value;
}
@Override
public void appendSQL(final StringBuilder _cmd)
{
_cmd.append(this.value);
}
@Override
public String toString()
{
return this.value.toString();
}
}
/**
* Value to be escaped.
*/
protected static class EscapedValue
extends SQLSelectPart
{
/**
* Value.
*/
private final String value;
/**
* @param _value Value
*/
public EscapedValue(final String _value)
{
this.value = _value;
}
@Override
public void appendSQL(final StringBuilder _cmd)
{
_cmd.append(Context.getDbType().escapeForWhere(this.value));
}
@Override
public String toString()
{
return Context.getDbType().escapeForWhere(this.value);
}
}
/**
* Value to be escaped.
*/
protected static class BooleanValue
extends SQLSelectPart
{
/**
* Value.
*/
private final Boolean value;
/**
* @param _value Value
*/
public BooleanValue(final Boolean _value)
{
this.value = _value;
}
@Override
public void appendSQL(final StringBuilder _cmd)
{
_cmd.append(Context.getDbType().getBooleanValue(this.value));
}
@Override
public String toString()
{
final String ret = Context.getDbType().getBooleanValue(this.value).toString();
return ret;
}
}
/**
*
*/
public static class SQLSelectPart
{
/**
* Part.
*/
private SQLPart sqlpart;
/**
* Constructor.
* @param _part SQLPart
*/
public SQLSelectPart(final SQLPart _part)
{
this.sqlpart = _part;
}
/**
* Constructor.
*/
protected SQLSelectPart()
{
}
/**
* @param _cmd StringBuilder to append to
*/
public void appendSQL(final StringBuilder _cmd)
{
_cmd.append(Context.getDbType().getSQLPart(this.sqlpart));
}
@Override
public String toString()
{
return Context.getDbType().getSQLPart(this.sqlpart);
}
}
/**
* Column.
*/
protected static class Column
extends SQLSelect.SQLSelectPart
{
/**
* Index of the table in the select statement where this column is
* defined.
*/
private final Integer tableIndex;
/** SQL name of the column. */
private final String columnName;
/**
* Default constructor.
*
* @param _tableIndex related index of the table
* @param _columnName SQL name of the column
*/
protected Column(final Integer _tableIndex,
final String _columnName)
{
this.tableIndex = _tableIndex;
this.columnName = _columnName;
}
/**
*
* @param _cmd string builder used to append SQL statement for this
* column
*/
@Override
public void appendSQL(final StringBuilder _cmd)
{
if (this.tableIndex != null) {
_cmd.append("T").append(this.tableIndex).append(".");
}
_cmd.append(Context.getDbType().getColumnQuote())
.append(this.columnName)
.append(Context.getDbType().getColumnQuote());
}
@Override
public String toString()
{
final StringBuilder cmd = new StringBuilder();
appendSQL(cmd);
return cmd.toString();
}
}
}