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
com.hfg xml, html, svg, and bioinformatics utility library
There is a newer version: 20240423
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 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 List mFromList;
   private List mGroupByList;

   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(mFromList))
      {
         sql.append("FROM ");
         sql.appendln(StringUtil.join(mFromList, ", "));
      }

      sql.append(generateWhereClause());
      sql.append(generateGroupByClause());
      sql.append(generateOrderByClause());
      sql.append(generateLimitClause());

      return sql.toString();
   }

   //---------------------------------------------------------------------------
   @Override
   public String toString()
   {
      return toSQL();
   }

   //---------------------------------------------------------------------------
   @Override
   public SQLCmd 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:
            mFromList.add(inValue.toSQL());
            break;

         case GROUP_BY:
            addGroupBy(inValue.toSQL());
            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 == mFromList)
      {
         mFromList = new ArrayList<>(20);
      }

      mFromList.add(inValue + (StringUtil.isSet(inAlias) ? " " + inAlias : ""));

      return this;
   }

   //---------------------------------------------------------------------------
   public SQLQuery addFrom(String inValue)
   {
      if (null == mFromList)
      {
         mFromList = new ArrayList<>(20);
      }

      mFromList.add(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 generateLimitClause()
   {
      StringBuilderPlus sql = new StringBuilderPlus();
      if (mLimtClause != null)
      {
         sql.append("LIMIT ");
         sql.appendln(mLimtClause.toString());
      }

      return sql.toString();
   }
}