com.croftsoft.core.sql
Class SqlLib

java.lang.Object
  com.croftsoft.core.sql.SqlLib

public final class SqlLib
extends Object

A library of SQL manipulation methods.

Since:

2001-06-06

Version:

2002-09-16

Author:

David Wallace Croft

Field Summary

static boolean	debug If set to true, debugging information will be printed to System.out.
static int	VARCHAR_LENGTH_MAX "All the major databases support VARCHAR lengths up to 254 characters." -- JDBC API Tutorial and Reference, 2nd Edition, p911.

Method Summary

static int	count(Connection connection, String tableName, Pair wherePair) Counts the rows in a table that meet the WHERE condition.
static int	count(Connection connection, String tableName, Pair[] wherePairs) Counts the rows in a table that meet the WHERE condition.
static String	createCountSql(String tableName, Pair wherePair) Creates an SQL SELECT COUNT(*) statement.
static String	createCountSql(String tableName, Pair[] wherePairs) Creates an SQL SELECT COUNT(*) statement.
static String	createDeleteSql(String tableName, Pair[] wherePairs) Creates an SQL DELETE statement.
static String	createInsertSql(String tableName, String[] values) Creates an SQL INSERT statement.
static String	createSelectSql(String[] selectFieldNames, String tableName, Pair wherePair) Creates an SQL SELECT statement.
static String	createSelectSql(String[] selectFieldNames, String tableName, Pair[] wherePairs) Creates an SQL SELECT statement.
static String	createSelectSql(String[] selectFieldNames, String tableName, Pair[] wherePairs, String orderBy) Creates an SQL SELECT statement.
static String	createUpdateSql(String tableName, Pair[] setPairs, Pair[] wherePairs) Creates an SQL UPDATE statement.
static int	delete(Connection connection, String tableName, Pair[] wherePairs) Removes rows from a table.
static int	dropTable(Connection connection, String tableName)
static String	escapeQuotes(String originalString) Doubles all single and double quotes in the original String.
static int	executeUpdate(Connection connection, String updateSql) Executes an SQL update statement.
static int	insert(Connection connection, String tableName, String[] values) Adds a row to a table.
static void	main(String[] args) Prints the result of the test method.
static BigDecimal	max(Connection connection, String columnName, String tableName, Pair wherePair) Returns the maximum column value.
static String[]	select(Connection connection, String[] selectFieldNames, String tableName, Pair wherePair) Selects the first row that meets the where condition.
static String[]	select(Connection connection, String[] selectFieldNames, String tableName, Pair[] wherePairs) Selects the first row that meets the where condition.
static String[]	select(Connection connection, String[] selectFieldNames, String tableName, Pair[] wherePairs, String orderBy, int maxRows) Returns the results of a SELECT query as a String array.
static String	select(Connection connection, String selectFieldName, String tableName, Pair wherePair) Retrieves the String value at a given row and column.
static String	select(Connection connection, String selectFieldName, String tableName, Pair[] wherePairs) Retrieves the String value at a given row and column.
static boolean	tableExists(Connection connection, String tableName)
static boolean	test(String[] args) Test method.
static int	truncateTable(Connection connection, String tableName)
static int	update(Connection connection, String tableName, Pair[] setPairs, Pair wherePair) Updates rows in a table.
static int	update(Connection connection, String tableName, Pair[] setPairs, Pair[] wherePairs) Updates rows in a table.

Methods inherited from class java.lang.Object

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

Field Detail

debug

public static boolean debug

If set to true, debugging information will be printed to System.out.

VARCHAR_LENGTH_MAX

public static int VARCHAR_LENGTH_MAX

"All the major databases support VARCHAR lengths up to 254 characters." -- JDBC API Tutorial and Reference, 2nd Edition, p911.

Method Detail

main

public static void main(String[] args)

Prints the result of the test method.

test

public static boolean test(String[] args)

Test method.

count

public static int count(Connection connection,
                        String tableName,
                        Pair wherePair)
                 throws SQLException

Counts the rows in a table that meet the WHERE condition.

Example query:

 SELECT COUNT(*) FROM TABLE_USER WHERE GENDER='M'
 

Parameters:

wherePair - If the wherePair object is null, all rows will be counted. Instance variable wherePair.value may be null. A non-null wherePair.value will converted by escapeQuotes().

Returns:

The count of rows selected.

Throws:

NullArgumentException - If tableName is null.

SQLException

count

public static int count(Connection connection,
                        String tableName,
                        Pair[] wherePairs)
                 throws SQLException

Counts the rows in a table that meet the WHERE condition.

Example query:

 SELECT COUNT(*) FROM TABLE_USER WHERE GENDER='M' AND AGE='34'
 

Parameters:

wherePairs - If the wherePairs object is null, all rows will be counted. If the length of wherePairs is greater than one, the where clause will be the conjuction ("AND") of the individual where pairs. A null wherePair.value will be translated as "IS NULL". A non-null wherePair.value will converted by escapeQuotes().

Returns:

The count of rows selected.

Throws:

NullArgumentException - If tableName is null.

SQLException

createCountSql

public static String createCountSql(String tableName,
                                    Pair wherePair)

Creates an SQL SELECT COUNT(*) statement. Used to count the number of rows that meet the criterion.

Example output:

 SELECT COUNT(*) FROM TABLE_USER WHERE GENDER='M'
 

Parameters:

wherePair - If the wherePair object is null, no "where" clause will be appended, indicating that all rows in the table should be selected. Instance variable wherePair.value may be null. A non-null wherePair.value will converted by escapeQuotes().

Throws:

NullArgumentException - If tableName is null.

createCountSql

public static String createCountSql(String tableName,
                                    Pair[] wherePairs)

Creates an SQL SELECT COUNT(*) statement. Used to count the number of rows that meet the criteria.

Example output:

 SELECT COUNT(*) FROM TABLE_USER WHERE GENDER='M' AND AGE='34'
 

Parameters:

wherePairs - If the wherePairs object is null, no "where" clause will be appended, indicating that all rows in the table should be selected. If the length of wherePairs is greater than one, the where clause will be the conjuction ("AND") of the individual where pairs. A null wherePair.value will be translated as "IS NULL". A non-null wherePair.value will converted by escapeQuotes().

Throws:

NullArgumentException - If tableName is null.

createDeleteSql

public static String createDeleteSql(String tableName,
                                     Pair[] wherePairs)

Creates an SQL DELETE statement.

Example output:

 DELETE FROM TABLE_USER WHERE GENDER='M' AND AGE='34'
 

Parameters:

wherePairs - If the wherePairs object is null, no "where" clause will be appended, indicating that all rows in the table should be selected. If the length of wherePairs is greater than one, the where clause will be the conjuction ("AND") of the individual where pairs. A null wherePair.value will be translated as "IS NULL". A non-null wherePair.value will converted by escapeQuotes().

Throws:

NullArgumentException - If tableName is null.

createInsertSql

public static String createInsertSql(String tableName,
                                     String[] values)

Creates an SQL INSERT statement.

Example output:

 INSERT INTO TABLE_USER VALUES('croft','abc''123',NULL)
 

Parameters:

values - Individual elements of the array may be null. Non-null values will be converted by escapeQuotes().

Throws:

NullArgumentException - If tableName or values is null.

createSelectSql

public static String createSelectSql(String[] selectFieldNames,
                                     String tableName,
                                     Pair wherePair)

Creates an SQL SELECT statement.

Example output:

 SELECT FIRST_NAME,LAST_NAME FROM TABLE_USER WHERE USERNAME='croft'
 

Parameters:

selectFieldNames - Must not be null. Elements must not be null. Use "*" to select all fields.

wherePair - If the wherePair object is null, no "where" clause will be appended, indicating that all rows in the table should be selected. Instance variable wherePair.value may be null. A non-null wherePair.value will converted by escapeQuotes().

Throws:

NullArgumentException - If tableName, selectFieldNames, or an element of setFieldNames is null.

createSelectSql

public static String createSelectSql(String[] selectFieldNames,
                                     String tableName,
                                     Pair[] wherePairs)

Creates an SQL SELECT statement.

Example output:

 SELECT NAME,SALARY FROM TABLE_USER WHERE GENDER='M' AND AGE='34'
 

Parameters:

selectFieldNames - Must not be null. Elements must not be null. Use "*" to select all fields.

wherePairs - If the wherePairs object is null, no "where" clause will be appended, indicating that all rows in the table should be selected. If the length of wherePairs is greater than one, the where clause will be the conjuction ("AND") of the individual where pairs. A null wherePair.value will be translated as "IS NULL". A non-null wherePair.value will converted by escapeQuotes().

Throws:

NullArgumentException - If tableName, selectFieldNames, or an element of setFieldNames is null.

createSelectSql

public static String createSelectSql(String[] selectFieldNames,
                                     String tableName,
                                     Pair[] wherePairs,
                                     String orderBy)

Creates an SQL SELECT statement.

Example output:

 SELECT NAME,SALARY FROM TABLE_USER WHERE GENDER='M' AND AGE='34'
 

Parameters:

selectFieldNames - Must not be null. Elements must not be null. Use "*" to select all fields.

wherePairs - If the wherePairs object is null, no "where" clause will be appended, indicating that all rows in the table should be selected. If the length of wherePairs is greater than one, the where clause will be the conjuction ("AND") of the individual where pairs. A null wherePair.value will be translated as "IS NULL". A non-null wherePair.value will converted by escapeQuotes().

orderBy - The column name to use for ordering.

Throws:

NullArgumentException - If tableName, selectFieldNames, or an element of setFieldNames is null.

createUpdateSql

public static String createUpdateSql(String tableName,
                                     Pair[] setPairs,
                                     Pair[] wherePairs)

Creates an SQL UPDATE statement.

Example output:

 UPDATE TABLE_USER SET GENDER='M',BIRTHDAY=NULL WHERE USERNAME='joe'
 

Parameters:

setPairs - Must not be null. Array elements must not be null. An element setPair.value may be null. A non-null setPair.value will converted by escapeQuotes().

wherePairs - If the wherePairs object is null, no "where" clause will be appended, indicating that all rows in the table should be selected. If the length of wherePairs is greater than one, the where clause will be the conjuction ("AND") of the individual where pairs. A null wherePair.value will be translated as "IS NULL". A non-null wherePair.value will converted by escapeQuotes().

Throws:

NullArgumentException - If tableName, setPairs, or an element of setPairs is null.

delete

public static int delete(Connection connection,
                         String tableName,
                         Pair[] wherePairs)
                  throws SQLException

Removes rows from a table.

Parameters:

wherePairs - See method createDeleteSql() for description.

Returns:

The number of rows deleted.

Throws:

SQLException

dropTable

public static int dropTable(Connection connection,
                            String tableName)
                     throws SQLException

Throws:

SQLException

escapeQuotes

public static String escapeQuotes(String originalString)

Doubles all single and double quotes in the original String.

Used to prepare a String to be passed as an SQL statement variable. For example, notice how the single quote is doubled inside the the password "abc'123" which contains an apostrophe:

 INSERT INTO TABLE_USER VALUES ('croft', 'abc''123')
 

executeUpdate

public static int executeUpdate(Connection connection,
                                String updateSql)
                         throws SQLException

Executes an SQL update statement.

This convenience method creates a new Statement instance, executes the update, and then ensures that the Statement is closed before return or abnormal exit.

Returns:

The number of rows created.

Throws:

SQLException

insert

public static int insert(Connection connection,
                         String tableName,
                         String[] values)
                  throws SQLException

Adds a row to a table.

Parameters:

values - Each value will be converted by escapeQuotes() before being used.

Returns:

The number of rows created.

Throws:

SQLException

max

public static BigDecimal max(Connection connection,
                             String columnName,
                             String tableName,
                             Pair wherePair)
                      throws SQLException

Returns the maximum column value.

Example query:

 SELECT MAX(SALARY) FROM TABLE_EMPLOYEE WHERE DEPARTMENT_ID='3'
 

Parameters:

columnName - Must not be null.

wherePair - If the wherePair object is null, all rows will be included. Instance variable wherePair.value may be null. A non-null wherePair.value will converted by escapeQuotes().

Returns:

The maximum column value as a BigDecimal or null if there are no non-null values selected to compare.

Throws:

NullArgumentException - If columnName or tableName is null.

SQLException

select

public static String[] select(Connection connection,
                              String[] selectFieldNames,
                              String tableName,
                              Pair wherePair)
                       throws SQLException

Selects the first row that meets the where condition.

Only returns the values for the first row selected.

Parameters:

selectFieldNames - Must not be null. Elements must not be null. Use new String[]{"*"} to select all fields.

wherePair - See method createSelectSql() for description.

Returns:

The field values for the first row selected or null if no row was selected.

Throws:

NullArgumentException - If tableName, selectFieldNames, or an element of setFieldNames is null.

SQLException

select

public static String[] select(Connection connection,
                              String[] selectFieldNames,
                              String tableName,
                              Pair[] wherePairs)
                       throws SQLException

Selects the first row that meets the where condition.

Only returns the values for the first row selected.

Parameters:

selectFieldNames - Must not be null. Elements must not be null. Use new String[]{"*"} to select all fields.

wherePairs - See method createSelectSql() for description.

Returns:

The field values for the first row selected or null if no row was selected.

Throws:

NullArgumentException - If tableName, selectFieldNames, or an element of setFieldNames is null.

SQLException

select

public static String[] select(Connection connection,
                              String[] selectFieldNames,
                              String tableName,
                              Pair[] wherePairs,
                              String orderBy,
                              int maxRows)
                       throws SQLException

Returns the results of a SELECT query as a String array.

This method lets you return multiple column values from a single row or multiple row values from a single column. The results are then returned as a 1-dimensional String array. If the input variable maxRows is zero, just the column values from the first row selected are returned. If maxRows is greater than zero, up to that number of row values from a single column are returned.

Parameters:

selectFieldNames - Must not be null. Elements must not be null. Use new String[]{"*"} to select all fields.

wherePairs - See method createSelectSql() for description.

orderBy - The column name to use for ordering.

maxRows - If 0, multiple column values from a single row will be returned. If greater than 0, multiple row values from a single column will be returned, up to maxRows in length.

Returns:

The field values for the first row selected or null if no row was selected.

Throws:

NullArgumentException - If tableName, selectFieldNames, or an element of setFieldNames is null.

SQLException

select

public static String select(Connection connection,
                            String selectFieldName,
                            String tableName,
                            Pair wherePair)
                     throws SQLException

Retrieves the String value at a given row and column.

Only returns a single value for the first row selected.

Parameters:

selectFieldName - Must not be null. If selectFieldName is "*", this method will return the value associated with the first field name only.

wherePair - See method createSelectSql() for description.

Returns:

The column value for the first row selected or null if no row was selected.

Throws:

NullArgumentException - If tableName or selectFieldName is null.

SQLException

select

public static String select(Connection connection,
                            String selectFieldName,
                            String tableName,
                            Pair[] wherePairs)
                     throws SQLException

Retrieves the String value at a given row and column.

Only returns a single value for the first row selected.

Parameters:

selectFieldName - Must not be null. If selectFieldName is "*", this method will return the value associated with the first field name only.

wherePairs - See method createSelectSql() for description.

Returns:

The column value for the first row selected or null if no row was selected.

Throws:

NullArgumentException - If tableName or selectFieldName is null.

SQLException

tableExists

public static boolean tableExists(Connection connection,
                                  String tableName)
                           throws SQLException

Throws:

SQLException

truncateTable

public static int truncateTable(Connection connection,
                                String tableName)
                         throws SQLException

Throws:

SQLException

update

public static int update(Connection connection,
                         String tableName,
                         Pair[] setPairs,
                         Pair wherePair)
                  throws SQLException

Updates rows in a table.

Parameters:

setPairs - Must not be null. Array elements must not be null. An element setPair.value may be null. A non-null setPair.value will converted by escapeQuotes().

wherePair - See method createUpdateSql() for description.

Returns:

The number of rows updated.

Throws:

NullArgumentException - If tableName, setPairs, or an element of setPairs is null.

SQLException

update

public static int update(Connection connection,
                         String tableName,
                         Pair[] setPairs,
                         Pair[] wherePairs)
                  throws SQLException

Updates rows in a table.

Parameters:

setPairs - Must not be null. Array elements must not be null. An element setPair.value may be null. A non-null setPair.value will converted by escapeQuotes().

wherePairs - See method createUpdateSql() for description.

Returns:

The number of rows updated.

Throws:

NullArgumentException - If tableName, setPairs, or an element of setPairs is null.

SQLException