org.compiere.util
Class DB

java.lang.Object
  org.compiere.util.DB

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

Field Summary

static String

SQLSTATEMENT_SEPARATOR SQL Statement Separator "; "

Constructor Summary

DB()

Method Summary

static void	closeTarget() Close Target Connections
static boolean	commit(boolean throwException, String trxName) Commit - commit on RW 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 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, int param, boolean ignoreError, String trxName) Execute Update.
static int	executeUpdate(String sql, int param, String trxName) Execute Update.
static int	executeUpdate(String sql, Object[] params, boolean ignoreError, String trxName) Execute Update.
static int	executeUpdate(String sql, String trxName) Execute Update.
static int	executeUpdateEx(String SQL, String trxName) 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() Return (pooled) r/w AutoCommit, Serializable connection.
static CompiereDatabase	getDatabase() Get Database Driver.
static CompiereDatabase	getDatabase(String URL) Get Database Driver.
static String	getDatabaseInfo() Get Database Info
static String	getDocumentNo(Ctx ctx, int WindowNo, String TableName, boolean onlyDocType, String trxName) Get Document Number for current document.
static String	getDocumentNo(int C_DocType_ID, String trxName) Get Document No based on Document Type
static String	getDocumentNo(int AD_Client_ID, String TableName, String trxName) Get Document No from table
static KeyNamePair[]	getKeyNamePairs(String sql, boolean optional) Get Array of Key Name Pairs
static int	getNextID(Ctx ctx, String TableName, String trxName) Get next number for Key column = 0 is Error
static int	getNextID(int AD_Client_ID, String TableName, String trxName) Get next number for Key column = 0 is Error.
static RowSet	getRowSet(String sql, boolean local) Get Row Set.
static int	getSQLValue(String trxName, String sql) Get Value from sql
static int	getSQLValue(String trxName, String sql, int int_param1) Get Value from sql
static int	getSQLValue(String trxName, String sql, int int_param1, int int_param2) Get Value from sql
static int	getSQLValue(String trxName, String sql, int int_param1, int int_param2, String s_param3) Get Value from sql
static int	getSQLValue(String trxName, String sql, int int_param1, String s_param2) Get Value from sql
static int	getSQLValue(String trxName, String sql, String str_param1) Get Value from sql
static BigDecimal	getSQLValueBD(String trxName, String sql, int int_param1) Get BigDecimal Value from sql
static String	getSQLValueString(String trxName, String sql, int int_param1) Get String Value from sql
static String	getSqlWhere(String columnName, String parameter) Return where clause for column and parameter
static String	getSqlWhere(String columnName, Timestamp parameter) Return where clause for column and parameter
static boolean	isConnected() Is there a connection to the database ?
static boolean	isDatabaseOK(Ctx ctx) Check database Version with Code version
static boolean	isDB2() Do we have a DB2 DB ?
static boolean	isMSSQLServer() Do we have a MS SQL Server ?
static boolean	isOracle() Do we have an Oracle DB ?
static boolean	isOracleXE() Do we have Oracle XE ?
static boolean	isPostgreSQL() Do we have a PostgreSQL DB ?
static boolean	isRemoteObjects() Is this a remote client connection
static boolean	isRemoteProcess() Is this a remote client connection
static boolean	isSOTrx(String TableName, String whereClause) Is Sales Order Trx.
static String	NULL(String sqlClause, int dataType) Get a string representation of literal used in SQL clause
static CallableStatement	prepareCall(String RO_SQL) Prepare Forward Read Only Call
static CPreparedStatement	prepareStatement(String RO_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 RO_SQL, String trxName) Prepare Read Only 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 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
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

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

isConnected

public static boolean isConnected()

Is there a connection to the database ?

Returns:

true, if connected to database

getConnectionRW

public static Connection getConnectionRW()

Return (pooled) r/w AutoCommit, Serializable connection. For Transaction control use Trx.getConnection()

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)

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

getDatabase

public static CompiereDatabase getDatabase()

Get Database Driver. Access to database specific functionality.

Returns:

Compiere Database Driver

getDatabase

public static CompiereDatabase getDatabase(String URL)

Get Database Driver. Access to database specific functionality.

Parameters:

URL - JDBC connection url

Returns:

Compiere Database Driver

isOracle

public static boolean isOracle()

Do we have an Oracle DB ?

Returns:

true if connected to Oracle

isOracleXE

public static boolean isOracleXE()

Do we have Oracle XE ?

Returns:

true if connected to Oracle XE

isDB2

public static boolean isDB2()

Do we have a DB2 DB ?

Returns:

true if connected to DB2

isPostgreSQL

public static boolean isPostgreSQL()

Do we have a PostgreSQL DB ?

Returns:

true if connected to PostgreSQL

isMSSQLServer

public static boolean isMSSQLServer()

Do we have a MS SQL Server ?

Returns:

true if connected to MS SQL

getDatabaseInfo

public static String getDatabaseInfo()

Get Database Info

Returns:

info

isDatabaseOK

public static boolean isDatabaseOK(Ctx ctx)

Check database Version with Code version

Parameters:

ctx - context

Returns:

true if Database version (date) is the same

closeTarget

public static void closeTarget()

Close Target Connections

prepareCall

public static CallableStatement prepareCall(String RO_SQL)

Prepare Forward Read Only Call

Parameters:

RO_SQL - sql (RO)

Returns:

Callable Statement

prepareStatement

public static CPreparedStatement prepareStatement(String RO_SQL)

Deprecated.

Prepare Read Only Statement

Parameters:

RO_SQL - sql (RO)

Returns:

Prepared Statement

prepareStatement

public static CPreparedStatement prepareStatement(String RO_SQL,
                                                  String trxName)

Prepare Read Only Statement

Parameters:

RO_SQL - sql (RO)

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

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

executeUpdate

public static int executeUpdate(String sql,
                                int param,
                                String trxName)

Execute Update. saves "DBExecuteError" in Log

Parameters:

sql - sql

param - int param

trxName - transaction

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

Parameters:

sql - 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,
                                Object[] params,
                                boolean ignoreError,
                                String trxName)

Execute Update. saves "DBExecuteError" in Log

Parameters:

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

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 SQLException

Execute Update and throw exception.

Parameters:

SQL - sql

trxName - transaction

Returns:

number of rows updated or -1 if error

Throws:

SQLException

commit

public static boolean commit(boolean throwException,
                             String trxName)
                      throws SQLException

Commit - commit on RW connection. Is not required as RW connection is AutoCommit (exception: with transaction)

Parameters:

throwException - if true, re-throws exception

trxName - transaction name

Returns:

true if not needed or success

Throws:

SQLException

rollback

public static boolean rollback(boolean throwException,
                               String trxName)
                        throws SQLException

Rollback - rollback on RW connection. Is has no effect as RW connection is AutoCommit (exception: with transaction)

Parameters:

throwException - if true, re-throws exception

trxName - transaction name

Returns:

true if not needed or success

Throws:

SQLException

getRowSet

public static RowSet getRowSet(String sql,
                               boolean local)

Get Row Set. When a Rowset is closed, it also closes the underlying connection. If the created RowSet is transfered by RMI, closing it makes no difference

Parameters:

sql - sql

local - local RowSet (own connection)

Returns:

row set or null

getSQLValue

public static int getSQLValue(String trxName,
                              String sql)

Get Value from sql

Parameters:

trxName - trx

sql - sql

Returns:

first value or -1

getSQLValue

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

Get Value from sql

Parameters:

trxName - trx

sql - sql

int_param1 - parameter 1

Returns:

first value or -1

getSQLValue

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

Get Value from sql

Parameters:

trxName - trx

sql - sql

int_param1 - parameter 1

int_param2 - parameter 2

Returns:

first value or -1

getSQLValue

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

Get Value from sql

Parameters:

trxName - trx

sql - sql

str_param1 - parameter 1

Returns:

first value or -1

getSQLValue

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

Get Value from sql

Parameters:

trxName - trx

sql - sql

int_param1 - parameter 1

s_param2 - parameter 2

Returns:

first value or -1

getSQLValue

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

Get Value from sql

Parameters:

trxName - trx

sql - sql

int_param1 - parameter 1

int_param2 - parameter 2

s_param2 - parameter 3

Returns:

first value or -1

getSQLValueString

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

Get String Value from sql

Parameters:

trxName - trx

sql - sql

int_param1 - parameter 1

Returns:

first value or null

getSQLValueBD

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

Get BigDecimal Value from sql

Parameters:

trxName - trx

sql - sql

int_param1 - parameter 1

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 key name pairs

isSOTrx

public static boolean isSOTrx(String TableName,
                              String whereClause)

Is Sales Order Trx. Assumes Sales Order. Queries IsSOTrx of table with where clause

Parameters:

TableName - table

whereClause - where clause

Returns:

true (default) or false if tested that not SO

getNextID

public static int getNextID(Ctx ctx,
                            String TableName,
                            String trxName)

Get next number for Key column = 0 is Error. * @param ctx client

Parameters:

TableName - table name

trxName - optionl transaction name

Returns:

next no

getNextID

public static int getNextID(int AD_Client_ID,
                            String TableName,
                            String trxName)

Get next number for Key column = 0 is Error.

Parameters:

AD_Client_ID - client

TableName - table name

trxName - optional Transaction Name

Returns:

next no

getDocumentNo

public static String getDocumentNo(int C_DocType_ID,
                                   String trxName)

Get Document No based on Document Type

Parameters:

C_DocType_ID - document type

trxName - optional Transaction Name

Returns:

document no or null

getDocumentNo

public static String getDocumentNo(int AD_Client_ID,
                                   String TableName,
                                   String trxName)

Get Document No from table

Parameters:

AD_Client_ID - client

TableName - table name

trxName - optional Transaction Name

Returns:

document no or null

getDocumentNo

public static String getDocumentNo(Ctx ctx,
                                   int WindowNo,
                                   String TableName,
                                   boolean onlyDocType,
                                   String trxName)

Get Document Number for current document.
- first search for DocType based Document No - 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

public static boolean isRemoteObjects()

Is this a remote client connection

Returns:

true if client and RMI or Objects on Server

isRemoteProcess

public static boolean isRemoteProcess()

Is this a remote client connection

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

NULL

public static String NULL(String sqlClause,
                          int dataType)

Get a string representation of literal used in SQL clause

Parameters:

sqlClause - "S", "U", "I", "W"

dataType - java.sql.Types

Returns:

NULL or db2: nullif(x,x)

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,'9G999G990D00','NLS_NUMERIC_CHARACTERS='',.''')) or TRIM(TO_CHAR(columnName,'TM9')) depending on DisplayType and Language

See Also:

DisplayType, Env

TO_NUMBER

public 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 insert 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 insert statement (NULL if null)

getSqlWhere

public static String getSqlWhere(String columnName,
                                 String parameter)

Return where clause for column and parameter

Parameters:

columnName - column name

parameter - parameter value

Returns:

where clause with IS NULL, LIKE or = based on parameter

getSqlWhere

public static String getSqlWhere(String columnName,
                                 Timestamp parameter)

Return where clause for column and parameter

Parameters:

columnName - column name

parameter - parameter value

Returns:

where clause with IS NULL, or = based on parameter