com.jalios.util
Class DBUtil

java.lang.Object
  com.jalios.util.DBUtil

public class DBUtil
extends java.lang.Object

This class provides a set of static methods which perform various utility operations on databases.

Since:

jcms-5.5.0

Version:

$Revision: 29010 $

Author:

Olivier Dedieu

Field Summary

static java.lang.String

REVISION

Constructor Summary

DBUtil()

Method Summary

static void	addDataSource(java.lang.String name, javax.sql.DataSource dataSource) Adds the given data source.
static java.lang.String	buildSelectQuery(java.lang.String dataSource, java.lang.String select, java.lang.String from, java.lang.String orderby, java.lang.String[] whereParams, java.lang.String[] whereValues, boolean and) This convenient method build an SQL Select query from the given parameters.
static boolean	checkDataSource(java.lang.String dataSource) Returns true if the given dataSource is available (i.e. a connection can be open on this dataSource).
static SQLQueryResult	executeQuery(java.lang.String dataSource, java.lang.String sqlQuery, int maxRows) This convient method performs a query and return a SQLQueryResult
static void	executeUpdate(java.lang.String dataSource, java.lang.String sql) This convenient method executes an update (insert/update/delete) in the given dataSource.
static boolean[]	getColumnsNumericsMetaData(java.lang.String dataSource, java.lang.String tables, java.lang.String[] columns) This method do a small request to the given database to extract metadata of given columns.
static java.sql.Connection	getConnection(java.lang.String dataSource) Attempts to establish a connection with a given data source.
static java.sql.Connection	getConnection(java.lang.String dataSource, java.lang.String username, java.lang.String password) Attempts to establish a connection with a given data source.
static javax.sql.DataSource	getDataSource(java.lang.String dataSource) Lookup for the given data source.
static java.util.Map<java.lang.String,javax.sql.DataSource>	getDataSourceMap() Lookup for all "java:com/env/jdbc/*" data sources declared in the AppServer.
static java.lang.Object	getFirstValue(java.sql.Connection conn, java.lang.String sqlQuery) This convenient method performs a query then it returns the value of the first column of the first row of the result set
static java.lang.Object	getFirstValue(java.lang.String dataSource, java.lang.String sqlQuery) This convenient method performs a query then it returns the value of the first column of the first row of the result set.
static java.lang.Object	getFirstValue(java.lang.String dataSource, java.lang.String select, java.lang.String from, java.lang.String orderby, java.lang.String[] whereParams, java.lang.String[] whereValues, boolean and) This convenient method performs a query then it returns the value of the first column of the first row of the result set.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

REVISION

public static final java.lang.String REVISION

See Also:

Constant Field Values

Constructor Detail

DBUtil

public DBUtil()

Method Detail

getDataSourceMap

public static java.util.Map<java.lang.String,javax.sql.DataSource> getDataSourceMap()

Lookup for all "java:com/env/jdbc/*" data sources declared in the AppServer. Returns a map of name / DataSource. For instance if two data sources are declared in the AppServer as "jdbc/db1" and "jdbc/db2", this method returns a map wich contains 2 entries : ("jdbc/db1", DataSource for db1) and ("jdbc/db2", DataSource for db2).

Returns:

a map of name / DataSource

Since:

jcms-5.5.0

See Also:

getDataSource(String)

getDataSource

public static javax.sql.DataSource getDataSource(java.lang.String dataSource)

Lookup for the given data source.

Parameters:

dataSource - the name of the data source (eg. "jdbc/TestDB")

Returns:

the DataSource bound to the given name

Since:

jcms-5.5.0

See Also:

getDataSourceMap()

getConnection

public static java.sql.Connection getConnection(java.lang.String dataSource)
                                         throws java.sql.SQLException

Attempts to establish a connection with a given data source.

Parameters:

dataSource - the name of the data source (eg. "jdbc/TestDB")

Returns:

a connection to the data source

Throws:

java.sql.SQLException

Since:

jcms-5.5.0

getConnection

public static java.sql.Connection getConnection(java.lang.String dataSource,
                                                java.lang.String username,
                                                java.lang.String password)
                                         throws java.sql.SQLException

Attempts to establish a connection with a given data source.

Parameters:

dataSource - the name of the data source (eg. "jdbc/TestDB")

username - the database user on whose behalf the connection is being made

password - the user's password

Returns:

a connection to the data source

Throws:

java.sql.SQLException

Since:

jcms-5.5.0

buildSelectQuery

public static java.lang.String buildSelectQuery(java.lang.String dataSource,
                                                java.lang.String select,
                                                java.lang.String from,
                                                java.lang.String orderby,
                                                java.lang.String[] whereParams,
                                                java.lang.String[] whereValues,
                                                boolean and)
                                         throws java.sql.SQLException

This convenient method build an SQL Select query from the given parameters. It will use "'" parameter for String value and none for numerical values.

Parameters:

dataSource - the name of the data source (eg. "jdbc/TestDB")

select - the table filed to select or *

from - the table name to work with.

orderby - the order by parameter

whereParams - an array of parameters

whereValues - an array of parameters value

and - should be 'AND' or 'OR' between parameters

Returns:

String a datasource compatible SQL Query

Throws:

java.sql.SQLException

Since:

jcms-5.5.0

getColumnsNumericsMetaData

public static boolean[] getColumnsNumericsMetaData(java.lang.String dataSource,
                                                   java.lang.String tables,
                                                   java.lang.String[] columns)
                                            throws java.sql.SQLException

This method do a small request to the given database to extract metadata of given columns. It return an array of boolean with value true if the column is "numeric" otherwise false.

"numeric" means Types.BIGINT, Types.INTEGER, Types.SMALLINT, Types.NUMERIC

Parameters:

dataSource - the name of the data source (eg. "jdbc/TestDB")

tables - the table names

columns - the column names

Returns:

boolean[] an array of boolean

Throws:

java.sql.SQLException

getFirstValue

public static java.lang.Object getFirstValue(java.lang.String dataSource,
                                             java.lang.String select,
                                             java.lang.String from,
                                             java.lang.String orderby,
                                             java.lang.String[] whereParams,
                                             java.lang.String[] whereValues,
                                             boolean and)
                                      throws java.sql.SQLException

This convenient method performs a query then it returns the value of the first column of the first row of the result set.

Parameters:

dataSource - the name of the data source (eg. "jdbc/TestDB")

select - the table filed to select or *

from - the table name to work with.

orderby - the order by parameter

whereParams - an array of parameters

whereValues - an array of parameters value

and - should be 'AND' or 'OR' between parameters

Returns:

the first value or null if no result

Throws:

java.sql.SQLException

Since:

jcms-5.5.0

getFirstValue

public static java.lang.Object getFirstValue(java.lang.String dataSource,
                                             java.lang.String sqlQuery)
                                      throws java.sql.SQLException

This convenient method performs a query then it returns the value of the first column of the first row of the result set.

Parameters:

dataSource - the name of the data source (eg. "jdbc/TestDB")

sqlQuery - the SQL query to be performed

Returns:

the first value or null if no result

Throws:

java.sql.SQLException

Since:

jcms-5.5.0

getFirstValue

public static java.lang.Object getFirstValue(java.sql.Connection conn,
                                             java.lang.String sqlQuery)

This convenient method performs a query then it returns the value of the first column of the first row of the result set. *

Parameters:

conn - the JDBC connection

sqlQuery - the SQL query to be performed

Returns:

the first value or null if no result

Since:

jcms-5.5.0

executeQuery

public static SQLQueryResult executeQuery(java.lang.String dataSource,
                                          java.lang.String sqlQuery,
                                          int maxRows)

This convient method performs a query and return a SQLQueryResult

Parameters:

dataSource - the name of the data source (eg. "jdbc/TestDB")

sqlQuery - the SQL (SELECT) query

maxRows - the max rows limit; zero means there is no limit

Returns:

a SQLQueryResult

Since:

jcms-5.5.0

executeUpdate

public static void executeUpdate(java.lang.String dataSource,
                                 java.lang.String sql)

This convenient method executes an update (insert/update/delete) in the given dataSource.

Parameters:

dataSource - the data source

sql - update query

Since:

jcms-6.0.0

checkDataSource

public static boolean checkDataSource(java.lang.String dataSource)

Returns true if the given dataSource is available (i.e. a connection can be open on this dataSource).

Parameters:

dataSource - the data source.

Returns:

true if the given dataSource is available.

Since:

jcms-6.0.0

addDataSource

public static void addDataSource(java.lang.String name,
                                 javax.sql.DataSource dataSource)

Adds the given data source.

Parameters:

name - the name of the data source.

dataSource - the data source.

Since:

jcms-6.0.0