com.samskivert.jdbc

Class JDBCUtil

java.lang.Object

com.samskivert.jdbc.JDBCUtil

public class JDBCUtil
extends Object

A repository for JDBC related utility functions.

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
static interface	JDBCUtil.BatchProcessor Used for batchQuery(java.sql.Connection, java.lang.String, java.util.Collection<?>, boolean, int, com.samskivert.jdbc.JDBCUtil.BatchProcessor).

Constructor Summary

Constructors

Constructor and Description
JDBCUtil()

Method Summary

Modifier and Type	Method and Description
static boolean	addColumn(Connection conn, String table, String cname, String cdef, String afterCname) Adds a column (with name 'cname' and definition 'cdef') to the specified table.
static boolean	addIndexToTable(Connection conn, String table, String cname, String iname) Adds an index on the specified column (cname) to the specified table.
static void	batchQuery(Connection conn, String query, Collection<?> keys, boolean escapeKeys, int batchSize, JDBCUtil.BatchProcessor processor) Issues a query with a potentially large number of keys in batches.
static void	changeColumn(Connection conn, String table, String cname, String cdef) Changes a column's definition.
static void	checkedUpdate(PreparedStatement stmt, int expectedCount) Calls stmt.executeUpdate() on the supplied statement, checking to see that it returns the expected update count and throwing a persistence exception if it does not.
static void	checkedUpdate(Statement stmt, String query, int expectedCount) Calls stmt.executeUpdate() on the supplied statement with the supplied query, checking to see that it returns the expected update count and throwing a persistence exception if it does not.
static void	close(Connection conn) Closes the supplied JDBC connection and gracefully handles being passed null (by doing nothing).
static void	close(Statement stmt) Closes the supplied JDBC statement and gracefully handles being passed null (by doing nothing).
static boolean	createTableIfMissing(Connection conn, String table, String[] definition, String postamble) Used to programatically create a database table.
static boolean	dropColumn(Connection conn, String table, String cname) Removes a column from the specified table.
static boolean	dropIndex(Connection conn, String table, String cname, String iname) Removes a named index from the specified table.
static void	dropPrimaryKey(Connection conn, String table) Removes the primary key from the specified table.
static String	escape(Object[] values) Escapes a list of values, separating the escaped values by commas.
static String	escape(String text) Escapes any single quotes in the supplied text and wraps it in single quotes to make it safe for embedding into a database query.
static String	getColumnDefaultValue(Connection conn, String table, String column) Returns a string representation of the default value for the specified column in the specified table.
static int	getColumnSize(Connection conn, String table, String column) Returns the size for the specified column in the specified table.
static int	getColumnType(Connection conn, String table, String column) Returns the type (as specified in Types for the specified column in the specified table.
static String	getIndexName(Connection conn, String table, String column) Returns the name of the index for the specified column in the specified table.
static boolean	isColumnNullable(Connection conn, String table, String column) Determines whether or not the specified column accepts null values.
static String	jigger(String text) Many databases simply fail to handle Unicode text properly and this routine provides a common workaround which is to represent a UTF-8 string as an ISO-8859-1 string.
static Connection	makeCollector(Connection conn, List<Statement> stmts) Wraps the given connection in a proxied instance that will add all statements returned by methods called on the proxy (such as Connection.createStatement()) to the supplied list.
String	quote(Date date) Converts the date to a string and surrounds it in single-quotes via the escape method.
static String	safeJigger(String text) Utility method to jigger the specified string so that it's safe to use in a regular Statement.
static boolean	tableContainsColumn(Connection conn, String table, String column) Returns true if the table with the specified name exists and contains a column with the specified name, false if either condition does not hold true.
static boolean	tableContainsIndex(Connection conn, String table, String column, String index) Returns true if the index on the specified column exists for the specified table, false if it does not.
static boolean	tableContainsPrimaryKey(Connection conn, String table, String column) Returns true if the specified table contains a primary key on the specified column.
static boolean	tableExists(Connection conn, String name) Returns true if the table with the specified name exists, false if it does not.
static String	unjigger(String text) Reverses jigger(java.lang.String).
static void	warnedUpdate(PreparedStatement stmt, int expectedCount) Calls stmt.executeUpdate() on the supplied statement, checking to see that it returns the expected update count and logging a warning if it does not.
static void	warnedUpdate(Statement stmt, String query, int expectedCount) Calls stmt.executeUpdate() on the supplied statement with the supplied query, checking to see that it returns the expected update count and logging a warning if it does not.

Methods inherited from class java.lang.Object

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

Constructor Detail

JDBCUtil

public JDBCUtil()

Method Detail

close

public static void close(Statement stmt)
                  throws SQLException

Closes the supplied JDBC statement and gracefully handles being passed null (by doing nothing).

Throws:

SQLException

close

public static void close(Connection conn)
                  throws SQLException

Closes the supplied JDBC connection and gracefully handles being passed null (by doing nothing).

Throws:

SQLException

makeCollector

public static Connection makeCollector(Connection conn,
                                       List<Statement> stmts)

Wraps the given connection in a proxied instance that will add all statements returned by methods called on the proxy (such as Connection.createStatement()) to the supplied list. Thus you can create the proxy, pass the proxy to code that creates and uses statements and then close any statements created by the code that operated on that Connection before returning it to a pool, for example.

checkedUpdate

public static void checkedUpdate(PreparedStatement stmt,
                                 int expectedCount)
                          throws SQLException,
                                 PersistenceException

Calls stmt.executeUpdate() on the supplied statement, checking to see that it returns the expected update count and throwing a persistence exception if it does not.

Throws:

SQLException

PersistenceException

checkedUpdate

public static void checkedUpdate(Statement stmt,
                                 String query,
                                 int expectedCount)
                          throws SQLException,
                                 PersistenceException

Calls stmt.executeUpdate() on the supplied statement with the supplied query, checking to see that it returns the expected update count and throwing a persistence exception if it does not.

Throws:

SQLException

PersistenceException

warnedUpdate

public static void warnedUpdate(PreparedStatement stmt,
                                int expectedCount)
                         throws SQLException

Calls stmt.executeUpdate() on the supplied statement, checking to see that it returns the expected update count and logging a warning if it does not.

Throws:

SQLException

batchQuery

public static void batchQuery(Connection conn,
                              String query,
                              Collection<?> keys,
                              boolean escapeKeys,
                              int batchSize,
                              JDBCUtil.BatchProcessor processor)
                       throws SQLException

Issues a query with a potentially large number of keys in batches. For example, you might have 10,000 ids that you wish to use in an "in" clause, but don't trust the database to be smart about optimizing that many keys, so instead you use batchQuery like so:

    Collection<Integer> keys = ...;
    String query = "select NAME from USERS where USER_ID in (#KEYS)";
    JDBCUtil.BatchProcessor proc = new JDBCUtil.BatchProcessor() {
        public void process (ResultSet row) {
            String name = rs.getString(1);
            // do whatever with name
        }
    };
    JDBCUtil.batchQuery(conn, query, keys, false, 500, proc);
 

Parameters:

query - the SQL query to run for each batch with the string #KEYS# in the place where the batch of keys should be substituted.

escapeKeys - if true, escape(java.lang.String) will be called on each key to escape any dangerous characters and wrap the key in quotes.

batchSize - the number of keys at a time to substitute in for #KEYS#.

Throws:

SQLException

warnedUpdate

public static void warnedUpdate(Statement stmt,
                                String query,
                                int expectedCount)
                         throws SQLException

Calls stmt.executeUpdate() on the supplied statement with the supplied query, checking to see that it returns the expected update count and logging a warning if it does not.

Throws:

SQLException

quote

public String quote(Date date)

Converts the date to a string and surrounds it in single-quotes via the escape method. If the date is null, returns null.

escape

public static String escape(String text)

Escapes any single quotes in the supplied text and wraps it in single quotes to make it safe for embedding into a database query.

escape

public static String escape(Object[] values)

Escapes a list of values, separating the escaped values by commas. See escape(String).

jigger

public static String jigger(String text)

Many databases simply fail to handle Unicode text properly and this routine provides a common workaround which is to represent a UTF-8 string as an ISO-8859-1 string. If you don't need to use the database's collation routines, this allows you to do pretty much exactly what you want at the expense of having to jigger and dejigger every goddamned string that might contain multibyte characters every time you access the database. Three cheers for progress!

unjigger

public static String unjigger(String text)

Reverses jigger(java.lang.String).

safeJigger

public static String safeJigger(String text)

Utility method to jigger the specified string so that it's safe to use in a regular Statement.

createTableIfMissing

public static boolean createTableIfMissing(Connection conn,
                                           String table,
                                           String[] definition,
                                           String postamble)
                                    throws SQLException

Used to programatically create a database table. Does nothing if the table already exists.

Returns:

true if the table was created, false if it already existed.

Throws:

SQLException

tableExists

public static boolean tableExists(Connection conn,
                                  String name)
                           throws SQLException

Returns true if the table with the specified name exists, false if it does not. Note: the table name is case sensitive.

Throws:

SQLException

tableContainsColumn

public static boolean tableContainsColumn(Connection conn,
                                          String table,
                                          String column)
                                   throws SQLException

Returns true if the table with the specified name exists and contains a column with the specified name, false if either condition does not hold true. Note: the names are case sensitive.

Throws:

SQLException

tableContainsIndex

public static boolean tableContainsIndex(Connection conn,
                                         String table,
                                         String column,
                                         String index)
                                  throws SQLException

Returns true if the index on the specified column exists for the specified table, false if it does not. Optionally you can specifiy a non null index name, and the table will be checked to see if it contains that specifically named index. Note: the names are case sensitive.

Throws:

SQLException

tableContainsPrimaryKey

public static boolean tableContainsPrimaryKey(Connection conn,
                                              String table,
                                              String column)
                                       throws SQLException

Returns true if the specified table contains a primary key on the specified column.

Throws:

SQLException

getIndexName

public static String getIndexName(Connection conn,
                                  String table,
                                  String column)
                           throws SQLException

Returns the name of the index for the specified column in the specified table.

Throws:

SQLException

getColumnType

public static int getColumnType(Connection conn,
                                String table,
                                String column)
                         throws SQLException

Returns the type (as specified in Types for the specified column in the specified table.

Throws:

SQLException

isColumnNullable

public static boolean isColumnNullable(Connection conn,
                                       String table,
                                       String column)
                                throws SQLException

Determines whether or not the specified column accepts null values.

Returns:

true if the column accepts null values, false if it does not (or its nullability is unknown)

Throws:

SQLException

getColumnSize

public static int getColumnSize(Connection conn,
                                String table,
                                String column)
                         throws SQLException

Returns the size for the specified column in the specified table. For char or date types this is the maximum number of characters, for numeric or decimal types this is the precision.

Throws:

SQLException

getColumnDefaultValue

public static String getColumnDefaultValue(Connection conn,
                                           String table,
                                           String column)
                                    throws SQLException

Returns a string representation of the default value for the specified column in the specified table. This may be null.

Throws:

SQLException

addColumn

public static boolean addColumn(Connection conn,
                                String table,
                                String cname,
                                String cdef,
                                String afterCname)
                         throws SQLException

Adds a column (with name 'cname' and definition 'cdef') to the specified table.

Parameters:

afterCname - (optional) the name of the column after which to add the new column.

Returns:

true if the column was added, false if it already existed.

Throws:

SQLException

changeColumn

public static void changeColumn(Connection conn,
                                String table,
                                String cname,
                                String cdef)
                         throws SQLException

Changes a column's definition. Takes a full column definition 'cdef' (including the name of the column) with which to replace the specified column 'cname'. NOTE: A handy thing you can do with this is to rename a column by providing a column definition that has a different name, but the same column type.

Throws:

SQLException

dropColumn

public static boolean dropColumn(Connection conn,
                                 String table,
                                 String cname)
                          throws SQLException

Removes a column from the specified table.

Returns:

true if the column was dropped, false if it did not exist in the first place.

Throws:

SQLException

dropIndex

public static boolean dropIndex(Connection conn,
                                String table,
                                String cname,
                                String iname)
                         throws SQLException

Removes a named index from the specified table.

Returns:

true if the index was dropped, false if it did not exist in the first place.

Throws:

SQLException

dropPrimaryKey

public static void dropPrimaryKey(Connection conn,
                                  String table)
                           throws SQLException

Removes the primary key from the specified table.

Throws:

SQLException

addIndexToTable

public static boolean addIndexToTable(Connection conn,
                                      String table,
                                      String cname,
                                      String iname)
                               throws SQLException

Adds an index on the specified column (cname) to the specified table. Optionally supply an index name, otherwise the index is named after the column.

Returns:

true if the index was added, false if it already existed.

Throws:

SQLException