com.jalios.util
Class DBUtil

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

public class DBUtil
extends 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 String REVISION
           
 
Constructor Summary
DBUtil()
           
 
Method Summary
static void addDataSource(String name, DataSource dataSource)
          Adds the given data source.
static String buildSelectQuery(String dataSource, String select, String from, String orderby, String[] whereParams, String[] whereValues, boolean and)
          This convenient method build an SQL Select query from the given parameters.
static boolean checkDataSource(String dataSource)
          Returns true if the given dataSource is available (i.e.
static SQLQueryResult executeQuery(String dataSource, String sqlQuery, int maxRows)
          This convient method performs a query and return a SQLQueryResult
static void executeUpdate(String dataSource, String sql)
          This convenient method executes an update (insert/update/delete) in the given dataSource.
static boolean[] getColumnsNumericsMetaData(String dataSource, String tables, String[] columns)
          This method do a small request to the given database to extract metadata of given columns.
static Connection getConnection(String dataSource)
          Attempts to establish a connection with a given data source.
static Connection getConnection(String dataSource, String username, String password)
          Attempts to establish a connection with a given data source.
static DataSource getDataSource(String dataSource)
          Lookup for the given data source.
static Map<String,DataSource> getDataSourceMap()
          Lookup for all "java:com/env/jdbc/*" data sources declared in the AppServer.
static Object getFirstValue(Connection conn, 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 Object getFirstValue(String dataSource, 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 Object getFirstValue(String dataSource, String select, String from, String orderby, String[] whereParams, 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 String REVISION
See Also:
Constant Field Values
Constructor Detail

DBUtil

public DBUtil()
Method Detail

getDataSourceMap

public static Map<String,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 DataSource getDataSource(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 Connection getConnection(String dataSource)
                                throws 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:
SQLException
Since:
jcms-5.5.0

getConnection

public static Connection getConnection(String dataSource,
                                       String username,
                                       String password)
                                throws 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:
SQLException
Since:
jcms-5.5.0

buildSelectQuery

public static String buildSelectQuery(String dataSource,
                                      String select,
                                      String from,
                                      String orderby,
                                      String[] whereParams,
                                      String[] whereValues,
                                      boolean and)
                               throws 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:
SQLException
Since:
jcms-5.5.0

getColumnsNumericsMetaData

public static boolean[] getColumnsNumericsMetaData(String dataSource,
                                                   String tables,
                                                   String[] columns)
                                            throws 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:
SQLException

getFirstValue

public static Object getFirstValue(String dataSource,
                                   String select,
                                   String from,
                                   String orderby,
                                   String[] whereParams,
                                   String[] whereValues,
                                   boolean and)
                            throws 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:
SQLException
Since:
jcms-5.5.0

getFirstValue

public static Object getFirstValue(String dataSource,
                                   String sqlQuery)
                            throws 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:
SQLException
Since:
jcms-5.5.0

getFirstValue

public static Object getFirstValue(Connection conn,
                                   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(String dataSource,
                                          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(String dataSource,
                                 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(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(String name,
                                 DataSource dataSource)
Adds the given data source.

Parameters:
name - the name of the data source.
dataSource - the data source.
Since:
jcms-6.0.0


Copyright © 2001-2010 Jalios SA. All Rights Reserved.