Package com.jalios.util

Class 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
    Author:
    Olivier Dedieu

    • Constructor Summary

      Constructors 
      Constructor Description
      DBUtil()  

    • Method Summary

      All Methods Static Methods Concrete Methods 
      Modifier and Type Method Description
      static void addDataSource​(java.lang.String dsName, javax.sql.DataSource dataSource)
      Adds the given data source.
      static java.lang.String buildSelectQuery​(java.lang.String dsName, 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 dsName)
      Returns true if the given dataSource is available (i.e.
      static boolean checkDataSource​(java.lang.String dsName, boolean searchOutInitDS)
      Returns true if the given dataSource is available (i.e.
      static SQLQueryResult executeQuery​(java.lang.String dsName, java.lang.String sqlQuery, int maxRows)
      This convient method performs a query and return a SQLQueryResult
      static void executeUpdate​(java.lang.String dsName, 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 dsName, 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 dsName, 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 dsName)
      Attempts to establish a connection with a given data source.
      static java.sql.Connection getConnection​(java.lang.String dsName, java.lang.String username, java.lang.String password)
      Attempts to establish a connection with a given data source.
      static java.sql.Connection getConnection​(java.lang.String dsName, java.lang.String username, java.lang.String password, boolean searchOutInitDS)
      Attempts to establish a connection with a given data source.
      static DatabaseInfo getDatabaseInfo​(java.lang.String dsName)
      Returns the Database info for the given data source.
      static DatabaseInfo getDatabaseInfo​(java.lang.String dsName, boolean searchOutInitDS)
      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 dsName)
      Lookup for the given data source.
      static javax.sql.DataSource getDataSource​(java.lang.String dsName, boolean searchOutInitDS)
      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.lang.String dsName, 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 dsName, 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 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 void initDataSourceMap​(java.util.Set<java.lang.String> dataSourceSet)
      This method must be called before any other methods accessing to the dataSourceMap.
      static void resetC3p0Pools()  

      • Methods inherited from class java.lang.Object

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

    • 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> dataSourceSet)
        This method must be called before any other methods accessing to the dataSourceMap.
        Parameters:
        dataSourceSet - the name of data sources to initialize
        Since:
        jcms-10.0.7

      • getDataSource

        public static javax.sql.DataSource getDataSource​(java.lang.String dsName)
        Lookup for the given data source.
        Parameters:
        dsName - 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()

      • getDataSource

        public static javax.sql.DataSource getDataSource​(java.lang.String dsName,
                                                 boolean searchOutInitDS)
        Lookup for the given data source.
        Parameters:
        dsName - the name of the data source (eg. "jdbc/TestDB")
        searchOutInitDS - if true search outside the initialized Data Sources
        Returns:
        the DataSource bound to the given name
        Since:
        jcms-10.0.7
        See Also:
        getDataSourceMap()

      • getConnection

        public static java.sql.Connection getConnection​(java.lang.String dsName)
                                         throws java.sql.SQLException
        Attempts to establish a connection with a given data source.
        Parameters:
        dsName - 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 dsName,
                                                java.lang.String username,
                                                java.lang.String password)
                                         throws java.sql.SQLException
        Attempts to establish a connection with a given data source.
        Parameters:
        dsName - 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

      • getConnection

        public static java.sql.Connection getConnection​(java.lang.String dsName,
                                                java.lang.String username,
                                                java.lang.String password,
                                                boolean searchOutInitDS)
                                         throws java.sql.SQLException
        Attempts to establish a connection with a given data source.
        Parameters:
        dsName - 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
        searchOutInitDS - if true search outside the initialized Data Sources
        Returns:
        a connection to the data source
        Throws:
        java.sql.SQLException - if an error occurs
        Since:
        jcms-10.0.7

      • buildSelectQuery

        public static java.lang.String buildSelectQuery​(java.lang.String dsName,
                                                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:
        dsName - 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 dsName,
                                                   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:
        dsName - 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 dsName,
                                                            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:
        dsName - 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 dsName,
                                             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:
        dsName - 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 dsName,
                                             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:
        dsName - 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 dsName,
                                          java.lang.String sqlQuery,
                                          int maxRows)
        This convient method performs a query and return a SQLQueryResult
        Parameters:
        dsName - 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 dsName,
                                 java.lang.String sql)
        This convenient method executes an update (insert/update/delete) in the given dataSource.
        Parameters:
        dsName - the data source
        sql - update query
        Since:
        jcms-6.0.0

      • checkDataSource

        public static boolean checkDataSource​(java.lang.String dsName)
        Returns true if the given dataSource is available (i.e. a connection can be open on this dataSource).
        Parameters:
        dsName - the data source name.
        Returns:
        true if the given dataSource is available.
        Since:
        jcms-6.0.0

      • checkDataSource

        public static boolean checkDataSource​(java.lang.String dsName,
                                      boolean searchOutInitDS)
        Returns true if the given dataSource is available (i.e. a connection can be open on this dataSource).
        Parameters:
        dsName - the data source name.
        searchOutInitDS - if true search outside the initialized Data Sources
        Returns:
        true if the given dataSource is available.
        Since:
        jcms-10.0.7

      • addDataSource

        public static void addDataSource​(java.lang.String dsName,
                                 javax.sql.DataSource dataSource)
        Adds the given data source.
        Parameters:
        dsName - the name of the data source.
        dataSource - the data source.
        Since:
        jcms-6.0.0

      • getDatabaseInfo

        public static DatabaseInfo getDatabaseInfo​(java.lang.String dsName)
        Returns the Database info for the given data source.
        Parameters:
        dsName - 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 dsName,
                                           boolean searchOutInitDS)
        Returns the Database info for the given data source.
        Parameters:
        dsName - the data source.
        searchOutInitDS - if true search outside the initialized Data Sources
        Returns:
        the Database info for the given data source.
        Since:
        jcms-10.0.7

      • 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

      • resetC3p0Pools

        public static void resetC3p0Pools()