weave.utils

Class SQLUtils

java.lang.Object

weave.utils.SQLUtils

public class SQLUtils
extends java.lang.Object

SQLUtils

Author:

Andy Dufilie, Andrew Wilkinson, Kyle Monico, Yen-Fu Luo, Philip Kovac, Patrick Stickney, John Fallon

Nested Class Summary

Nested Classes

Modifier and Type	Class and Description
static class	SQLUtils.CompareMode Specifies how two SQL terms should be compared
static class	SQLUtils.WhereClause<V>
static class	SQLUtils.WhereClauseBuilder<V>

Field Summary

Fields

Modifier and Type	Field and Description
static java.lang.String	DEFAULT_SQLITE_DATABASE
static java.lang.String	MYSQL
static java.lang.String	ORACLE
static java.lang.String	POSTGRESQL
static java.lang.String	SQLITE
static java.lang.String	SQLSERVER
static char	WILDCARD_ESCAPE The escape character (backslash) used by convertWildcards() and getLikeEscapeClause()

Constructor Summary

Constructors

Constructor and Description
SQLUtils()

Method Summary

Methods

Modifier and Type	Method and Description
static void	addColumn(java.sql.Connection conn, java.lang.String schemaName, java.lang.String tableName, java.lang.String columnName, java.lang.String columnType)
static void	addForeignKey(java.sql.Connection conn, java.lang.String schemaName, java.lang.String tableName, java.lang.String keyName, java.lang.String targetTable, java.lang.String targetKey)
static java.lang.String	binarySQLType(java.lang.String dbms) This function returns the name of a binary data type that can be used in SQL queries.
static java.lang.String	caseSensitiveCompare(java.sql.Connection conn, java.lang.String expr1, java.lang.String expr2) This will build a case sensitive compare expression out of two sql query expressions.
static void	cleanup(java.sql.Connection obj)
static void	cleanup(java.sql.ResultSet obj)
static void	cleanup(java.sql.Statement obj)
static boolean	connectionIsValid(java.sql.Connection conn) This function tests if a given Connection is valid, and closes the connection if it is not.
protected static <T> T	constrainOracleQueryParam(T param)
protected static <T> void	constrainQueryParams(java.sql.Connection conn, java.util.List<T> params)
protected static <T> void	constrainQueryParams(java.sql.Connection conn, T[] params)
static java.lang.String	convertWildcards(java.lang.String searchString) Converts a search string which uses basic '?' and '*' wildcards into an equivalent SQL search string.
static void	createIndex(java.sql.Connection conn, java.lang.String schemaName, java.lang.String tableName, java.lang.String[] columnNames)
static void	createIndex(java.sql.Connection conn, java.lang.String schemaName, java.lang.String tableName, java.lang.String[] columnNames, java.lang.Integer[] columnLengths)
static void	createSchema(java.sql.Connection conn, java.lang.String schema) Creates a schema in a non-SQLite database.
static void	createTable(java.sql.Connection conn, java.lang.String schemaName, java.lang.String tableName, java.util.List<java.lang.String> columnNames, java.util.List<java.lang.String> columnTypes, java.util.List<java.lang.String> primaryKeyColumns)
static <V> int	deleteRows(java.sql.Connection conn, java.lang.String schemaName, java.lang.String tableName, SQLUtils.WhereClause<V> where) This function will delete from a table the rows that have a specified set of column values.
static void	dropTableIfExists(java.sql.Connection conn, java.lang.String schema, java.lang.String table)
static int	executeUpdate(java.sql.Connection connection, java.lang.String query)
static int	executeUpdate(java.sql.Connection conn, java.lang.String query, java.lang.Object[] params)
static java.lang.String	fixColumnName(java.lang.String colName, java.lang.String suffix)
static java.lang.String	getBigIntTypeString(java.sql.Connection conn)
static java.util.List<java.lang.String>	getColumn(java.sql.Connection conn, java.lang.String schemaName, java.lang.String tableName, java.lang.String columnArg)
static java.lang.String[]	getColumnNamesFromResultSet(java.sql.ResultSet rs)
static java.util.List<java.lang.String>	getColumns(java.sql.Connection conn, java.lang.String schemaName, java.lang.String tableName)
static java.sql.Connection	getConnection(java.lang.String connectString)
static java.sql.Connection	getConnection(java.lang.String connectString, java.lang.String user, java.lang.String pass)
static java.lang.String	getConnectString(java.lang.String dbms, java.lang.String ip, java.lang.String port, java.lang.String database, java.lang.String user, java.lang.String pass)
protected static java.lang.String	getCSVNullValue(java.sql.Connection conn)
static java.lang.String	getDateTimeTypeString(java.sql.Connection conn)
static java.lang.String	getDbmsFromConnection(java.sql.Connection conn)
static java.lang.String	getDbmsFromConnectString(java.lang.String connectString)
static java.lang.String	getDoubleTypeString(java.sql.Connection conn)
static java.lang.String	getDriver(java.lang.String dbms)
static java.util.List<java.lang.Integer>	getIntColumn(java.sql.Connection conn, java.lang.String schemaName, java.lang.String tableName, java.lang.String columnArg)
static java.lang.String	getIntTypeString(java.sql.Connection conn)
static java.lang.String	getLikeEscapeClause(java.sql.Connection conn) Returns an ESCAPE clause for use with a LIKE comparison.
static java.lang.String	getLongBlobTypeString(java.sql.Connection conn)
static <VALUE_TYPE> java.util.List<java.util.Map<java.lang.String,VALUE_TYPE>>	getRecordsFromQuery(java.sql.Connection conn, java.util.List<java.lang.String> selectColumns, java.lang.String fromSchema, java.lang.String fromTable, SQLUtils.WhereClause<VALUE_TYPE> where, java.lang.String orderBy, java.lang.Class<VALUE_TYPE> valueType)
static <VALUE_TYPE> java.util.List<java.util.Map<java.lang.String,VALUE_TYPE>>	getRecordsFromResultSet(java.sql.ResultSet rs, java.lang.Class<VALUE_TYPE> valueType)
static <V> SQLResult	getResultFromQuery(java.sql.Connection conn, java.util.List<java.lang.String> selectColumns, java.lang.String fromSchema, java.lang.String fromTable, java.util.Map<java.lang.String,V> whereParams, java.util.Set<java.lang.String> caseSensitiveFields)
static <TYPE> SQLResult	getResultFromQuery(java.sql.Connection connection, java.lang.String query, TYPE[] params, boolean convertToStrings) Example usage: getResultFromQuery(conn, "SELECT a, b FROM mytable WHERE c = ? and d = ?", new Object[]&#124; "my-c-value", 0xDDDD &#125;, false)
static java.util.List<java.lang.String>	getSchemas(java.sql.Connection conn)
static int	getSingleIntFromQuery(java.sql.Connection conn, java.lang.String query, int defaultValue)
static int	getSingleIntFromQuery(java.sql.Connection conn, java.lang.String query, java.lang.Object[] params, int defaultValue)
static int	getSingleIntFromQuery(java.sql.Statement stmt, java.lang.String query, int defaultValue)
static java.sql.Connection	getStaticReadOnlyConnection(java.lang.String connectString, java.lang.String user, java.lang.String pass) This function returns a read-only connection that can be reused.
static java.util.List<java.lang.String>	getTables(java.sql.Connection conn, java.lang.String schemaName)
static java.lang.String	getTinyIntTypeString(java.sql.Connection conn)
static java.lang.String	getVarcharTypeString(java.sql.Connection conn, int length)
static <V> int	insertRow(java.sql.Connection conn, java.lang.String schemaName, java.lang.String tableName, java.util.Map<java.lang.String,V> record)
static int	insertRowReturnID(java.sql.Connection conn, java.lang.String schemaName, java.lang.String tableName, java.util.Map<java.lang.String,java.lang.Object> data, java.lang.String idField) Generates a new id manually using MAX(idField)+1.
static <V> int	insertRows(java.sql.Connection conn, java.lang.String schemaName, java.lang.String tableName, java.util.List<java.util.Map<java.lang.String,V>> records)
static boolean	isMySQL(java.sql.Connection conn) This function checks if a connection is for a MySQL server.
static boolean	isOracleServer(java.sql.Connection conn) This function checks if a connection is for an Oracle server.
static boolean	isPostgreSQL(java.sql.Connection conn) This function checks if a connection is for a PostgreSQL server.
static boolean	isSQLite(java.sql.Connection conn) This function checks if a connection is for a SQLite server.
static boolean	isSQLServer(java.sql.Connection conn) This function checks if a connection is for a Microsoft SQL Server.
static <TYPE> java.sql.PreparedStatement	prepareStatement(java.sql.Connection conn, java.lang.String query, java.util.List<TYPE> params)
static <TYPE> java.sql.PreparedStatement	prepareStatement(java.sql.Connection conn, java.lang.String query, TYPE[] params)
static java.lang.String	quoteSchemaTable(java.sql.Connection conn, java.lang.String schema, java.lang.String table) Returns quoted schema & table to use in SQL queries for the given Connection.
static java.lang.String	quoteSchemaTable(java.lang.String dbms, java.lang.String schema, java.lang.String table) Returns quoted schema & table to use in SQL queries for the given DBMS.
static java.lang.String	quoteSymbol(java.sql.Connection conn, java.lang.String symbol)
static java.lang.String	quoteSymbol(java.lang.String dbms, java.lang.String symbol)
static boolean	schemaExists(java.sql.Connection conn, java.lang.String schema)
static <TYPE> void	setPreparedStatementParams(java.sql.PreparedStatement cstmt, java.util.List<TYPE> params)
static <TYPE> void	setPreparedStatementParams(java.sql.PreparedStatement cstmt, TYPE[] params)
static void	setSQLServerIdentityInsert(java.sql.Connection conn, java.lang.String schema, java.lang.String table, boolean on) This function should only be called on a SQL Server connection.
static boolean	sqlTypeIsGeometry(int sqlType)
static boolean	sqlTypeIsNumeric(int sqlType)
static void	staticCleanup() Call this when shutting down your application to deregister SQL drivers.
static java.lang.String	stringCast(java.sql.Connection conn, java.lang.String queryExpression) This will wrap a query expression in a string cast.
static boolean	tableExists(java.sql.Connection conn, java.lang.String schema, java.lang.String table)
static void	testConnection(java.sql.Connection conn) This function will test a connection by running a simple test query.
static java.lang.String	unquoteSymbol(java.sql.Connection conn, java.lang.String symbol)
static java.lang.String	unquoteSymbol(java.lang.String dbms, java.lang.String symbol)
static int	updateRows(java.sql.Connection conn, java.lang.String fromSchema, java.lang.String fromTable, java.util.Map<java.lang.String,java.lang.Object> whereParams, java.util.Map<java.lang.String,java.lang.Object> dataUpdate, java.util.Set<java.lang.String> caseSensitiveFields)

Methods inherited from class java.lang.Object

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

Field Detail

MYSQL

public static java.lang.String MYSQL

SQLITE

public static java.lang.String SQLITE

POSTGRESQL

public static java.lang.String POSTGRESQL

SQLSERVER

public static java.lang.String SQLSERVER

ORACLE

public static java.lang.String ORACLE

DEFAULT_SQLITE_DATABASE

public static java.lang.String DEFAULT_SQLITE_DATABASE

WILDCARD_ESCAPE

public static final char WILDCARD_ESCAPE

The escape character (backslash) used by convertWildcards() and getLikeEscapeClause()

See Also:

convertWildcards(String), getLikeEscapeClause(Connection), Constant Field Values

Constructor Detail

SQLUtils

public SQLUtils()

Method Detail

getDriver

public static java.lang.String getDriver(java.lang.String dbms)
                                  throws java.rmi.RemoteException

Parameters:

dbms - The name of a DBMS (MySQL, PostgreSQL, ...)

Returns:

A driver name that can be used in the getConnection() function.

Throws:

java.rmi.RemoteException

getDbmsFromConnection

public static java.lang.String getDbmsFromConnection(java.sql.Connection conn)

getDbmsFromConnectString

public static java.lang.String getDbmsFromConnectString(java.lang.String connectString)
                                                 throws java.rmi.RemoteException

Throws:

java.rmi.RemoteException

getConnectString

public static java.lang.String getConnectString(java.lang.String dbms,
                                java.lang.String ip,
                                java.lang.String port,
                                java.lang.String database,
                                java.lang.String user,
                                java.lang.String pass)

Parameters:

dbms - The name of a DBMS (MySQL, PostgreSQL, Microsoft SQL Server)

ip - The IP address of the DBMS.

port - The port the DBMS is on (optional, can be "" to use default).

database - The name of a database to connect to (can be "" for MySQL)

user - The username to use when connecting.

pass - The password associated with the username.

Returns:

A connect string that can be used in the getConnection() function.

staticCleanup

public static void staticCleanup()

Call this when shutting down your application to deregister SQL drivers.

testConnection

public static void testConnection(java.sql.Connection conn)
                           throws java.sql.SQLException

This function will test a connection by running a simple test query. We cannot rely on Connection.isValid(timeout) because it does not work in some drivers. Running a test query is a reliable way to find out if the connection is valid.

Parameters:

conn - A SQL Connection.

Throws:

java.sql.SQLException - Thrown if the test query fails.

connectionIsValid

public static boolean connectionIsValid(java.sql.Connection conn)

This function tests if a given Connection is valid, and closes the connection if it is not.

Parameters:

conn - A Connection object which may or may not be valid.

Returns:

A value of true if the given Connection is still connected.

getStaticReadOnlyConnection

public static java.sql.Connection getStaticReadOnlyConnection(java.lang.String connectString,
                                              java.lang.String user,
                                              java.lang.String pass)
                                                       throws java.rmi.RemoteException

This function returns a read-only connection that can be reused. The connection should not be closed.

Parameters:

connectString - The connect string used to create the Connection.

Returns:

A static read-only Connection.

Throws:

java.rmi.RemoteException

getConnection

public static java.sql.Connection getConnection(java.lang.String connectString)
                                         throws java.rmi.RemoteException

Parameters:

connectString - The connect string to use.

Returns:

A new SQL connection using the specified driver & connect string

Throws:

java.rmi.RemoteException

getConnection

public static java.sql.Connection getConnection(java.lang.String connectString,
                                java.lang.String user,
                                java.lang.String pass)
                                         throws java.rmi.RemoteException

Parameters:

connectString - The connect string to use.

user - The user name

pass - The password

Returns:

A new SQL connection using the specified driver & connect string

Throws:

java.rmi.RemoteException

fixColumnName

public static java.lang.String fixColumnName(java.lang.String colName,
                             java.lang.String suffix)

Parameters:

colName -

Returns:

colName with special characters replaced and truncated to 30 characters.

quoteSymbol

public static java.lang.String quoteSymbol(java.lang.String dbms,
                           java.lang.String symbol)
                                    throws java.lang.IllegalArgumentException

Parameters:

dbms - The name of a DBMS (MySQL, PostgreSQL, ...)

symbol - The symbol to quote.

Returns:

The symbol surrounded in quotes, usable in queries for the specified DBMS.

Throws:

java.lang.IllegalArgumentException

quoteSymbol

public static java.lang.String quoteSymbol(java.sql.Connection conn,
                           java.lang.String symbol)
                                    throws java.sql.SQLException,
                                           java.lang.IllegalArgumentException

Parameters:

conn - An SQL connection.

symbol - The symbol to quote.

Returns:

The symbol surrounded in quotes, usable in queries for the specified connection.

Throws:

java.sql.SQLException

java.lang.IllegalArgumentException

unquoteSymbol

public static java.lang.String unquoteSymbol(java.lang.String dbms,
                             java.lang.String symbol)

Parameters:

dbms - The name of a DBMS (MySQL, PostgreSQL, ...)

symbol - The quoted symbol.

Returns:

The symbol without its dbms-specific quotes.

unquoteSymbol

public static java.lang.String unquoteSymbol(java.sql.Connection conn,
                             java.lang.String symbol)
                                      throws java.sql.SQLException,
                                             java.lang.IllegalArgumentException

Parameters:

conn - An SQL connection.

symbol - The quoted symbol.

Returns:

The symbol without its dbms-specific quotes.

Throws:

java.sql.SQLException

java.lang.IllegalArgumentException

caseSensitiveCompare

public static java.lang.String caseSensitiveCompare(java.sql.Connection conn,
                                    java.lang.String expr1,
                                    java.lang.String expr2)
                                             throws java.sql.SQLException

This will build a case sensitive compare expression out of two sql query expressions.

Parameters:

conn - An SQL Connection.

expr1 - The first SQL expression to be used in the comparison.

expr2 - The second SQL expression to be used in the comparison.

Returns:

A SQL expression comparing the two expressions using case-sensitive string comparison.

Throws:

java.sql.SQLException

stringCast

public static java.lang.String stringCast(java.sql.Connection conn,
                          java.lang.String queryExpression)
                                   throws java.sql.SQLException

This will wrap a query expression in a string cast. If the database is not supported by this function, the queryExpression will not be altered.

Parameters:

conn - An SQL Connection.

queryExpression - An expression to be used in a SQL Query.

Returns:

The query expression wrapped in a string cast.

Throws:

java.sql.SQLException

binarySQLType

public static java.lang.String binarySQLType(java.lang.String dbms)

This function returns the name of a binary data type that can be used in SQL queries.

Parameters:

dbms - The name of a DBMS (MySQL, PostgreSQL, ...)

Returns:

The name of the binary SQL type to use for the given DBMS.

quoteSchemaTable

public static java.lang.String quoteSchemaTable(java.lang.String dbms,
                                java.lang.String schema,
                                java.lang.String table)

Returns quoted schema & table to use in SQL queries for the given DBMS.

Parameters:

dbms - The name of a DBMS (MySQL, PostgreSQL, ...)

schema - The schema the table resides in.

table - The table.

Returns:

The schema & table name surrounded in quotes, usable in queries for the specified DBMS.

quoteSchemaTable

public static java.lang.String quoteSchemaTable(java.sql.Connection conn,
                                java.lang.String schema,
                                java.lang.String table)
                                         throws java.sql.SQLException

Returns quoted schema & table to use in SQL queries for the given Connection.

Parameters:

conn - An SQL connection.

schema - The schema the table resides in.

table - The table.

Returns:

The schema & table name surrounded in quotes, usable in queries for the specified connection.

Throws:

java.sql.SQLException

sqlTypeIsNumeric

public static boolean sqlTypeIsNumeric(int sqlType)

sqlTypeIsGeometry

public static boolean sqlTypeIsGeometry(int sqlType)

getResultFromQuery

public static <TYPE> SQLResult getResultFromQuery(java.sql.Connection connection,
                                  java.lang.String query,
                                  TYPE[] params,
                                  boolean convertToStrings)
                                    throws java.sql.SQLException

Example usage: getResultFromQuery(conn, "SELECT a, b FROM mytable WHERE c = ? and d = ?", new Object[]| "my-c-value", 0xDDDD }, false)

Parameters:

connection - An SQL Connection

query - An SQL Query with '?' place holders for parameters

params - Parameters for the SQL query for all '?' place holders, or null if there are no parameters.

Returns:

A SQLResult object containing the result of the query

Throws:

java.sql.SQLException

getRecordsFromResultSet

public static <VALUE_TYPE> java.util.List<java.util.Map<java.lang.String,VALUE_TYPE>> getRecordsFromResultSet(java.sql.ResultSet rs,
                                                                                              java.lang.Class<VALUE_TYPE> valueType)
                                                                                          throws java.sql.SQLException

Parameters:

rs - The ResultSet returned from a SQL query.

valueType - The Class used for casting values in the ResultSet.

Returns:

A list of field-value pairs containing the record data.

Throws:

java.sql.SQLException - If the query fails.

getColumnNamesFromResultSet

public static java.lang.String[] getColumnNamesFromResultSet(java.sql.ResultSet rs)
                                                      throws java.sql.SQLException

Throws:

java.sql.SQLException

getRecordsFromQuery

public static <VALUE_TYPE> java.util.List<java.util.Map<java.lang.String,VALUE_TYPE>> getRecordsFromQuery(java.sql.Connection conn,
                                                                                          java.util.List<java.lang.String> selectColumns,
                                                                                          java.lang.String fromSchema,
                                                                                          java.lang.String fromTable,
                                                                                          SQLUtils.WhereClause<VALUE_TYPE> where,
                                                                                          java.lang.String orderBy,
                                                                                          java.lang.Class<VALUE_TYPE> valueType)
                                                                                      throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

selectColumns - The list of column names, or null for all columns

fromSchema - The schema containing the table to perform the SELECT statement on.

fromTable - The table to perform the SELECT statement on.

where - Used to construct the WHERE clause

orderBy - The field to order by, or null for no specific order.

valueType - Either String.class or Object.class to denote the VALUE_TYPE class.

Returns:

The resulting rows returned by the query.

Throws:

java.sql.SQLException - If the query fails.

getResultFromQuery

public static <V> SQLResult getResultFromQuery(java.sql.Connection conn,
                               java.util.List<java.lang.String> selectColumns,
                               java.lang.String fromSchema,
                               java.lang.String fromTable,
                               java.util.Map<java.lang.String,V> whereParams,
                               java.util.Set<java.lang.String> caseSensitiveFields)
                                    throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

selectColumns - The list of columns in the SELECT statement, or null for all columns.

fromSchema - The schema containing the table to perform the SELECT statement on.

fromTable - The table to perform the SELECT statement on.

whereParams - A map of column names to String values used to construct a WHERE clause.

Returns:

The resulting rows returned by the query.

Throws:

java.sql.SQLException - If the query fails.

executeUpdate

public static int executeUpdate(java.sql.Connection connection,
                java.lang.String query)
                         throws java.sql.SQLException

Throws:

java.sql.SQLException

executeUpdate

public static int executeUpdate(java.sql.Connection conn,
                java.lang.String query,
                java.lang.Object[] params)
                         throws java.sql.SQLException

Throws:

java.sql.SQLException

getSingleIntFromQuery

public static int getSingleIntFromQuery(java.sql.Connection conn,
                        java.lang.String query,
                        int defaultValue)
                                 throws java.sql.SQLException

Throws:

java.sql.SQLException

getSingleIntFromQuery

public static int getSingleIntFromQuery(java.sql.Statement stmt,
                        java.lang.String query,
                        int defaultValue)
                                 throws java.sql.SQLException

Throws:

java.sql.SQLException

getSingleIntFromQuery

public static int getSingleIntFromQuery(java.sql.Connection conn,
                        java.lang.String query,
                        java.lang.Object[] params,
                        int defaultValue)
                                 throws java.sql.SQLException

Throws:

java.sql.SQLException

getSchemas

public static java.util.List<java.lang.String> getSchemas(java.sql.Connection conn)
                                                   throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

Returns:

A List of schema names

Throws:

java.sql.SQLException - If the query fails.

getTables

public static java.util.List<java.lang.String> getTables(java.sql.Connection conn,
                                         java.lang.String schemaName)
                                                  throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

schemaName - A schema name accessible through the given connection

Returns:

A List of table names in the given schema

Throws:

java.sql.SQLException - If the query fails.

getColumns

public static java.util.List<java.lang.String> getColumns(java.sql.Connection conn,
                                          java.lang.String schemaName,
                                          java.lang.String tableName)
                                                   throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

schemaName - A schema name accessible through the given connection

tableName - A table name existing in the given schema

Returns:

A List of column names in the given table

Throws:

java.sql.SQLException - If the query fails.

createSchema

public static void createSchema(java.sql.Connection conn,
                java.lang.String schema)
                         throws java.sql.SQLException

Creates a schema in a non-SQLite database. For SQLite, this does nothing.

Parameters:

conn - An existing SQL Connection

schema - The value to be used as the Schema name

Throws:

java.sql.SQLException - If the query fails.

createTable

public static void createTable(java.sql.Connection conn,
               java.lang.String schemaName,
               java.lang.String tableName,
               java.util.List<java.lang.String> columnNames,
               java.util.List<java.lang.String> columnTypes,
               java.util.List<java.lang.String> primaryKeyColumns)
                        throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

schemaName - A schema name accessible through the given connection

tableName - The value to be used as the table name

columnNames - The values to be used as the column names

columnTypes - The SQL types to use when creating the table

primaryKeyColumns - The list of columns to be used for primary keys

Throws:

java.sql.SQLException - If the query fails.

addForeignKey

public static void addForeignKey(java.sql.Connection conn,
                 java.lang.String schemaName,
                 java.lang.String tableName,
                 java.lang.String keyName,
                 java.lang.String targetTable,
                 java.lang.String targetKey)
                          throws java.sql.SQLException

Throws:

java.sql.SQLException

prepareStatement

public static <TYPE> java.sql.PreparedStatement prepareStatement(java.sql.Connection conn,
                                                 java.lang.String query,
                                                 java.util.List<TYPE> params)
                                                   throws java.sql.SQLException

Throws:

java.sql.SQLException

prepareStatement

public static <TYPE> java.sql.PreparedStatement prepareStatement(java.sql.Connection conn,
                                                 java.lang.String query,
                                                 TYPE[] params)
                                                   throws java.sql.SQLException

Throws:

java.sql.SQLException

constrainQueryParams

protected static <T> void constrainQueryParams(java.sql.Connection conn,
                            java.util.List<T> params)

constrainQueryParams

protected static <T> void constrainQueryParams(java.sql.Connection conn,
                            T[] params)

constrainOracleQueryParam

protected static <T> T constrainOracleQueryParam(T param)

setPreparedStatementParams

public static <TYPE> void setPreparedStatementParams(java.sql.PreparedStatement cstmt,
                                     java.util.List<TYPE> params)
                                       throws java.sql.SQLException

Throws:

java.sql.SQLException

setPreparedStatementParams

public static <TYPE> void setPreparedStatementParams(java.sql.PreparedStatement cstmt,
                                     TYPE[] params)
                                       throws java.sql.SQLException

Throws:

java.sql.SQLException

updateRows

public static int updateRows(java.sql.Connection conn,
             java.lang.String fromSchema,
             java.lang.String fromTable,
             java.util.Map<java.lang.String,java.lang.Object> whereParams,
             java.util.Map<java.lang.String,java.lang.Object> dataUpdate,
             java.util.Set<java.lang.String> caseSensitiveFields)
                      throws java.sql.SQLException

Throws:

java.sql.SQLException

insertRowReturnID

public static int insertRowReturnID(java.sql.Connection conn,
                    java.lang.String schemaName,
                    java.lang.String tableName,
                    java.util.Map<java.lang.String,java.lang.Object> data,
                    java.lang.String idField)
                             throws java.sql.SQLException

Generates a new id manually using MAX(idField)+1.

Parameters:

conn -

schemaName -

tableName -

data - Unquoted field names mapped to raw values.

idField -

Returns:

The ID of the new row.

Throws:

java.sql.SQLException

isPostgreSQL

public static boolean isPostgreSQL(java.sql.Connection conn)

This function checks if a connection is for a PostgreSQL server.

Parameters:

conn - A SQL Connection.

Returns:

A value of true if the Connection is for a PostgreSQL server.

Throws:

java.sql.SQLException

isMySQL

public static boolean isMySQL(java.sql.Connection conn)

This function checks if a connection is for a MySQL server.

Parameters:

conn - A SQL Connection.

Returns:

A value of true if the Connection is for a MySQL server.

Throws:

java.sql.SQLException

isOracleServer

public static boolean isOracleServer(java.sql.Connection conn)

This function checks if a connection is for an Oracle server.

Parameters:

conn - A SQL Connection.

Returns:

A value of true if the Connection is for an Oracle server.

Throws:

java.sql.SQLException

isSQLServer

public static boolean isSQLServer(java.sql.Connection conn)

This function checks if a connection is for a Microsoft SQL Server.

Parameters:

conn - A SQL Connection.

Returns:

A value of true if the Connection is for a Microsoft SQL Server.

isSQLite

public static boolean isSQLite(java.sql.Connection conn)

This function checks if a connection is for a SQLite server.

Parameters:

conn - A SQL Connection.

Returns:

A value of true if the Connection is for a SQLite Server.

createIndex

public static void createIndex(java.sql.Connection conn,
               java.lang.String schemaName,
               java.lang.String tableName,
               java.lang.String[] columnNames)
                        throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

schemaName - A schema name accessible through the given connection

tableName - The name of an existing table

columnNames - The names of the columns to use

Throws:

java.sql.SQLException - If the query fails.

createIndex

public static void createIndex(java.sql.Connection conn,
               java.lang.String schemaName,
               java.lang.String tableName,
               java.lang.String[] columnNames,
               java.lang.Integer[] columnLengths)
                        throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

schemaName - A schema name accessible through the given connection

tableName - The name of an existing table

columnNames - The names of the columns to use.

columnLengths - The lengths to use as indices, may be null.

Throws:

java.sql.SQLException - If the query fails.

addColumn

public static void addColumn(java.sql.Connection conn,
             java.lang.String schemaName,
             java.lang.String tableName,
             java.lang.String columnName,
             java.lang.String columnType)
                      throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

schemaName - A schema name accessible through the given connection

tableName - The name of an existing table

columnName - The name of the column to create

columnType - An SQL type to use when creating the column

Throws:

java.sql.SQLException - If the query fails.

getColumn

public static java.util.List<java.lang.String> getColumn(java.sql.Connection conn,
                                         java.lang.String schemaName,
                                         java.lang.String tableName,
                                         java.lang.String columnArg)
                                                  throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

schemaName - A schema name accessible through the given connection

tableName - A table name existing in the given schema

columnArg - The name of the column to grab

Returns:

A List of string values from the column

Throws:

java.sql.SQLException - If the query fails.

getIntColumn

public static java.util.List<java.lang.Integer> getIntColumn(java.sql.Connection conn,
                                             java.lang.String schemaName,
                                             java.lang.String tableName,
                                             java.lang.String columnArg)
                                                      throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

schemaName - A schema name accessible through the given connection

tableName - A table name existing in the given schema

columnArg - The name of the integer column to grab

Returns:

A List of integer values from the column

Throws:

java.sql.SQLException - If the query fails.

insertRow

public static <V> int insertRow(java.sql.Connection conn,
                java.lang.String schemaName,
                java.lang.String tableName,
                java.util.Map<java.lang.String,V> record)
                     throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

schemaName - A schema name accessible through the given connection

tableName - A table name existing in the given schema

record - The record to be inserted into the table

Returns:

The number of rows inserted.

Throws:

java.sql.SQLException - If the query fails.

insertRows

public static <V> int insertRows(java.sql.Connection conn,
                 java.lang.String schemaName,
                 java.lang.String tableName,
                 java.util.List<java.util.Map<java.lang.String,V>> records)
                      throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

schemaName - A schema name accessible through the given connection

tableName - A table name existing in the given schema

records - The records to be inserted into the table

Returns:

The number of rows inserted.

Throws:

java.sql.SQLException - If the query fails.

schemaExists

public static boolean schemaExists(java.sql.Connection conn,
                   java.lang.String schema)
                            throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

schema - The name of a schema to check for.

Returns:

true if the schema exists

Throws:

java.sql.SQLException - If the getSchemas query fails.

dropTableIfExists

public static void dropTableIfExists(java.sql.Connection conn,
                     java.lang.String schema,
                     java.lang.String table)
                              throws java.sql.SQLException

Throws:

java.sql.SQLException

deleteRows

public static <V> int deleteRows(java.sql.Connection conn,
                 java.lang.String schemaName,
                 java.lang.String tableName,
                 SQLUtils.WhereClause<V> where)
                      throws java.sql.SQLException

This function will delete from a table the rows that have a specified set of column values.

Parameters:

conn - An existing SQL Connection

schemaName - A schema name accessible through the given connection

tableName - A table name existing in the given schema

where - The conditions to be used in the WHERE clause of the query

Returns:

The number of rows that were deleted.

Throws:

java.sql.SQLException - If the query fails.

tableExists

public static boolean tableExists(java.sql.Connection conn,
                  java.lang.String schema,
                  java.lang.String table)
                           throws java.sql.SQLException

Parameters:

conn - An existing SQL Connection

schema - The name of a schema to check in.

table - The name of a table to check for.

Returns:

true if the table exists in the specified schema.

Throws:

java.sql.SQLException - If either getSchemas() or getTables() fails.

cleanup

public static void cleanup(java.sql.ResultSet obj)

cleanup

public static void cleanup(java.sql.Statement obj)

cleanup

public static void cleanup(java.sql.Connection obj)

getVarcharTypeString

public static java.lang.String getVarcharTypeString(java.sql.Connection conn,
                                    int length)

getTinyIntTypeString

public static java.lang.String getTinyIntTypeString(java.sql.Connection conn)
                                             throws java.sql.SQLException

Throws:

java.sql.SQLException

getIntTypeString

public static java.lang.String getIntTypeString(java.sql.Connection conn)

getDoubleTypeString

public static java.lang.String getDoubleTypeString(java.sql.Connection conn)

getBigIntTypeString

public static java.lang.String getBigIntTypeString(java.sql.Connection conn)

getDateTimeTypeString

public static java.lang.String getDateTimeTypeString(java.sql.Connection conn)

getLongBlobTypeString

public static java.lang.String getLongBlobTypeString(java.sql.Connection conn)

getCSVNullValue

protected static java.lang.String getCSVNullValue(java.sql.Connection conn)

setSQLServerIdentityInsert

public static void setSQLServerIdentityInsert(java.sql.Connection conn,
                              java.lang.String schema,
                              java.lang.String table,
                              boolean on)
                                       throws java.sql.SQLException

This function should only be called on a SQL Server connection.

Parameters:

conn -

schema -

table -

on -

Throws:

java.sql.SQLException

convertWildcards

public static java.lang.String convertWildcards(java.lang.String searchString)

Converts a search string which uses basic '?' and '*' wildcards into an equivalent SQL search string.

Parameters:

searchString - A search string which uses basic '?' and '*' wildcards

Returns:

The equivalent SQL search string using a backslash (\) as an escape character.

See Also:

getLikeEscapeClause(Connection)

getLikeEscapeClause

public static java.lang.String getLikeEscapeClause(java.sql.Connection conn)

Returns an ESCAPE clause for use with a LIKE comparison.

Parameters:

conn - The SQL Connection where the ESCAPE clause will be used.

Returns:

The ESCAPE clause specifying a backslash (\) as the escape character.

See Also:

convertWildcards(String)