Package org.compiere.model

Class Query

java.lang.Object
org.compiere.model.Query
public class Query extends Object
Query model
Author:
Low Heng Sin, Teo Sarca, www.arhipac.ro
  • FR [ 1981760 ] Improve Query class
  • BF [ 2030280 ] org.compiere.model.Query apply access filter issue
  • FR [ 2041894 ] Add Query.match() method
  • FR [ 2107068 ] Query.setOrderBy should be more error tolerant
  • FR [ 2107109 ] Add method Query.setOnlyActiveRecords
  • FR [ 2421313 ] Introduce Query.firstOnly convenient method
  • FR [ 2546052 ] Introduce Query aggregate methods
  • FR [ 2726447 ] Query aggregate methods for all return types
  • FR [ 2818547 ] Implement Query.setOnlySelection https://sourceforge.net/p/adempiere/feature-requests/759/
  • FR [ 2818646 ] Implement Query.firstId/firstIdOnly https://sourceforge.net/p/adempiere/feature-requests/760/, Redhuan D. Oon
  • FR: [ 2214883 ] Remove SQL code and Replace for Query // introducing SQL String prompt in log.info
  • FR: [ 2214883 ] - to introduce .setClient_ID

    • Field Details

    • Constructor Details

    • Method Details

      • setParameters

        public Query setParameters(Object... parameters)
        Set query parameters
        Parameters:
        parameters -

      • setParameters

        public Query setParameters(List<Object> parameters)
        Set query parameters
        Parameters:
        parameters - collection of parameters

      • setOrderBy

        public Query setOrderBy(String orderBy)
        Set order by clause.
        If the string starts with "ORDER BY" then "ORDER BY" keywords will be discarded.
        Parameters:
        orderBy - SQL ORDER BY clause

      • setApplyAccessFilter

        public Query setApplyAccessFilter(boolean flag)
        Turn on/off the addition of data access filter
        Parameters:
        flag -

      • setApplyAccessFilter

        public Query setApplyAccessFilter(boolean fullyQualified, boolean RW)
        Turn on data access filter with controls
        Parameters:
        fullyQualified -
        RW -

      • setOnlyActiveRecords

        public Query setOnlyActiveRecords(boolean onlyActiveRecords)
        Select only active records (i.e. IsActive='Y')
        Parameters:
        onlyActiveRecords -

      • setClient_ID

        public Query setClient_ID()
        Set Client_ID true for WhereClause to auto include AD_Client_ID

      • setClient_ID

        public Query setClient_ID(boolean isIncludeClient)
        Set include or not include AD_Client_ID in where clause
        Parameters:
        isIncludeClient -

      • setOnlySelection

        public Query setOnlySelection(int AD_PInstance_ID)
        Only records that are in T_Selection with AD_PInstance_ID.
        Parameters:
        AD_PInstance_ID -

      • setForUpdate

        public Query setForUpdate(boolean forUpdate)
        Add FOR UPDATE clause
        Parameters:
        forUpdate -

      • setNoVirtualColumn

        public Query setNoVirtualColumn(boolean noVirtualColumn)
        Virtual columns are lazy loaded by default. In case lazy loading is not desired use this method with the false value.
        Parameters:
        noVirtualColumn - Whether to load (false value) all declared virtual columns at once or use lazy loading (true value).
        See Also:

      • setQueryTimeout

        public Query setQueryTimeout(int seconds)
        Set query statement timeout in seconds
        Parameters:
        seconds -

      • addJoinClause

        public Query addJoinClause(String joinClause)
        Add join clause (with JOIN keyword)
        Parameters:
        joinClause -

      • addTableDirectJoin

        public void addTableDirectJoin(String foreignTableName)
        Convenient method to add table direct type of joint.
        For e.g, if foreignTableName is C_BPartner and TableName for Query is AD_User,
        this will add join clause of
        "INNER JOIN C_BPartner ON (AD_User.C_BPartner_ID=C_BPartner.C_BParner_ID)".
        Parameters:
        foreignTableName -

      • list

        public <T extends PO> List<T> list() throws DBException
        Get a list of POs that match the query criteria.
        Returns:
        PO List
        Throws:
        DBException

      • first

        public <T extends PO> T first() throws DBException
        Get first PO that match query criteria
        Returns:
        first PO
        Throws:
        DBException

      • firstOnly

        public <T extends PO> T firstOnly() throws DBException
        Get first PO that match query criteria.
        If there are more PO that match query criteria, an exception will be thrown.
        Returns:
        first PO
        Throws:
        DBException

      • firstId

        public int firstId() throws DBException
        Get first matching record id
        Returns:
        first record ID or -1 if not found
        Throws:
        DBException

      • firstIdOnly

        public int firstIdOnly() throws DBException
        Get first matching record id.
        If there are more than 1 matching records, an exception is thrown.
        Returns:
        first record ID
        Throws:
        DBException

      • getSQL

        public String getSQL() throws DBException
        Get full SQL select statement
        Returns:
        SQL select statement
        Throws:
        DBException

      • aggregate

        public BigDecimal aggregate(String sqlExpression, String sqlFunction) throws DBException
        Get aggregate for given expression on this query criteria
        Parameters:
        sqlExpression - can be null if sqlFunction is COUNT, otherwise mandatory
        sqlFunction - aggregate function
        Returns:
        aggregated value
        Throws:
        DBException

      • aggregate

        public <T> T aggregate(String sqlExpression, String sqlFunction, Class<T> returnType) throws DBException
        Get aggregate for given expression on this query criteria
        Type Parameters:
        T -
        Parameters:
        sqlExpression - can be null if sqlFunction is COUNT, otherwise mandatory
        sqlFunction - aggregate function
        returnType -
        Returns:
        aggregated value
        Throws:
        DBException

      • count

        public int count() throws DBException
        Count records that match query criteria
        Returns:
        count
        Throws:
        DBException

      • sum

        public BigDecimal sum(String sqlExpression)
        SUM sqlExpression for records that match query criteria
        Parameters:
        sqlExpression -
        Returns:
        sum

      • match

        public boolean match() throws DBException
        Check if there are any matching records for this query criteria
        Returns:
        true if exists, false otherwise
        Throws:
        DBException

      • stream

        public <T extends PO> Stream<T> stream() throws DBException
        Get an Stream implementation to fetch one PO at a time. This method will only create POs on-demand and they will become eligible for garbage collection once they have been consumed by the stream, so unlike list() it doesn't have to hold a copy of all the POs in the result set in memory at one time.

        And unlike {#link #iterate()}, it only creates one ResultSet and iterates over it, creating a PO for each row. (iterate(), on the other hand, has to re-run the query for each element).

        For situations where you need to iterate over a result set and operate on the results one-at-a-time rather than operate on the group as a whole, this method is likely to give better performance than list() or iterate().

        However, because it keeps the underlying ResultSet open, you need to make sure that the stream is properly disposed of using close() or else you will get resource leaks. As Stream extends AutoCloseable, you can use it in a try-with-resources statement to automatically close it when you are done.

        Returns:
        Stream of POs.
        Throws:
        DBException

      • iterate

        public <T extends PO> Iterator<T> iterate() throws DBException
        Get an Iterator implementation to fetch one PO at a time.
        The implementation first retrieve all IDS that match the query criteria and issue SQL query to fetch each PO when caller want to fetch the next PO.
        This minimize memory usage (at both application and DB server end) but it is slower than the list, stream and scroll method.
        Returns:
        Iterator
        Throws:
        DBException

      • scroll

        public <T extends PO> POResultSet<T> scroll() throws DBException
        Get a simple wrapper over a jdbc resultset.
        It is the caller responsibility to call the close method to release the underlying database resources.
        Since POResultSet implements the AutoCloseable interface, it is recommended to use it in a try-with-resources statement to automatically close it when you are done.
        Returns:
        POResultSet
        Throws:
        DBException

      • setPageSize

        public Query setPageSize(int pPageSize)
        Set the pagination of the query.
        Parameters:
        pPageSize - Limit number of query rows to return.

      • setPage

        public Query setPage(int pPageSize, int pPagesToSkip)
        Set the pagination of the query.
        Parameters:
        pPageSize - Limit number of rows to return.
        pPagesToSkip - Number of pages to skipped on query run. ZERO for first page.

      • setRecordstoSkip

        public Query setRecordstoSkip(int pRecordsToSkip)
        Set the number of records to skip (a.k.a. OFFSET)
        Parameters:
        pRecordsToSkip - Number of records to skip

      • getIDs

        public int[] getIDs()
        Get all matching record IDs for this Query
        Returns:
        Array of matching record IDs

      • setVirtualColumns

        public Query setVirtualColumns(String... virtualColumns)
        Virtual columns to be included in the query.
        Parameters:
        virtualColumns - virtual column names

      • selectColumns

        public Query selectColumns(String... columns)
        Set the columns to include in select query.
        Note that this doesn't effect iterate().
        Parameters:
        columns -