```
public final class DB
extends Object
```
General Database 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

Fields
Modifier and Type Field Description

static String SQLSTATEMENT_SEPARATOR
SQL Statement Separator "; "

Constructor Summary

Constructors
Constructor Description

DB()

Method Summary

All Methods Static Methods Concrete Methods Deprecated Methods
Modifier and Type	Method	Description
`static boolean`	`afterMigration(Properties ctx)`	Check need for post Upgrade
`static void`	`close(ResultSet rs)`	convenient method to close result set
`static void`	`close(ResultSet rs, Statement st)`	convenient method to close result set and statement
`static void`	`close(Statement st)`	convenient method to close statement
`static void`	`close(POResultSet<?> rs)`	convenient method to close a `POResultSet`
`static void`	`closeTarget()`	Close Target
`static boolean`	`commit(boolean throwException, String trxName)`	Commit - commit on RW connection.
`static boolean`	`connect()`	Connect to database and initialise all connections.
`static Connection`	`createConnection(boolean autoCommit, boolean readOnly, int trxLevel)`	Create new Connection.
`static Connection`	`createConnection(boolean autoCommit, int trxLevel)`	Create new Connection.
`static Statement`	`createStatement()`	Create Read Only Statement
`static Statement`	`createStatement(int resultSetType, int resultSetConcurrency, String trxName)`	Create Statement.
`static void`	`createT_Selection(int AD_PInstance_ID, Collection<Integer> selection, String trxName)`	Create persistent selection in T_Selection table remain this function for backward compatibility.
`static void`	`createT_SelectionNew(int AD_PInstance_ID, Collection<KeyNamePair> saveKeys, String trxName)`	Create persistent selection in T_Selection table saveKeys is map with key is rowID, value is list value of all viewID viewIDIndex is index of viewID need save.
`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.
`static int`	`executeUpdate(String sql, boolean ignoreError, String trxName, int timeOut)`	Execute Update.
`static int`	`executeUpdate(String sql, int param, boolean ignoreError, String trxName)`	Execute Update.
`static int`	`executeUpdate(String sql, int param, boolean ignoreError, String trxName, int timeOut)`	Execute Update.
`static int`	`executeUpdate(String sql, int param, String trxName)`	Execute Update.
`static int`	`executeUpdate(String sql, int param, String trxName, int timeOut)`	Execute Update.
`static int`	`executeUpdate(String sql, Object[] params, boolean ignoreError, String trxName)`	Execute Update.
`static int`	`executeUpdate(String sql, Object[] params, boolean ignoreError, String trxName, int timeOut)`	Execute Update.
`static int`	`executeUpdate(String sql, String trxName)`	Execute Update.
`static int`	`executeUpdate(String sql, String trxName, int timeOut)`	Execute Update.
`static int`	`executeUpdateEx(String sql, Object[] params, String trxName)`	Execute Update and throw exception.
`static int`	`executeUpdateEx(String sql, Object[] params, String trxName, int timeOut)`	Execute Update and throw exception.
`static int`	`executeUpdateEx(String sql, String trxName)`	Execute Update and throw exception.
`static int`	`executeUpdateEx(String sql, String trxName, int timeOut)`	Execute Update and throw exception.
`static int`	`executeUpdateMultiple(String sql, boolean ignoreError, String trxName)`	Execute multiple Update statements.
`static Connection`	`getConnectionID()`	Return everytime a new r/w no AutoCommit, Serializable connection.
`static Connection`	`getConnectionRO()`	Return read committed, read/only from pool.
`static Connection`	`getConnectionRW()`
`static Connection`	`getConnectionRW(boolean createNew)`	Return (pooled) r/w AutoCommit, Serializable connection.
`static AdempiereDatabase`	`getDatabase()`	Get Database Driver.
`static AdempiereDatabase`	`getDatabase(String URL)`	Get Database Driver.
`static String`	`getDatabaseInfo()`	Get Database Info
`static 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 Type
`static String`	`getDocumentNo(int C_DocType_ID, String trxName, boolean definite, PO po)`	Get Document No based on Document Type
`static String`	`getDocumentNo(int AD_Client_ID, String TableName, String trxName)`	Get Document No from table
`static String`	`getDocumentNo(int AD_Client_ID, String TableName, String trxName, PO po)`	Get Document No from table
`static String`	`getDocumentNo(Properties ctx, int WindowNo, String TableName, boolean onlyDocType, String trxName)`	Get Document Number for current document.
`static int[]`	`getIDsEx(String trxName, String sql, Object... params)`	Get Array of IDs
`static KeyNamePair[]`	`getKeyNamePairs(String sql, boolean optional)`	Get Array of Key Name Pairs
`static KeyNamePair[]`	`getKeyNamePairs(String sql, boolean optional, Object... params)`	Get Array of Key Name Pairs
`static 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 Pairs
`static int`	`getNextID(int AD_Client_ID, String TableName, String trxName)`	Get next number for Key column = 0 is Error.
`static int`	`getNextID(Properties ctx, String TableName, String trxName)`	Get next number for Key column = 0 is Error
`static Connection`	`getReportingConnectionRO()`	Return a replica connection if possible, otherwise read committed, read/only from pool.
`static RowSet`	`getRowSet(String sql)`	Get Row Set.
`static List<List<Object>>`	`getSQLArrayObjectsEx(String trxName, String sql, Object... params)`	Get an array of arrays of objects from sql (one per each row, and one per each column on 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
`static Exception`	`getSQLException(Exception e)`	Try to get the SQLException from Exception
`static 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
`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
`static BigDecimal`	`getSQLValueBD(String trxName, String sql, int int_param1)`
`static BigDecimal`	`getSQLValueBD(String trxName, String sql, Object... params)`	Get BigDecimal Value from sql
`static BigDecimal`	`getSQLValueBD(String trxName, String sql, List<Object> params)`	Get BigDecimal Value from sql
`static BigDecimal`	`getSQLValueBDEx(String trxName, String sql, Object... params)`	Get BigDecimal Value from sql
`static BigDecimal`	`getSQLValueBDEx(String trxName, String sql, List<Object> params)`	Get BigDecimal Value from sql
`static int`	`getSQLValueEx(String trxName, String sql, Object... params)`	Get int Value from sql
`static int`	`getSQLValueEx(String trxName, String sql, List<Object> params)`	Get String Value from sql
`static List<Object>`	`getSQLValueObjectsEx(String trxName, String sql, Object... params)`	Get an array of objects from sql (one per each column on the select clause), column indexing starts with 0
`static String`	`getSQLValueString(String trxName, String sql, int int_param1)`
`static String`	`getSQLValueString(String trxName, String sql, Object... params)`	Get String Value from sql
`static String`	`getSQLValueString(String trxName, String sql, List<Object> params)`	Get String Value from sql
`static String`	`getSQLValueStringEx(String trxName, String sql, Object... params)`	Get String Value from sql
`static String`	`getSQLValueStringEx(String trxName, String sql, List<Object> params)`	Get String Value from sql
`static Timestamp`	`getSQLValueTS(String trxName, String sql, Object... params)`	Get Timestamp Value from sql
`static Timestamp`	`getSQLValueTS(String trxName, String sql, List<Object> params)`	Get Timestamp Value from sql
`static Timestamp`	`getSQLValueTSEx(String trxName, String sql, Object... params)`	Get Timestamp Value from sql
`static Timestamp`	`getSQLValueTSEx(String trxName, String sql, List<Object> params)`	Get BigDecimal Value from sql
`static ValueNamePair[]`	`getValueNamePairs(String sql, boolean optional, List<Object> params)`	Get Array of ValueNamePair items.
`static String`	`inClauseForCSV(String columnName, String csv)`
`static String`	`intersectClauseForCSV(String columnName, String csv)`
`static boolean`	`isBuildOK(Properties ctx)`	Check Build Version of Database against running client
`static boolean`	`isConnected()`
`static boolean`	`isConnected(boolean createNew)`	Is there a connection to the database ?
`static boolean`	`isDatabaseOK(Properties ctx)`	Check database Version with Code version
`static boolean`	`isGenerateUUIDSupported()`
`static boolean`	`isOracle()`	Do we have an Oracle DB ?
`static boolean`	`isPostgreSQL()`	Do we have a Postgre DB ?
`static boolean`	`isRemoteObjects()`	Deprecated.
`static boolean`	`isRemoteProcess()`	Deprecated.
`static boolean`	`isSelectStatement(String sql)`
`static boolean`	`isSOTrx(String TableName, String whereClause)`
`static boolean`	`isSOTrx(String TableName, String whereClause, int windowNo)`	Is Sales Order Trx.
`static boolean`	`isTableOrViewExists(String tableName)`
`static void`	`main(String[] args)`	Run Post Migration manually
`static CallableStatement`	`prepareCall(String sql)`	Prepare Forward Read Only Call
`static CallableStatement`	`prepareCall(String SQL, int resultSetConcurrency, String trxName)`	Prepare Call
`static PreparedStatement`	`prepareNormalReadReplicaStatement(String sql, String trxName)`	Prepare Read Replica Statement
`static 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)`	Prepare Statement.
`static CPreparedStatement`	`prepareStatement(String sql, String trxName)`	Prepare Statement
`static void`	`printWarning(String comment, SQLWarning warning)`	Print SQL Warnings.
`static boolean`	`rollback(boolean throwException, String trxName)`	Rollback - rollback on RW connection.
`static void`	`setDBTarget(CConnection cc)`	Set connection
`static void`	`setParameter(PreparedStatement pstmt, int index, Object param)`	Set PreparedStatement's parameter.
`static void`	`setParameters(PreparedStatement stmt, Object[] params)`	Set parameters for given statement
`static void`	`setParameters(PreparedStatement stmt, List<?> params)`	Set parameters for given statement
`static String`	`subsetClauseForCSV(String columnName, String csv)`
`static String`	`TO_CHAR(String columnName, int displayType, String AD_Language)`	Create SQL for formatted Date, Number
`static String`	`TO_DATE(Timestamp day)`	Create SQL TO Date String from Timestamp
`static String`	`TO_DATE(Timestamp time, boolean dayOnly)`	Create SQL TO Date String from Timestamp
`static String`	`TO_NUMBER(BigDecimal number, int displayType)`	Return number as string for INSERT statements with correct precision
`static String`	`TO_STRING(String txt)`	Package Strings for SQL command in quotes
`static String`	`TO_STRING(String txt, int maxLength)`	Package Strings for SQL command in quotes.
`static void`	`updateMail()`	Update Mail Settings for System Client and System User

Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail
- SQLSTATEMENT_SEPARATOR
```
public static final String SQLSTATEMENT_SEPARATOR
```
  SQL Statement Separator "; "
  
  See Also:
  
  Constant Field Values

Constructor Detail
- DB
```
public DB()
```

Method Detail

afterMigration
```
public static boolean afterMigration(Properties ctx)
```
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

setDBTarget

public static void setDBTarget(CConnection cc)

Set connection

Parameters:: cc - connection

connect
```
public static boolean connect()
```
Connect to database and initialise all connections.

Returns:

True if success, false otherwise

isConnected
```
public static boolean isConnected()
```
Returns:

true, if connected to database

isConnected
```
public static boolean isConnected(boolean createNew)
```
Is there a connection to the database ?

Parameters:

createNew - If true, try to connect it not already connected

Returns:

true, if connected to database

getConnectionRW

public static Connection getConnectionRW()

Returns:: Connection (r/w)

getConnectionRW
```
public static Connection getConnectionRW(boolean createNew)
```
Return (pooled) r/w AutoCommit, Serializable connection. For Transaction control use Trx.getConnection()

Parameters:

createNew - If true, try to create new connection if no existing connection

Returns:

Connection (r/w)

getConnectionID
```
public static Connection getConnectionID()
```
Return everytime a new r/w no AutoCommit, Serializable connection. To be used to ID

Returns:

Connection (r/w)

getConnectionRO
```
public static Connection getConnectionRO()
```
Return read committed, read/only from pool.

Returns:

Connection (r/o)

getReportingConnectionRO
```
public static Connection getReportingConnectionRO()
```
Return a replica connection if possible, otherwise read committed, read/only 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 application

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
```
public static Connection createConnection(boolean autoCommit,
                                          boolean readOnly,
                                          int trxLevel)
```
Create new Connection. The connection must be closed explicitly by the application

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

getDatabase
```
public static AdempiereDatabase getDatabase()
```
Get Database Driver. Access to database specific functionality.

Returns:

Adempiere Database Driver

getDatabase
```
public static AdempiereDatabase getDatabase(String URL)
```
Get Database Driver. Access to database specific functionality.

Parameters:

URL - JDBC connection url

Returns:

Adempiere Database Driver

isOracle
```
public static boolean isOracle()
```
Do we have an Oracle DB ?

Returns:

true if connected to Oracle

isPostgreSQL
```
public static boolean isPostgreSQL()
```
Do we have a Postgre DB ?

Returns:

true if connected to PostgreSQL

getDatabaseInfo

public static String getDatabaseInfo()

Get Database Info

Returns:: info

isDatabaseOK
```
public static boolean isDatabaseOK(Properties ctx)
```
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 Target

prepareCall
```
public static CallableStatement prepareCall(String sql)
```
Prepare Forward Read Only Call

Parameters:

sql - SQL

Returns:

Callable Statement

prepareCall

public static CallableStatement prepareCall(String SQL,
                                            int resultSetConcurrency,
                                            String trxName)

Prepare Call

Parameters:: SQL - 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)

Prepare Statement

Parameters:: sql -; trxName - transaction
Returns:: Prepared Statement

prepareStatement
```
public static CPreparedStatement prepareStatement(String sql,
                                                  int resultSetType,
                                                  int resultSetConcurrency)
```
Deprecated.

Prepare Statement.

Parameters:

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

prepareStatement

public static CPreparedStatement prepareStatement(String sql,
                                                  int resultSetType,
                                                  int resultSetConcurrency,
                                                  String trxName)

Prepare Statement.

Parameters:: 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; trxName - transaction name
Returns:: Prepared Statement r/o or r/w depending on concur

createStatement
```
public static Statement createStatement()
```
Create Read Only Statement

Returns:

Statement

createStatement
```
public static Statement createStatement(int resultSetType,
                                        int resultSetConcurrency,
                                        String trxName)
```
Create Statement.

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 - either r/w ir r/o depending on concur

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 - 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

Parameters:

sql - 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

Parameters:

sql - 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 - 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

Parameters:

sql - sql

ignoreError - if true, no execution error is reported

trxName - transaction

Returns:

number of rows updated or -1 if error