Package org.compiere.util
Class DB
java.lang.Object
org.compiere.util.DB
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 Summary
-
Constructor Summary
-
Method Summary
Modifier and TypeMethodDescriptionstatic boolean
afterMigration
(Properties ctx) Deprecated, for removal: This API element is subject to removal in a future version.static void
Convenient method to close result setstatic void
Convenient method to close result set and statementstatic void
Convenient method to close statementstatic void
close
(POResultSet<?> rs) Convenient method to close aPOResultSet
static void
Close DB connection profilestatic boolean
Commit transactionstatic boolean
connect()
Deprecated.static Connection
createConnection
(boolean autoCommit, boolean readOnly, int trxLevel) Deprecated, for removal: This API element is subject to removal in a future version.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.static Statement
Create Statement proxystatic Statement
createStatement
(int resultSetType, int resultSetConcurrency, String trxName) Create Statement Proxy.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.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.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.static int
executeUpdate
(String sql) Deprecated.static int
executeUpdate
(String sql, boolean ignoreError) Deprecated.static int
executeUpdate
(String sql, boolean ignoreError, String trxName) Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, String)
instead.static int
executeUpdate
(String sql, boolean ignoreError, String trxName, int timeOut) Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, String, int)
instead.static int
executeUpdate
(String sql, int param, boolean ignoreError, String trxName) Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, Object[], String)
instead.static int
executeUpdate
(String sql, int param, boolean ignoreError, String trxName, int timeOut) Execute Update.
Saves "DBExecuteError" in Log.static int
executeUpdate
(String sql, int param, String trxName) Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, Object[], String)
instead.static int
executeUpdate
(String sql, int param, String trxName, int timeOut) Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, Object[], String, int)
instead.static int
executeUpdate
(String sql, Object[] params, boolean ignoreError, String trxName) Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, Object[], String)
instead.static int
executeUpdate
(String sql, Object[] params, boolean ignoreError, String trxName, int timeOut) Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, Object[], String, int)
instead.static int
executeUpdate
(String sql, String trxName) Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, String)
instead.static int
executeUpdate
(String sql, String trxName, int timeOut) Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, String, int)
instead.static int
executeUpdateEx
(String sql, Object[] params, String trxName) Execute update and throw DBException if there are errors.static int
executeUpdateEx
(String sql, Object[] params, String trxName, int timeOut) Execute update and throw DBException if there are errors.static int
executeUpdateEx
(String sql, String trxName) Execute update and throw DBException if there are errors.static int
executeUpdateEx
(String sql, String trxName, int timeOut) Execute update and throw DBException if there are errors.static int
executeUpdateMultiple
(String sql, boolean ignoreError, String trxName) Execute multiple Update statements.
Saves (last) "DBExecuteError" in Log.static Connection
Get auto commit connection from connection pool.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 useTrx
to works with not autoCommit connection.static Connection
Deprecated, for removal: This API element is subject to removal in a future version.static Connection
Deprecated, for removal: This API element is subject to removal in a future version.static Connection
Deprecated, for removal: This API element is subject to removal in a future version.static Connection
getConnectionRW
(boolean createNew) Deprecated, for removal: This API element is subject to removal in a future version.static AdempiereDatabase
Get Database Adapter.
Access to database specific functionality.static AdempiereDatabase
getDatabase
(String URL) Get Database Adapter.
Access to database specific functionality.static String
Get Database Infostatic String
getDocumentNo
(int C_DocType_ID, String trxName) Deprecated.static String
getDocumentNo
(int C_DocType_ID, String trxName, boolean definite) Get Document No based on Document Typestatic String
getDocumentNo
(int C_DocType_ID, String trxName, boolean definite, PO po) Get Document No based on Document Typestatic String
getDocumentNo
(int AD_Client_ID, String TableName, String trxName) Get Document No for tablestatic String
getDocumentNo
(int AD_Client_ID, String TableName, String trxName, PO po) Get Document No for tablestatic String
getDocumentNo
(Properties ctx, int WindowNo, String TableName, boolean onlyDocType, String trxName) Get Document Number for current document.static int[]
Get Array of IDsstatic String
static KeyNamePair[]
getKeyNamePairs
(String sql, boolean optional) Get Array of Key Name Pairsstatic KeyNamePair[]
getKeyNamePairs
(String sql, boolean optional, Object... params) Get Array of Key Name Pairsstatic KeyNamePair[]
getKeyNamePairs
(String sql, boolean optional, List<Object> params) Get Array of KeyNamePair items.static KeyNamePair[]
getKeyNamePairs
(String trxName, String sql, boolean optional, Object... params) Get Array of Key Name Pairsstatic int
Get next id for tablestatic int
getNextID
(Properties ctx, String TableName, String trxName) Get next id for tablestatic Connection
Return a replica connection if possible, otherwise from pool.static RowSet
Get Row Set.
When a Rowset is closed, it also closes the underlying connection.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 consumptionstatic Exception
Try to get the SQLException from Exceptionstatic int
getSQLValue
(String trxName, String sql) static int
getSQLValue
(String trxName, String sql, int int_param1) static int
getSQLValue
(String trxName, String sql, int int_param1, int int_param2) static int
getSQLValue
(String trxName, String sql, int int_param1, String str_param2) static int
getSQLValue
(String trxName, String sql, Object... params) Get int Value from sql.
Developer is recommended to callgetSQLValueEx(String, String, Object...)
instead.static int
getSQLValue
(String trxName, String sql, String str_param1) static int
getSQLValue
(String trxName, String sql, List<Object> params) Get int value from sql.
Developer is recommended to callgetSQLValueEx(String, String, List)
instead.static BigDecimal
getSQLValueBD
(String trxName, String sql, int int_param1) static BigDecimal
getSQLValueBD
(String trxName, String sql, Object... params) Get BigDecimal Value from sql.
Developer is recommended to callgetSQLValueBDEx(String, String, Object...)
instead.static BigDecimal
getSQLValueBD
(String trxName, String sql, List<Object> params) Get BigDecimal Value from sql.
Developer is recommended to callgetSQLValueBDEx(String, String, List)
instead.static BigDecimal
getSQLValueBDEx
(String trxName, String sql, Object... params) Get BigDecimal value from sqlstatic BigDecimal
getSQLValueBDEx
(String trxName, String sql, List<Object> params) Get BigDecimal Value from sqlstatic int
getSQLValueEx
(String trxName, String sql, Object... params) Get int Value from sqlstatic int
getSQLValueEx
(String trxName, String sql, List<Object> params) Get int value from sqlgetSQLValueObjectsEx
(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 0static String
getSQLValueString
(String trxName, String sql, int int_param1) static String
getSQLValueString
(String trxName, String sql, Object... params) Get String Value from sqlstatic String
getSQLValueString
(String trxName, String sql, List<Object> params) Get string value from sql.
Developer is recommended to callgetSQLValueStringEx(String, String, List)
instead.static String
getSQLValueStringEx
(String trxName, String sql, Object... params) Get string value from sqlstatic String
getSQLValueStringEx
(String trxName, String sql, List<Object> params) Get String Value from sqlstatic Timestamp
getSQLValueTS
(String trxName, String sql, Object... params) Get Timestamp Value from sql.
Developer is recommended to callgetSQLValueTSEx(String, String, Object...)
instead.static Timestamp
getSQLValueTS
(String trxName, String sql, List<Object> params) Get Timestamp Value from sql.
Developer is recommended to callgetSQLValueTSEx(String, String, List)
instead.static Timestamp
getSQLValueTSEx
(String trxName, String sql, Object... params) Get Timestamp Value from sqlstatic Timestamp
getSQLValueTSEx
(String trxName, String sql, List<Object> params) Get Timestamp Value from sqlstatic ValueNamePair[]
getValueNamePairs
(String sql, boolean optional, List<Object> params) Get Array of ValueNamePair items.static String
inClauseForCSV
(String columnName, String csv) Create IN clause for csv valuestatic String
inClauseForCSV
(String columnName, String csv, boolean isNotClause) Create IN clause for csv valuestatic String
intersectClauseForCSV
(String columnName, String csv) Create intersect clause for csv value (i.e columnName is an intersect with the csv value set)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)static boolean
isBuildOK
(Properties ctx) Check Build Version of Database against running clientstatic boolean
Is connected to DB.static boolean
isConnected
(boolean createNew) Deprecated, for removal: This API element is subject to removal in a future version.static boolean
isDatabaseOK
(Properties ctx) Deprecated, for removal: This API element is subject to removal in a future version.static boolean
Is DB support generate_uuid functionstatic boolean
isOracle()
Is connected to Oracle DB ?static boolean
Is connected to PostgreSQL DB ?static boolean
Deprecated, for removal: This API element is subject to removal in a future version.static boolean
Deprecated, for removal: This API element is subject to removal in a future version.static boolean
isSelectStatement
(String sql) Is sql a SELECT statementstatic boolean
Delegate toisSOTrx(String, String, int)
with -1 for windowNo parameter.static boolean
Is Sales Order Trx.
Assumes Sales Order.static boolean
isTableOrViewExists
(String tableName) Is table or view existsstatic CallableStatement
prepareCall
(String sql) Create callable statement proxystatic CallableStatement
prepareCall
(String SQL, int resultSetConcurrency, String trxName) Create callable statement proxystatic PreparedStatement
prepareNormalReadReplicaStatement
(String sql, String trxName) Create Read Replica Prepared Statement proxystatic CPreparedStatement
prepareStatement
(String sql) Deprecated.static CPreparedStatement
prepareStatement
(String sql, int resultSetType, int resultSetConcurrency) Deprecated.static CPreparedStatement
prepareStatement
(String sql, int resultSetType, int resultSetConcurrency, String trxName) Create prepare Statement proxystatic CPreparedStatement
prepareStatement
(String sql, String trxName) Create prepare Statement proxystatic CPreparedStatement
prepareStatement
(Connection connection, String sql) Create prepare Statement proxystatic CPreparedStatement
prepareStatement
(Connection connection, String sql, int resultSetType, int resultSetConcurrency) Create prepare Statement proxystatic void
printWarning
(String comment, SQLWarning warning) Print SQL Warnings.static boolean
Rollback transactionstatic void
Set active connection profilestatic void
setParameter
(PreparedStatement pstmt, int index, Object param) Set PreparedStatement's parameter.
Similar with callingpstmt.setObject(index, param)
static void
setParameters
(PreparedStatement stmt, Object[] params) Set parameters for given statementstatic void
setParameters
(PreparedStatement stmt, List<?> params) Set parameters for given statementstatic String
subsetClauseForCSV
(String columnName, String csv) Create subset clause for csv value (i.e columnName is a subset of the csv value set)static String
Create SQL for formatted Date, Numberstatic String
Create SQL TO Date String from Timestampstatic String
Create SQL TO Date String from Timestampstatic String
Return string as JSON object for INSERT statements with correct precisionstatic String
TO_NUMBER
(BigDecimal number, int displayType) Return number as string for INSERT statements with correct precisionstatic String
Package Strings for SQL command in quotesstatic String
Package Strings for SQL command in quotes.static void
Update Mail Settings for System Client and System User (idempiereEnv.properties)
-
Field Details
-
SQLSTATEMENT_SEPARATOR
SQL Statement Separator "; "- See Also:
-
-
Constructor Details
-
DB
public DB()
-
-
Method Details
-
afterMigration
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
Set active connection profile- Parameters:
cc
- connection profile
-
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, for removal: This API element is subject to removal in a future version.Replace byisConnected()
Is there a connection to the database ?- Parameters:
createNew
- ignore- Returns:
- true, if connected to database
-
getConnection
Get auto commit connection from connection pool.- Returns:
Connection
-
getConnection
Get auto or not auto commit connection from connection pool.
Usually, developer should use @getConnection()
instead to get auto commit connection and useTrx
to works with not autoCommit connection.- Parameters:
autoCommit
-- Returns:
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, 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, for removal: This API element is subject to removal in a future version.Replace by @getConnection()
. UseTrx
instead for readonly transaction. Return read committed, read/only from pool.- Returns:
- Connection (r/o)
-
getReportingConnectionRO
Return a replica connection if possible, otherwise from pool.- Returns:
- Connection (r/o)
-
createConnection
Create new Connection.
The connection must be closed explicitly by the caller.
Usually, developer should not call this directly.- Parameters:
autoCommit
- auto committrxLevel
- - 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 bycreateConnection(boolean, int)
. UseTrx
instead for readonly transaction. Create new Connection. The connection must be closed explicitly by the application.- Parameters:
autoCommit
- auto commitreadOnly
- ignoretrxLevel
- - Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_REPEATABLE_READ, or Connection.TRANSACTION_READ_COMMITTED.- Returns:
- Connection connection
-
getDatabase
Get Database Adapter.
Access to database specific functionality.- Returns:
- iDempiere Database Adapter
-
getDatabase
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
Get Database Info- Returns:
- info
-
isDatabaseOK
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
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
Create callable statement proxy- Parameters:
sql
-- Returns:
- Callable Statement
-
prepareCall
Create callable statement proxy- Parameters:
SQL
-resultSetConcurrency
-trxName
-- Returns:
- Callable Statement
-
prepareStatement
Deprecated.Prepare Statement- Parameters:
sql
-- Returns:
- Prepared Statement
-
prepareStatement
Create prepare Statement proxy- Parameters:
sql
-trxName
- transaction- Returns:
- Prepared Statement
-
prepareStatement
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_SENSITIVEresultSetConcurrency
- - 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_SENSITIVEresultSetConcurrency
- - ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLEtrxName
- 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 statementresultSetType
- - ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_SENSITIVEresultSetConcurrency
- - ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE- Returns:
- Prepared Statement r/o or r/w depending on concur
-
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_SENSITIVEresultSetConcurrency
- - ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLEtrxName
- transaction name- Returns:
- Statement
-
setParameters
Set parameters for given statement- Parameters:
stmt
- statementsparams
- parameters array; if null or empty array, no parameters are set- Throws:
SQLException
-
setParameters
Set parameters for given statement- Parameters:
stmt
- statementsparams
- 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 callingpstmt.setObject(index, param)
- Parameters:
pstmt
-index
-param
-- Throws:
SQLException
-
executeUpdate
Deprecated.Execute Update. saves "DBExecuteError" in Log- Parameters:
sql
-- Returns:
- number of rows updated or -1 if error
-
executeUpdate
Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, String)
instead.- Parameters:
sql
-trxName
- optional transaction name- Returns:
- number of rows updated or -1 if error
-
executeUpdate
Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, String, int)
instead.- Parameters:
sql
-trxName
- optional transaction nametimeOut
- optional timeout parameter- Returns:
- number of rows updated or -1 if error
-
executeUpdate
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
Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, String)
instead.- Parameters:
sql
-ignoreError
- if true, no execution error is reportedtrxName
- transaction- Returns:
- number of rows updated or -1 if error
-
executeUpdate
Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, String, int)
instead.- Parameters:
sql
-ignoreError
- if true, no execution error is reportedtrxName
- transactiontimeOut
- optional timeOut parameter- Returns:
- number of rows updated or -1 if error
-
executeUpdate
Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, Object[], String)
instead.- Parameters:
sql
-param
- int paramtrxName
- transaction- Returns:
- number of rows updated or -1 if error
-
executeUpdate
Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, Object[], String, int)
instead.- Parameters:
sql
-param
- int paramtrxName
- transactiontimeOut
- optional timeOut parameter- Returns:
- number of rows updated or -1 if error
-
executeUpdate
Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, Object[], String)
instead.- Parameters:
sql
-param
- int parameterignoreError
- if true, no execution error is reportedtrxName
- 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 callexecuteUpdateEx(String, Object[], String, int)
instead.- Parameters:
sql
-param
- int parameterignoreError
- if true, no execution error is reportedtrxName
- transactiontimeOut
- optional timeOut parameter- Returns:
- number of rows updated or -1 if error
-
executeUpdate
Execute Update.
Saves "DBExecuteError" in Log.
Developer is recommended to callexecuteUpdateEx(String, Object[], String)
instead.- Parameters:
sql
-params
- array of parametersignoreError
- if true, no execution error is reportedtrxName
- 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 callexecuteUpdateEx(String, Object[], String, int)
instead.- Parameters:
sql
-params
- array of parametersignoreError
- if true, no execution error is reportedtrxName
- optional transaction nametimeOut
- optional timeOut parameter- Returns:
- number of rows updated or -1 if error
-
executeUpdateEx
Execute update and throw DBException if there are errors.- Parameters:
sql
-params
- statement parameterstrxName
- 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 parameterstrxName
- transactiontimeOut
- optional timeOut parameter- Returns:
- number of rows updated
- Throws:
DBException
-
executeUpdateMultiple
Execute multiple Update statements.
Saves (last) "DBExecuteError" in Log.- Parameters:
sql
- multiple sql statements separated by "; " SQLSTATEMENT_SEPARATORignoreError
- if true, no execution error is reportedtrxName
- optional transaction name- Returns:
- number of rows updated or -1 if error
-
executeUpdateEx
Execute update and throw DBException if there are errors.- Parameters:
sql
-trxName
-- Throws:
DBException
-
executeUpdateEx
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 exceptiontrxName
- transaction name- Returns:
- true if not needed (trxName is null) or success
- Throws:
SQLException
IllegalStateException
-
rollback
Rollback transaction- Parameters:
throwException
- if true, re-throws exceptiontrxName
- transaction name- Returns:
- true if not needed (trxName is null) or success
- Throws:
SQLException
-
getRowSet
Get Row Set.
When a Rowset is closed, it also closes the underlying connection.- Parameters:
sql
-- Returns:
- row set or null
-
getSQLValueEx
Get int Value from sql- Parameters:
trxName
- optional transaction namesql
-params
- array of parameters- Returns:
- first value or -1 if not found
- Throws:
DBException
- if there is any SQLException
-
getSQLValueEx
Get int value from sql- Parameters:
trxName
- optional transaction namesql
-params
- collection of parameters- Returns:
- first value or -1
- Throws:
DBException
- if there is any SQLException
-
getSQLValue
Get int Value from sql.
Developer is recommended to callgetSQLValueEx(String, String, Object...)
instead.- Parameters:
trxName
- optional transaction namesql
-params
- array of parameters- Returns:
- first value or -1 if not found or error
-
getSQLValue
Get int value from sql.
Developer is recommended to callgetSQLValueEx(String, String, List)
instead.- Parameters:
trxName
- optional transaction namesql
-params
- collection of parameters- Returns:
- first value or null
-
getSQLValueStringEx
Get string value from sql- Parameters:
trxName
- optional transaction namesql
-params
- array of parameters- Returns:
- first value or null
- Throws:
DBException
- if there is any SQLException
-
getSQLValueStringEx
Get String Value from sql- Parameters:
trxName
- optional transaction namesql
-params
- collection of parameters- Returns:
- first value or null
- Throws:
DBException
- if there is any SQLException
-
getSQLValueString
Get String Value from sql- Parameters:
trxName
- optional transaction namesql
-params
- array of parameters- Returns:
- first value or null
-
getSQLValueString
Get string value from sql.
Developer is recommended to callgetSQLValueStringEx(String, String, List)
instead.- Parameters:
trxName
- optional transaction namesql
-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 namesql
-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 namesql
-params
- collection of parameters- Returns:
- first value or null if not found
- Throws:
DBException
- if there is any SQLException
-
getSQLValueBD
Get BigDecimal Value from sql.
Developer is recommended to callgetSQLValueBDEx(String, String, Object...)
instead.- Parameters:
trxName
- optional transaction namesql
-params
- array of parameters- Returns:
- first value or null
-
getSQLValueBD
Get BigDecimal Value from sql.
Developer is recommended to callgetSQLValueBDEx(String, String, List)
instead.- Parameters:
trxName
- optional transaction namesql
-params
- collection of parameters- Returns:
- first value or null
-
getSQLValueTSEx
Get Timestamp Value from sql- Parameters:
trxName
- optional transaction namesql
-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 namesql
-params
- collection of parameters- Returns:
- first value or null if not found
- Throws:
DBException
- if there is any SQLException
-
getSQLValueTS
Get Timestamp Value from sql.
Developer is recommended to callgetSQLValueTSEx(String, String, Object...)
instead.- Parameters:
trxName
- optional transaction namesql
-params
- array of parameters- Returns:
- first value or null
-
getSQLValueTS
Get Timestamp Value from sql.
Developer is recommended to callgetSQLValueTSEx(String, String, List)
instead.- Parameters:
trxName
- optional transaction namesql
-params
- collection of parameters- Returns:
- first value or null
-
getKeyNamePairs
Get Array of Key Name Pairs- Parameters:
sql
- select with id / name as first / second columnoptional
- if true (-1,"") is added- Returns:
- array of
KeyNamePair
- See Also:
-
getKeyNamePairs
Get Array of Key Name Pairs- Parameters:
sql
- select with id / name as first / second columnoptional
- if true (-1,"") is addedparams
- 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 columnoptional
- if true (-1,"") is addedparams
- query parameters- Returns:
- Array of Key Name Pairs
-
getIDsEx
Get Array of IDs- Parameters:
trxName
-sql
- select with id as first columnparams
- query parameters- Throws:
DBException
- if there is any SQLException
-
isSOTrx
Is Sales Order Trx.
Assumes Sales Order. Query IsSOTrx value of table with where clause- Parameters:
TableName
- tablewhereClause
- where clausewindowNo
-- Returns:
- true (default) or false if tested that not SO
-
isSOTrx
Delegate toisSOTrx(String, String, int)
with -1 for windowNo parameter.- Parameters:
TableName
-whereClause
-- Returns:
- true (default) or false if tested that not SO
-
getNextID
Get next id for table- Parameters:
ctx
- clientTableName
- table nametrxName
- optional transaction name- Returns:
- next id no
-
getNextID
Get next id for table- Parameters:
AD_Client_ID
- clientTableName
- table nametrxName
- optional Transaction Name- Returns:
- next id no
-
getDocumentNo
Deprecated.Get Document No based on Document Type (backward compatibility)- Parameters:
C_DocType_ID
- document typetrxName
- optional Transaction Name- Returns:
- document no or null
-
getDocumentNo
Get Document No based on Document Type- Parameters:
C_DocType_ID
- document typetrxName
- optional Transaction Namedefinite
- asking for a definitive or temporary sequence- Returns:
- document no or null
-
getDocumentNo
Get Document No based on Document Type- Parameters:
C_DocType_ID
- document typetrxName
- optional Transaction Namedefinite
- asking for a definitive or temporary sequencepo
- PO- Returns:
- document no or null
-
getDocumentNo
Get Document No for table- Parameters:
AD_Client_ID
- clientTableName
- table nametrxName
- optional Transaction Name- Returns:
- document no or null
-
getDocumentNo
Get Document No for table- Parameters:
AD_Client_ID
- clientTableName
- table nametrxName
- optional Transaction Namepo
-- 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
- contextWindowNo
- windowTableName
- tableonlyDocType
- Do not search for document no based on TableNametrxName
- optional Transaction Name- Returns:
- DocumentNo or null, if no doc number defined
-
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, 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
Print SQL Warnings.
Usage: DB.printWarning("comment", rs.getWarnings());- Parameters:
comment
- commentwarning
- warning
-
TO_DATE
Create SQL TO Date String from Timestamp- Parameters:
time
- Date to be converteddayOnly
- 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
Create SQL TO Date String from Timestamp- Parameters:
day
- day time- Returns:
- TO_DATE String (day only)
-
TO_CHAR
Create SQL for formatted Date, Number- Parameters:
columnName
- the column name in the SQLdisplayType
- Display TypeAD_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
Return number as string for INSERT statements with correct precision- Parameters:
number
- numberdisplayType
- display Type- Returns:
- number as string
-
TO_STRING
Package Strings for SQL command in quotes- Parameters:
txt
- String with text- Returns:
- escaped string for sql statement (NULL if null)
-
TO_STRING
Package Strings for SQL command in quotes.- include in ' (single quotes) - replace ' with ''
- Parameters:
txt
- String with textmaxLength
- Maximum Length of content or 0 to ignore- Returns:
- escaped string for sql statement (NULL if null)
-
TO_JSON
Return string as JSON object for INSERT statements with correct precision- Parameters:
value
-- Returns:
- value as json
-
getJSONCast
- Returns:
- string with right casting for JSON inserts
-
close
Convenient method to close result set- Parameters:
rs
-
-
close
Convenient method to close statement- Parameters:
st
-
-
close
Convenient method to close result set and statement- Parameters:
rs
- result setst
- statement- See Also:
-
close
Convenient method to close aPOResultSet
- Parameters:
rs
- result set- See Also:
-
getSQLException
Try to get the SQLException from Exception- Parameters:
e
- Exception- Returns:
- SQLException if found or provided exception elsewhere
-
getSQLValue
-
getSQLValue
-
getSQLValue
-
getSQLValue
-
getSQLValue
-
getSQLValueString
-
getSQLValueBD
-
getValueNamePairs
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
- ifValueNamePair.EMPTY
is addedparams
- query parameters- Returns:
- array of
ValueNamePair
or empty array - Throws:
DBException
- if there is any SQLException
-
getKeyNamePairs
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
- ifValueNamePair.EMPTY
is addedparams
- 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 KeyNamePairtrxName
-
-
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
Is table or view exists- Parameters:
tableName
-- Returns:
- true if table or view with name=tableName exists in db
-
getSQLValueObjectsEx
Get a list of objects from sql (one per each column in the select clause), column indexing starts with 0- Parameters:
trxName
- optional transaction namesql
-params
- array of parameters- Returns:
- null if not found
- Throws:
DBException
- if there is any SQLException
-
getSQLArrayObjectsEx
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 namesql
-params
- array of parameters- Returns:
- null if not found
- Throws:
DBException
- if there is any SQLException
-
prepareNormalReadReplicaStatement
Create Read Replica Prepared Statement proxy- Parameters:
sql
-trxName
- transaction- Returns:
- Prepared Statement (from replica if possible, otherwise normal statement)
-
inClauseForCSV
Create IN clause for csv value- Parameters:
columnName
-csv
- comma separated value- Returns:
- IN clause
-
inClauseForCSV
Create IN clause for csv value- Parameters:
columnName
-csv
- comma separated valueisNotClause
- true to append NOT before IN- Returns:
- IN clause
-
subsetClauseForCSV
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
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
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
Is sql a SELECT statement- Parameters:
sql
-- Returns:
- true if it is a SELECT statement
-