com.antelmann.sql
Class ConnectionHelper

java.lang.Object
  com.antelmann.sql.ConnectionHelper

public final class ConnectionHelper
extends Object

The class JDBC provides several useful methods for handling JDBC connections.

Author:

Holger Antelmann

See Also:

JDBCTableModel

Constructor Summary

ConnectionHelper(Connection con)

Method Summary

void	copyTable(String origTable, Connection target, String copyTable, boolean createTable) creates a new table in Connection target corresponding to the given table in this Connection and transfers all data
void	copyTo(Connection target, boolean createTables) copies all tables from one connection into the other connection
static String	createTableString(ResultSetMetaData data, String tableName) creates the DDL statement that would create a table identical to the one denoted by the given meta data
void	delete(RowItem row)
int	deleteRow(String tableName, String idField, Object idValue) deletes all rows in the given table where the given field equals the given value
int	deleteRows(String tableName, Map<String,Object> fieldRestrictions)
int	executeUpdate(String sql) shortcut to perform an update via SQL statement
static RowItem	extractRow(ResultSet rs, String idField) the current row should not have been accessed, yet
ArrayList<HashMap<String,Object>>	extractRows(ResultSet rs) returns a map per row, where column names are mapped to their respective value.
ResultSet	filterTable(String tableName, Map<String,Object> fieldRestrictions) returns all columns (*) that correspond to the given restrictions, where each key is tested to be equal to the value.
ResultSet	filterTable(String tableName, Map<String,Object> fieldRestrictions, int resultSetType, int resultSetConcurrency) the integer parameters correspond to the prepare statement for the connection After being done w/ the ResultSet, it should be closed along w/ the corresponding statement.
ResultSet	filterTableUpdateable(String tableName, Map<String,Object> fieldRestrictions) returns an updateable and scrollable ResultSet.
Object	getAverageValue(String table, String columnField)
Connection	getConnection()
int	getCount(String table)
Object	getFunctionValue(String table, String columnField, String function) returns the max value of the given field in the given table; null is returned if no entries were found in the database.
Object	getMaxValue(String table, String columnField)
Object	getMinValue(String table, String columnField)
HashMap<String,Object>	getRow(String tableName, String idField, Object idValue) may return null if no row was found; returns the first occurrence if multiple rows exist
RowItem	getRowItem(String tableName, String idField, Object idValue) may return null if no row was found; returns the first occurrence if multiple rows exist
ArrayList<RowItem>	getRowItems(ResultSet rs, String idField, Filter<RowItem> filter) filter may be null
ArrayList<RowItem>	getRowItems(String tableName, String idField, Filter<RowItem> filter) filter may be null
ArrayList<RowItem>	getRowItems(String tableName, String idField, Map<String,Object> fieldRestrictions) supports Range objects in the fieldRestrictions
Object	getSumValue(String table, String columnField)
ArrayList<HashMap<String,Object>>	getTable(String tableName) retrieves the entire table and returns it as a list of maps containing the field names mapped to their respective values
ResultSetTableModel	getTableModel(String tableName, String idColumnName, Map<String,Object> fieldRestrictions) returns a table model based on the table filtered by the given restrictions
String[]	getTableNames()
String[]	getViews()
void	insert(RowItem row)
int	insertRow(String tableName, Map<String,Object> fieldValues) inserts the given field values as a row into the given table of the given connection.
int	insertRow(String tableName, Object... values) inserts the given values as a single row into the given table of the given connection.
int	insertTableModel(String tableName, TableModel model) the table model is inserted based on the column order rather than column names.
void	setConnection(Connection con)
void	update(RowItem row)
void	updateRow(String tableName, String idField, Object idValue, Map<String,Object> fieldValues) updates the row in the given table by applying all of the given field values.
void	updateRow(String tableName, String idField, Object idValue, Map<String,Object> fieldValues, boolean ignoreInvalidColumns) updates the row in the given table by applying the given field values.

Methods inherited from class java.lang.Object

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

Constructor Detail

ConnectionHelper

public ConnectionHelper(Connection con)

Method Detail

getConnection

public Connection getConnection()

setConnection

public void setConnection(Connection con)

getMaxValue

public Object getMaxValue(String table,
                          String columnField)
                   throws SQLException

Throws:

SQLException

getMinValue

public Object getMinValue(String table,
                          String columnField)
                   throws SQLException

Throws:

SQLException

getSumValue

public Object getSumValue(String table,
                          String columnField)
                   throws SQLException

Throws:

SQLException

getAverageValue

public Object getAverageValue(String table,
                              String columnField)
                       throws SQLException

Throws:

SQLException

getCount

public int getCount(String table)
             throws SQLException

Throws:

SQLException

getFunctionValue

public Object getFunctionValue(String table,
                               String columnField,
                               String function)
                        throws SQLException

returns the max value of the given field in the given table; null is returned if no entries were found in the database. The return value represents the value found in the given field column.

Throws:

SQLException

insertRow

public int insertRow(String tableName,
                     Object... values)
              throws SQLException

inserts the given values as a single row into the given table of the given connection.
It is assumed that the given vaules correspond to the columns found in the table specified by the connection and the table name. Attention: currently, this method doesn't properly handle NULL values; so if a NULL value is found, a SQLException is thrown

Returns:

the row count for the insertRow statement

Throws:

SQLException

See Also:

insertRow(String, Map)

insertRow

public int insertRow(String tableName,
                     Map<String,Object> fieldValues)
              throws SQLException

inserts the given field values as a row into the given table of the given connection. Currently, this method doesn't properly handle null values, so do not map to those in the fieldValues; if you do, those key/value pairs will be filtered out

Returns:

the row count for the insertRow statement

Throws:

SQLException

getTableModel

public ResultSetTableModel getTableModel(String tableName,
                                         String idColumnName,
                                         Map<String,Object> fieldRestrictions)
                                  throws SQLException

returns a table model based on the table filtered by the given restrictions

Parameters:

tableName - the name of the table to be used

idColumnName - the name of the column representing the primary key; this may be null

fieldRestrictions - mapping columnNames to values to which the result is to be limited; may be null

Throws:

SQLException

extractRow

public static RowItem extractRow(ResultSet rs,
                                 String idField)
                          throws SQLException

the current row should not have been accessed, yet

Throws:

SQLException

updateRow

public void updateRow(String tableName,
                      String idField,
                      Object idValue,
                      Map<String,Object> fieldValues)
               throws SQLException

updates the row in the given table by applying all of the given field values. The given idField is considered to be a primary key for the table. An SQLException is also thrown if no row has been found or any fieldValues cannot be stored.

Throws:

SQLException

updateRow

public void updateRow(String tableName,
                      String idField,
                      Object idValue,
                      Map<String,Object> fieldValues,
                      boolean ignoreInvalidColumns)
               throws SQLException

updates the row in the given table by applying the given field values. The given idField is considered to be a primary key for the table. An SQLException is also thrown if no row has been found. if ignoreInvalidColumns is true, no exception is raised on trying to update columns that either do not exist or cannot be updated with the given value.

Throws:

SQLException

update

public void update(RowItem row)
            throws SQLException

Throws:

SQLException

insert

public void insert(RowItem row)
            throws SQLException

Throws:

SQLException

delete

public void delete(RowItem row)
            throws SQLException

Throws:

SQLException

insertTableModel

public int insertTableModel(String tableName,
                            TableModel model)
                     throws SQLException

the table model is inserted based on the column order rather than column names. Note that null values within the table model will not work!

Throws:

SQLException

getTableNames

public String[] getTableNames()
                       throws SQLException

Throws:

SQLException

getViews

public String[] getViews()
                  throws SQLException

Throws:

SQLException

executeUpdate

public int executeUpdate(String sql)
                  throws SQLException

shortcut to perform an update via SQL statement

Throws:

SQLException

deleteRow

public int deleteRow(String tableName,
                     String idField,
                     Object idValue)
              throws SQLException

deletes all rows in the given table where the given field equals the given value

Throws:

SQLException

deleteRows

public int deleteRows(String tableName,
                      Map<String,Object> fieldRestrictions)
               throws SQLException

Throws:

SQLException

getRow

public HashMap<String,Object> getRow(String tableName,
                                     String idField,
                                     Object idValue)
                              throws SQLException

may return null if no row was found; returns the first occurrence if multiple rows exist

Throws:

SQLException

getRowItem

public RowItem getRowItem(String tableName,
                          String idField,
                          Object idValue)
                   throws SQLException

may return null if no row was found; returns the first occurrence if multiple rows exist

Throws:

SQLException

getTable

public ArrayList<HashMap<String,Object>> getTable(String tableName)
                                           throws SQLException

retrieves the entire table and returns it as a list of maps containing the field names mapped to their respective values

Throws:

SQLException

copyTable

public void copyTable(String origTable,
                      Connection target,
                      String copyTable,
                      boolean createTable)
               throws SQLException

creates a new table in Connection target corresponding to the given table in this Connection and transfers all data

Throws:

SQLException

copyTo

public void copyTo(Connection target,
                   boolean createTables)
            throws SQLException

copies all tables from one connection into the other connection

Throws:

SQLException

createTableString

public static String createTableString(ResultSetMetaData data,
                                       String tableName)
                                throws SQLException

creates the DDL statement that would create a table identical to the one denoted by the given meta data

Throws:

SQLException

getRowItems

public ArrayList<RowItem> getRowItems(String tableName,
                                      String idField,
                                      Filter<RowItem> filter)
                               throws SQLException

filter may be null

Throws:

SQLException

getRowItems

public ArrayList<RowItem> getRowItems(ResultSet rs,
                                      String idField,
                                      Filter<RowItem> filter)
                               throws SQLException

filter may be null

Throws:

SQLException

getRowItems

public ArrayList<RowItem> getRowItems(String tableName,
                                      String idField,
                                      Map<String,Object> fieldRestrictions)
                               throws SQLException

supports Range objects in the fieldRestrictions

Throws:

SQLException

See Also:

Range

extractRows

public ArrayList<HashMap<String,Object>> extractRows(ResultSet rs)
                                              throws SQLException

returns a map per row, where column names are mapped to their respective value. It remains the caller's responsability to close the ResultSet as well as the corresponding statement.

Throws:

SQLException

filterTable

public ResultSet filterTable(String tableName,
                             Map<String,Object> fieldRestrictions)
                      throws SQLException

returns all columns (*) that correspond to the given restrictions, where each key is tested to be equal to the value. After being done w/ the ResultSet, it should be closed along w/ the corresponding statement.

Throws:

SQLException

filterTableUpdateable

public ResultSet filterTableUpdateable(String tableName,
                                       Map<String,Object> fieldRestrictions)
                                throws SQLException

returns an updateable and scrollable ResultSet. After being done w/ the ResultSet, it should be closed along w/ the corresponding statement.

Throws:

SQLException

filterTable

public ResultSet filterTable(String tableName,
                             Map<String,Object> fieldRestrictions,
                             int resultSetType,
                             int resultSetConcurrency)
                      throws SQLException,
                             ClassCastException

the integer parameters correspond to the prepare statement for the connection After being done w/ the ResultSet, it should be closed along w/ the corresponding statement. The restriction map explicitly supports Range objects as values.

Throws:

ClassCastException - if a Range object doesn't receive the expected type

SQLException

See Also:

Range, Connection.prepareStatement(String, int, int)