Package org.compiere.model

Class Query

java.lang.Object
org.compiere.model.Query
public class Query extends Object
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 -
        Returns:

      • 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 routine to include AD_Client_ID

      • setClient_ID

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

      • 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).
        Returns:
        See Also:

      • setQueryTimeout

        public Query setQueryTimeout(int seconds)

      • addJoinClause

        public Query addJoinClause(String 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
        Return a list of all po that match the query criteria.
        Returns:
        List
        Throws:
        DBException

      • first

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

      • firstOnly

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

      • firstId

        public int firstId() throws DBException
        Return first ID
        Returns:
        first ID or -1 if not found
        Throws:
        DBException

      • firstIdOnly

        public int firstIdOnly() throws DBException
        Return first ID. If there are more results and exception is thrown.
        Returns:
        first ID or -1 if not found
        Throws:
        DBException

      • getSQL

        public String getSQL() throws DBException
        red1 - returns full SQL string - for caller needs
        Returns:
        buildSQL(null,true)
        Throws:
        DBException

      • aggregate

        public BigDecimal aggregate(String sqlExpression, String sqlFunction) throws DBException
        Aggregate given expression on this criteria
        Parameters:
        sqlExpression -
        sqlFunction -
        Returns:
        aggregated value
        Throws:
        DBException

      • aggregate

        public <T> T aggregate(String sqlExpression, String sqlFunction, Class<T> returnType) throws DBException
        Aggregate given expression on this criteria
        Type Parameters:
        T -
        Parameters:
        sqlExpression -
        sqlFunction -
        returnType -
        Returns:
        aggregated value
        Throws:
        DBException

      • count

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

      • sum

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

      • match

        public boolean match() throws DBException
        Check if there items for query criteria
        Returns:
        true if exists, false otherwise
        Throws:
        DBException

      • stream

        public <T extends PO> Stream<T> stream() throws DBException
        Return 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
        Return 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 the PO when caller want to fetch the next PO. This minimize memory usage but it is slower than the list method.
        Returns:
        Iterator
        Throws:
        DBException

      • scroll

        public <T extends PO> POResultSet<T> scroll() throws DBException
        Return a simple wrapper over a jdbc resultset. It is the caller responsibility to call the close method to release the underlying database resources.
        Returns:
        POResultSet
        Throws:
        DBException

      • setPageSize

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

      • setPage

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

      • setRecordstoSkip

        public Query setRecordstoSkip(int pRecordsToSkip)
        Set the number of records to skip (a.k.a. OFFSET)
        Parameters:
        pRecordsToSkip - Limit current query rows return.
        Returns:
        current Query

      • getIDs

        public int[] getIDs()
        Get a Array with the IDs for this Query
        Returns:
        Get a Array with the IDs

      • setVirtualColumns

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