Package org.apache.empire.db

Class DBUtils

java.lang.Object
org.apache.empire.db.DBUtils
All Implemented Interfaces:
DBContextAware
public class DBUtils extends Object implements DBContextAware
DBUtils This class provides various query functions and functions for command execution. It also performs logging
Author:
doebele

  • Field Details

    • log

      protected static final org.slf4j.Logger log

    • longRunndingStmtThreshold

      protected long longRunndingStmtThreshold

    • DEFAULT_LIST_CAPACITY

      protected int DEFAULT_LIST_CAPACITY

    • MAX_QUERY_ROWS

      protected int MAX_QUERY_ROWS

    • LOG_MAX_STRING_LENGTH

      protected int LOG_MAX_STRING_LENGTH

    • LOG_NEW_LINE

      protected String LOG_NEW_LINE

    • context

      protected final DBContext context

    • dbms

      protected final DBMSHandler dbms

  • Constructor Details

    • DBUtils

      public DBUtils(DBContext context)
      DBUtils constructor
      Parameters:
      context - the database context

  • Method Details

    • getContext

      public DBContext getContext()
      Returns the current Context
      Specified by:
      getContext in interface DBContextAware
      Returns:
      the database context

    • paramValueToString

      protected String paramValueToString(Object param)
      Get single parameter as string (for logging only)
      Parameters:
      param - the parameter
      Returns:
      the formatted parameter value

    • paramsToString

      protected String paramsToString(Object[] params)
      Get all parameters as string (for logging only)
      Parameters:
      params - the parameter
      Returns:
      the formatted parameters

    • logQueryStatement

      protected void logQueryStatement(String sqlCmd, Object[] sqlParams)
      Log Query Statement
      Parameters:
      sqlCmd - the sql command
      sqlParams - the command params

    • logUpdateStatement

      protected void logUpdateStatement(String sqlCmd, Object[] sqlParams)
      Log Update Statement
      Parameters:
      sqlCmd - the sql command
      sqlParams - the command params

    • executeSQL

      public int executeSQL(String sqlCmd, Object[] sqlParams, DBMSHandler.DBSetGenKeys setGenKeys)
      Executes an update, insert or delete SQL-Statement.
      We recommend to use a DBCommand object in order to build the sqlCmd.

      Parameters:
      sqlCmd - the SQL-Command
      sqlParams - a list of objects to replace sql parameters
      setGenKeys - callback to set the generated key for a each new record
      Returns:
      the row count for insert, update or delete or 0 for SQL statements that return nothing

    • executeQuery

      public ResultSet executeQuery(String sqlCmd, Object[] sqlParams, boolean scrollable)
      Executes a select SQL-Statement and returns a ResultSet containing the query results.
      This function returns a JDBC ResultSet.
      Instead of using this function directly you should use a DBReader object instead.

      Parameters:
      sqlCmd - the SQL-Command
      sqlParams - a list of parameters for parameter queries (may depend on dbms)
      scrollable - true if the reader should be scrollable or false if not
      Returns:
      the JDBC ResutSet

    • querySingleValue

      public Object querySingleValue(String sqlCmd, Object[] sqlParams, DataType dataType, boolean failOnNoResult)
      Returns the value of the first row/column of a sql-query as an object. If the query does not return a result the value ObjectUtils.NO_VALUE is returned.
      Parameters:
      sqlCmd - the SQL-Command
      sqlParams - list of query parameter values
      dataType - the expected data type
      failOnNoResult - if true a QueryNoResultException result is thrown if no record exists otherwise null is returned
      Returns:
      the value of the first column in the first row of the query

    • querySingleValue

      public final <T> T querySingleValue(DBCommandExpr cmd, Class<T> resultType, boolean failOnNoResult)
      Returns the value of the first row/column of a sql-query as an object. If the query does not return a result a QueryNoResultException is thrown
      Parameters:
      cmd - the Command object that contains the select statement
      resultType - the expected data type
      failOnNoResult - flag whether to fail on empty resultset
      Returns:
      the value of the first column in the first row of the query

    • querySingleValue

      public final Object querySingleValue(DBCommandExpr cmd, boolean failOnNoResult)
      Returns the value of the first row/column of a sql-query as an object. If the query does not return a result a QueryNoResultException is thrown
      Parameters:
      cmd - the Command object that contains the select statement
      failOnNoResult - flag whether to fail on empty resultset
      Returns:
      the value of the first column in the first row of the query

    • querySingleValue

      public final Object querySingleValue(DBCommandExpr cmd)
      Returns the value of the first row/column of a sql-query as an object. If the query does not return a result a QueryNoResultException is thrown
      Parameters:
      cmd - the Command object that contains the select statement
      Returns:
      the value of the first column in the first row of the query

    • querySingleInt

      public final int querySingleInt(String sqlCmd, Object[] sqlParams, int defaultValue)
      Returns the value of the first row/column of a sql-query as an int. If the query does not return a result or if the query result is NULL, then the defaultValue is returned
      Parameters:
      sqlCmd - the sql command
      sqlParams - the command params
      defaultValue - the default value
      Returns:
      the result as a int value

    • querySingleInt

      public final int querySingleInt(DBCommandExpr cmd, int defaultValue)
      Returns the value of the first row/column of a sql-query as an int. If the query does not return a result or if the query result is NULL, then the defaultValue is returned
      Parameters:
      cmd - the Command object that contains the select statement
      defaultValue - the default value if no value was returned by the database
      Returns:
      the result as a int value

    • querySingleInt

      public final int querySingleInt(DBCommandExpr cmd)
      Returns the value of the first row/column of a sql-query as an int. If the query does not return a result a QueryNoResultException is thrown
      Parameters:
      cmd - the Command object that contains the select statement
      Returns:
      the result as a int value

    • querySingleLong

      public final long querySingleLong(String sqlCmd, Object[] sqlParams, long defaultValue)
      Returns the value of the first row/column of a sql-query as an int. If the query does not return a result or if the query result is NULL, then the defaultValue is returned
      Parameters:
      sqlCmd - the sql command
      sqlParams - the command params
      defaultValue - the default value
      Returns:
      the result as a int value

    • querySingleLong

      public final long querySingleLong(DBCommandExpr cmd, long defaultValue)
      Returns the value of the first row/column of a sql-query as a long. If the query does not return a result or if the query result is NULL, then the defaultValue is returned
      Parameters:
      cmd - the Command object that contains the select statement
      defaultValue - the default value
      Returns:
      the result as a long value

    • querySingleLong

      public final long querySingleLong(DBCommandExpr cmd)
      Returns the value of the first row/column of a sql-query as a long. If the query does not return a result a QueryNoResultException is thrown
      Parameters:
      cmd - the Command object that contains the select statement
      Returns:
      the result as a long value

    • querySingleString

      public final String querySingleString(DBCommandExpr cmd, String defaultValue)
      Returns the value of the first row/column of a sql-query as a string. If the query does not return a result or if the query result is NULL, then the defaultValue is returned
      Parameters:
      cmd - the Command object that contains the select statement
      defaultValue - the default value if no value was returned by the database
      Returns:
      the result as a String object, if no result a empty String

    • querySingleString

      public final String querySingleString(DBCommandExpr cmd)
      Returns the value of the first row/column of a sql-query as a string. If the query does not return a result a QueryNoResultException is thrown. If the query result is NULL an empty string is returned.
      Parameters:
      cmd - the Command object that contains the select statement
      Returns:
      the result as a String object, if no result a empty String

    • queryRowCount

      public int queryRowCount(DBCommand cmd)
      Returns the number of rows returned by executing the select statement
      Parameters:
      cmd - the select command
      Returns:
      the number of rows that will be returned

    • querySimpleList

      public <T> int querySimpleList(Class<T> c, String sqlCmd, Object[] sqlParams, DataType dataType, Collection<T> result, int maxRows)
      Adds the first column of a query result to a collection. If the query has no result, an empty list is returned.
      Type Parameters:
      T - the type for the list
      Parameters:
      c - the class type for the list
      sqlCmd - the sql command
      sqlParams - the command params
      dataType - the expected data type
      result - the reusult colletion
      maxRows - maximum number of rows or -1 for all rows
      Returns:
      the number of elements that have been added to the collection

    • querySimpleList

      public final <T> int querySimpleList(Class<T> c, DBCommandExpr cmd, Collection<T> result)
      Adds the first column of a query result to a collection. If the query has no result, an empty list is returned.
      Type Parameters:
      T - the type for the list
      Parameters:
      c - the class type for the list
      cmd - the Command object that contains the select statement
      result - the collection to which to add the result
      Returns:
      the number of elements that have been added to the collection

    • querySimpleList

      public final <T> List<T> querySimpleList(Class<T> c, DBCommandExpr cmd)
      Returns a one dimensional array from an sql query. The array is filled with the values of the first column.
      Type Parameters:
      T - the type for the list
      Parameters:
      c - the class type for the list
      cmd - the Command object that contains the select statement
      Returns:
      a list of the values of the first column of an sql query

    • querySimpleList

      public final List<Object> querySimpleList(DBCommandExpr cmd)
      Returns a one dimensional array from an sql query. The array is filled with the values of the first column.
      Parameters:
      cmd - the Command object that contains the select statement
      Returns:
      a list of values of type Object

    • queryOptionList

      public int queryOptionList(String sqlCmd, Object[] sqlParams, Options options)
      Fills an option list provided with the result from a query. The option list is filled with the values of the first and second column. Optionally a third column may provide a boolean value for active or disabled. IMPORTANT: The query must contain unique values in the first column!
      Parameters:
      sqlCmd - the sql command
      sqlParams - the command params
      options - the option list to where the options are added
      Returns:
      an Options object containing a set a of values and their corresponding names

    • queryOptionList

      public final int queryOptionList(DBCommandExpr cmd, Options options)
      Fills an option list provided with the result from a query. The option list is filled with the values of the first and second column.
      Parameters:
      cmd - the Command object that contains the select statement
      options - the option list to where the options are added
      Returns:
      an Options object containing a set a of values and their corresponding names

    • queryOptionList

      public final Options queryOptionList(DBCommandExpr cmd)
      Returns a list of key value pairs from an sql query. The option list is filled with the values of the first and second column.
      Parameters:
      cmd - the Command object that contains the select statement
      Returns:
      an Options object containing a set a of values and their corresponding names

    • queryObjectList

      public int queryObjectList(String sqlCmd, Object[] sqlParams, Collection<Object[]> result, int maxRows)
      Adds the result of a query to a given collection.
      The individual rows will be added as an array of objects (object[])

      This function should only be used for small lists. Otherwise a DBReader should be used!

      Parameters:
      sqlCmd - the sql command
      sqlParams - the command params
      result - the result colleciton
      maxRows - the maximum number of rows
      Returns:
      the number of rows queried

    • queryObjectList

      public final List<Object[]> queryObjectList(DBCommandExpr cmd)
      Returns the result of a query as a list Object-Arrays This function should only be used for small lists.
      Parameters:
      cmd - the Command object that contains the select statement
      Returns:
      a list of object arrays

    • querySingleRow

      public Object[] querySingleRow(String sqlCmd, Object[] sqlParams, boolean failOnNoResult)
      Returns all values of the first row of a sql-query as an array. If the query does not return a result a QueryNoResultException is thrown
      Parameters:
      sqlCmd - the SQL-Command
      sqlParams - list of query parameter values
      failOnNoResult - if true a QueryNoResultException is thrown when no result otherwise null is returned
      Returns:
      the values of the first row or null

    • querySingleRow

      public final Object[] querySingleRow(String sqlCmd, Object[] sqlParams)
      Returns all values of the first row of a sql-query as an array. If the query does not return a result a QueryNoResultException is thrown
      Parameters:
      sqlCmd - the SQL-Command
      sqlParams - list of query parameter values
      Returns:
      the values of the first row

    • querySingleRow

      public final Object[] querySingleRow(DBCommandExpr cmd, boolean failOnNoResult)
      Returns all values of the first row of a sql-query as an array. If the query does not return a result a QueryNoResultException is thrown
      Parameters:
      cmd - the Command object that contains the select statement
      failOnNoResult - if true a QueryNoResultException is thrown when no result otherwise null is returned
      Returns:
      the values of the first row

    • querySingleRow

      public final Object[] querySingleRow(DBCommandExpr cmd)
      Returns all values of the first row of a sql-query as an array. If the query does not return a result a QueryNoResultException is thrown
      Parameters:
      cmd - the Command object that contains the select statement
      Returns:
      the values of the first row

    • getInitialListCapacity

      protected int getInitialListCapacity(int pageSize)
      Returns the initial array list capacity. Usually returns 0 in order to use the ArrayList default.
      Parameters:
      pageSize - the designated page size
      Returns:
      the initial array list capacity or 0 to use the default

    • queryRowLimitExeeded

      protected void queryRowLimitExeeded()
      Called to inform that the limit for DataList, Record and Bean queries has exceeded the maximum value

    • handleQueryNoResult

      protected void handleQueryNoResult(DBCommandExpr cmd, Class<?> resultType)
      Called when a query has no result
      Parameters:
      cmd - the command
      resultType - the result type class

    • createDefaultDataListFactory

      protected <T extends DataListEntry> DataListFactory<T> createDefaultDataListFactory(Class<T> entryClass, DataListHead head)
      Crates a default DataListFactory for a DataListEntry class The DataListEntry class must provide the following constructor DataListEntry(DataListFactory<? extends DataListEntry> head, int rownum, Object values[])
      Parameters:
      entryClass - the entryClass for which to create the list head
      head - the data list head
      Returns:
      the data list factory

    • createDefaultDataListHead

      protected DataListHead createDefaultDataListHead(DBCommandExpr cmd, Class<? extends DataListEntry> entryClass)
      Crates a default DataListHead for a DataListEntry class
      Parameters:
      cmd - the cmd for which to create the DataListHead
      entryClass - the entry type class
      Returns:
      the DataListHead instance

    • queryDataList

      public <T extends DataListEntry> List<T> queryDataList(DBCommandExpr cmd, DataListFactory<T> factory, int first, int pageSize)
      Executes a query and returns a list of DataListEntry items
      Parameters:
      cmd - the command
      factory - the Factory to be used for each list item
      first - the number of records to skip from the beginning of the result
      pageSize - the maximum number of items to add to the list or -1 (default) for a maximum of MAX_QUERY_ROWS
      Returns:
      the list

    • queryDataList

      public final <T extends DataListEntry> List<T> queryDataList(DBCommandExpr cmd, Class<T> entryClass, DataListHead head)
      Queries a list of DataListEntry items
      Parameters:
      cmd - the query command
      entryClass - the entry type class
      head - the list head
      Returns:
      the data list

    • queryDataList

      public final <T extends DataListEntry> List<T> queryDataList(DBCommandExpr cmd, Class<T> entryClass)
      Queries a list of DataListEntry items
      Parameters:
      cmd - the query command
      entryClass - the entry type class
      Returns:
      the data list

    • queryDataList

      public final <T extends DataListEntry> List<T> queryDataList(DBCommandExpr cmd, Class<T> entryClass, int first, int maxItems)
      Queries a list of DataListEntry items
      Parameters:
      cmd - the query command
      entryClass - the entry type class
      first - the first record to add
      maxItems - the maximum number of records to add
      Returns:
      the data list

    • queryDataList

      public final List<DataListEntry> queryDataList(DBCommandExpr cmd)
      Queries a list of DataListEntry items
      Parameters:
      cmd - the query command
      Returns:
      the data list

    • queryDataList

      public final List<DataListEntry> queryDataList(DBCommandExpr cmd, int first, int maxItems)
      Queries a list of DataListEntry items
      Parameters:
      cmd - the query command
      first - the first record to add
      maxItems - the maximum number of records to add
      Returns:
      the data list

    • queryDataEntry

      public final <T extends DataListEntry> T queryDataEntry(DBCommandExpr cmd, Class<T> entryClass, DataListHead head)
      Queries a single DataListEntry item
      Parameters:
      cmd - the query command
      entryClass - the result class
      head - the list head
      Returns:
      the data entry

    • queryDataEntry

      public final <T extends DataListEntry> T queryDataEntry(DBCommandExpr cmd, Class<T> entryClass)
      Queries a single DataListEntry item
      Parameters:
      cmd - the query command
      entryClass - the result class
      Returns:
      the data entry

    • queryDataEntry

      @Deprecated public final <T extends DataListEntry> T queryDataEntry(DBCommandExpr cmd, Class<T> entryClass, boolean failOnNoResult)
      Deprecated.
      Queries a single DataListEntry item Deprecated. Please consider calling without the "failOnNoResult" parameter and overriding handleQueryNoResult()
      Parameters:
      cmd - the query command
      entryClass - the result class
      failOnNoResult - flag whether to fail on empty resultset
      Returns:
      the data entry

    • queryDataEntry

      public final DataListEntry queryDataEntry(DBCommandExpr cmd)
      Queries a single DataListEntry item
      Parameters:
      cmd - the query command
      Returns:
      the data entry

    • createDefaultRecordListFactory

      protected <R extends DBRecordBase> DBRecordListFactory<R> createDefaultRecordListFactory(Class<R> recordClass, DBRowSet rowset)
      Crates a default DBRecordListFactory for a DBRecord class The DBRecord class must provide the following constructor DBRecord(DBContext context, DBRowSet rowset)
      Parameters:
      recordClass - the recordClass for which to create the list head
      rowset - the underlying rowset for which to create the factory
      Returns:
      the record factory

    • queryRecordList

      public <R extends DBRecordBase> List<R> queryRecordList(DBCommand cmd, DBRecordListFactory<R> factory, int first, int pageSize)
      Executes a query and returns a list of DBRecord items
      Parameters:
      cmd - the command
      factory - the factory for creating record objects
      first - the number of records to skip from the beginning of the result
      pageSize - the maximum number of items to add to the list or -1 (default) for a maximum of MAX_QUERY_ROWS
      Returns:
      the list

    • queryRecordList

      public final <R extends DBRecordBase> List<R> queryRecordList(DBCommand cmd, DBRowSet rowset, Class<R> recordType)
      Executes a query and returns a list of DBRecord items
      Parameters:
      cmd - the command holding the constraints and order or the query
      rowset - the rowset for which to query the records
      recordType - the record class
      Returns:
      the list of DBRecord items

    • queryRecordList

      public final List<DBRecord> queryRecordList(DBCommand cmd, DBRowSet rowset)
      Executes a query and returns a list of DBRecord items
      Parameters:
      cmd - the command holding the constraints and order or the query
      rowset - the rowset for which to query the records
      Returns:
      the list of DBRecord items

    • createDefaultBeanListFactory

      protected <T> DBBeanListFactory<T> createDefaultBeanListFactory(Class<T> beanType, Column[] keyColumns, List<? extends DBColumnExpr> selectColumns)
      Crates a default DBBeanListFactory for Java bean class The DBRecord class must provide either a standard construtor with correspondig property set fundtions or a constructor using the fields of the query
      Parameters:
      beanType - the beanType for which to create the list head
      keyColumns - the key columns
      selectColumns - the select columns
      Returns:
      the bean factory

    • getRowsetBeanListFactory

      public <T> DBBeanListFactory<T> getRowsetBeanListFactory(Class<T> beanType, DBRowSet rowset)
      gets or creates DBBeanListFactory for the given rowset
      Parameters:
      beanType - the beanType for which to create the list head
      rowset - the rowset for which to return the factory
      Returns:
      the bean factory

    • getCommandBeanListFactory

      public <T> DBBeanListFactory<T> getCommandBeanListFactory(Class<T> beanType, DBCommandExpr cmd)
      gets or creates DBBeanListFactory for the given rowset
      Parameters:
      beanType - the beanType for which to create the list head
      cmd - the command
      Returns:
      the bean factory

    • queryBeanList

      public <T> List<T> queryBeanList(DBCommandExpr cmd, DBBeanListFactory<T> factory, Object parent, int first, int pageSize)
      Query a list of simple Java objects (beans)
      Parameters:
      cmd - the command
      factory - the bean factory
      parent - the parent object for the created beans (optional)
      first - the first row
      pageSize - the maximum number of items to add to the list or -1 (default) for a maximum of MAX_QUERY_ROWS
      Returns:
      the bean list

    • queryBeanList

      public final <T> List<T> queryBeanList(DBCommandExpr cmd, DBBeanListFactory<T> factory, Object parent)
      Queries a list of Java beans for a given command
      Parameters:
      cmd - the query command
      factory - the beanType factory used to instantiate the bean
      parent - (optional) the parent bean if any
      Returns:
      the list of java beans

    • queryBeanList

      public <T> List<T> queryBeanList(DBCommandExpr cmd, Class<T> beanType, DBRowSet rowset, Object parent)
      Queries a list of Java beans for a given command
      Parameters:
      cmd - the query command
      beanType - the beanType
      rowset - the rowset
      parent - (optional) the parent bean if any
      Returns:
      the list of java beans

    • queryBeanList

      public <T> List<T> queryBeanList(DBCommandExpr cmd, Class<T> beanType, Object parent)
      Queries a list of Java beans for a given command
      Parameters:
      cmd - the query command
      beanType - the beanType
      parent - (optional) the parent bean if any
      Returns:
      the list of java beans

    • queryBean

      public <T> T queryBean(DBCommandExpr cmd, DBBeanListFactory<T> factory)
      queries a single Java Bean for a given command
      Parameters:
      cmd - the query command
      factory - the factory to create the bean instance
      Returns:
      the bean instance

    • queryBean

      public <T> T queryBean(DBCommandExpr cmd, Class<T> beanType)
      Queries a single Java Bean for a given command
      Parameters:
      cmd - the query command
      beanType - the beanType
      Returns:
      the list of java beans

    • queryBean

      public final <T> T queryBean(Class<T> beanType, DBRowSet rowset, DBCompareExpr whereConstraints)
      Queries a single bean based on a where constraint
      Parameters:
      beanType - the beanType
      rowset - the rowset used for the query
      whereConstraints - the constraints for the query
      Returns:
      the entity bean

    • queryBean

      public final <T> T queryBean(Class<T> beanType, DBCompareExpr whereConstraints)
      Queries a single bean based on a where constraint
      Parameters:
      beanType - the beanType
      whereConstraints - the constraints for the query
      Returns:
      the entity bean

    • queryBean

      public final <T> T queryBean(Class<T> beanType, DBRowSet rowset, Object[] key)
      Queries a single bean based on primary key values
      Parameters:
      beanType - the beanType
      rowset - the rowset used for the query
      key - the primary key
      Returns:
      the entity bean