All Downloads are FREE. Search and download functionalities are using the official Maven repository.

org.tentackle.dbms.Query Maven / Gradle / Ivy

Go to download 

/*
 * Tentackle - https://tentackle.org
 *
 * 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
 */


package org.tentackle.dbms;

import org.tentackle.common.BMoney;
import org.tentackle.common.Binary;
import org.tentackle.common.DMoney;
import org.tentackle.log.Logger;
import org.tentackle.misc.Convertible;
import org.tentackle.session.PersistenceException;
import org.tentackle.sql.Backend;
import org.tentackle.sql.DataType;
import org.tentackle.sql.DataTypeFactory;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Time;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;


/**
 * A database query.
 * 

 * Combines the generation of an SQL-string and parameter set for
 * the ResultSetWrapper. Useful for one-time queries entered by the user.

 * Notice that by default the underlying prepared statement is closed
 * when the result-set (or cursor) is closed by the application.
 * You can change that behaviour, however.
 * 

 * Notice further, that the columns returned by the select
 * must be provided by the application via add() _before_ execution.
 * The SELECT, however, and LIMIT/OFFSET clauses are provided by the query automatically,
 * because they are backend-specific.
 *
 * @author harald
 */
public class Query {

  private static final Logger LOGGER = Logger.get(Query.class);


  private final List items;    // query parameters
  private int fetchSize;                  // fetchsize, 0 = default
  private int maxRows;                    // max. number of rows fetched at all, 0 = no limit
  private int offset;                     // != 0 if add an offset clause
  private int limit;                      // != 0 if add a limit clause
  private boolean statementCached;        // true if cache prepared statement


  /**
   * Creates a query.
   */
  public Query() {
    items = new ArrayList<>();
  }


  /**
   * Enables caching of the prepared statement.

   * By default, queries are one-time prepared statements that will be closed
   * when the resultset is closed. However, if a query uses a fixed SQL string
   * and is executed a significant number of times, the prepared statement
   * can be prepared only once and re-used afterward. Please notice, that once
   * prepared with caching, the statement will remain open until it is closed
   * explicitly.
   *
   * @param statementCached true if prepare only once and reuse cached statement, false if one-shot
   */
  public void setStatementCached(boolean statementCached) {
    this.statementCached = statementCached;
  }

  /**
   * Returns whether statement caching is enabled.
   *
   * @return true if prepare only once and reuse cached statement, false if one-shot (default)
   */
  public boolean isStatementCached() {
    return statementCached;
  }


  /**
   * Sets an offset, i.e. number of rows to skip in a query.
   * By default, the offset is 0.
   * @param offset the offset, 0 to disable
   */
  public void setOffset(int offset) {
    this.offset = offset;
  }

  /**
   * Gets the offset of this query.
   *
   * @return the number of rows to skip, 0 if no offset
   */
  public int getOffset() {
    return offset;
  }


  /**
   * Sets the maximum number of rows to retrieve for this query.
   *e
   * @param limit the maximum number of rows, 0 if unlimited (default)
   */
  public void setLimit(int limit) {
    this.limit = limit;
  }

  /**
   * Gets the maximum number of rows for this query.
   *
   * @return the maximum number of rows, 0 if unlimited (default)
   */
  public int getLimit() {
    return limit;
  }


  /**
   * Appends an SQL-part and corresponding parameters to this query.
   * 
   * Example: add(" AND CN_ID=?", object.getId());
   *          add(" AND CN_MONEY=?", object.getAmount());
   * 

   *
   * @param sql is the SQL-string, null or empty if just data
   * @param data is an array of parameters
   */
  public void add(CharSequence sql, Object... data) {
    items.add(new QueryItem(sql, data));
  }

  /**
   * Appends an SQL-part and corresponding parameters to this query.

   * Same as {@link #add(CharSequence, Object...)} but with a {@link List} of parameters
   * instead of an array or varargs.
   *
   * @param sql is the SQL-string, null or empty if just data
   * @param data the {@link List} of parameters
   */
  public void add(CharSequence sql, List data) {
    items.add(new QueryItem(sql, data));
  }

  /**
   * Appends a backend-specific SQL-part to this query.
   *
   * @param sqlSupplier supplier for backend specific SQL code
   */
  public void add(SqlSupplier sqlSupplier) {
    items.add(new QueryItem(sqlSupplier));
  }

  /**
   * Appends an SQL-part and corresponding parameters to this query.
   * 
   * Example: add(" AND CN_ID=?", object.getId());
   *          add(" AND CN_MONEY=?", object.getAmount());
   * 

   *
   * @param index index at which the specified SQL is to be inserted
   * @param sql is the SQL-string, null or empty if just data
   * @param data is an array of parameters
   */
  public void add(int index, CharSequence sql, Object... data) {
    items.add(index, new QueryItem(sql, data));
  }

  /**
   * Appends an SQL-part and corresponding parameters to this query.

   * Same as {@link #add(int, CharSequence, Object...)} but with a {@link List} of parameters
   * instead of an array or varargs.
   *
   * @param index index at which the specified SQL is to be inserted
   * @param sql is the SQL-string, null or empty if just data
   * @param data the {@link List} of parameters
   */
  public void add(int index, CharSequence sql, List data) {
    items.add(index, new QueryItem(sql, data));
  }

  /**
   * Appends a backend-specific SQL-part to this query.
   *
   * @param index index at which the specified SQL is to be inserted
   * @param sqlSupplier supplier for backend specific SQL code
   */
  public void add(int index, SqlSupplier sqlSupplier) {
    items.add(index, new QueryItem(sqlSupplier));
  }


  /**
   * Sets the optional fetchsize. 0 = drivers default.
   *
   * @param fetchSize the fetchsize
   * @see ResultSetWrapper#setFetchSize(int)
   */
  public void setFetchSize(int fetchSize) {
    this.fetchSize = fetchSize;
  }

  /**
   * Gets the fetchsize.
   *
   * @return the fetchsize
   * @see ResultSetWrapper#getFetchSize()
   */
  public int getFetchSize() {
    return fetchSize;
  }


  /**
   * Sets the optional maximum row count for this cursor.
   *
   * @param maxRows the max rows, 0 = no limit
   */
  public void setMaxRows(int maxRows) {
    this.maxRows = maxRows;
  }

  /**
   * Gets the maximum row count for this cursor
   *
   * @return the max rows, 0 = no limit
   */
  public int getMaxRows() {
    return maxRows;
  }


  /**
   * Executes the query.
   *
   * @param db is the session
   * @param resultSetType is one of ResultSet.TYPE_...
   * @param resultSetConcurrency is one of ResultSet.CONCUR_..
   *
   * @return the result set
   */
  public ResultSetWrapper execute(Db db, int resultSetType, int resultSetConcurrency) {

    PreparedStatementWrapper st;

    if (statementCached) {
      StringBuilder sql = createSql(db.getBackend());
      StatementKey key = createStatementKey(sql.toString());
      // prepare if new, else re-use
      st = db.getPreparedStatement(key, false, resultSetType, resultSetConcurrency, b -> sql);
    }
    else {
      st = db.createPreparedStatement(this::createSql, resultSetType, resultSetConcurrency);
    }

    // apply parameters to statement
    apply(db, st);

    // set fetchsize and maxrows if != 0
    if (fetchSize != 0) {
      st.setFetchSize(fetchSize);
    }
    if (maxRows != 0) {
      st.setMaxRows(maxRows);
    }

    /*
     * for postgres: run the query in an extra tx. Otherwise, postgres will ignore fetchsize
     * and load everything into memory.
     */
    ResultSetWrapper rs = null;

    try {
      if (db.getBackend().isTxRequiredForFetchsize() && fetchSize != 0 &&
          resultSetType == ResultSet.TYPE_FORWARD_ONLY &&
          resultSetConcurrency == ResultSet.CONCUR_READ_ONLY) {
        rs = st.executeQuery(true);   // true = commit tx when closing resultset
      }
      else {
        // standard dbms
        rs = st.executeQuery();
      }

      if (!statementCached) {
        rs.setCloseStatementOnclose(true);
      }
    }
    catch (RuntimeException ex) {
      if (rs != null) {
        try {
          rs.close();
        }
        catch (RuntimeException rex) {
          LOGGER.warning("closing resultset failed: " + rex.getMessage());
        }
      }

      try {
        st.consume();
      }
      catch (RuntimeException rex) {
        LOGGER.warning("marking statement consumed failed: " + rex.getMessage());
      }

      if (!st.isClosed()) {
        try {
          st.close();
        }
        catch (RuntimeException rex) {
          LOGGER.warning("closing statement failed: " + rex.getMessage());
        }
      }

      throw ex;
    }

    return rs;
  }

  /**
   * Executes the query with ResultSet.CONCUR_READ_ONLY.
   *
   * @param db is the session
   * @param resultSetType is one of ResultSet.TYPE_...
   *
   * @return the result set
   */
  public ResultSetWrapper execute(Db db, int resultSetType) {
    return execute(db, resultSetType, ResultSet.CONCUR_READ_ONLY);
  }

  /**
   * Executes the query with ResultSet.TYPE_FORWARD_ONLY and ResultSet.CONCUR_READ_ONLY.
   *
   * @param db is the session
   *
   * @return the result set
   */
  public ResultSetWrapper execute(Db db) {
    return execute(db, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
  }


  /**
   * Gets the number of objects returned by this query.
   * 

   * This is not done by retrieving all rows but by a {@code "SELECT COUNT(1)"}.
   * Applications may use this in conjunction with limit and offset for pagination
   * (in web pages, for example).
   *
   * @param db the session
   * @return the number of rows for this query
   */
  public int getRowCount(Db db) {
    try (PreparedStatementWrapper st = db.createPreparedStatement(b -> {
      StringBuilder buf = new StringBuilder();
      buf.append(buildInnerSql(b));
      buf.insert(0, "SELECT COUNT(*) FROM (");
      buf.append(')');
      if (db.getBackend().isAliasRequiredForSubSelect()) {
        buf.append(" AS F_O_O");
      }
      return buf.toString();
    })) {
      applyParameters(st, 1);
      int count = 0;
      try (ResultSetWrapper rs = st.executeQuery()) {
        if (rs.next()) {
          count = rs.getInt(1);
        }
      }
      return count;
    }
  }


  /**
   * Applies the query parameters to the statement.

   * Optionally applies limit, offset as well.
   *
   * @param db the session
   * @param st the prepared statement
   */
  public void apply(Db db, PreparedStatementWrapper st) {
    int ndx = db.getBackend().setLeadingSelectParameters(st, limit, offset);  // optionally prepend limit/offset
    ndx = applyParameters(st, ndx);                                           // set the query parameters
    db.getBackend().setTrailingSelectParameters(st, ndx, limit, offset);      // optionally append limit/offset
  }


  /**
   * Apply the query parameters only.
   *
   * @param st the statement
   * @param ndx the starting index
   * @return the next parameter index
   */
  @SuppressWarnings({ "rawtypes", "unchecked" })
  public int applyParameters(PreparedStatementWrapper st, int ndx) {

    for (QueryItem item : items) {
      if (item.data != null) {    // just in case somebody passed in (Object[])null for data
        for (int dataIndex = 0; dataIndex < item.data.length; dataIndex++) {
          Object data = item.data[dataIndex];
          if (data instanceof Convertible) {
            data = ((Convertible) data).toExternal();
          }
          if (data instanceof Number) {
            if (data instanceof DMoney) {
              st.setDMoney(ndx, (DMoney) data);
              ndx += 2;
            }
            else if (data instanceof BMoney) {
              st.setBMoney(ndx, (BMoney) data);
              ndx += 2;
            }
            else if (data instanceof Double) {
              st.setDouble(ndx++, (Double) data);
            }
            else if (data instanceof Float) {
              st.setFloat(ndx++, (Float) data);
            }
            else if (data instanceof Long) {
              st.setLong(ndx++, (Long) data);
            }
            else if (data instanceof Integer) {
              st.setInteger(ndx++, (Integer) data);
            }
            else if (data instanceof Short) {
              st.setShort(ndx++, (Short) data);
            }
            else if (data instanceof Byte) {
              st.setByte(ndx++, (Byte) data);
            }
            else if (data instanceof BigDecimal) {
              st.setBigDecimal(ndx++, (BigDecimal) data);
            }
          }
          else if (data instanceof String) {
            st.setString(ndx++, (String) data);
          }
          else if (data instanceof Character) {
            st.setChar(ndx++, (Character) data);
          }
          else if (data instanceof Boolean) {
            st.setBoolean(ndx++, (Boolean) data);
          }
          else if (data instanceof Timestamp) {
            st.setTimestamp(ndx++, (Timestamp) data);
          }
          else if (data instanceof Date) {
            st.setDate(ndx++, (Date) data);
          }
          else if (data instanceof Time) {
            st.setTime(ndx++, (Time) data);
          }
          else if (data instanceof LocalDateTime) {
            st.setLocalDateTime(ndx++, (LocalDateTime) data);
          }
          else if (data instanceof LocalDate) {
            st.setLocalDate(ndx++, (LocalDate) data);
          }
          else if (data instanceof LocalTime) {
            st.setLocalTime(ndx++, (LocalTime) data);
          }
          else if (data instanceof Binary) {
            st.setBinary(ndx++, (Binary) data);
          }
          else {
            if (data == null) {
              throw new PersistenceException("null value in '" + item + "' arg[" + dataIndex + "]");
            }
            else {
              DataType dataType = DataTypeFactory.getInstance().get(data.getClass());
              if (dataType == null) {
                throw new PersistenceException(
                    "unsupported type " + data.getClass() + " in '" + item + "' arg[" + dataIndex + "]");
              }
              // the application must provide the correct number of question marks!
              st.set(dataType, ndx, data, false, null);
              ndx += dataType.getColumnCount(st.getSession().getBackend());
            }
          }
        }
      }
    }

    return ndx;
  }




  /**
   * Creates the SQL-code of this query.

   * Optionally modifies the query according to limit and offset.
   *
   * @param backend the backend
   * @return the SQL code
   */
  public StringBuilder createSql(Backend backend) {
    StringBuilder sql = new StringBuilder();
    sql.append(buildInnerSql(backend));
    backend.buildSelectSql(sql, false, limit, offset);
    return sql;
  }

  /**
   * Creates the statement key.
   *
   * @param sql the SQL string sent to the backend
   * @return the key
   */
  public StatementKey createStatementKey(String sql) {
    return new StatementKey(sql);
  }

  /**
   * Builds the inner sql query string.
   *
   * @param backend the backend
   * @return the sql string
   */
  public StringBuilder buildInnerSql(Backend backend) {
    StringBuilder buf = new StringBuilder();
    for (QueryItem item : items) {
      buf.append(item.createSql(backend));
    }
    return buf;
  }



  /**
   * bundles sql + parameters
   */
  private static class QueryItem {

    private final String sql;                 // sql string
    private final SqlSupplier sqlSupplier;    // or SQL supplier
    private final Object[] data;              // data items

    private QueryItem(CharSequence sql, Object... data) {
      this.sql = sql == null ? "" : sql.toString();
      this.sqlSupplier = null;
      this.data = data;
    }

    private QueryItem(CharSequence sql, List data) {
      this.sql = sql == null ? "" : sql.toString();
      this.sqlSupplier = null;
      this.data = data.toArray();
    }

    private QueryItem(SqlSupplier sqlSupplier) {
      this.sqlSupplier = Objects.requireNonNull(sqlSupplier, "SqlSupplier must be provided");
      this.sql = null;
      this.data = null;
    }

    private String createSql(Backend backend) {
      return sqlSupplier != null ? sqlSupplier.get(backend).toString() : sql;
    }
  }
}
    

    

    

            

    

            



    
    






    
© 2015 - 2024 Weber Informatics LLC | Privacy Policy