com.edorasware.commons.core.util.db

Class DbUtils

java.lang.Object

com.edorasware.commons.core.util.db.DbUtils

public final class DbUtils
extends Object

Provides utility methods related to database operations.

Field Summary

Fields

Modifier and Type	Field and Description
static com.google.common.collect.ImmutableList<DatabaseType>	DATABASES_WITH_EMPTY_STRING_TO_NULL_CONVERSION Constants that defines the databases for which replace '' string with null.
static com.google.common.collect.ImmutableList<DatabaseType>	DATABASES_WITH_LIMITED_IN_PREDICATE_QUERY_SUPPORT Constants that defines the databases for which only limited support for `in` predicates .
static com.google.common.collect.ImmutableList<DatabaseType>	DATABASES_WITH_LIMITED_LARGE_STRING_QUERY_SUPPORT Constants that defines the databases for which only limited support exists to query large strings.
static String[]	JDBC_TABLE_TYPES_WHITE_LIST Constant that defines what JDBC table types to include by default when querying for table metadata.
static int	MAX_BYTES_IN_VARCHAR_COLUMN Constant the defines the cross-database maximum number of bytes that are stored in a VARCHAR table column.

Method Summary

Modifier and Type	Method and Description
static com.google.common.collect.ImmutableList<String>	adjustCasing(List<String> tableNames, DatabaseType databaseType) Some databases like PostgreSQL by default convert all table names to lower-case, hence we need to adjust the casing before we compare table names in Java code.
static String	adjustCasing(String tableName, DatabaseType databaseType) Some databases like PostgreSQL by default convert all table names to lower-case, hence we need to adjust the casing before we compare a table name in Java code.
static boolean	containsAllTables(List<String> tableNames, DataSource dataSource, String catalog, String schema) Checks if all tables (not views) with the given names exist in the database.
static boolean	containsForeignKey(String referencedTableName, String foreignKeyConstraintName, DataSource dataSource, String catalog, String schema) Checks if an foreign key constraint with the given name exists for the given primary key table in the database.
static boolean	containsIndex(String tableName, String indexName, DataSource dataSource, String catalog, String schema) Checks if an index with the given name exists for a given table in the database.
static boolean	containsTable(String tableName, DataSource dataSource, String catalog, String schema) Checks if a table (not a view) with the given name exists in the database.
static byte[]	getBlobBytes(ResultSet resultSet, String columnLabel) Retrieves the value of the given column as a byte[] value of a Blob.
static com.google.common.base.Optional<DatabaseMetaData>	getDatabaseMetaData(DataSource dataSource) Gets the database driver name from the given data source.
static Double	getDouble(ResultSet resultSet, String columnLabel) Retrieves the value of the given column as a Double value.
static Integer	getInteger(ResultSet resultSet, String columnLabel) Retrieves the value of the given column as an Integer value.
static Long	getLong(ResultSet resultSet, String columnLabel) Retrieves the value of the given column as a Long value.
static DatabaseType	inferDatabaseType(DataSource dataSource) Infers the database type from the given data source.

Methods inherited from class java.lang.Object

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

Field Detail

JDBC_TABLE_TYPES_WHITE_LIST

public static final String[] JDBC_TABLE_TYPES_WHITE_LIST

Constant that defines what JDBC table types to include by default when querying for table metadata.

MAX_BYTES_IN_VARCHAR_COLUMN

public static final int MAX_BYTES_IN_VARCHAR_COLUMN

Constant the defines the cross-database maximum number of bytes that are stored in a VARCHAR table column.

See Also:

Constant Field Values

DATABASES_WITH_LIMITED_LARGE_STRING_QUERY_SUPPORT

public static final com.google.common.collect.ImmutableList<DatabaseType> DATABASES_WITH_LIMITED_LARGE_STRING_QUERY_SUPPORT

Constants that defines the databases for which only limited support exists to query large strings.

DATABASES_WITH_LIMITED_IN_PREDICATE_QUERY_SUPPORT

public static final com.google.common.collect.ImmutableList<DatabaseType> DATABASES_WITH_LIMITED_IN_PREDICATE_QUERY_SUPPORT

Constants that defines the databases for which only limited support for `in` predicates .

DATABASES_WITH_EMPTY_STRING_TO_NULL_CONVERSION

public static final com.google.common.collect.ImmutableList<DatabaseType> DATABASES_WITH_EMPTY_STRING_TO_NULL_CONVERSION

Constants that defines the databases for which replace '' string with null.

Method Detail

containsTable

public static boolean containsTable(String tableName,
                                    DataSource dataSource,
                                    String catalog,
                                    String schema)

Checks if a table (not a view) with the given name exists in the database. The table name comparison is done case-insensitive.

Parameters:

tableName - the name of the table to find

dataSource - the data source

catalog - the catalog name

schema - the schema name

Returns:

true if a table with the given name exists

containsAllTables

public static boolean containsAllTables(List<String> tableNames,
                                        DataSource dataSource,
                                        String catalog,
                                        String schema)

Checks if all tables (not views) with the given names exist in the database. The table name comparison is done case-insensitive.

Parameters:

tableNames - the names of the tables to find

dataSource - the data source

catalog - the catalog name

schema - the schema name

Returns:

true if all tables with the given names exist

containsIndex

public static boolean containsIndex(String tableName,
                                    String indexName,
                                    DataSource dataSource,
                                    String catalog,
                                    String schema)

Checks if an index with the given name exists for a given table in the database. The table name and index name comparisons are done case-insensitive.

Parameters:

tableName - the name of the table for which to find the index

indexName - the name of the index

dataSource - the data source

catalog - the catalog name

schema - the schema name

Returns:

true if an index with the given name exists

containsForeignKey

public static boolean containsForeignKey(String referencedTableName,
                                         String foreignKeyConstraintName,
                                         DataSource dataSource,
                                         String catalog,
                                         String schema)

Checks if an foreign key constraint with the given name exists for the given primary key table in the database. The table name and index name comparisons are done case-insensitive.

Parameters:

referencedTableName - the name of the table that is referenced

foreignKeyConstraintName - the name of the foreign key to find

dataSource - the data source

catalog - the catalog name

schema - the schema name

Returns:

true if a foreign key constraint with the given name exists

adjustCasing

public static String adjustCasing(String tableName,
                                  DatabaseType databaseType)

Some databases like PostgreSQL by default convert all table names to lower-case, hence we need to adjust the casing before we compare a table name in Java code.

Parameters:

tableName - the table name

databaseType - the database type

Returns:

the table name with adjusted casing

adjustCasing

public static com.google.common.collect.ImmutableList<String> adjustCasing(List<String> tableNames,
                                                                           DatabaseType databaseType)

Some databases like PostgreSQL by default convert all table names to lower-case, hence we need to adjust the casing before we compare table names in Java code.

Parameters:

tableNames - the table names

databaseType - the database type

Returns:

the table names with adjusted casing

getInteger

public static Integer getInteger(ResultSet resultSet,
                                 String columnLabel)
                          throws SQLException

Retrieves the value of the given column as an Integer value.

Parameters:

resultSet - the record from which to retrieve a column value

columnLabel - the column's label

Returns:

the column value as a Integer or null

Throws:

SQLException - thrown if accessing the result set fails

getLong

public static Long getLong(ResultSet resultSet,
                           String columnLabel)
                    throws SQLException

Retrieves the value of the given column as a Long value.

Parameters:

resultSet - the record from which to retrieve a column value

columnLabel - the column's label

Returns:

the column value as a Long or null

Throws:

SQLException - thrown if accessing the result set fails

getDouble

public static Double getDouble(ResultSet resultSet,
                               String columnLabel)
                        throws SQLException

Retrieves the value of the given column as a Double value.

Parameters:

resultSet - the record from which to retrieve a column value

columnLabel - the column's label

Returns:

the column value as a Double or null

Throws:

SQLException - thrown if accessing the result set fails

getBlobBytes

public static byte[] getBlobBytes(ResultSet resultSet,
                                  String columnLabel)
                           throws SQLException,
                                  IOException

Retrieves the value of the given column as a byte[] value of a Blob.

Parameters:

resultSet - the record from which to retrieve a column value

columnLabel - the column's label

Returns:

the column value as a byte[] of a Blob or null

Throws:

SQLException - thrown if accessing the result set fails

IOException - thrown if accessing the binary stream fails

inferDatabaseType

public static DatabaseType inferDatabaseType(DataSource dataSource)

Infers the database type from the given data source.

Parameters:

dataSource - the data source

Returns:

the inferred database type

getDatabaseMetaData

public static com.google.common.base.Optional<DatabaseMetaData> getDatabaseMetaData(DataSource dataSource)

Gets the database driver name from the given data source.

Parameters:

dataSource - the data source

Returns:

the database driver name