com.hfg.sql.SQLQuery Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of com_hfg Show documentation
Show all versions of com_hfg Show documentation
com.hfg xml, html, svg, and bioinformatics utility library
package com.hfg.sql;
import com.hfg.sql.jdbc.JDBCException;
import com.hfg.sql.jdbc.SQLStatementOptions;
import com.hfg.sql.table.DatabaseCol;
import com.hfg.sql.table.DatabaseTable;
import com.hfg.sql.table.field.DatabaseField;
import com.hfg.util.collection.CollectionUtil;
import com.hfg.util.StringBuilderPlus;
import com.hfg.util.StringUtil;
import com.hfg.util.collection.OrderedSet;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
//------------------------------------------------------------------------------
/**
Container for building a SQL query.
@author J. Alex Taylor, hairyfatguy.com
*/
//------------------------------------------------------------------------------
// com.hfg XML/HTML Coding Library
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
//
// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
// [email protected]
//------------------------------------------------------------------------------
public class SQLQuery extends SQLCmd
{
private List mSelectList;
private OrderedSet mFromClauses;
private List mJoinClauses;
private List mGroupByList;
private SQLClause mOffsetClause;
private SQLClause mLimtClause;
//###########################################################################
// PUBLIC METHODS
//###########################################################################
//---------------------------------------------------------------------------
public ResultSet execute(Connection inConn)
throws SQLException
{
return SQLUtil.executeQuery(inConn, toSQL());
}
//---------------------------------------------------------------------------
public ResultSet execute(Connection inConn, SQLStatementOptions inOptions)
throws SQLException
{
return SQLUtil.executeQuery(inConn, toSQL(), inOptions);
}
//---------------------------------------------------------------------------
public String toSQL()
{
StringBuilderPlus sql = new StringBuilderPlus("SELECT ");
sql.appendln(StringUtil.join(mSelectList, ", "));
if (CollectionUtil.hasValues(mFromClauses))
{
sql.append(" FROM ");
sql.appendln(StringUtil.join(mFromClauses, ", "));
}
if (CollectionUtil.hasValues(mJoinClauses))
{
for (JoinClause joinClause : mJoinClauses)
{
sql.append(" " + joinClause.getJoinType() + " ")
.append(joinClause.toSQL());
}
}
sql.append(generateWhereClause());
sql.append(generateGroupByClause());
sql.append(generateOrderByClause());
sql.append(generateOffsetClause());
sql.append(generateLimitClause());
return sql.toString();
}
//---------------------------------------------------------------------------
@Override
public String toString()
{
return toSQL();
}
//---------------------------------------------------------------------------
@Override
public SQLQuery addClauses(Collection inClauses)
{
return (SQLQuery) super.addClauses(inClauses);
}
//---------------------------------------------------------------------------
@Override
public SQLQuery addClause(SQLClause inValue)
throws JDBCException
{
switch (inValue.getType())
{
// Handle query-specific clause types
case FROM:
mFromClauses.add(inValue.toSQL());
break;
case JOIN:
if (null == mJoinClauses)
{
mJoinClauses = new ArrayList<>(2);
}
JoinClause joinClause;
if (inValue instanceof JoinClause)
{
joinClause = (JoinClause) inValue;
}
else
{
joinClause = new JoinClause(inValue.toSQL());
}
mJoinClauses.add(joinClause);
break;
case GROUP_BY:
addGroupBy(inValue.toSQL());
break;
case OFFSET:
mOffsetClause = inValue;
break;
case LIMIT:
mLimtClause = inValue;
break;
// Handle general clause types
default:
super.addClause(inValue);
}
return this;
}
//---------------------------------------------------------------------------
public SQLQuery addSelect(DatabaseCol inColumn)
{
addSelect(inColumn.getQualifiedName());
return this;
}
//---------------------------------------------------------------------------
public SQLQuery addSelect(String inValue)
{
if (null == mSelectList)
{
mSelectList = new ArrayList<>(50);
}
mSelectList.add(inValue);
return this;
}
//---------------------------------------------------------------------------
public SQLQuery addFrom(DatabaseTable inValue)
{
return addFrom(inValue, inValue.getAlias());
}
//---------------------------------------------------------------------------
public SQLQuery addFrom(DatabaseTable inValue, String inAlias)
{
addFrom(inValue.getQualifiedName(), inAlias);
return this;
}
//---------------------------------------------------------------------------
public SQLQuery addFrom(String inValue, String inAlias)
{
if (null == mFromClauses)
{
mFromClauses = new OrderedSet<>(10);
}
mFromClauses.add(inValue + (StringUtil.isSet(inAlias) ? " " + inAlias : ""));
return this;
}
//---------------------------------------------------------------------------
public SQLQuery addFrom(String inValue)
{
if (null == mFromClauses)
{
mFromClauses = new OrderedSet<>(10);
}
mFromClauses.add(inValue);
return this;
}
//---------------------------------------------------------------------------
public SQLQuery addJoin(JoinClause inValue)
{
if (null == mJoinClauses)
{
mJoinClauses = new OrderedSet<>(5);
}
mJoinClauses.add(inValue);
return this;
}
//---------------------------------------------------------------------------
public SQLQuery addJoin(String inValue)
{
if (null == mJoinClauses)
{
mJoinClauses = new OrderedSet<>(5);
}
mJoinClauses.add(new JoinClause(inValue));
return this;
}
//---------------------------------------------------------------------------
public SQLQuery addWhereClause(String inValue)
{
super.addClause(new WhereClause(inValue));
return this;
}
//---------------------------------------------------------------------------
public SQLQuery addWhereClause(DatabaseField inValue)
{
super.addClause(new WhereClause(inValue));
return this;
}
//---------------------------------------------------------------------------
public SQLQuery addWhereClauseGroup(WhereClauseGroup inValue)
{
return (SQLQuery) super.addWhereClauseGroup(inValue);
}
//---------------------------------------------------------------------------
public SQLQuery addGroupBy(DatabaseCol inValue)
{
addGroupBy(inValue.getQualifiedName());
return this;
}
//---------------------------------------------------------------------------
public SQLQuery addGroupBy(String inValue)
{
if (null == mGroupByList)
{
mGroupByList = new ArrayList<>(50);
}
mGroupByList.add(inValue);
return this;
}
//---------------------------------------------------------------------------
public SQLQuery addOrderBy(String inValue)
{
addClause(new OrderByClause(inValue));
return this;
}
//---------------------------------------------------------------------------
public SQLQuery setLimit(int inValue)
{
addClause(new LimitClause(inValue));
return this;
}
//###########################################################################
// PRIVATE METHODS
//###########################################################################
//---------------------------------------------------------------------------
private String generateGroupByClause()
{
StringBuilderPlus sql = new StringBuilderPlus();
if (CollectionUtil.hasValues(mGroupByList))
{
sql.append("GROUP BY ");
sql.appendln(StringUtil.join(mGroupByList, ", "));
}
return sql.toString();
}
//---------------------------------------------------------------------------
private String generateOffsetClause()
{
StringBuilderPlus sql = new StringBuilderPlus();
if (mOffsetClause != null)
{
sql.append("OFFSET ");
sql.appendln(mOffsetClause.toString());
}
return sql.toString();
}
//---------------------------------------------------------------------------
private String generateLimitClause()
{
StringBuilderPlus sql = new StringBuilderPlus();
if (mLimtClause != null)
{
sql.append("LIMIT ");
sql.appendln(mLimtClause.toString());
}
return sql.toString();
}
}