com.antelmann.sql
Class ConnectionHelper

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

All Implemented Interfaces:

Wrapped<Connection>

Direct Known Subclasses:

MSAccessHelper, MySQLHelper, SQLServerHelper

public class ConnectionHelper
extends Object
implements Wrapped<Connection>

The class JDBC provides several useful methods for handling JDBC connections. Note that this class is not synchronized, i.e. the methods can work simultaneously on one shared Connection instance.

Author:

Holger Antelmann

See Also:

JDBCTableModel, SQLConstraint, SQLMapFilter, DefaultMappingInfo.getNameForType(int)

Constructor Summary

ConnectionHelper(Connection con)

Method Summary

void	addIndex(String table, String indexName, boolean unique, String... columns)
void	addOrderBy(Appendable q, String... orderByColumns) the given columns are used to append an 'order by' statement; if a column is preceded by a '-' sign, it will be converted to a descending sorting order Note that the columns must already be 'escaped' if required.
void	addPrimaryIndex(String table, String... columns)
void	addWhereClause(Appendable sql, List<Object> parameters, Map<String,?> restrictions) calls addWhereClause(sql, parameters, restrictions, true)
void	addWhereClause(Appendable sql, List<Object> parameters, Map<String,?> restrictions, boolean combineWithAndIfTrue) takes a given query, a given list of parameters (for binding the query to a PreparedStatement), a map of restrictions and processes the map so that it will be put into the query and the parameters.
void	addWhereClause(Appendable sql, List<Object> parameters, String[] textColumns, String... patternList) constructs a where-clause for a larger SQL-statement that searches for the given pattern in the given text columns
void	addWhereClauseOnSearchPattern(String[] patternList, Appendable sb, List<Object> params, Map<String,Integer> map, String... columnNames) allows to add full text search in all columns to an existing query; every pattern must be found in some column
void	addWhereClauseOnSearchPattern(String[] patternList, Appendable sb, List<Object> params, String table) allows to add full text search in all columns to an existing query; every pattern must be found in some column
boolean	contains(RowItem row) checks to see whether the given row can be found in this connection (based on the table and the id)
boolean	contains(String tableName, String columnName, Object value)
static Range<Date>	convertDateRangeToSQL(Range<Date> range) utility function to ease of use
static Range<Timestamp>	convertTimestampRangeToSQL(Range<Date> range) utility function to ease of use
int	copyIndices(String sourceTable, Connection target, String targetTable) returns the number of indices that were actually created; existing index names as well as 'primary' keys are skipped
boolean	copyPrimaryKey(String sourceTable, Connection target, String targetTable)
void	copyTable(String sourceTable, Connection target, String targetTable, boolean createTable) creates a new table in Connection target corresponding to the given table in this Connection and transfers all data
void	copyTable(String sourceTable, Connection target, String targetTable, boolean createTable, Monitor mon) 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
int	countDistinctValues(String table, String columnField, Map<String,?> restrictions)
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 columnField, Object columnValue) deletes all rows in the given table where the given field equals the given value
int	deleteRows(String tableName, Map<String,?> fieldRestrictions) the fieldRestrictions support Range, LikeCondition, NotNullCondition, IsNullCondition objects and Object[] as values
void	dropIndex(String table, String index)
String	escape(String columnOrTableName) escapes column and/or table names.
String[]	escape(String[] columnNames)
int	executeUpdate(String sql, Object... params) shortcut to perform an update via SQL statement
void	exportAsCSV(File file, Monitor mon, String sql, Object... params)
void	exportAsCSV(ResultSet rs, Writer writer, Monitor mon)
void	exportAsCSV(ResultSet rs, Writer writer, Monitor mon, boolean useQuotes, String fieldSeparator, String recordSeparator, boolean includeHeader) streams the given ResultSet to the given Writer; note that neither resource is closed within this call!
void	exportAsCSV(Writer writer, Monitor mon, String sql, Object... params)
HashMap<String,Object>	extractRow(ResultSet rs, String... columns) extracts the current row in the given ResultSet into a HashMap; if no columns are given, all columns will be included.
RowItem	extractRowItem(ResultSet rs, String idColumnName, String... columns) if no further column names are given, all columns will be included
ArrayList<HashMap<String,Object>>	extractRows(ResultSet rs, String... columns) returns a map per row, where column names are mapped to their respective value.
ResultSet	filterTable(String tableName, Map<String,?> 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,?> fieldRestrictions, boolean combineWithAndIfTrue) 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,?> fieldRestrictions, boolean combineWithAndIfTrue, int resultSetType, int resultSetConcurrency, String... orderByColumns) calls filterTable(String, String[], Map, boolean, int, int, String...)
ResultSet	filterTable(String tableName, Map<String,?> fieldRestrictions, int resultSetType, int resultSetConcurrency, String... orderByColumns) calls filterTable(tableName, fieldRestrictions, true, resultSetType, resultSetConcurrency, orderByColumns)
ResultSet	filterTable(String tableName, String[] columns, Map<String,?> fieldRestrictions, boolean combineWithAndIfTrue, int resultSetType, int resultSetConcurrency, String... orderByColumns) allows to easily filter a table or query After being done w/ the ResultSet, it should be closed along w/ the corresponding statement.
ResultSet	filterTable(String tableName, String columnName, Object value)
ResultSet	filterTableForText(int resultSetType, int resultSetConcurrency, String table, String[] patternList, String... restrictToColumn)
ResultSet	filterTableForText(String table, String... patternList) in order to be in the result table, every given pattern must occur somewhere in a column
ResultSet	filterTableForText(String table, String[] patternList, String... restrictToColumn)
ResultSet	filterTableScrollable(String tableName, Map<String,?> fieldRestrictions) returns all columns (*) that correspond to the given restrictions, where each key is tested to be equal to the value.
ResultSet	filterTableUpdateable(String tableName, Map<String,?> fieldRestrictions) returns an updateable and scrollable ResultSet.
Map<Object,Integer>	findDuplicates(String table, Map<String,?> restrictions, String... column)
ArrayList<RowItem>	findInTable(String tableName, String idColumn, String pattern, Monitor mon)
Object	getAggregatedFunctionValue(String table, String column, SQLFunction function, String method, Map<String,?> fieldRestrictions)
Object	getAggregatedFunctionValue(String table, String column, String function, String method, Map<String,?> fieldRestrictions) returns the value of the given method on the given field in the given table based on the given function; null is returned if no entries were found in the database.
Object	getAverageValue(String table, String columnField)
ArrayList<String>	getColumnNames(String table)
String[]	getCommonColumns(String... tables)
Connection	getConnection()
int	getCount(String table)
int	getCount(String table, Map<String,?> fieldRestrictions)
DateFormat	getDateFormat() if non-null, this format is used to convert between String and Date
String	getDBType()
HashSet<Object>	getDistinctValues(String table, String columnField) returns all distinct values of the given column from the given table based on a group by statement.
HashSet<Object>	getDistinctValues(String table, String columnField, Map<String,?> fieldRestrictions) returns all distinct values of the given column from the given table based on a group by statement.
HashMap<Object,Integer>	getDistinctValuesWithCount(String table, Map<String,?> fieldRestrictions, Filter<Integer> numberFilter, String... columnField) returns all distinct values of the given column from the given table with their count based on a group by statement.
HashMap<Object,Integer>	getDistinctValuesWithCount(String table, Map<String,?> fieldRestrictions, String... columnField) returns all distinct values of the given column from the given table with their count based on a group by statement.
int	getFetchSize() if other than 0 it is applied to the filter.. methods
protected ResultSet	getFirstRowFor(Statement stmt, String tableNameOrQueryString) first tries the 'top' syntax and then the 'limit' syntax to provide the result; if all fails, the complete ResultSet is produced.
TableModelWrapper	getForeignKeysModel(String table)
HashMap<String,SimpleEntry<String,String>>	getForeingKeys(String table)
Object	getFunctionValue(String table, String columnField, SQLFunction func)
Object	getFunctionValue(String table, String columnField, SQLFunction function, Map<String,?> fieldRestrictions)
Object	getFunctionValue(String table, String columnField, String function)
Object	getFunctionValue(String table, String columnField, String function, Map<String,?> fieldRestrictions) returns the value of the given field in the given table based on the given function; null is returned if no entries were found in the database.
Map<String,List<String>>	getIndicesForTable(String table, boolean uniqueOnly)
int	getMaxTextLength(String table, String textColumn, Map<String,?> restrictions)
Object	getMaxValue(String table, String columnField)
DefaultTableModel	getMetaDataAsTableModel(String tableName) Deprecated.
Object	getMinValue(String table, String columnField)
String[]	getPrimaryKeysForTable(String table) returns the column names of the primary key(s) for the given table.
Set<String>	getReservedWords() returns all reserved words including keywords, string and system functions
ResultSet	getResultSet(int resultSetType, int resultSetConcurrency, String sql, Object... params) allows to quickly generate the ResultSet for a query
ResultSet	getResultSet(String sql, Object... params) uses ResultSet.TYPE_FORWARD_ONLY and ResultSet.CONCUR_READ_ONLY
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; null is not allowed as an idValue.
ArrayList<RowItem>	getRowItems(ResultSet rs, String idField, Filter<? super RowItem> filter) filter may be null; the table name is obtained from the idField column
ArrayList<RowItem>	getRowItems(String tableName, ResultSet rs, String idField, Filter<? super RowItem> filter) allows to specify a tableName explicitly for the returned RowItem objects Note that the given ResultSet must still be closed after calling this method!
ArrayList<RowItem>	getRowItems(String tableName, String idField)
ArrayList<RowItem>	getRowItems(String tableName, String idField, Filter<? super RowItem> filter) filter may be null
ArrayList<RowItem>	getRowItems(String tableName, String idField, Map<String,?> 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,?> fieldRestrictions) returns a table model based on the table filtered by the given restrictions
String[]	getTableNames()
static String	getTypeNameFor(int type) returns the declared field name for the given Types value
Object	getValue(String tableName, String idColumn, Object idValue, String valueColumn) a simplified way to retrieve the value of a column by querying the table based on a candidate key
String[]	getViews()
void	importTableModel(String table, TableModel model)
void	insert(RowItem row)
int	insertRow(String tableName, Map<String,?> fieldValues)
int	insertRow(String tableName, Map<String,?> fieldValues, boolean ignoreInvalidColumns) 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	insertRowWithGeneratedKeys(String tableName, Map<String,?> fieldValues) inserts the given field values as a row into the given table of the given connection and returns the generated key
int	insertTableModel(String tableName, TableModel model, boolean batchMode) the table model is inserted based on the column order rather than column names.
static boolean	isCharType(int sqlType) returns true only if the given sqlType is for characters
static boolean	isDateType(int sqlType) returns true only if the given sqlType is for date/time
boolean	isMSAccess()
boolean	isMySQL()
static boolean	isNumberType(int sqlType) returns true only if the given sqlType is for numbers
static boolean	isSelect(String name) true only if the given string starts with 'select '
boolean	isSQLServer()
AbstractIterator<Map<String,Object>>	iterate(ResultSet rs) remove() and close() are properly implemented in the returned iterator
Object	javaToSql(Object obj, int sqlType, int size) supports a few standard types to ease translations between java and sql types.
HashMap<Object,Object>	loadAsMap(String table, String keyColumn, String valueColumn, Map<String,?> restrictions)
TableModelWrapper	loadTable(ResultSet rs, boolean closeAfterUse) allows to load a table for ResultSets that only support ResultSet.TYPE_FORWARD_ONLY
TableModelWrapper	loadTable(String table)
void	lockTable(boolean exclusive, String... tables) useful for early requiring locks to avoid deadlocks
Hashtable<String,String>	mapColumnNamesToDisplayNames(String tableNameOrQueryString) creates a special map to retrieve the label names from the column names as provided from the meta data
Hashtable<String,Integer>	mapColumnNamesToSize(ResultSet rs)
Hashtable<String,Integer>	mapColumnNamesToSize(ResultSetMetaData meta)
Hashtable<String,Integer>	mapColumnNamesToSize(String tableNameOrQueryString)
Hashtable<String,Integer>	mapColumnNamesToSQLTypes(ResultSetMetaData meta)
Hashtable<String,Integer>	mapColumnNamesToSQLTypes(String tableNameOrQueryString) creates a special map to retrieve the SQL data types from the column names as provided from the meta data of the connection
Hashtable<String,String>	mapColumnNamesToTypeString(ResultSetMetaData meta)
Hashtable<String,String>	mapColumnNamesToTypeString(String tableNameOrQueryString)
boolean	needsSelect(String name)
void	processColumn(String table, String column, Processor<Object,?> processor, Map<String,?> restrictions)
ResultSet	queryDuplicates(String table, String... duplicateColumns) finds records where the given columns appear more than once with their respective values
int	replace(String table, String column, Object oldValue, Object newValue)
void	searchDatabaseFor(String text, PrintStream ps, Monitor monitor) searches through the entire database for the given text anywhere in any column and prints hints into the given stream.
AbstractIterator<Object>	searchDuplicates(String table, String column, Map<String,?> restrictions)
void	setDateFormat(DateFormat dateFormat)
void	setDoNotEscapeNames()
void	setEscapeChars(char beginQuote, char endQuote) allows to explicitly set the escape chars used in escape(String) , so that the default mechanism does not need to determine the chars by accessing the meta data of the connection
void	setFetchSize(int fetchSize) Note that for MySQL you want to use Integer.MIN_VALUE if you do not want to load the entire ResultSet into memory
<T> T	sqlToJava(Object obj, Class<T> requiredJavaType) supports a few standard types to ease translations between sql and java types
int	transferTable(String sourceTable, Connection target, String targetTable, Monitor mon, Map<String,?> restrictions)
String	trimToLastDot(String name)
Connection	unwrap() returns the embedded instance that this wrapper encloses
void	update(RowItem row)
int	update(String table, String column, Object newValue, Map<String,?> restrictions)
int	update(String table, String idColumn, Object idValue, String valueColumn, Object newValue)
<T extends Exception> int	updateRows(Processor<ResultSet,T> updater, String table, Map<String,?> fieldRestrictions, boolean combineWithAndIfTrue, Monitor mon)
int	updateRows(String tableName, String idField, Object idValue, Map<String,?> fieldValues) updates the rows in the given table by applying all of the given field values.
int	updateRows(String tableName, String idField, Object idValue, Map<String,?> fieldValues, boolean ignoreInvalidColumns) updates all rows in the given table corresponding to the idValue by applying the given field values.
void	updateTable(String table, String idColumn, TableModel model)

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

getFetchSize

public int getFetchSize()

if other than 0 it is applied to the filter.. methods

See Also:

setFetchSize(int)

setFetchSize

public void setFetchSize(int fetchSize)

Note that for MySQL you want to use Integer.MIN_VALUE if you do not want to load the entire ResultSet into memory

getConnection

public Connection getConnection()

unwrap

public final Connection unwrap()

Description copied from interface: Wrapped

returns the embedded instance that this wrapper encloses

Specified by:

unwrap in interface Wrapped<Connection>

getDBType

public String getDBType()

isMySQL

public boolean isMySQL()

isSQLServer

public boolean isSQLServer()

isMSAccess

public boolean isMSAccess()

setEscapeChars

public void setEscapeChars(char beginQuote,
                           char endQuote)

allows to explicitly set the escape chars used in escape(String) , so that the default mechanism does not need to determine the chars by accessing the meta data of the connection

See Also:

escape(String)

setDoNotEscapeNames

public void setDoNotEscapeNames()

escape

public String escape(String columnOrTableName)

escapes column and/or table names. Currently, this implementation only supports MS Access, MS SQL Server, MySQL automatically

See Also:

setEscapeChars(char, char)

escape

public String[] escape(String[] columnNames)

lockTable

@Todo(value="currently, this implementation does nothing")
public void lockTable(boolean exclusive,
                                String... tables)
               throws SQLException

useful for early requiring locks to avoid deadlocks

Throws:

SQLException

getTypeNameFor

public static String getTypeNameFor(int type)

returns the declared field name for the given Types value

needsSelect

public boolean needsSelect(String name)

isSelect

public static boolean isSelect(String name)

true only if the given string starts with 'select '

getReservedWords

public Set<String> getReservedWords()
                             throws SQLException

returns all reserved words including keywords, string and system functions

Throws:

SQLException

getPrimaryKeysForTable

public String[] getPrimaryKeysForTable(String table)
                                throws SQLException

returns the column names of the primary key(s) for the given table. If no primary key exists, an empty array is returned.

Throws:

SQLException

getIndicesForTable

public Map<String,List<String>> getIndicesForTable(String table,
                                                   boolean uniqueOnly)
                                            throws SQLException

Throws:

SQLException

addIndex

public void addIndex(String table,
                     String indexName,
                     boolean unique,
                     String... columns)
              throws SQLException

Throws:

SQLException

trimToLastDot

public String trimToLastDot(String name)

addPrimaryIndex

public void addPrimaryIndex(String table,
                            String... columns)
                     throws SQLException

Throws:

SQLException

dropIndex

public void dropIndex(String table,
                      String index)
               throws SQLException

Throws:

SQLException

copyPrimaryKey

public boolean copyPrimaryKey(String sourceTable,
                              Connection target,
                              String targetTable)
                       throws SQLException

Throws:

SQLException

copyIndices

public int copyIndices(String sourceTable,
                       Connection target,
                       String targetTable)
                throws SQLException

returns the number of indices that were actually created; existing index names as well as 'primary' keys are skipped

Throws:

SQLException

getForeingKeys

public HashMap<String,SimpleEntry<String,String>> getForeingKeys(String table)
                                                          throws SQLException

Returns:

a map that maps the column name of the given table to an entry with a key that denotes the foreign table and a value that denotes the foreign column

Throws:

SQLException

getForeignKeysModel

public TableModelWrapper getForeignKeysModel(String table)
                                      throws SQLException

Throws:

SQLException

loadAsMap

public HashMap<Object,Object> loadAsMap(String table,
                                        String keyColumn,
                                        String valueColumn,
                                        Map<String,?> restrictions)
                                 throws SQLException

Throws:

SQLException

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

getCount

public int getCount(String table,
                    Map<String,?> fieldRestrictions)
             throws SQLException

Throws:

SQLException

getFunctionValue

public Object getFunctionValue(String table,
                               String columnField,
                               SQLFunction func)
                        throws SQLException

Throws:

SQLException

getFunctionValue

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

Throws:

SQLException

getFunctionValue

public Object getFunctionValue(String table,
                               String columnField,
                               SQLFunction function,
                               Map<String,?> fieldRestrictions)
                        throws SQLException

Throws:

SQLException

getFunctionValue

public Object getFunctionValue(String table,
                               String columnField,
                               String function,
                               Map<String,?> fieldRestrictions)
                        throws SQLException

returns the value of the given field in the given table based on the given function; null is returned if no entries were found in the database. The fieldRestrictions support Range, NotNullCondition, LikeCondition and Object[] objects as values. The return value represents the value found in the given field column.

Throws:

SQLException

getAggregatedFunctionValue

public Object getAggregatedFunctionValue(String table,
                                         String column,
                                         SQLFunction function,
                                         String method,
                                         Map<String,?> fieldRestrictions)
                                  throws SQLException

Throws:

SQLException

getAggregatedFunctionValue

public Object getAggregatedFunctionValue(String table,
                                         String column,
                                         String function,
                                         String method,
                                         Map<String,?> fieldRestrictions)
                                  throws SQLException

returns the value of the given method on the given field in the given table based on the given function; null is returned if no entries were found in the database. The fieldRestrictions support Range, NotNullCondition, LikeCondition and Object[] objects as values. The return value represents the aggregated value based on the method applied onto the given column.

Parameters:

table - the table to be queried (this may also be a select statement)

column - the relevant column to be looked at

function - the aggregation function to be used

method - allows to specify a method to be performed on the column (like 'len'); this may be null

fieldRestrictions - a restriction map that allows to filter the data

Throws:

SQLException

getMaxTextLength

public int getMaxTextLength(String table,
                            String textColumn,
                            Map<String,?> restrictions)
                     throws SQLException

Throws:

SQLException

getDistinctValues

public HashSet<Object> getDistinctValues(String table,
                                         String columnField)
                                  throws SQLException

returns all distinct values of the given column from the given table based on a group by statement. Note that the returned set may contain NULL as a value.

Throws:

SQLException

getDistinctValues

public HashSet<Object> getDistinctValues(String table,
                                         String columnField,
                                         Map<String,?> fieldRestrictions)
                                  throws SQLException

returns all distinct values of the given column from the given table based on a group by statement. The fieldRestrictions support Range, NotNullCondition, IsNullCondition, LikeCondition and Object[] objects as values. Note that the returned set may contain NULL as a value.

Throws:

SQLException

countDistinctValues

public int countDistinctValues(String table,
                               String columnField,
                               Map<String,?> restrictions)
                        throws SQLException

Throws:

SQLException

replace

public int replace(String table,
                   String column,
                   Object oldValue,
                   Object newValue)
            throws SQLException

Throws:

SQLException

update

public int update(String table,
                  String idColumn,
                  Object idValue,
                  String valueColumn,
                  Object newValue)
           throws SQLException

Throws:

SQLException

update

public int update(String table,
                  String column,
                  Object newValue,
                  Map<String,?> restrictions)
           throws SQLException

Throws:

SQLException

getDistinctValuesWithCount

public HashMap<Object,Integer> getDistinctValuesWithCount(String table,
                                                          Map<String,?> fieldRestrictions,
                                                          String... columnField)
                                                   throws SQLException

returns all distinct values of the given column from the given table with their count based on a group by statement. The fieldRestrictions support Range, NotNullCondition, IsNullCondition, LikeCondition and Object[] objects as values. Note that the returned set may contain NULL as a value. Note also, that if a columnFiled contains NULL-values, the count for NULL is always 0.

Throws:

SQLException

getDistinctValuesWithCount

public HashMap<Object,Integer> getDistinctValuesWithCount(String table,
                                                          Map<String,?> fieldRestrictions,
                                                          Filter<Integer> numberFilter,
                                                          String... columnField)
                                                   throws SQLException

returns all distinct values of the given column from the given table with their count based on a group by statement. The fieldRestrictions support Range, NotNullCondition, IsNullCondition, LikeCondition and Object[] objects as values. The numberFilter allows to only have those included, where the count passes the filter, which allows e.g. to return only those that exist more than once. Note that the returned set may contain NULL as a value. Note also, that if a columnFiled contains NULL-values, the count for NULL is always 0.

Throws:

SQLException

queryDuplicates

public ResultSet queryDuplicates(String table,
                                 String... duplicateColumns)
                          throws SQLException

finds records where the given columns appear more than once with their respective values

Throws:

SQLException

See Also:

SubSelectCondition.createDuplicateConstraint(String, String...)

findDuplicates

public Map<Object,Integer> findDuplicates(String table,
                                          Map<String,?> restrictions,
                                          String... column)
                                   throws SQLException

Throws:

SQLException

searchDuplicates

public AbstractIterator<Object> searchDuplicates(String table,
                                                 String column,
                                                 Map<String,?> restrictions)
                                          throws SQLException

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.

Returns:

the row count for the insertRow statement

Throws:

SQLException

See Also:

insertRow(String, Map)

insertRow

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

Throws:

SQLException

insertRow

public int insertRow(String tableName,
                     Map<String,?> fieldValues,
                     boolean ignoreInvalidColumns)
              throws SQLException

inserts the given field values as a row into the given table of the given connection. (Outdated: previously, 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 and ignored, so that this method relies on the default values of the database to actually put a null value there.) This implementation supports InputStreams and Readers as fieldValues values.

Parameters:

ignoreInvalidColumns - if true, invalid column names are ignored as well as setting invalid values on valid columns

Returns:

the row count for the insertRow statement

Throws:

SQLException

insertRowWithGeneratedKeys

public int insertRowWithGeneratedKeys(String tableName,
                                      Map<String,?> fieldValues)
                               throws SQLException

inserts the given field values as a row into the given table of the given connection and returns the generated key

Returns:

the row count for the insertRow statement

Throws:

SQLException

getTableModel

public ResultSetTableModel getTableModel(String tableName,
                                         String idColumnName,
                                         Map<String,?> 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

loadTable

public TableModelWrapper loadTable(String table)
                            throws SQLException

Throws:

SQLException

See Also:

loadTable(ResultSet, boolean)

loadTable

public TableModelWrapper loadTable(ResultSet rs,
                                   boolean closeAfterUse)
                            throws SQLException

allows to load a table for ResultSets that only support ResultSet.TYPE_FORWARD_ONLY

Returns:

a (read-only) TableModelWrapper that wraps a DefaultTableModel

Throws:

SQLException

importTableModel

public void importTableModel(String table,
                             TableModel model)
                      throws SQLException

Throws:

SQLException

updateTable

public void updateTable(String table,
                        String idColumn,
                        TableModel model)
                 throws IllegalArgumentException,
                        SQLException

Throws:

IllegalArgumentException

SQLException

updateRows

public int updateRows(String tableName,
                      String idField,
                      Object idValue,
                      Map<String,?> fieldValues)
               throws SQLException,
                      IllegalArgumentException

updates the rows in the given table by applying all of the given field values. If the given idField is the primary key for the table, only one row is updated.

Returns:

the number of rows updated (at least 1, since if no row is found, IllegalArgumentException is thrown)

Throws:

SQLException - if any fieldValues cannot be stored.

IllegalArgumentException - if no row has been found to be updated

updateRows

public int updateRows(String tableName,
                      String idField,
                      Object idValue,
                      Map<String,?> fieldValues,
                      boolean ignoreInvalidColumns)
               throws SQLException,
                      IllegalArgumentException

updates all rows in the given table corresponding to the idValue by applying the given field values. If the given idField is the primary key for the table, only one row is updated. 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. This implementation also supports InputStreams and Readers as values.

Returns:

the number of rows updated (at least 1, since if no row is found, IllegalArgumentException is thrown)

Throws:

IllegalArgumentException - if no row has been found to be updated.

SQLException

contains

public boolean contains(RowItem row)
                 throws SQLException

checks to see whether the given row can be found in this connection (based on the table and the id)

Throws:

SQLException

contains

public boolean contains(String tableName,
                        String columnName,
                        Object value)
                 throws SQLException

Throws:

SQLException

update

public void update(RowItem row)
            throws SQLException,
                   IllegalArgumentException

Throws:

SQLException

IllegalArgumentException

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,
                            boolean batchMode)
                     throws SQLException

the table model is inserted based on the column order rather than column names. Note that null values within the table model may not work for all columns on certain drivers!

Throws:

SQLException

getColumnNames

public ArrayList<String> getColumnNames(String table)
                                 throws SQLException

Throws:

SQLException

getTableNames

public String[] getTableNames()
                       throws SQLException

Throws:

SQLException

getViews

public String[] getViews()
                  throws SQLException

Throws:

SQLException

executeUpdate

public int executeUpdate(String sql,
                         Object... params)
                  throws SQLException

shortcut to perform an update via SQL statement

Throws:

SQLException

deleteRow

public int deleteRow(String tableName,
                     String columnField,
                     Object columnValue)
              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,?> fieldRestrictions)
               throws SQLException

the fieldRestrictions support Range, LikeCondition, NotNullCondition, IsNullCondition objects and Object[] as values

Throws:

SQLException

See Also:

Range, SQLConstraint

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; null is not allowed as an idValue.

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 sourceTable,
                      Connection target,
                      String targetTable,
                      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

copyTable

public void copyTable(String sourceTable,
                      Connection target,
                      String targetTable,
                      boolean createTable,
                      Monitor mon)
               throws SQLException

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

Throws:

SQLException

transferTable

public int transferTable(String sourceTable,
                         Connection target,
                         String targetTable,
                         Monitor mon,
                         Map<String,?> restrictions)
                  throws SQLException

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 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

iterate

public AbstractIterator<Map<String,Object>> iterate(ResultSet rs)
                                             throws SQLException

remove() and close() are properly implemented in the returned iterator

Throws:

SQLException

getRowItems

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

Throws:

SQLException

getRowItems

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

filter may be null

Throws:

SQLException

getRowItems

@Warning(value="ResultSet is NOT automatically closed within this method!")
public ArrayList<RowItem> getRowItems(ResultSet rs,
                                                   String idField,
                                                   Filter<? super RowItem> filter)
                               throws SQLException

filter may be null; the table name is obtained from the idField column

Throws:

SQLException

getRowItems

@Warning(value="ResultSet is NOT automatically closed within this method!")
public ArrayList<RowItem> getRowItems(String tableName,
                                                   ResultSet rs,
                                                   String idField,
                                                   Filter<? super RowItem> filter)
                               throws SQLException

allows to specify a tableName explicitly for the returned RowItem objects Note that the given ResultSet must still be closed after calling this method!

Throws:

SQLException

getRowItems

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

supports Range objects in the fieldRestrictions

Throws:

SQLException

See Also:

Range

extractRows

@Warning(value="ResultSet is NOT automatically closed within this method!")
public ArrayList<HashMap<String,Object>> extractRows(ResultSet rs,
                                                                  String... columns)
                                              throws SQLException

returns a map per row, where column names are mapped to their respective value. It remains the caller's responsibility to close the ResultSet as well as the corresponding statement. This method is not the most effective way of retrieving the data, as the access to the meta data is quite inefficient in this case; regard this only as a convenience method.

Throws:

SQLException

extractRow

@Warning(value="ResultSet is NOT automatically closed within this method!")
public HashMap<String,Object> extractRow(ResultSet rs,
                                                      String... columns)
                                  throws SQLException

extracts the current row in the given ResultSet into a HashMap; if no columns are given, all columns will be included.

Throws:

SQLException

extractRowItem

@Warning(value="ResultSet is NOT automatically closed within this method!")
public RowItem extractRowItem(ResultSet rs,
                                           String idColumnName,
                                           String... columns)
                       throws SQLException,
                              IllegalArgumentException

if no further column names are given, all columns will be included

Throws:

SQLException

IllegalArgumentException

getValue

public Object getValue(String tableName,
                       String idColumn,
                       Object idValue,
                       String valueColumn)
                throws SQLException,
                       IllegalArgumentException

a simplified way to retrieve the value of a column by querying the table based on a candidate key

Throws:

IllegalArgumentException - if the idValue was not found in the table

SQLException

filterTable

public ResultSet filterTable(String tableName,
                             String columnName,
                             Object value)
                      throws SQLException

Throws:

SQLException

filterTable

public ResultSet filterTable(String tableName,
                             Map<String,?> 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

filterTable

public ResultSet filterTable(String tableName,
                             Map<String,?> fieldRestrictions,
                             boolean combineWithAndIfTrue)
                      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

filterTableScrollable

public ResultSet filterTableScrollable(String tableName,
                                       Map<String,?> 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.

Returns:

a scrollable, read-only ResultSet

Throws:

SQLException

filterTableUpdateable

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

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

Returns:

a scrollable, updateable ResultSet

Throws:

SQLException

filterTable

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

calls filterTable(tableName, fieldRestrictions, true, resultSetType, resultSetConcurrency, orderByColumns)

Throws:

SQLException

ClassCastException

See Also:

filterTable(String, Map, boolean, int, int, String...)

filterTable

public ResultSet filterTable(String tableName,
                             Map<String,?> fieldRestrictions,
                             boolean combineWithAndIfTrue,
                             int resultSetType,
                             int resultSetConcurrency,
                             String... orderByColumns)
                      throws SQLException,
                             ClassCastException

calls filterTable(String, String[], Map, boolean, int, int, String...) with null for columns, so that '*' is used

Throws:

SQLException

ClassCastException

updateRows

public <T extends Exception> int updateRows(Processor<ResultSet,T> updater,
                                            String table,
                                            Map<String,?> fieldRestrictions,
                                            boolean combineWithAndIfTrue,
                                            Monitor mon)
               throws SQLException,
                      T extends Exception,
                      CancellationWarning

Throws:

SQLException

T extends Exception

CancellationWarning

processColumn

public void processColumn(String table,
                          String column,
                          Processor<Object,?> processor,
                          Map<String,?> restrictions)
                   throws SQLException

Throws:

SQLException

filterTable

public ResultSet filterTable(String tableName,
                             String[] columns,
                             Map<String,?> fieldRestrictions,
                             boolean combineWithAndIfTrue,
                             int resultSetType,
                             int resultSetConcurrency,
                             String... orderByColumns)
                      throws SQLException,
                             ClassCastException

allows to easily filter a table or query After being done w/ the ResultSet, it should be closed along w/ the corresponding statement. The restriction map explicitly supports Range, LikeCondition, NotNullCondition, IsNullCondition objects and also Object[] (for in(..) statements) as values. To sort columns in a descending order, use a minus sign before the column name.

Parameters:

tableName - denotes the name of the table; if the String starts with 'select ', then the tableName is taken as a query string and the 'columns'-parameter is ignored, as it is expected to be already part of the select statement

columns - if non-null and with a length of at least 1, the select statement is not using '*' but the given columns instead

resultSetType - corresponds to the equivalent parameter in preparing a statement for a connection

resultSetConcurrency - corresponds to the equivalent parameter in preparing a statement for a connection

Throws:

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

SQLException

See Also:

Range, SQLConstraint, Connection.prepareStatement(String, int, int), getResultSet(int, int, String, Object...)

getResultSet

public ResultSet getResultSet(String sql,
                              Object... params)
                       throws SQLException

uses ResultSet.TYPE_FORWARD_ONLY and ResultSet.CONCUR_READ_ONLY

Throws:

SQLException

See Also:

getResultSet(int, int, String, Object...)

getResultSet

public ResultSet getResultSet(int resultSetType,
                              int resultSetConcurrency,
                              String sql,
                              Object... params)
                       throws SQLException

allows to quickly generate the ResultSet for a query

Parameters:

sql - specifies the sql statement to be executed (may contain question marks that are resolved via parameters)

resultSetType - corresponds to the equivalent parameter in preparing a statement for a connection

resultSetConcurrency - corresponds to the equivalent parameter in preparing a statement for a connection

Throws:

SQLException

See Also:

Connection.prepareStatement(String, int, int), filterTable(String, String[], Map, boolean, int, int, String...)

addOrderBy

public void addOrderBy(Appendable q,
                       String... orderByColumns)

the given columns are used to append an 'order by' statement; if a column is preceded by a '-' sign, it will be converted to a descending sorting order Note that the columns must already be 'escaped' if required.

addWhereClause

public void addWhereClause(Appendable sql,
                           List<Object> parameters,
                           Map<String,?> restrictions)

calls addWhereClause(sql, parameters, restrictions, true)

See Also:

addWhereClause(Appendable, List, Map, boolean)

addWhereClause

public void addWhereClause(Appendable sql,
                           List<Object> parameters,
                           Map<String,?> restrictions,
                           boolean combineWithAndIfTrue)

takes a given query, a given list of parameters (for binding the query to a PreparedStatement), a map of restrictions and processes the map so that it will be put into the query and the parameters. Note that the SQL-keyword 'where' itself is not added to the query string, so that this method could be called multiple times on the same query. All restrictions will be combined using 'and' or 'or' depending on the last parameter. The restriction map supports several special values, such as subclasses of SQLConstraint and also Object arrays (which will be translated into an in(..) statement) even in combination with NotCondition instances. Range and LikeCondition objects are supported as restriction values. The values itself are only represented as question marks within the String, i.e. they still need to be replaced by the methods of PreparedStatement. This method will add all required parameters to be bound to the given list of parameters.

Parameters:

sql - contains the current query string as it stands; this method will add to it

parameters - the current parameters that are to be set for binding the query string to those; this method will add to it

restrictions - the map of restrictions, where a column name is mapped to its restriction

combineWithAndIfTrue - if true, multiple restrictions are combined with 'and', otherwise with 'or'

See Also:

Range, SQLConstraint

addWhereClause

public void addWhereClause(Appendable sql,
                           List<Object> parameters,
                           String[] textColumns,
                           String... patternList)

constructs a where-clause for a larger SQL-statement that searches for the given pattern in the given text columns

Parameters:

sql - contains the current query string as it stands; this method will add to it

parameters - the current parameters that are to be set for binding the query string to those; this method will add to it

textColumns - the text columns that are to be searched for the given pattern

patternList - the pattern of which each has to be found in at least one of the given columns

getFirstRowFor

protected ResultSet getFirstRowFor(Statement stmt,
                                   String tableNameOrQueryString)
                            throws SQLException

first tries the 'top' syntax and then the 'limit' syntax to provide the result; if all fails, the complete ResultSet is produced.

Throws:

SQLException

mapColumnNamesToDisplayNames

public Hashtable<String,String> mapColumnNamesToDisplayNames(String tableNameOrQueryString)
                                                      throws SQLException

creates a special map to retrieve the label names from the column names as provided from the meta data

Returns:

a Hashtable mapping the column names to their respective label names

Throws:

SQLException

mapColumnNamesToSQLTypes

public Hashtable<String,Integer> mapColumnNamesToSQLTypes(String tableNameOrQueryString)
                                                   throws SQLException

creates a special map to retrieve the SQL data types from the column names as provided from the meta data of the connection

Parameters:

tableNameOrQueryString -

Returns:

a Hashtable mapping the column names to their respective SQL data types

Throws:

SQLException

mapColumnNamesToSQLTypes

public Hashtable<String,Integer> mapColumnNamesToSQLTypes(ResultSetMetaData meta)
                                                   throws SQLException

Throws:

SQLException

mapColumnNamesToSize

public Hashtable<String,Integer> mapColumnNamesToSize(String tableNameOrQueryString)
                                               throws SQLException

Throws:

SQLException

mapColumnNamesToSize

public Hashtable<String,Integer> mapColumnNamesToSize(ResultSet rs)
                                               throws SQLException

Throws:

SQLException

mapColumnNamesToSize

public Hashtable<String,Integer> mapColumnNamesToSize(ResultSetMetaData meta)
                                               throws SQLException

Throws:

SQLException

mapColumnNamesToTypeString

public Hashtable<String,String> mapColumnNamesToTypeString(String tableNameOrQueryString)
                                                    throws SQLException

Throws:

SQLException

mapColumnNamesToTypeString

public Hashtable<String,String> mapColumnNamesToTypeString(ResultSetMetaData meta)
                                                    throws SQLException

Throws:

SQLException

getMetaDataAsTableModel

@Deprecated
public DefaultTableModel getMetaDataAsTableModel(String tableName)
                                          throws SQLException

Deprecated.

Throws:

SQLException

filterTableForText

public ResultSet filterTableForText(String table,
                                    String... patternList)
                             throws SQLException

in order to be in the result table, every given pattern must occur somewhere in a column

Throws:

SQLException

filterTableForText

public ResultSet filterTableForText(String table,
                                    String[] patternList,
                                    String... restrictToColumn)
                             throws SQLException

Throws:

SQLException

filterTableForText

public ResultSet filterTableForText(int resultSetType,
                                    int resultSetConcurrency,
                                    String table,
                                    String[] patternList,
                                    String... restrictToColumn)
                             throws SQLException

Throws:

SQLException

addWhereClauseOnSearchPattern

public void addWhereClauseOnSearchPattern(String[] patternList,
                                          Appendable sb,
                                          List<Object> params,
                                          String table)
                                   throws SQLException

allows to add full text search in all columns to an existing query; every pattern must be found in some column

Parameters:

patternList - the patterns that are to be searched for

sb - contains the query string this method will add to

params - contains the parameters for the PreparedStatement which are filled in parallel to the query string

table - the name of the table to be searched

Throws:

SQLException

addWhereClauseOnSearchPattern

public void addWhereClauseOnSearchPattern(String[] patternList,
                                          Appendable sb,
                                          List<Object> params,
                                          Map<String,Integer> map,
                                          String... columnNames)

allows to add full text search in all columns to an existing query; every pattern must be found in some column

Parameters:

patternList - the patterns that are to be searched for

sb - contains the query string this method will add to

params - contains the parameters for the PreparedStatement which are filled in parallel to the query string

map - the meta data of the relevant query in a map that maps the column name to its corresponding SQL type (can be easily obtained via mapColumnNamesToSQLTypes(String))

columnNames - if one or more column names are given, the search is only conducted in these columns

See Also:

mapColumnNamesToSQLTypes(ResultSetMetaData), mapColumnNamesToSQLTypes(String)

getDateFormat

public DateFormat getDateFormat()

if non-null, this format is used to convert between String and Date

See Also:

javaToSql(Object, int, int), sqlToJava(Object, Class), setDateFormat(DateFormat)

setDateFormat

public void setDateFormat(DateFormat dateFormat)

See Also:

getDateFormat()

sqlToJava

public <T> T sqlToJava(Object obj,
                       Class<T> requiredJavaType)

supports a few standard types to ease translations between sql and java types

See Also:

javaToSql(Object, int, int)

javaToSql

public Object javaToSql(Object obj,
                        int sqlType,
                        int size)

supports a few standard types to ease translations between java and sql types. Objects that are supported SQLConstraint are left intact as they are supported in generating where-clauses; if they are used as values for insert/update, they will create an error.

See Also:

addWhereClause(Appendable, List, Map, boolean), sqlToJava(Object, Class)

findInTable

public ArrayList<RowItem> findInTable(String tableName,
                                      String idColumn,
                                      String pattern,
                                      Monitor mon)
                               throws SQLException

Throws:

SQLException

searchDatabaseFor

public void searchDatabaseFor(String text,
                              PrintStream ps,
                              Monitor monitor)
                       throws SQLException

searches through the entire database for the given text anywhere in any column and prints hints into the given stream. The monitor simply allows to cancel the operation at any time.

Throws:

SQLException

getCommonColumns

public String[] getCommonColumns(String... tables)
                          throws SQLException

Throws:

SQLException

exportAsCSV

public void exportAsCSV(File file,
                        Monitor mon,
                        String sql,
                        Object... params)
                 throws SQLException,
                        IOException

Throws:

SQLException

IOException

exportAsCSV

public void exportAsCSV(Writer writer,
                        Monitor mon,
                        String sql,
                        Object... params)
                 throws SQLException,
                        IOException

Throws:

SQLException

IOException

exportAsCSV

@Warning(value="ResultSet is NOT automatically closed within this method!")
public void exportAsCSV(ResultSet rs,
                                     Writer writer,
                                     Monitor mon)
                 throws SQLException,
                        IOException

Throws:

SQLException

IOException

exportAsCSV

@Warning(value="ResultSet is NOT automatically closed within this method!")
public void exportAsCSV(ResultSet rs,
                                     Writer writer,
                                     Monitor mon,
                                     boolean useQuotes,
                                     String fieldSeparator,
                                     String recordSeparator,
                                     boolean includeHeader)
                 throws SQLException,
                        IOException

streams the given ResultSet to the given Writer; note that neither resource is closed within this call!

Parameters:

mon - used to monitor the progress; note, however, that the min/max number is not automatically set!

Throws:

SQLException

IOException

isDateType

public static boolean isDateType(int sqlType)

returns true only if the given sqlType is for date/time

See Also:

Types

isCharType

public static boolean isCharType(int sqlType)

returns true only if the given sqlType is for characters

See Also:

Types

isNumberType

public static boolean isNumberType(int sqlType)

returns true only if the given sqlType is for numbers

See Also:

Types

convertTimestampRangeToSQL

public static Range<Timestamp> convertTimestampRangeToSQL(Range<Date> range)

utility function to ease of use

convertDateRangeToSQL

public static Range<Date> convertDateRangeToSQL(Range<Date> range)

utility function to ease of use