org.identityconnectors.dbcommon

Class SQLUtil

java.lang.Object

org.identityconnectors.dbcommon.SQLUtil

public final class SQLUtil
extends Object

The SQL helper/util class.

Since:

1.0

Method Summary

Modifier and Type	Method and Description
static Object	attribute2jdbcValue(Object value, int sqlType) Convert the attribute to expected jdbc type using java conversions.
static byte[]	blob2ByteArray(Blob blobValue) Make a blob conversion.
static void	closeQuietly(Connection connection) Deprecated. @see org.identityconnectors.common.IOUtil.quietClose(Statement);
static void	closeQuietly(DatabaseConnection conn) Ignores any exception thrown by the Connection parameter when closed, it also checks for null.
static void	closeQuietly(ResultSet resultSet) Deprecated. @see org.identityconnectors.common.IOUtil.quietClose(ResultSet);
static void	closeQuietly(Statement statement) Deprecated. @see org.identityconnectors.common.IOUtil.quietClose(Statement);
static String	date2String(Date value) Date to string.
static int	executeUpdateStatement(Connection conn, String sql, SQLParam... params) Executes DML sql statement.
static Map<String,SQLParam>	getColumnValues(ResultSet resultSet) Read one row from database result set and convert a columns to attribute set.
static Connection	getDatasourceConnection(String datasourceName) Get the connection from the datasource.
static Connection	getDatasourceConnection(String datasourceName, Hashtable<?,?> env) Get the connection from the datasource.
static Connection	getDatasourceConnection(String datasourceName, String user, GuardedString password) Get the connection from the dataSource with specified user and password.
static Connection	getDatasourceConnection(String datasourceName, String user, GuardedString password, Hashtable<?,?> env) Get the connection from the dataSource with specified user and password.
static Connection	getDriverMangerConnection(String driver, String url, Properties properties) Gets a Connection using the basic driver manager.
static Connection	getDriverMangerConnection(String driver, String url, String login, GuardedString password) Gets a Connection using the basic driver manager.
static Connection	getDriverMangerConnection(String driver, String url, String login, GuardedString password, Properties properties) Gets a Connection using the basic driver manager.
static Class<?>	getSQLAttributeType(int sqlType) Convert database type to connector supported set of attribute types Can be redefined for different databases.
static SQLParam	getSQLParam(ResultSet resultSet, int i, String name, int sqlType) Retrieve the SQL value from result set.
static Object	jdbc2AttributeValue(Object value) The conversion to required attribute type.
static String	normalizeNullValues(String sql, List<SQLParam> params, List<SQLParam> out) The null param value normalizer.
static void	rollbackQuietly(Connection conn) Ignores any exception thrown by the Connection parameter when closed, it also checks for null.
static void	rollbackQuietly(DatabaseConnection conn) Ignores any exception thrown by the DatabaseConnection parameter when closed, it also checks for null.
static List<Object[]>	selectRows(Connection conn, String sql, SQLParam... params) Selects all rows from select.
static Object	selectSingleValue(Connection conn, String sql, SQLParam... params) Selects single value (first column) from select.
static void	setGuardedStringParam(PreparedStatement stmt, int idx, GuardedString guard) The helper guardedString bind method.
static void	setParams(CallableStatement statement, List<SQLParam> params) This method binds the "?"
static void	setParams(PreparedStatement statement, List<SQLParam> params) This method binds the "?"
static void	setSQLParam(PreparedStatement stmt, int idx, SQLParam parm) Set a parameter to statement.
static Boolean	string2Boolean(String val) Convert String to boolean.
static Date	string2Date(String param) String to Date.
static Time	string2Time(String param) String to Time.
static Timestamp	string2Timestamp(String param) Convert string to Timestamp.
static String	time2String(Time value) Time to String format.
static String	timestamp2String(Timestamp value) Convert timestamp to string.

Methods inherited from class java.lang.Object

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

Method Detail

getDatasourceConnection

public static Connection getDatasourceConnection(String datasourceName,
                                                 Hashtable<?,?> env)

Get the connection from the datasource.

Parameters:

datasourceName -

env - propertyHastable

Returns:

the connection get from default jndi context

getDatasourceConnection

public static Connection getDatasourceConnection(String datasourceName,
                                                 String user,
                                                 GuardedString password,
                                                 Hashtable<?,?> env)

Get the connection from the dataSource with specified user and password.

Parameters:

datasourceName -

user - DB user

password - DB password

env - propertyHastable

Returns:

the connection get from dataSource

getDatasourceConnection

public static Connection getDatasourceConnection(String datasourceName,
                                                 String user,
                                                 GuardedString password)

Get the connection from the dataSource with specified user and password.

Parameters:

datasourceName -

user - DB user

password - DB password

Returns:

the connection get from dataSource

getDatasourceConnection

public static Connection getDatasourceConnection(String datasourceName)

Get the connection from the datasource.

Parameters:

datasourceName -

Returns:

the connection get from default jndi context

getDriverMangerConnection

public static Connection getDriverMangerConnection(String driver,
                                                   String url,
                                                   String login,
                                                   GuardedString password)

Gets a Connection using the basic driver manager.

Parameters:

driver - jdbc driver name

url - jdbc connection url

login - jdbc login name

password - jdbc password

Returns:

a valid connection

getDriverMangerConnection

public static Connection getDriverMangerConnection(String driver,
                                                   String url,
                                                   String login,
                                                   GuardedString password,
                                                   Properties properties)

Gets a Connection using the basic driver manager.

Parameters:

driver - jdbc driver name

url - jdbc connection url

login - jdbc login name

password - jdbc password

properties - a list of arbitrary string tag/value pairs as connection arguments

Returns:

a valid connection

getDriverMangerConnection

public static Connection getDriverMangerConnection(String driver,
                                                   String url,
                                                   Properties properties)

Gets a Connection using the basic driver manager.

Parameters:

driver - jdbc driver name

url - jdbc connection url

properties - a list of arbitrary string tag/value pairs as connection arguments

Returns:

a valid connection

rollbackQuietly

public static void rollbackQuietly(Connection conn)

Ignores any exception thrown by the Connection parameter when closed, it also checks for null.

Parameters:

conn - JDBC connection to rollback.

rollbackQuietly

public static void rollbackQuietly(DatabaseConnection conn)

Ignores any exception thrown by the DatabaseConnection parameter when closed, it also checks for null.

Parameters:

conn - DatabaseConnection to rollback.

closeQuietly

public static void closeQuietly(Connection connection)

Deprecated. @see org.identityconnectors.common.IOUtil.quietClose(Statement);

Ignores any exception thrown by the Connection parameter when closed, it also checks for null.

Parameters:

connection - JDBC connection to close.

closeQuietly

public static void closeQuietly(DatabaseConnection conn)

Ignores any exception thrown by the Connection parameter when closed, it also checks for null.

Parameters:

conn - DatabaseConnection to close.

closeQuietly

public static void closeQuietly(Statement statement)

Deprecated. @see org.identityconnectors.common.IOUtil.quietClose(Statement);

Ignores any exception thrown by the Statement.close() method.

Parameters:

statement - Statement to close.

closeQuietly

public static void closeQuietly(ResultSet resultSet)

Deprecated. @see org.identityconnectors.common.IOUtil.quietClose(ResultSet);

Closes the ResultSet and ignores any Exception that may be thrown by the ResultSet.close() method.

Parameters:

resultSet - ResultSet to close quitely.

date2String

public static String date2String(Date value)

Date to string.

Parameters:

value - Date value

Returns:

String value

time2String

public static String time2String(Time value)

Time to String format.

Parameters:

value - Time value

Returns:

String value

timestamp2String

public static String timestamp2String(Timestamp value)

Convert timestamp to string.

Parameters:

value - Timestamp

Returns:

the string value

string2Time

public static Time string2Time(String param)

String to Time.

Parameters:

param - String

Returns:

the Time value

string2Date

public static Date string2Date(String param)

String to Date.

Parameters:

param - the String value

Returns:

Date value

string2Timestamp

public static Timestamp string2Timestamp(String param)

Convert string to Timestamp.

Parameters:

param - String value

Returns:

Timestamp value

string2Boolean

public static Boolean string2Boolean(String val)

Convert String to boolean.

Parameters:

val - string value

Returns:

Boolean value

normalizeNullValues

public static String normalizeNullValues(String sql,
                                         List<SQLParam> params,
                                         List<SQLParam> out)

The null param value normalizer.

Parameters:

sql -

params - list

out - out param list

Returns:

the modified string

blob2ByteArray

public static byte[] blob2ByteArray(Blob blobValue)
                             throws SQLException

Make a blob conversion.

Parameters:

blobValue - blob

Returns:

a converted value

Throws:

SQLException

setParams

public static void setParams(PreparedStatement statement,
                             List<SQLParam> params)
                      throws SQLException

This method binds the "?" markers in SQL statement with the parameters given as values.

It concentrates the replacement of all params. GuardedString are handled so the password is never visible.

Parameters:

statement -

params - a List of the object arguments

Throws:

SQLException - an exception in statement

setParams

public static void setParams(CallableStatement statement,
                             List<SQLParam> params)
                      throws SQLException

This method binds the "?" markers in SQL statement with the parameters given as values.

It concentrates the replacement of all params. GuardedString are handled so the password is never visible.

Parameters:

statement -

params - a List of the object arguments

Throws:

SQLException - an exception in statement

getColumnValues

public static Map<String,SQLParam> getColumnValues(ResultSet resultSet)
                                            throws SQLException

Read one row from database result set and convert a columns to attribute set.

Parameters:

resultSet - database data

Returns:

The transformed attribute set

Throws:

SQLException

getSQLParam

public static SQLParam getSQLParam(ResultSet resultSet,
                                   int i,
                                   String name,
                                   int sqlType)
                            throws SQLException

Retrieve the SQL value from result set.

Parameters:

resultSet - the result set

i - index

name - param name

sqlType - expected SQL type or Types.NULL for generic

Returns:

the object return the retrieved object

Throws:

SQLException - any SQL error

getSQLAttributeType

public static Class<?> getSQLAttributeType(int sqlType)

Convert database type to connector supported set of attribute types Can be redefined for different databases.

Parameters:

sqlType - #Types

Returns:

a connector supported class

setSQLParam

public static void setSQLParam(PreparedStatement stmt,
                               int idx,
                               SQLParam parm)
                        throws SQLException

Set a parameter to statement. The conversion to required database type is expected to be done.

Parameters:

stmt - the statement to set

idx - index of the parameter

parm - the SQLParam value

Throws:

SQLException - something wrong

jdbc2AttributeValue

public static Object jdbc2AttributeValue(Object value)
                                  throws SQLException

The conversion to required attribute type.

Parameters:

value - to be converted to an attribute

Returns:

a attribute's supported object

Throws:

SQLException - something is not ok

attribute2jdbcValue

public static Object attribute2jdbcValue(Object value,
                                         int sqlType)
                                  throws SQLException

Convert the attribute to expected jdbc type using java conversions. Some database strategy sets all attributes as string, other convert them first and than set as native.

Parameters:

value - the value to be converted

sqlType - the target sql type

Returns:

the converted object value

Throws:

SQLException

setGuardedStringParam

public static void setGuardedStringParam(PreparedStatement stmt,
                                         int idx,
                                         GuardedString guard)
                                  throws SQLException

The helper guardedString bind method.

Parameters:

stmt - to bind to

idx - index of the object

guard - a GuardedString parameter

Throws:

SQLException

selectSingleValue

public static Object selectSingleValue(Connection conn,
                                       String sql,
                                       SQLParam... params)
                                throws SQLException

Selects single value (first column) from select. It fetches only first row, does not check whether more rows are returned by select. If no row is returned, returns null

Parameters:

conn - JDBC connection

sql - Select statement with or without parameters

params - Parameters to use in statement

Returns:

first row and first column value

Throws:

SQLException

selectRows

public static List<Object[]> selectRows(Connection conn,
                                        String sql,
                                        SQLParam... params)
                                 throws SQLException

Selects all rows from select. It uses ResultSet.getMetaData() to find columns count and use ResultSet.getObject(int) to retrieve column value.

Parameters:

conn - JDBC connection

sql - SQL select with or without params

params -

Returns:

list of selected rows

Throws:

SQLException

executeUpdateStatement

public static int executeUpdateStatement(Connection conn,
                                         String sql,
                                         SQLParam... params)
                                  throws SQLException

Executes DML sql statement. This can be useful to execute insert/update/delete or some database specific statement in one call

Parameters:

conn -

sql -

params -

Returns:

number of rows affected as defined by PreparedStatement.executeUpdate()

Throws:

SQLException