/*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you 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.
*/
package org.apache.empire.db;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.Vector;
import org.apache.empire.commons.Errors;
import org.apache.empire.data.DataType;
import org.apache.empire.db.expr.compare.DBCompareColExpr;
import org.apache.empire.db.expr.compare.DBCompareExpr;
import org.apache.empire.db.expr.join.DBJoinExpr;
import org.apache.empire.db.expr.join.DBJoinExprEx;
import org.apache.empire.db.expr.set.DBSetExpr;
/**
* This abstract class handles the creation of the SQL-Commands.
* There are methods to create SQL-Commandos, like update, insert,
* delete and select.
* <P>
*
*
*/
public abstract class DBCommand extends DBCommandExpr
implements Cloneable
{
public static final class DBCmdParameter extends DBExpr
{
private DBCommand cmd;
protected int index;
protected DBCmdParameter(DBCommand cmd, int index)
{
this.cmd = cmd;
this.index = index;
}
@Override
public void addSQL(StringBuilder buf, long context)
{
buf.append("?"); //$NON-NLS-1$
}
/**
* @see org.apache.empire.db.DBExpr#addReferencedColumns(Set)
*/
@Override
public void addReferencedColumns(Set<DBColumn> list)
{
// Nothing to add
}
@Override
public DBDatabase getDatabase()
{
return cmd.getDatabase();
}
public Object getValue()
{
return cmd.cmdParams.get(index);
}
public void setValue(Object value)
{
cmd.cmdParams.set(index, value);
}
}
// Distinct Select
protected boolean selectDistinct = false;
// Lists
protected List<DBColumnExpr> select = null;
protected List<DBSetExpr> set = null;
protected List<DBJoinExpr> joins = null; // Join Info
protected List<DBCompareExpr> where = null;
protected List<DBCompareExpr> having = null;
protected List<DBColumnExpr> groupBy = null;
// Params for prepared Statements
protected Vector<Object> cmdParams = null;
// Database
private DBDatabase db;
/**
* Constructs a new DBCommand object and set the specified DBDatabase object.
*
* @param db the current database object
*/
protected DBCommand(DBDatabase db)
{
this.db = db;
}
/**
* Creates a clone of this class.
*/
@Override
public DBCommand clone()
{
try
{
return (DBCommand)super.clone();
} catch(CloneNotSupportedException e)
{
log.error("Cloning DBCommand object failed!", e);
return null;
}
}
/**
* Returns the current DBDatabase object.
*
* @return the current DBDatabase object
*/
@Override
public DBDatabase getDatabase()
{
return db;
}
@Override
public boolean isValid()
{
return isValidQuery() || isValidUpdate();
}
/**
* Returns whether the command object can produce a select sql-statement.
*
* @return true if at least one select expression has been set
*/
public boolean isValidQuery()
{
return (select != null);
}
/**
* Returns whether the command object can produce a update sql-statement.
*
* @return true if a set expression has been set.
*/
public boolean isValidUpdate()
{
return (set != null);
}
/**
* Sets whether or not the select statement should contain
* the distinct directive .
*/
public void selectDistinct()
{
this.selectDistinct = true;
}
/**
* Returns whether or not the select statement will be distinct or not.
*
* @return true if the select will contain the distinct directive or false otherwise.
*/
public boolean isSelectDistinct()
{
return selectDistinct;
}
/**
* Adds a DBColumnExpr object to the Vector: 'select'.
*
* @param expr the DBColumnExpr object
*/
public void select(DBColumnExpr expr)
{ // Select this column
if (select == null)
select = new ArrayList<DBColumnExpr>();
if (expr != null && select.contains(expr) == false)
select.add(expr);
}
/**
* This helper function adds two DBColumnExpr objects
* to the Vector: 'select'
*
* @param expr1 the first DBColumnExpr object
* @param expr2 the second DBColumnExpr object
*/
public void select(DBColumnExpr expr1, DBColumnExpr expr2)
{
select(expr1);
select(expr2);
}
/**
* This helper function adds three DBColumnExpr objects to the Vector: 'select'.
*/
public void select(DBColumnExpr expr1, DBColumnExpr expr2, DBColumnExpr expr3)
{
select(expr1);
select(expr2);
select(expr3);
}
/**
* This helper function adds four DBColumnExpr objects to the Vector: 'select'.
*/
public void select(DBColumnExpr expr1, DBColumnExpr expr2, DBColumnExpr expr3, DBColumnExpr expr4)
{
select(expr1);
select(expr2);
select(expr3);
select(expr4);
}
/**
* This helper function adds five DBColumnExpr objects
* to the Vector: 'select'.
*/
public void select(DBColumnExpr expr1, DBColumnExpr expr2, DBColumnExpr expr3, DBColumnExpr expr4, DBColumnExpr expr5)
{
select(expr1);
select(expr2);
select(expr3);
select(expr4);
select(expr5);
}
/**
* This helper function adds an array of DBColumnExpr
* objects to list of select-columns.
*/
public void select(DBColumnExpr[] exprList)
{
for (int i=0; i<exprList.length; i++)
{
select(exprList[i]);
}
}
/**
* Adds a list of column expression to the select clause
*/
public void select(Collection<DBColumnExpr> columns)
{
for (DBColumnExpr expr : columns)
select(expr);
}
/**
* Adds a list of column expression to the select clause
*/
public void select(List<DBColumn> columns)
{
for (int i = 0; i < columns.size(); i++)
select(columns.get(i));
}
private boolean useCmdParam(DBColumn col)
{
DataType dt = col.getDataType();
return ( dt==DataType.BLOB || dt==DataType.CLOB );
}
private Object getCmdParamValue(DBColumn col, Object value)
{
switch (col.getDataType())
{
case BLOB:
if (value == null)
return null;
if (value instanceof DBBlobData)
return value;
if (value instanceof byte[])
return new DBBlobData((byte[])value);
// create a blob data
return new DBBlobData(value.toString());
case CLOB:
if (value == null)
return null;
if (value instanceof DBClobData)
return value;
// create a clob data
return new DBClobData(value.toString());
default:
return value;
}
}
/**
* Inserts DBSetExpr objects to the Vector 'set'.
*
* @param expr the DBSetExpr object(s)
*/
public void set(DBSetExpr expr)
{
if (set == null)
set = new ArrayList<DBSetExpr>();
for (int i = 0; i < set.size(); i++)
{
DBSetExpr chk = set.get(i);
if (chk.column.equals(expr.column))
{ // Overwrite existing value
if (useCmdParam(expr.column) && (expr.value instanceof DBExpr) == false
&& chk.value instanceof DBCmdParameter)
{ // replace parameter
int index = ((DBCmdParameter) chk.value).index;
this.setCmdParam(index, getCmdParamValue(expr.column, expr.value));
}
else
{ // replace value
chk.value = expr.value;
}
return;
}
}
// Replace with parameter
if (useCmdParam(expr.column) && (expr.value instanceof DBExpr)==false)
expr.value = addCmdParam(getCmdParamValue(expr.column, expr.value));
// new Value!
set.add(expr);
}
/**
* checks whether a column is in the list of set expressions
* @param column
* @return
*/
private boolean hasSetExprOn(DBColumn column)
{
if (set==null)
return false;
Iterator<DBSetExpr> i = set.iterator();
while (i.hasNext())
{
DBSetExpr chk = i.next();
if (chk.column.equals(column))
return true;
}
return false;
}
/**
* Sets a object to in the list of Parameters.
*/
public boolean setCmdParam(int index, Object item)
{
if (index<0 || index>999)
return error(Errors.InvalidArg, index, "index");
if (cmdParams==null)
cmdParams= new Vector<Object>();
if (index>=cmdParams.size())
{ // extend the parameter list
cmdParams.setSize(index+1);
}
cmdParams.set(index, item);
return success();
}
/**
* Adds an object to in the list of Parameters
* and returns a parameter object.
*/
public DBCmdParameter addCmdParam(Object item)
{
if (cmdParams==null)
cmdParams= new Vector<Object>();
// Adds the parameter
int index = cmdParams.size();
if (cmdParams.add(item)==false)
return null; // unknown error
// Creates a Parameter expression
return new DBCmdParameter(this, index);
}
/**
* Adds a join to the list of join expressions.
*
* @param join the join expression
*/
public void join(DBJoinExpr join)
{
if (joins == null)
joins = new ArrayList<DBJoinExpr>();
// Create a new join
for (int i = 0; i < joins.size(); i++)
{ // Check whether join exists
DBJoinExpr item = joins.get(i);
if (item.equals(join))
return;
}
joins.add(join);
}
/**
* Adds a join based on two columns to the list of join expressions.
*
* @param left the left join value
* @param right the right join
* @param joinType type of join (INNER, LEFT, RIGHT)
*/
public DBJoinExpr join(DBColumnExpr left, DBColumn right, DBJoinType joinType)
{
DBJoinExpr join = new DBJoinExpr(left, right, joinType);
join(join);
return join;
}
/**
* Adds an inner join based on two columns to the list of join expressions.
*
* @param left the left join value
* @param right the right join
*/
public DBJoinExpr join(DBColumnExpr left, DBColumn right)
{
return join(left, right, DBJoinType.INNER);
}
/**
* Adds a join based on a compare expression to the command.
*
* @param rowset table of view which to join
* @param cmp the compare expression with wich to join the table
* @param joinType type of join (INNER, LEFT, RIGHT)
*/
public DBJoinExpr join(DBRowSet rowset, DBCompareExpr cmp, DBJoinType joinType)
{
DBJoinExpr join = new DBJoinExprEx(rowset, cmp, joinType);
join(join);
return join;
}
/**
* Adds an inner join based on a compare expression to the command.
*
* @param rowset table of view which to join
* @param cmp the compare expression with wich to join the table
*/
public DBJoinExpr join(DBRowSet rowset, DBCompareExpr cmp)
{
return join(rowset, cmp, DBJoinType.INNER);
}
/**
* Adds a list of join expressions to the command.
*
* @param joinExprList list of join expressions
*/
public void addJoins(List<DBJoinExpr> joinExprList)
{
if (joins == null)
{
joins = new ArrayList<DBJoinExpr>();
}
this.joins.addAll(joinExprList);
}
/**
* Returns true if the command has a join on the given
* table or false otherwise.
*
* @return true if the command has a join on the given table or false otherwise
*/
public boolean hasJoinOn(DBRowSet rowset)
{
if (joins==null)
return false;
// Examine all joins
for (DBJoinExpr join : joins)
{
if (join.isJoinOn(rowset))
return true;
}
// not found
return false;
}
/**
* Adds a compare expression to the list of constraints.
* If another restriction already exists for the same column it will be replaced.
*
* @param expr the DBCompareExpr object
*/
public void where(DBCompareExpr expr)
{
if (where == null)
where = new ArrayList<DBCompareExpr>();
setCompare(where, expr);
}
/**
* Returns true if the command has constaints or false if not.
*
* @return true if constraints have been set on the command
*/
public boolean hasWhereConstraints()
{
return (where!=null && where.size()>0);
}
/**
* Returns a copy of the defined where clauses.
*
* @return vector of where clauses
*/
public List<DBCompareExpr> getWhereConstraints()
{
if (where != null)
{ // Return a Copy of all Where Constraints
return new ArrayList<DBCompareExpr>(where);
}
return null;
}
/**
* Returns a copy of the defined joins.
*
* @return vector of joins
*/
public List<DBJoinExpr> getJoins()
{
if (joins != null)
{
return new ArrayList<DBJoinExpr>(joins);
}
return null;
}
/**
* Adds a list of constraints to the command.
*
* @param constraints list of constraints
*/
public void addWhereConstraints(List<DBCompareExpr> constraints)
{
if (where == null)
{
where = new ArrayList<DBCompareExpr>();
}
this.where.addAll(constraints);
}
/**
* Sets a having contraint.
*
* @param expr the DBCompareExpr object
*/
// having
public void having(DBCompareExpr expr)
{
if (having == null)
having = new ArrayList<DBCompareExpr>();
setCompare(having, expr);
}
/**
* Adds a list columns to the group by phrase of an sql statement.
*
* @param exprList array of columns by which to group the rows
*/
public void groupBy(DBColumnExpr[] exprList)
{
if (groupBy == null)
groupBy = new ArrayList<DBColumnExpr>();
// group by
for (int i=0; i<exprList.length; i++)
{
DBColumnExpr expr = exprList[i];
if (expr.isAggregate()==false && groupBy.contains(expr)==false)
groupBy.add(expr);
}
}
/**
* Adds a column to the group by phrase of an sql statement.
*
* @param expr the DBCompareExpr object
*/
// groupBy
public void groupBy(DBColumnExpr expr)
{
if (expr.isAggregate())
return;
if (groupBy == null)
groupBy = new ArrayList<DBColumnExpr>();
// group by
groupBy.add(expr);
}
/**
* Adds two columns to the group by phrase of an sql statement.
*/
// groupBy
public void groupBy(DBColumnExpr expr1, DBColumnExpr expr2)
{
groupBy(expr1);
groupBy(expr2);
}
/**
* Adds three columns to the group by phrase of an sql statement.
*/
// groupBy
public void groupBy(DBColumnExpr expr1, DBColumnExpr expr2, DBColumnExpr expr3)
{
groupBy(expr1);
groupBy(expr2);
groupBy(expr3);
}
/**
* Returns true if the command has constaints or false if not.
*
* @return true if constraints have been set on the command
*/
public boolean hasSelectExpr()
{
return (select!=null && select.size()>0);
}
@Override
public boolean getSelect(StringBuilder buf)
{
if (select == null)
return error(Errors.ObjectNotValid, getClass().getName()); // invalid!
// Prepares statement
addSelect(buf);
// From clause
addFrom(buf);
// Add Where
addWhere(buf);
// Add Grouping
addGrouping(buf);
// Add Order
addOrder(buf);
// done
return success();
}
/**
* Returns a array of all DBColumnExpr object of the Vector: 'select'.
*
* @return a array of all DBColumnExpr object of the Vector: 'select'
*/
@Override
public DBColumnExpr[] getSelectExprList()
{
int count = (select != null) ? select.size() : 0;
if (count < 1)
return null;
// The List
DBColumnExpr[] exprList = new DBColumnExpr[count];
for (int i = 0; i < count; i++)
exprList[i] = select.get(i);
// The expression List
return exprList;
}
/**
* Clears the select distinct option.
*/
public void clearSelectDistinct()
{
this.selectDistinct = false;
}
/**
* Clears the list of selected columns.
*/
public void clearSelect()
{
select = null;
}
/**
* Clears the list of set expressions.
*/
public void clearSet()
{
set = null;
cmdParams = null;
}
/**
* Clears the list of join expressions.
*/
public void clearJoin()
{
joins = null;
}
/**
* Clears the list of where constraints.
*/
public void clearWhere()
{
where = null;
}
/**
* Clears the list of having constraints.
*/
public void clearHaving()
{
having = null;
}
/**
* Clears the list of group by constraints.
*/
public void clearGroupBy()
{
groupBy = null;
}
/**
* Clears the entire command object.
*/
public void clear()
{
clearSelectDistinct();
clearSelect();
clearSet();
clearJoin();
clearWhere();
clearHaving();
clearGroupBy();
clearOrderBy();
cmdParams = null;
}
/**
* Compares the DBCompareExpr object with the Elements
* of the Vector 'where' or 'having'.
*
* @param list the Vector 'where' or 'having'
* @param expr the DBCompareExpr object
* @param expr the DBCompareExpr object
*/
private void setCompare(List<DBCompareExpr> list, DBCompareExpr expr)
{ // adds a comparison to the where or having list
for (int i = 0; i < list.size(); i++)
{ // check expression
DBCompareExpr other = list.get(i);
if (expr.isMutuallyExclusive(other)==false)
continue;
// columns match
list.set(i, expr);
return;
}
// neue expression, or possible another expression
// for the same column when allowMultiple == true
list.add(expr);
}
/**
* Gets a list of all tables referenced by the query.
*
* @return list of all rowsets (tables or views) used by the query
*/
protected List<DBRowSet> getTableList()
{
// Check all tables
int i = 0;
Set<DBColumn> columns = new HashSet<DBColumn>();
for (i = 0; select != null && i < select.size(); i++)
((DBExpr) select.get(i)).addReferencedColumns(columns);
for (i = 0; joins != null && i < joins.size(); i++)
((DBExpr) joins.get(i)).addReferencedColumns(columns);
for (i = 0; where != null && i < where.size(); i++)
((DBExpr) where.get(i)).addReferencedColumns(columns);
for (i = 0; groupBy != null && i < groupBy.size(); i++)
((DBExpr) groupBy.get(i)).addReferencedColumns(columns);
for (i = 0; having != null && i < having.size(); i++)
((DBExpr) having.get(i)).addReferencedColumns(columns);
for (i = 0; orderBy != null && i < orderBy.size(); i++)
((DBExpr) orderBy.get(i)).addReferencedColumns(columns);
// now we have all columns
List<DBRowSet> tables = new ArrayList<DBRowSet>();
Iterator<DBColumn> iterator = columns.iterator();
while (iterator.hasNext())
{ // get the table
DBColumn col = iterator.next();
DBRowSet table = col.getRowSet();
if (table == cmdQuery)
{ // Recursion
log.error("Recursive Column Selection in Command!");
continue;
}
if (tables.contains(table) == false && table != null)
{ // Add table
tables.add(table);
}
}
return tables;
}
/**
* Adds Columns
*/
@Override
public void addReferencedColumns(Set<DBColumn> list)
{
// nothing to do!
return;
}
/**
* Returns the list of parameter values for a prepared statement.
*
* @return the list of parameter values for a prepared statement
*/
public Object[] getCmdParams()
{
if (cmdParams==null)
return null;
// return Params
return cmdParams.toArray();
}
/**
* Creates the update SQL-Command.
*
* @return the update SQL-Command
*/
public String getUpdate()
{
if (set == null)
return null;
StringBuilder buf = new StringBuilder("UPDATE ");
// addTableExpr(buf, CTX_NAME);
DBRowSet table = set.get(0).getTable();
table.addSQL(buf, CTX_FULLNAME);
// Set Expressions
buf.append("\r\nSET ");
addListExpr(buf, set, CTX_DEFAULT, ", ");
// Constraints
if (where != null)
{ // add where condition
buf.append("\r\nWHERE ");
if (where != null)
addListExpr(buf, where, CTX_NAME | CTX_VALUE, " AND ");
}
return buf.toString();
}
/**
* Creates the insert SQL-Command.
*
* @return the insert SQL-Command
*/
// get Insert
public String getInsert()
{
if (set==null || set.get(0)==null)
return null;
StringBuilder buf = new StringBuilder("INSERT INTO ");
// addTableExpr(buf, CTX_NAME);
DBRowSet table = set.get(0).getTable();
table.addSQL(buf, CTX_FULLNAME);
// Set Expressions
buf.append("( ");
// Set Expressions
ArrayList<DBCompareColExpr> compexpr = null;
if (where != null)
{ // Convert ColumnExpression List to Column List
compexpr = new ArrayList<DBCompareColExpr>(where.size());
for (DBCompareExpr expr : where)
{ if (expr instanceof DBCompareColExpr)
{ DBColumn column = ((DBCompareColExpr)expr).getColumnExpr().getUpdateColumn();
if (column!=null && hasSetExprOn(column)==false)
compexpr.add((DBCompareColExpr)expr);
}
}
// Add Column Names from where clause
if (compexpr.size()>0)
{
// add List
addListExpr(buf, compexpr, CTX_NAME, ", ");
// add separator
if (set != null)
buf.append(", ");
}
else
{ // No columns to set
compexpr = null;
}
}
if (set != null)
addListExpr(buf, set, CTX_NAME, ", ");
// Values
buf.append(") VALUES ( ");
if (compexpr != null)
addListExpr(buf, compexpr, CTX_VALUE, ", ");
if (compexpr != null && set != null)
buf.append(", ");
if (set != null)
addListExpr(buf, set, CTX_VALUE, ", ");
// End
buf.append(")");
return buf.toString();
}
/**
* Creates the delete SQL-Command.
*
* @return the delete SQL-Command
*/
public String getDelete(DBTable table)
{
StringBuilder buf = new StringBuilder("DELETE FROM ");
table.addSQL(buf, CTX_FULLNAME);
// Set Expressions
if (where != null)
{ // add where condition
buf.append("\r\nWHERE ");
if (where != null)
addListExpr(buf, where, CTX_NAME|CTX_VALUE, " AND ");
}
return buf.toString();
}
// ------- Select Statement Parts -------
protected void addSelect(StringBuilder buf)
{
// Prepares statement
buf.append("SELECT ");
if (selectDistinct)
buf.append("DISTINCT ");
// Add Select Expressions
addListExpr(buf, select, CTX_ALL, ", ");
}
protected void addFrom(StringBuilder buf)
{
buf.append("\r\nFROM ");
// Join
boolean sep = false;
List<DBRowSet> tables = getTableList();
if (joins!=null )
{ // Join
List<DBRowSet> joinTables = new ArrayList<DBRowSet>();
// for (int i=0;i<joins.size();i++)
// buf.append("(");
for (int i=0;i<joins.size();i++)
{ // Joins zusammenbauen
long context;
DBJoinExpr join = joins.get(i);
if (i<1)
{ // Add Join Tables
joinTables.add(join.getLeft() .getUpdateColumn().getRowSet());
joinTables.add(join.getRight().getUpdateColumn().getRowSet());
// Remove from List
tables.remove(join.getLeft() .getUpdateColumn().getRowSet());
tables.remove(join.getRight().getUpdateColumn().getRowSet());
// Context
context = CTX_NAME|CTX_VALUE;
}
else
{ // Extend the join
if ( joinTables.contains(join.getRight().getUpdateColumn().getRowSet()))
join.reverse();
// Add Right Table
joinTables.add(join.getRight().getUpdateColumn().getRowSet());
tables .remove(join.getRight().getUpdateColumn().getRowSet());
// Context
context = CTX_VALUE;
buf.append( "\t" );
}
join.addSQL(buf, context);
// buf.append(")");
if( i!=joins.size()-1 )
buf.append("\r\n");
}
sep = true;
}
for (int i=0; i<tables.size(); i++)
{
if (sep) buf.append(", ");
DBRowSet t = tables.get(i);
t.addSQL(buf, CTX_DEFAULT|CTX_ALIAS);
sep = true;
}
}
protected void addWhere(StringBuilder buf)
{
if (where != null)
{
buf.append("\r\nWHERE ");
// add where expression
addListExpr(buf, where, CTX_DEFAULT, " AND ");
}
}
protected void addGrouping(StringBuilder buf)
{
if (groupBy != null)
{ // Having
buf.append("\r\nGROUP BY ");
addListExpr(buf, groupBy, CTX_DEFAULT, ", ");
}
if (having != null)
{ // Having
buf.append("\r\nHAVING ");
addListExpr(buf, having, CTX_DEFAULT, " AND ");
}
}
protected void addOrder(StringBuilder buf)
{
if (orderBy != null)
{ // order By
buf.append("\r\nORDER BY ");
addListExpr(buf, orderBy, CTX_DEFAULT, ", ");
}
}
}