Package org.compiere.util

Class DB

java.lang.Object
org.compiere.util.DB
public final class DB extends Object
Static methods for JDBC interface
Version:
$Id: DB.java,v 1.8 2006/10/09 00:22:29 jjanke Exp $ ---
Author:
Jorg Janke, Ashley Ramdass (Posterita)
  • Modifications: removed static references to database connection and instead always get a new connection from database pool manager which manages all connections set rw/ro properties for the connection accordingly., Teo Sarca, SC ARHIPAC SERVICE SRL
  • BF [ 1647864 ] WAN: delete record error
  • FR [ 1884435 ] Add more DB.getSQLValue helper methods
  • FR [ 1904460 ] DB.executeUpdate should handle Boolean params
  • BF [ 1962568 ] DB.executeUpdate should handle null params
  • FR [ 1984268 ] DB.executeUpdateEx should throw DBException
  • FR [ 1986583 ] Add DB.executeUpdateEx(String, Object[], String)
  • BF [ 2030233 ] Remove duplicate code from DB class
  • FR [ 2107062 ] Add more DB.getKeyNamePairs methods
  • FR [ 2448461 ] Introduce DB.getSQLValue*Ex methods
  • FR [ 2781053 ] Introduce DB.getValueNamePairs
  • FR [ 2818480 ] Introduce DB.createT_Selection helper method https://sourceforge.net/p/adempiere/feature-requests/757/, Teo Sarca, teo.sarca@gmail.com
  • BF [ 2873324 ] DB.TO_NUMBER should be a static method https://sourceforge.net/p/adempiere/bugs/2160/
  • FR [ 2873891 ] DB.getKeyNamePairs should use trxName https://sourceforge.net/p/adempiere/feature-requests/847/, Paul Bowden, phib BF 2900767 Zoom to child tab - inefficient queries

    • Field Details

      • SQLSTATEMENT_SEPARATOR

        public static final String SQLSTATEMENT_SEPARATOR
        SQL Statement Separator "; "
        See Also:

    • Constructor Details

      • DB

        public DB()

    • Method Details

      • afterMigration

        @Deprecated(forRemoval=true, since="11") public static boolean afterMigration(Properties ctx)
        Deprecated, for removal: This API element is subject to removal in a future version.
        Check need for post Upgrade
        Parameters:
        ctx - context
        Returns:
        true if post upgrade ran - false if there was no need

      • updateMail

        public static void updateMail()
        Update Mail Settings for System Client and System User (idempiereEnv.properties)

      • setDBTarget

        public static void setDBTarget(CConnection cc)
        Set active connection profile
        Parameters:
        cc - connection profile

      • connect

        @Deprecated public static boolean connect()
        Deprecated.
        Connect to database and initialise all connections.
        Returns:
        True if success, false otherwise

      • isConnected

        public static boolean isConnected()
        Is connected to DB.
        Returns:
        true, if connected to database

      • isConnected

        @Deprecated(since="10", forRemoval=true) public static boolean isConnected(boolean createNew)
        Deprecated, for removal: This API element is subject to removal in a future version.
        Replace by isConnected() Is there a connection to the database ?
        Parameters:
        createNew - ignore
        Returns:
        true, if connected to database

      • getConnection

        public static Connection getConnection()
        Get auto commit connection from connection pool.
        Returns:
        Connection

      • getConnection

        public static Connection getConnection(boolean autoCommit)
        Get auto or not auto commit connection from connection pool.
        Usually, developer should use @getConnection() instead to get auto commit connection and use Trx to works with not autoCommit connection.
        Parameters:
        autoCommit -
        Returns:
        Connection

      • getConnectionRW

        @Deprecated(since="10", forRemoval=true) public static Connection getConnectionRW()
        Deprecated, for removal: This API element is subject to removal in a future version.
        Replace by @getConnection()
        Returns:
        Connection (r/w)

      • getConnectionRW

        @Deprecated(since="10", forRemoval=true) public static Connection getConnectionRW(boolean createNew)
        Deprecated, for removal: This API element is subject to removal in a future version.
        Replace by @getConnection() Return (pooled) r/w AutoCommit, Serializable connection. For Transaction control use Trx.getConnection()
        Parameters:
        createNew - ignore
        Returns:
        Connection (r/w)

      • getConnectionID

        @Deprecated(since="10", forRemoval=true) public static Connection getConnectionID()
        Deprecated, for removal: This API element is subject to removal in a future version.
        Replace by @getConnection(boolean). Note that this is intended for internal use only from the beginning. Return everytime a new r/w no AutoCommit, Serializable connection. To be used to ID
        Returns:
        Connection (r/w)

      • getConnectionRO

        @Deprecated(since="10", forRemoval=true) public static Connection getConnectionRO()
        Deprecated, for removal: This API element is subject to removal in a future version.
        Replace by @getConnection(). Use Trx instead for readonly transaction. Return read committed, read/only from pool.
        Returns:
        Connection (r/o)

      • getReportingConnectionRO

        public static Connection getReportingConnectionRO()
        Return a replica connection if possible, otherwise from pool.
        Returns:
        Connection (r/o)

      • createConnection

        public static Connection createConnection(boolean autoCommit, int trxLevel)
        Create new Connection.
        The connection must be closed explicitly by the caller.
        Usually, developer should not call this directly.
        Parameters:
        autoCommit - auto commit
        trxLevel - - Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_REPEATABLE_READ, or Connection.TRANSACTION_READ_COMMITTED.
        Returns:
        Connection connection

      • createConnection

        @Deprecated(since="10", forRemoval=true) public static Connection createConnection(boolean autoCommit, boolean readOnly, int trxLevel)
        Deprecated, for removal: This API element is subject to removal in a future version.
        Replace by createConnection(boolean, int). Use Trx instead for readonly transaction. Create new Connection. The connection must be closed explicitly by the application.
        Parameters:
        autoCommit - auto commit
        readOnly - ignore
        trxLevel - - Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_REPEATABLE_READ, or Connection.TRANSACTION_READ_COMMITTED.
        Returns:
        Connection connection

      • getDatabase

        public static AdempiereDatabase getDatabase()
        Get Database Adapter.
        Access to database specific functionality.
        Returns:
        iDempiere Database Adapter

      • getDatabase

        public static AdempiereDatabase getDatabase(String URL)
        Get Database Adapter.
        Access to database specific functionality.
        Parameters:
        URL - JDBC connection url
        Returns:
        iDempiere Database Adapter

      • isOracle

        public static boolean isOracle()
        Is connected to Oracle DB ?
        Returns:
        true if connected to Oracle

      • isPostgreSQL

        public static boolean isPostgreSQL()
        Is connected to PostgreSQL DB ?
        Returns:
        true if connected to PostgreSQL

      • getDatabaseInfo

        public static String getDatabaseInfo()
        Get Database Info
        Returns:
        info

      • isDatabaseOK

        @Deprecated(since="10", forRemoval=true) public static boolean isDatabaseOK(Properties ctx)
        Deprecated, for removal: This API element is subject to removal in a future version.
        Check database Version with Code version
        Parameters:
        ctx - context
        Returns:
        true if Database version (date) is the same

      • isBuildOK

        public static boolean isBuildOK(Properties ctx)
        Check Build Version of Database against running client
        Parameters:
        ctx - context
        Returns:
        true if Database version (date) is the same

      • closeTarget

        public static void closeTarget()
        Close DB connection profile

      • prepareCall

        public static CallableStatement prepareCall(String sql)
        Create callable statement proxy
        Parameters:
        sql -
        Returns:
        Callable Statement

      • prepareCall

        public static CallableStatement prepareCall(String SQL, int resultSetConcurrency, String trxName)
        Create callable statement proxy
        Parameters:
        SQL -
        resultSetConcurrency -
        trxName -
        Returns:
        Callable Statement

      • prepareStatement

        public static CPreparedStatement prepareStatement(String sql)
        Deprecated.
        Prepare Statement
        Parameters:
        sql -
        Returns:
        Prepared Statement

      • prepareStatement

        public static CPreparedStatement prepareStatement(String sql, String trxName)
        Create prepare Statement proxy
        Parameters:
        sql -
        trxName - transaction
        Returns:
        Prepared Statement

      • prepareStatement

        public static CPreparedStatement prepareStatement(Connection connection, String sql)
        Create prepare Statement proxy
        Parameters:
        connection -
        sql -
        Returns:
        Prepared Statement

      • prepareStatement

        public static CPreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
        Deprecated.
        Prepare Statement.
        Parameters:
        sql -
        resultSetType - - ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_SENSITIVE
        resultSetConcurrency - - ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE
        Returns:
        Prepared Statement

      • prepareStatement

        public static CPreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, String trxName)
        Create prepare Statement proxy
        Parameters:
        sql -
        resultSetType - - ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_SENSITIVE
        resultSetConcurrency - - ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE
        trxName - transaction name
        Returns:
        Prepared Statement

      • prepareStatement

        public static CPreparedStatement prepareStatement(Connection connection, String sql, int resultSetType, int resultSetConcurrency)
        Create prepare Statement proxy
        Parameters:
        connection -
        sql - sql statement
        resultSetType - - ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_SENSITIVE
        resultSetConcurrency - - ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE
        Returns:
        Prepared Statement r/o or r/w depending on concur

      • createStatement

        public static Statement createStatement()
        Create Statement proxy
        Returns:
        Statement

      • createStatement

        public static Statement createStatement(int resultSetType, int resultSetConcurrency, String trxName)
        Create Statement Proxy.
        Parameters:
        resultSetType - - ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_SENSITIVE
        resultSetConcurrency - - ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE
        trxName - transaction name
        Returns:
        Statement

      • setParameters

        public static void setParameters(PreparedStatement stmt, Object[] params) throws SQLException
        Set parameters for given statement
        Parameters:
        stmt - statements
        params - parameters array; if null or empty array, no parameters are set
        Throws:
        SQLException

      • setParameters

        public static void setParameters(PreparedStatement stmt, List<?> params) throws SQLException
        Set parameters for given statement
        Parameters:
        stmt - statements
        params - parameters list; if null or empty list, no parameters are set
        Throws:
        SQLException

      • setParameter

        public static void setParameter(PreparedStatement pstmt, int index, Object param) throws SQLException
        Set PreparedStatement's parameter.
        Similar with calling pstmt.setObject(index, param)
        Parameters:
        pstmt -
        index -
        param -
        Throws:
        SQLException

      • executeUpdate

        public static int executeUpdate(String sql)
        Deprecated.
        Execute Update. saves "DBExecuteError" in Log
        Parameters:
        sql -
        Returns:
        number of rows updated or -1 if error

      • executeUpdate

        public static int executeUpdate(String sql, String trxName)
        Execute Update.
        Saves "DBExecuteError" in Log.
        Developer is recommended to call executeUpdateEx(String, String) instead.
        Parameters:
        sql -
        trxName - optional transaction name
        Returns:
        number of rows updated or -1 if error

      • executeUpdate

        public static int executeUpdate(String sql, String trxName, int timeOut)
        Execute Update.
        Saves "DBExecuteError" in Log.
        Developer is recommended to call executeUpdateEx(String, String, int) instead.
        Parameters:
        sql -
        trxName - optional transaction name
        timeOut - optional timeout parameter
        Returns:
        number of rows updated or -1 if error

      • executeUpdate

        public static int executeUpdate(String sql, boolean ignoreError)
        Deprecated.
        Execute Update. saves "DBExecuteError" in Log
        Parameters:
        sql -
        ignoreError - if true, no execution error is reported
        Returns:
        number of rows updated or -1 if error

      • executeUpdate

        public static int executeUpdate(String sql, boolean ignoreError, String trxName)
        Execute Update.
        Saves "DBExecuteError" in Log.
        Developer is recommended to call executeUpdateEx(String, String) instead.
        Parameters:
        sql -
        ignoreError - if true, no execution error is reported
        trxName - transaction
        Returns:
        number of rows updated or -1 if error

      • executeUpdate

        public static int executeUpdate(String sql, boolean ignoreError, String trxName, int timeOut)
        Execute Update.
        Saves "DBExecuteError" in Log.
        Developer is recommended to call executeUpdateEx(String, String, int) instead.
        Parameters:
        sql -
        ignoreError - if true, no execution error is reported
        trxName - transaction
        timeOut - optional timeOut parameter
        Returns:
        number of rows updated or -1 if error

      • executeUpdate

        public static int executeUpdate(String sql, int param, String trxName)
        Execute Update.
        Saves "DBExecuteError" in Log.
        Developer is recommended to call executeUpdateEx(String, Object[], String) instead.
        Parameters:
        sql -
        param - int param
        trxName - transaction
        Returns:
        number of rows updated or -1 if error

      • executeUpdate

        public static int executeUpdate(String sql, int param, String trxName, int timeOut)
        Execute Update.
        Saves "DBExecuteError" in Log.
        Developer is recommended to call executeUpdateEx(String, Object[], String, int) instead.
        Parameters:
        sql -
        param - int param
        trxName - transaction
        timeOut - optional timeOut parameter
        Returns:
        number of rows updated or -1 if error

      • executeUpdate

        public static int executeUpdate(String sql, int param, boolean ignoreError, String trxName)
        Execute Update.
        Saves "DBExecuteError" in Log.
        Developer is recommended to call executeUpdateEx(String, Object[], String) instead.
        Parameters:
        sql -
        param - int parameter
        ignoreError - if true, no execution error is reported
        trxName - transaction
        Returns:
        number of rows updated or -1 if error

      • executeUpdate

        public static int executeUpdate(String sql, int param, boolean ignoreError, String trxName, int timeOut)
        Execute Update.
        Saves "DBExecuteError" in Log. Developer is recommended to call executeUpdateEx(String, Object[], String, int) instead.
        Parameters:
        sql -
        param - int parameter
        ignoreError - if true, no execution error is reported
        trxName - transaction
        timeOut - optional timeOut parameter
        Returns:
        number of rows updated or -1 if error

      • executeUpdate

        public static int executeUpdate(String sql, Object[] params, boolean ignoreError, String trxName)
        Execute Update.
        Saves "DBExecuteError" in Log.
        Developer is recommended to call executeUpdateEx(String, Object[], String) instead.
        Parameters:
        sql -
        params - array of parameters
        ignoreError - if true, no execution error is reported
        trxName - optional transaction name
        Returns:
        number of rows updated or -1 if error

      • executeUpdate

        public static int executeUpdate(String sql, Object[] params, boolean ignoreError, String trxName, int timeOut)
        Execute Update.
        Saves "DBExecuteError" in Log.
        Developer is recommended to call executeUpdateEx(String, Object[], String, int) instead.
        Parameters:
        sql -
        params - array of parameters
        ignoreError - if true, no execution error is reported
        trxName - optional transaction name
        timeOut - optional timeOut parameter
        Returns:
        number of rows updated or -1 if error

      • executeUpdateEx

        public static int executeUpdateEx(String sql, Object[] params, String trxName) throws DBException
        Execute update and throw DBException if there are errors.
        Parameters:
        sql -
        params - statement parameters
        trxName - transaction
        Returns:
        number of rows updated
        Throws:
        SQLException
        DBException

      • executeUpdateEx

        public static int executeUpdateEx(String sql, Object[] params, String trxName, int timeOut) throws DBException
        Execute update and throw DBException if there are errors.
        Parameters:
        sql -
        params - statement parameters
        trxName - transaction
        timeOut - optional timeOut parameter
        Returns:
        number of rows updated
        Throws:
        DBException

      • executeUpdateMultiple

        public static int executeUpdateMultiple(String sql, boolean ignoreError, String trxName)
        Execute multiple Update statements.
        Saves (last) "DBExecuteError" in Log.
        Parameters:
        sql - multiple sql statements separated by "; " SQLSTATEMENT_SEPARATOR
        ignoreError - if true, no execution error is reported
        trxName - optional transaction name
        Returns:
        number of rows updated or -1 if error

      • executeUpdateEx

        public static int executeUpdateEx(String sql, String trxName) throws DBException
        Execute update and throw DBException if there are errors.
        Parameters:
        sql -
        trxName -
        Throws:
        DBException

      • executeUpdateEx

        public static int executeUpdateEx(String sql, String trxName, int timeOut) throws DBException
        Execute update and throw DBException if there are errors.
        Parameters:
        sql -
        trxName -
        timeOut -
        Throws:
        DBException

      • commit

        public static boolean commit(boolean throwException, String trxName) throws SQLException, IllegalStateException
        Commit transaction
        Parameters:
        throwException - if true, re-throws exception
        trxName - transaction name
        Returns:
        true if not needed (trxName is null) or success
        Throws:
        SQLException
        IllegalStateException

      • rollback

        public static boolean rollback(boolean throwException, String trxName) throws SQLException
        Rollback transaction
        Parameters:
        throwException - if true, re-throws exception
        trxName - transaction name
        Returns:
        true if not needed (trxName is null) or success
        Throws:
        SQLException

      • getRowSet

        public static RowSet getRowSet(String sql)
        Get Row Set.
        When a Rowset is closed, it also closes the underlying connection.
        Parameters:
        sql -
        Returns:
        row set or null

      • getSQLValueEx

        public static int getSQLValueEx(String trxName, String sql, Object... params) throws DBException
        Get int Value from sql
        Parameters:
        trxName - optional transaction name
        sql -
        params - array of parameters
        Returns:
        first value or -1 if not found
        Throws:
        DBException - if there is any SQLException

      • getSQLValueEx

        public static int getSQLValueEx(String trxName, String sql, List<Object> params)
        Get int value from sql
        Parameters:
        trxName - optional transaction name
        sql -
        params - collection of parameters
        Returns:
        first value or -1
        Throws:
        DBException - if there is any SQLException

      • getSQLValue

        public static int getSQLValue(String trxName, String sql, Object... params)
        Get int Value from sql.
        Developer is recommended to call getSQLValueEx(String, String, Object...) instead.
        Parameters:
        trxName - optional transaction name
        sql -
        params - array of parameters
        Returns:
        first value or -1 if not found or error

      • getSQLValue

        public static int getSQLValue(String trxName, String sql, List<Object> params)
        Get int value from sql.
        Developer is recommended to call getSQLValueEx(String, String, List) instead.
        Parameters:
        trxName - optional transaction name
        sql -
        params - collection of parameters
        Returns:
        first value or null

      • getSQLValueStringEx

        public static String getSQLValueStringEx(String trxName, String sql, Object... params)
        Get string value from sql
        Parameters:
        trxName - optional transaction name
        sql -
        params - array of parameters
        Returns:
        first value or null
        Throws:
        DBException - if there is any SQLException

      • getSQLValueStringEx

        public static String getSQLValueStringEx(String trxName, String sql, List<Object> params)
        Get String Value from sql
        Parameters:
        trxName - optional transaction name
        sql -
        params - collection of parameters
        Returns:
        first value or null
        Throws:
        DBException - if there is any SQLException

      • getSQLValueString

        public static String getSQLValueString(String trxName, String sql, Object... params)
        Get String Value from sql
        Parameters:
        trxName - optional transaction name
        sql -
        params - array of parameters
        Returns:
        first value or null

      • getSQLValueString

        public static String getSQLValueString(String trxName, String sql, List<Object> params)
        Get string value from sql.
        Developer is recommended to call getSQLValueStringEx(String, String, List) instead.
        Parameters:
        trxName - optional transaction name
        sql -
        params - collection of parameters
        Returns:
        first value or null

      • getSQLValueBDEx

        public static BigDecimal getSQLValueBDEx(String trxName, String sql, Object... params) throws DBException
        Get BigDecimal value from sql
        Parameters:
        trxName - optional transaction name
        sql -
        params - array of parameters
        Returns:
        first value or null if not found
        Throws:
        DBException - if there is any SQLException

      • getSQLValueBDEx

        public static BigDecimal getSQLValueBDEx(String trxName, String sql, List<Object> params) throws DBException
        Get BigDecimal Value from sql
        Parameters:
        trxName - optional transaction name
        sql -
        params - collection of parameters
        Returns:
        first value or null if not found
        Throws:
        DBException - if there is any SQLException

      • getSQLValueBD

        public static BigDecimal getSQLValueBD(String trxName, String sql, Object... params)
        Get BigDecimal Value from sql.
        Developer is recommended to call getSQLValueBDEx(String, String, Object...) instead.
        Parameters:
        trxName - optional transaction name
        sql -
        params - array of parameters
        Returns:
        first value or null

      • getSQLValueBD

        public static BigDecimal getSQLValueBD(String trxName, String sql, List<Object> params)
        Get BigDecimal Value from sql.
        Developer is recommended to call getSQLValueBDEx(String, String, List) instead.
        Parameters:
        trxName - optional transaction name
        sql -
        params - collection of parameters
        Returns:
        first value or null

      • getSQLValueTSEx

        public static Timestamp getSQLValueTSEx(String trxName, String sql, Object... params)
        Get Timestamp Value from sql
        Parameters:
        trxName - optional transaction name
        sql -
        params - array of parameters
        Returns:
        first value or null
        Throws:
        DBException - if there is any SQLException

      • getSQLValueTSEx

        public static Timestamp getSQLValueTSEx(String trxName, String sql, List<Object> params) throws DBException
        Get Timestamp Value from sql
        Parameters:
        trxName - optional transaction name
        sql -
        params - collection of parameters
        Returns:
        first value or null if not found
        Throws:
        DBException - if there is any SQLException

      • getSQLValueTS

        public static Timestamp getSQLValueTS(String trxName, String sql, Object... params)
        Get Timestamp Value from sql.
        Developer is recommended to call getSQLValueTSEx(String, String, Object...) instead.
        Parameters:
        trxName - optional transaction name
        sql -
        params - array of parameters
        Returns:
        first value or null

      • getSQLValueTS

        public static Timestamp getSQLValueTS(String trxName, String sql, List<Object> params)
        Get Timestamp Value from sql.
        Developer is recommended to call getSQLValueTSEx(String, String, List) instead.
        Parameters:
        trxName - optional transaction name
        sql -
        params - collection of parameters
        Returns:
        first value or null

      • getKeyNamePairs

        public static KeyNamePair[] getKeyNamePairs(String sql, boolean optional)
        Get Array of Key Name Pairs
        Parameters:
        sql - select with id / name as first / second column
        optional - if true (-1,"") is added
        Returns:
        array of KeyNamePair
        See Also:

      • getKeyNamePairsEx

        public static KeyNamePair[] getKeyNamePairsEx(String sql, boolean optional)
        Get Array of Key Name Pairs
        Parameters:
        sql - select with id / name as first / second column
        optional - if true (-1,"") is added
        Returns:
        array of KeyNamePair
        See Also:

      • getKeyNamePairs

        public static KeyNamePair[] getKeyNamePairs(String sql, boolean optional, Object... params)
        Get Array of Key Name Pairs
        Parameters:
        sql - select with id / name as first / second column
        optional - if true (-1,"") is added
        params - query parameters

      • getKeyNamePairsEx

        public static KeyNamePair[] getKeyNamePairsEx(String sql, boolean optional, Object... params)
        Get Array of Key Name Pairs
        Parameters:
        sql - select with id / name as first / second column
        optional - if true (-1,"") is added
        params - query parameters

      • getKeyNamePairs

        public static KeyNamePair[] getKeyNamePairs(String trxName, String sql, boolean optional, Object... params)
        Get Array of Key Name Pairs
        Parameters:
        trxName -
        sql - select with id / name as first / second column
        optional - if true (-1,"") is added
        params - query parameters
        Returns:
        Array of Key Name Pairs

      • getKeyNamePairsEx

        public static KeyNamePair[] getKeyNamePairsEx(String trxName, String sql, boolean optional, Object... params)
        Get Array of Key Name Pairs
        Parameters:
        trxName -
        sql - select with id / name as first / second column
        optional - if true (-1,"") is added
        params - query parameters
        Returns:
        Array of Key Name Pairs

      • getIDsEx

        public static int[] getIDsEx(String trxName, String sql, Object... params) throws DBException
        Get Array of IDs
        Parameters:
        trxName -
        sql - select with id as first column
        params - query parameters
        Throws:
        DBException - if there is any SQLException

      • isSOTrx

        public static boolean isSOTrx(String TableName, String whereClause, int windowNo)
        Is Sales Order Trx.
        Assumes Sales Order. Query IsSOTrx value of table with where clause
        Parameters:
        TableName - table
        whereClause - where clause
        windowNo -
        Returns:
        true (default) or false if tested that not SO

      • isSOTrx

        public static boolean isSOTrx(String TableName, String whereClause)
        Delegate to isSOTrx(String, String, int) with -1 for windowNo parameter.
        Parameters:
        TableName -
        whereClause -
        Returns:
        true (default) or false if tested that not SO

      • getNextID

        public static int getNextID(Properties ctx, String TableName, String trxName)
        Get next id for table
        Parameters:
        ctx - client
        TableName - table name
        trxName - optional transaction name
        Returns:
        next id no

      • getNextID

        public static int getNextID(int AD_Client_ID, String TableName, String trxName)
        Get next id for table
        Parameters:
        AD_Client_ID - client
        TableName - table name
        trxName - optional Transaction Name
        Returns:
        next id no

      • getDocumentNo

        public static String getDocumentNo(int C_DocType_ID, String trxName)
        Deprecated.
        Get Document No based on Document Type (backward compatibility)
        Parameters:
        C_DocType_ID - document type
        trxName - optional Transaction Name
        Returns:
        document no or null

      • getDocumentNo

        public static String getDocumentNo(int C_DocType_ID, String trxName, boolean definite)
        Get Document No based on Document Type
        Parameters:
        C_DocType_ID - document type
        trxName - optional Transaction Name
        definite - asking for a definitive or temporary sequence
        Returns:
        document no or null

      • getDocumentNo

        public static String getDocumentNo(int C_DocType_ID, String trxName, boolean definite, PO po)
        Get Document No based on Document Type
        Parameters:
        C_DocType_ID - document type
        trxName - optional Transaction Name
        definite - asking for a definitive or temporary sequence
        po - PO
        Returns:
        document no or null

      • getDocumentNo

        public static String getDocumentNo(int AD_Client_ID, String TableName, String trxName)
        Get Document No for table
        Parameters:
        AD_Client_ID - client
        TableName - table name
        trxName - optional Transaction Name
        Returns:
        document no or null

      • getDocumentNo

        public static String getDocumentNo(int AD_Client_ID, String TableName, String trxName, PO po)
        Get Document No for table
        Parameters:
        AD_Client_ID - client
        TableName - table name
        trxName - optional Transaction Name
        po -
        Returns:
        document no or null

      • getDocumentNo

        public static String getDocumentNo(Properties ctx, int WindowNo, String TableName, boolean onlyDocType, String trxName)
        Get Document Number for current document.
        - first search for DocumentNo based on DocType from environment context
        - then search for DocumentNo based on TableName
        Parameters:
        ctx - context
        WindowNo - window
        TableName - table
        onlyDocType - Do not search for document no based on TableName
        trxName - optional Transaction Name
        Returns:
        DocumentNo or null, if no doc number defined

      • isRemoteObjects

        @Deprecated(forRemoval=true) public static boolean isRemoteObjects()
        Deprecated, for removal: This API element is subject to removal in a future version.
        Is this a remote client connection. Deprecated, always return false.
        Returns:
        true if client and RMI or Objects on Server

      • isRemoteProcess

        @Deprecated(forRemoval=true) public static boolean isRemoteProcess()
        Deprecated, for removal: This API element is subject to removal in a future version.
        Is this a remote client connection Deprecated, always return false.
        Returns:
        true if client and RMI or Process on Server

      • printWarning

        public static void printWarning(String comment, SQLWarning warning)
        Print SQL Warnings.
        Usage: DB.printWarning("comment", rs.getWarnings());
        Parameters:
        comment - comment
        warning - warning

      • TO_DATE

        public static String TO_DATE(Timestamp time, boolean dayOnly)
        Create SQL TO Date String from Timestamp
        Parameters:
        time - Date to be converted
        dayOnly - true if time set to 00:00:00
        Returns:
        TO_DATE('2001-01-30 18:10:20',''YYYY-MM-DD HH24:MI:SS') or TO_DATE('2001-01-30',''YYYY-MM-DD')

      • TO_DATE

        public static String TO_DATE(Timestamp day)
        Create SQL TO Date String from Timestamp
        Parameters:
        day - day time
        Returns:
        TO_DATE String (day only)

      • TO_CHAR

        public static String TO_CHAR(String columnName, int displayType, String AD_Language)
        Create SQL for formatted Date, Number
        Parameters:
        columnName - the column name in the SQL
        displayType - Display Type
        AD_Language - 6 character language setting (from Env.LANG_*)
        Returns:
        TRIM(TO_CHAR(columnName,'999G999G999G990D00','NLS_NUMERIC_CHARACTERS='',.''')) or TRIM(TO_CHAR(columnName,'TM9')) depending on DisplayType and Language
        See Also:

      • TO_NUMBER

        public static String TO_NUMBER(BigDecimal number, int displayType)
        Return number as string for INSERT statements with correct precision
        Parameters:
        number - number
        displayType - display Type
        Returns:
        number as string

      • TO_STRING

        public static String TO_STRING(String txt)
        Package Strings for SQL command in quotes
        Parameters:
        txt - String with text
        Returns:
        escaped string for sql statement (NULL if null)

      • TO_STRING

        public static String TO_STRING(String txt, int maxLength)
        Package Strings for SQL command in quotes. 
                    -   include in ' (single quotes)
            -   replace ' with ''
        Parameters:
        txt - String with text
        maxLength - Maximum Length of content or 0 to ignore
        Returns:
        escaped string for sql statement (NULL if null)

      • TO_JSON

        public static String TO_JSON(String value)
        Return string as JSON object for INSERT statements with correct precision
        Parameters:
        value -
        Returns:
        value as json

      • getJSONCast

        public static String getJSONCast()
        Returns:
        string with right casting for JSON inserts

      • close

        public static void close(ResultSet rs)
        Convenient method to close result set
        Parameters:
        rs -

      • close

        public static void close(Statement st)
        Convenient method to close statement
        Parameters:
        st -

      • close

        public static void close(ResultSet rs, Statement st)
        Convenient method to close result set and statement
        Parameters:
        rs - result set
        st - statement
        See Also:

      • close

        public static void close(POResultSet<?> rs)
        Convenient method to close a POResultSet
        Parameters:
        rs - result set
        See Also:

      • getSQLException

        public static Exception getSQLException(Exception e)
        Try to get the SQLException from Exception
        Parameters:
        e - Exception
        Returns:
        SQLException if found or provided exception elsewhere

      • getSQLValue

        public static int getSQLValue(String trxName, String sql)

      • getSQLValue

        public static int getSQLValue(String trxName, String sql, int int_param1)

      • getSQLValue

        public static int getSQLValue(String trxName, String sql, int int_param1, int int_param2)

      • getSQLValue

        public static int getSQLValue(String trxName, String sql, String str_param1)

      • getSQLValue

        public static int getSQLValue(String trxName, String sql, int int_param1, String str_param2)

      • getSQLValueString

        public static String getSQLValueString(String trxName, String sql, int int_param1)

      • getSQLValueBD

        public static BigDecimal getSQLValueBD(String trxName, String sql, int int_param1)

      • getValueNamePairs

        public static ValueNamePair[] getValueNamePairs(String sql, boolean optional, List<Object> params)
        Get Array of ValueNamePair items. 
         Example:
 String sql = "SELECT Name, Description FROM AD_Ref_List WHERE AD_Reference_ID=?";
 ValueNamePair[] list = DB.getValueNamePairs(sql, false, params);
        Parameters:
        sql - SELECT Value_Column, Name_Column FROM ...
        optional - if ValueNamePair.EMPTY is added
        params - query parameters
        Returns:
        array of ValueNamePair or empty array
        Throws:
        DBException - if there is any SQLException

      • getKeyNamePairs

        public static KeyNamePair[] getKeyNamePairs(String sql, boolean optional, List<Object> params)
        Get Array of KeyNamePair items. 
         Example:
 String sql = "SELECT C_City_ID, Name FROM C_City WHERE C_City_ID=?";
 KeyNamePair[] list = DB.getKeyNamePairs(sql, false, params);
        Parameters:
        sql - SELECT ID_Column, Name_Column FROM ...
        optional - if ValueNamePair.EMPTY is added
        params - query parameters
        Returns:
        array of KeyNamePair or empty array
        Throws:
        DBException - if there is any SQLException

      • createT_Selection

        public static void createT_Selection(int AD_PInstance_ID, Collection<Integer> selection, String trxName)
        Insert selection into T_Selection table.
        Keeping this method for backward compatibility. refer: IDEMPIERE-1970
        Parameters:
        AD_PInstance_ID -
        selection -
        trxName -

      • createT_SelectionNew

        public static void createT_SelectionNew(int AD_PInstance_ID, Collection<KeyNamePair> saveKeys, String trxName)
        Insert selection into T_Selection table.
        saveKeys is map with rowID as key and list of viewID as value.
        Parameters:
        AD_PInstance_ID -
        saveKeys - - Collection of KeyNamePair
        trxName -

      • createT_SelectionNewNP

        public static void createT_SelectionNewNP(int AD_PInstance_ID, Collection<NamePair> saveKeys, String trxName)
        Insert selection into T_Selection table.
        saveKeys is map with rowID as key and list of viewID as value.
        Parameters:
        AD_PInstance_ID -
        saveKeys - can receive a Collection of KeyNamePair (IDs) or ValueNamePair (UUIDs)
        trxName -

      • isGenerateUUIDSupported

        public static boolean isGenerateUUIDSupported()
        Is DB support generate_uuid function
        Returns:
        true if current db have working generate_uuid function. generate_uuid doesn't work on 64 bit postgresql on windows yet.

      • isTableOrViewExists

        public static boolean isTableOrViewExists(String tableName)
        Is table or view exists
        Parameters:
        tableName -
        Returns:
        true if table or view with name=tableName exists in db

      • getSQLValueObjectsEx

        public static List<Object> getSQLValueObjectsEx(String trxName, String sql, Object... params)
        Get a list of objects from sql (one per each column in the select clause), column indexing starts with 0
        Parameters:
        trxName - optional transaction name
        sql -
        params - array of parameters
        Returns:
        null if not found
        Throws:
        DBException - if there is any SQLException

      • getSQLArrayObjectsEx

        public static List<List<Object>> getSQLArrayObjectsEx(String trxName, String sql, Object... params)
        Get a list of object list from sql (one object list per each row, and in the object list, one object per each column in the select clause), column indexing starts with 0.
        WARNING: This method must be used just for queries returning few records, using it for many records implies heavy memory consumption
        Parameters:
        trxName - optional transaction name
        sql -
        params - array of parameters
        Returns:
        null if not found
        Throws:
        DBException - if there is any SQLException

      • prepareNormalReadReplicaStatement

        public static PreparedStatement prepareNormalReadReplicaStatement(String sql, String trxName)
        Create Read Replica Prepared Statement proxy
        Parameters:
        sql -
        trxName - transaction
        Returns:
        Prepared Statement (from replica if possible, otherwise normal statement)

      • inClauseForCSV

        public static String inClauseForCSV(String columnName, String csv)
        Create IN clause for csv value
        Parameters:
        columnName -
        csv - comma separated value
        Returns:
        IN clause

      • inClauseForCSV

        public static String inClauseForCSV(String columnName, String csv, boolean isNotClause)
        Create IN clause for csv value
        Parameters:
        columnName -
        csv - comma separated value
        isNotClause - true to append NOT before IN
        Returns:
        IN clause

      • subsetClauseForCSV

        public static String subsetClauseForCSV(String columnName, String csv)
        Create subset clause for csv value (i.e columnName is a subset of the csv value set)
        Parameters:
        columnName -
        csv -
        Returns:
        subset sql clause

      • intersectClauseForCSV

        public static String intersectClauseForCSV(String columnName, String csv)
        Create intersect clause for csv value (i.e columnName is an intersect with the csv value set)
        Parameters:
        columnName -
        csv -
        Returns:
        intersect sql clause

      • intersectClauseForCSV

        public static String intersectClauseForCSV(String columnName, String csv, boolean isNotClause)
        Create intersect clause for csv value (i.e columnName is an intersect with the csv value set)
        Parameters:
        columnName -
        csv -
        isNotClause - true to append NOT before the intersect clause
        Returns:
        intersect sql clause

      • isSelectStatement

        public static boolean isSelectStatement(String sql)
        Is sql a SELECT statement
        Parameters:
        sql -
        Returns:
        true if it is a SELECT statement