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.

Warning : When working with SQL query, this class does not check tables, fields, columns and where clause parameter against SQL injection. Make sure to only use validated parameter values, i.e. do not use values entered by end user, but only values coming from configuration files or authorized administrators.

Since:

jcms-5.5.0

Version:

$Revision: 121495 $

Author:

Olivier Dedieu

Field Summary

Fields

Modifier and Type	Field and Description
static java.lang.String	REVISION

Constructor Summary

Constructors

Constructor and Description
DBUtil()

Method Summary

Modifier and Type	Method and Description
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.
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 java.sql.ResultSetMetaData	getColumnsMetaData(java.lang.String dataSource, java.lang.String tables, java.lang.String[] columns) Extract the metadata of the given columns (or all columns if the parameter is null) for the given tables.
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 DatabaseInfo	getDatabaseInfo(java.lang.String dataSource) Returns the Database info for the given data source.
static DatabaseInfo	getDatabaseInfo(java.lang.String url, java.lang.String user, java.lang.String password) Returns the Database info for the given JDBC parameters.
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.
static void	initDataSourceMap(java.util.Set<java.lang.String> includedDataSourceSet, java.util.Set<java.lang.String> excludedDataSourceSet) This method must be called before any other methods accessing to the dataSourceMap.

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)

initDataSourceMap

public static void initDataSourceMap(java.util.Set<java.lang.String> includedDataSourceSet,
                                     java.util.Set<java.lang.String> excludedDataSourceSet)

This method must be called before any other methods accessing to the dataSourceMap.

Parameters:

includedDataSourceSet - if not empty, only dataSource declared in this set will be used.

excludedDataSourceSet - if not empty, any dataSource declared in this set will be excluded.

Since:

jcms-7.1.3

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 - if an error occurs

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 - if an error occurs

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 - if an error occurs

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 - if an error occurs

getColumnsMetaData

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

Extract the metadata of the given columns (or all columns if the parameter is null) for the given tables.

Parameters:

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

tables - the table names

columns - the column names. If null, all columns of the given tables

Returns:

ResultSetMetaData with information about columns

Throws:

java.sql.SQLException - if an error occurs

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 - if an error occurs

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 - if an error occurs

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

getDatabaseInfo

public static DatabaseInfo getDatabaseInfo(java.lang.String dataSource)

Returns the Database info for the given data source.

Parameters:

dataSource - the data source.

Returns:

the Database info for the given data source.

Since:

jcms-10.0.2

getDatabaseInfo

public static DatabaseInfo getDatabaseInfo(java.lang.String url,
                                           java.lang.String user,
                                           java.lang.String password)

Returns the Database info for the given JDBC parameters.

Parameters:

url - the JDBC url

user - the JDBC user

password - the JDBC password

Returns:

the Database info for the given JDBC parameters.

Since:

jcms-10.0.2