Package org.jumpmind.db.sql

Class JdbcSqlTemplate

java.lang.Object

org.jumpmind.db.sql.AbstractSqlTemplate

org.jumpmind.db.sql.JdbcSqlTemplate

All Implemented Interfaces:

ISqlTemplate

Direct Known Subclasses:

Db2JdbcSqlTemplate, DerbyJdbcSqlTemplate, FirebirdJdbcSqlTemplate, GenericJdbcSqlTemplate, H2JdbcSqlTemplate, HanaSqlJdbcSqlTemplate, HsqlDb2JdbcSqlTemplate, HsqlDbJdbcSqlTemplate, InformixJdbcSqlTemplate, IngresJdbcSqlTemplate, InterbaseJdbcSqlTemplate, MsSqlJdbcSqlTemplate, MySqlJdbcSqlTemplate, NuoDbJdbcSqlTemplate, OracleJdbcSqlTemplate, PostgreSqlJdbcSqlTemplate, RaimaJdbcSqlTemplate, RedshiftJdbcSqlTemplate, SqliteJdbcSqlTemplate, SybaseJdbcSqlTemplate, VoltDbJdbcSqlTemplate

public class JdbcSqlTemplate
extends AbstractSqlTemplate
implements ISqlTemplate

Field Summary

Fields

Modifier and Type	Field	Description
protected DataSource	dataSource
protected int[]	dataTruncationCodes
protected String[]	dataTruncationStates
protected int[]	deadlockCodes
protected String[]	deadlockSqlStates
protected int[]	foreignKeyChildExistsViolationCodes
protected String[]	foreignKeyChildExistsViolationMessageParts
protected String[]	foreignKeyChildExistsViolationSqlStates
protected int[]	foreignKeyViolationCodes
protected String[]	foreignKeyViolationMessageParts
protected String[]	foreignKeyViolationSqlStates
protected boolean	isEmptyStringNulled
protected int	isolationLevel
protected SymmetricLobHandler	lobHandler
protected int[]	objectAlreadyExistsCodes
protected String[]	objectAlreadyExistsStates
protected int[]	objectDoesNotExistCodes
protected String[]	objectDoesNotExistStates
protected int[]	primaryKeyViolationCodes
protected String[]	primaryKeyViolationMessageParts
protected String[]	primaryKeyViolationSqlStates
protected boolean	requiresAutoCommitFalseToSetFetchSize
protected SqlTemplateSettings	settings
protected Boolean	supportsGetGeneratedKeys
protected String[]	uniqueKeyViolationNameRegex

Fields inherited from class org.jumpmind.db.sql.AbstractSqlTemplate

dateOverrideToTimestamp, identifierQuoteString, logSqlBuilder

Constructor Summary

Constructors

Constructor	Description
JdbcSqlTemplate(DataSource dataSource, SqlTemplateSettings settings, SymmetricLobHandler lobHandler, DatabaseInfo databaseInfo)

Method Summary

Modifier and Type	Method	Description
protected boolean	allowsNullForIdentityColumn()
static void	close(boolean autoCommitValue, int transactionIsolationLevel, Connection c)
static void	close(boolean autoCommitValue, Connection c)
static void	close(Connection c)
static void	close(PreparedStatement ps)
static void	close(ResultSet rs)
static void	close(Statement stmt)
boolean	doesObjectAlreadyExist(Throwable ex)
boolean	doesObjectNotExist(Throwable ex)
void	doSetValue(PreparedStatement ps, int parameterPosition, Object argValue)	Set the value for prepared statements specified parameter index using the passed in value.
<T> T	execute(IConnectionCallback<T> callback)
protected SQLException	findSQLException(Throwable ex)
protected Connection	getConnection()
int	getDatabaseMajorVersion()
int	getDatabaseMinorVersion()
String	getDatabaseProductName()
String	getDatabaseProductVersion()
DataSource	getDataSource()
String	getDriverName()
String	getDriverVersion()
int	getIsolationLevel()
SymmetricLobHandler	getLobHandler()
<T> T	getObjectFromResultSet(ResultSet rs, Class<T> clazz)
static Object	getResultSetValue(ResultSet rs, int index, boolean readStringsAsBytes)	Deprecated.
static Object	getResultSetValue(ResultSet rs, ResultSetMetaData metaData, int index, boolean readStringsAsBytes, boolean returnLobObjects)	Retrieve a JDBC column value from a ResultSet, using the most appropriate value type.
protected String	getSelectLastInsertIdSql(String sequenceName)
SqlTemplateSettings	getSettings()
Set<String>	getSqlKeywords()
String	getUniqueKeyViolationIndexName(Throwable ex)
protected int	getUpdateCount(Statement stmt)
long	insertWithGeneratedKey(String sql, String column, String sequenceName, Object[] args, int[] types)
protected long	insertWithGeneratedKey(Connection conn, String sql, String column, String sequenceName, Object[] args, int[] types)
boolean	isDataTruncationViolation(Throwable ex)
boolean	isDeadlock(Throwable ex)
boolean	isForeignKeyChildExistsViolation(Throwable ex)
boolean	isForeignKeyViolation(Throwable ex)
boolean	isRequiresAutoCommitFalseToSetFetchSize()
boolean	isStoresLowerCaseIdentifiers()
boolean	isStoresMixedCaseQuotedIdentifiers()
boolean	isStoresUpperCaseIdentifiers()
boolean	isUniqueKeyViolation(Throwable ex)
static String	lookupColumnName(ResultSetMetaData resultSetMetaData, int columnIndex)	Determine the column name to use.
byte[]	queryForBlob(String sql, int jdbcTypeCode, String jdbcTypeName, Object... args)
byte[]	queryForBlob(String sql, Object... args)	Deprecated.
String	queryForClob(String sql, int jdbcTypeCode, String jdbcTypeName, Object... args)
String	queryForClob(String sql, Object... args)	Deprecated.
<T> ISqlReadCursor<T>	queryForCursor(String sql, ISqlRowMapper<T> mapper, boolean returnLobObjects)
<T> ISqlReadCursor<T>	queryForCursor(String sql, ISqlRowMapper<T> mapper, Object[] args, int[] types)
<T> ISqlReadCursor<T>	queryForCursor(String sql, ISqlRowMapper<T> mapper, IConnectionHandler connectionHandler, Object[] args, int[] types)
<T> ISqlReadCursor<T>	queryForCursor(String sql, ISqlRowMapper<T> mapper, IConnectionHandler connectionHandler, Object[] args, int[] types, boolean returnLobObjects)
Map<String,Object>	queryForMap(String sql, Object... args)
<T> T	queryForObject(String sql, Class<T> clazz, Object... args)
protected void	setDecimalValue(PreparedStatement ps, int i, Object arg, int argType)
void	setIsolationLevel(int isolationLevel)
protected void	setNanOrNull(PreparedStatement ps, int i, Object arg, int argType)
void	setSettings(SqlTemplateSettings settings)
protected void	setTinyIntValue(PreparedStatement ps, int i, Object arg, int argType)
void	setValues(PreparedStatement ps, Object[] args)
void	setValues(PreparedStatement ps, Object[] args, int[] argTypes, org.springframework.jdbc.support.lob.LobHandler lobHandler)
ISqlTransaction	startSqlTransaction()
ISqlTransaction	startSqlTransaction(boolean autoCommit)
boolean	supportsGetGeneratedKeys()
boolean	supportsReturningKeys()
void	testConnection()
int	update(boolean autoCommit, boolean failOnError, boolean failOnDrops, boolean failOnSequenceCreate, int commitRate, ISqlResultsListener resultsListener, ISqlStatementSource source)
int	update(boolean autoCommit, boolean failOnError, int commitRate, String... sql)
int	update(boolean autoCommit, boolean failOnError, int commitRate, ISqlResultsListener resultsListener, String... sql)
int	update(String sql, Object[] args, int[] types)
protected int	verifyArgType(Object arg, int argType)

Methods inherited from class org.jumpmind.db.sql.AbstractSqlTemplate

expandArgs, expandSql, query, query, query, query, query, query, query, query, query, query, query, query, queryForCursor, queryForCursor, queryForInt, queryForInt, queryForLong, queryForMap, queryForMap, queryForObject, queryForRow, queryForString, queryWithHandler, translate, translate, update

Methods inherited from class java.lang.Object

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

Methods inherited from interface org.jumpmind.db.sql.ISqlTemplate

query, query, query, query, query, query, query, query, query, query, queryForCursor, queryForCursor, queryForInt, queryForInt, queryForLong, queryForMap, queryForMap, queryForObject, queryForRow, queryForString, queryWithHandler, translate, update

Field Details

dataSource

protected DataSource dataSource

requiresAutoCommitFalseToSetFetchSize

protected boolean requiresAutoCommitFalseToSetFetchSize

settings

protected SqlTemplateSettings settings

lobHandler

protected SymmetricLobHandler lobHandler

supportsGetGeneratedKeys

protected Boolean supportsGetGeneratedKeys

primaryKeyViolationCodes

protected int[] primaryKeyViolationCodes

primaryKeyViolationSqlStates

protected String[] primaryKeyViolationSqlStates

primaryKeyViolationMessageParts

protected String[] primaryKeyViolationMessageParts

uniqueKeyViolationNameRegex

protected String[] uniqueKeyViolationNameRegex

foreignKeyViolationCodes

protected int[] foreignKeyViolationCodes

foreignKeyViolationSqlStates

protected String[] foreignKeyViolationSqlStates

foreignKeyViolationMessageParts

protected String[] foreignKeyViolationMessageParts

foreignKeyChildExistsViolationCodes

protected int[] foreignKeyChildExistsViolationCodes

foreignKeyChildExistsViolationSqlStates

protected String[] foreignKeyChildExistsViolationSqlStates

foreignKeyChildExistsViolationMessageParts

protected String[] foreignKeyChildExistsViolationMessageParts

deadlockCodes

protected int[] deadlockCodes

deadlockSqlStates

protected String[] deadlockSqlStates

dataTruncationCodes

protected int[] dataTruncationCodes

dataTruncationStates

protected String[] dataTruncationStates

objectAlreadyExistsCodes

protected int[] objectAlreadyExistsCodes

objectAlreadyExistsStates

protected String[] objectAlreadyExistsStates

objectDoesNotExistCodes

protected int[] objectDoesNotExistCodes

objectDoesNotExistStates

protected String[] objectDoesNotExistStates

isolationLevel

protected int isolationLevel

isEmptyStringNulled

protected boolean isEmptyStringNulled

Constructor Details

JdbcSqlTemplate

public JdbcSqlTemplate(DataSource dataSource,
 SqlTemplateSettings settings,
 SymmetricLobHandler lobHandler,
 DatabaseInfo databaseInfo)

Method Details

getConnection

protected Connection getConnection()
                            throws SQLException

Throws:

SQLException

getDataSource

public DataSource getDataSource()

isRequiresAutoCommitFalseToSetFetchSize

public boolean isRequiresAutoCommitFalseToSetFetchSize()

setSettings

public void setSettings(SqlTemplateSettings settings)

getSettings

public SqlTemplateSettings getSettings()

getLobHandler

public SymmetricLobHandler getLobHandler()

queryForCursor

public <T> ISqlReadCursor<T> queryForCursor(String sql,
 ISqlRowMapper<T> mapper,
 Object[] args,
 int[] types)

Specified by:

queryForCursor in interface ISqlTemplate

queryForCursor

public <T> ISqlReadCursor<T> queryForCursor(String sql,
 ISqlRowMapper<T> mapper,
 IConnectionHandler connectionHandler,
 Object[] args,
 int[] types)

Specified by:

queryForCursor in interface ISqlTemplate

Overrides:

queryForCursor in class AbstractSqlTemplate

queryForCursor

public <T> ISqlReadCursor<T> queryForCursor(String sql,
 ISqlRowMapper<T> mapper,
 boolean returnLobObjects)

Specified by:

queryForCursor in interface ISqlTemplate

Overrides:

queryForCursor in class AbstractSqlTemplate

queryForCursor

public <T> ISqlReadCursor<T> queryForCursor(String sql,
 ISqlRowMapper<T> mapper,
 IConnectionHandler connectionHandler,
 Object[] args,
 int[] types,
 boolean returnLobObjects)

getIsolationLevel

public int getIsolationLevel()

setIsolationLevel

public void setIsolationLevel(int isolationLevel)

queryForObject

public <T> T queryForObject(String sql,
 Class<T> clazz,
 Object... args)

Specified by:

queryForObject in interface ISqlTemplate

queryForBlob

@Deprecated
public byte[] queryForBlob(String sql,
 Object... args)

Deprecated.

queryForBlob

public byte[] queryForBlob(String sql,
 int jdbcTypeCode,
 String jdbcTypeName,
 Object... args)

Specified by:

queryForBlob in interface ISqlTemplate

queryForClob

@Deprecated
public String queryForClob(String sql,
 Object... args)

Deprecated.

queryForClob

public String queryForClob(String sql,
 int jdbcTypeCode,
 String jdbcTypeName,
 Object... args)

Specified by:

queryForClob in interface ISqlTemplate

queryForMap

public Map<String,Object> queryForMap(String sql,
 Object... args)

Specified by:

queryForMap in interface ISqlTemplate

startSqlTransaction

public ISqlTransaction startSqlTransaction(boolean autoCommit)

Specified by:

startSqlTransaction in interface ISqlTemplate

startSqlTransaction

public ISqlTransaction startSqlTransaction()

Specified by:

startSqlTransaction in interface ISqlTemplate

getUpdateCount

protected int getUpdateCount(Statement stmt)
                      throws SQLException

Throws:

SQLException

update

public int update(String sql,
 Object[] args,
 int[] types)

Specified by:

update in interface ISqlTemplate

update

public int update(boolean autoCommit,
 boolean failOnError,
 int commitRate,
 String... sql)

Specified by:

update in interface ISqlTemplate

update

public int update(boolean autoCommit,
 boolean failOnError,
 int commitRate,
 ISqlResultsListener resultsListener,
 String... sql)

Specified by:

update in interface ISqlTemplate

update

public int update(boolean autoCommit,
 boolean failOnError,
 boolean failOnDrops,
 boolean failOnSequenceCreate,
 int commitRate,
 ISqlResultsListener resultsListener,
 ISqlStatementSource source)

Specified by:

update in interface ISqlTemplate

testConnection

public void testConnection()

Specified by:

testConnection in interface ISqlTemplate

execute

public <T> T execute(IConnectionCallback<T> callback)

lookupColumnName

public static String lookupColumnName(ResultSetMetaData resultSetMetaData,
 int columnIndex)
                               throws SQLException

Determine the column name to use. The column name is determined based on a lookup using ResultSetMetaData.

This method implementation takes into account recent clarifications expressed in the JDBC 4.0 specification:

columnLabel - the label for the column specified with the SQL AS clause. If the SQL AS clause was not specified, then the label is the name of the column.

Parameters:

resultSetMetaData - the current meta data to use

columnIndex - the index of the column for the look up

Returns:

the column name to use

Throws:

SQLException - in case of lookup failure

getResultSetValue

@Deprecated
public static Object getResultSetValue(ResultSet rs,
 int index,
 boolean readStringsAsBytes)
                                throws SQLException

Deprecated.

Throws:

SQLException

getResultSetValue

public static Object getResultSetValue(ResultSet rs,
 ResultSetMetaData metaData,
 int index,
 boolean readStringsAsBytes,
 boolean returnLobObjects)
                                throws SQLException

Retrieve a JDBC column value from a ResultSet, using the most appropriate value type. The returned value should be a detached value object, not having any ties to the active ResultSet: in particular, it should not be a Blob or Clob object but rather a byte array respectively String representation.

Uses the getObject(index) method, but includes additional "hacks" to get around Oracle 10g returning a non-standard object for its TIMESTAMP datatype and a java.sql.Date for DATE columns leaving out the time portion: These columns will explicitly be extracted as standard java.sql.Timestamp object.

Parameters:

rs - is the ResultSet holding the data

metaData -

index - is the column index

readStringsAsBytes - TODO

Returns:

the value object

Throws:

SQLException - if thrown by the JDBC API

See Also:

• Blob
• Clob
• Timestamp

close

public static void close(ResultSet rs)

close

public static void close(PreparedStatement ps)

close

public static void close(Statement stmt)

close

public static void close(boolean autoCommitValue,
 Connection c)

close

public static void close(boolean autoCommitValue,
 int transactionIsolationLevel,
 Connection c)

close

public static void close(Connection c)

getDatabaseMajorVersion

public int getDatabaseMajorVersion()

Specified by:

getDatabaseMajorVersion in interface ISqlTemplate

getDatabaseMinorVersion

public int getDatabaseMinorVersion()

Specified by:

getDatabaseMinorVersion in interface ISqlTemplate

getDatabaseProductName

public String getDatabaseProductName()

Specified by:

getDatabaseProductName in interface ISqlTemplate

isStoresMixedCaseQuotedIdentifiers

public boolean isStoresMixedCaseQuotedIdentifiers()

Specified by:

isStoresMixedCaseQuotedIdentifiers in interface ISqlTemplate

isStoresUpperCaseIdentifiers

public boolean isStoresUpperCaseIdentifiers()

Specified by:

isStoresUpperCaseIdentifiers in interface ISqlTemplate

isStoresLowerCaseIdentifiers

public boolean isStoresLowerCaseIdentifiers()

Specified by:

isStoresLowerCaseIdentifiers in interface ISqlTemplate

getDatabaseProductVersion

public String getDatabaseProductVersion()

Specified by:

getDatabaseProductVersion in interface ISqlTemplate

getDriverName

public String getDriverName()

Specified by:

getDriverName in interface ISqlTemplate

getDriverVersion

public String getDriverVersion()

Specified by:

getDriverVersion in interface ISqlTemplate

getSqlKeywords

public Set<String> getSqlKeywords()

Specified by:

getSqlKeywords in interface ISqlTemplate

supportsGetGeneratedKeys

public boolean supportsGetGeneratedKeys()

Specified by:

supportsGetGeneratedKeys in interface ISqlTemplate

supportsReturningKeys

public boolean supportsReturningKeys()

allowsNullForIdentityColumn

protected boolean allowsNullForIdentityColumn()

getSelectLastInsertIdSql

protected String getSelectLastInsertIdSql(String sequenceName)

insertWithGeneratedKey

public long insertWithGeneratedKey(String sql,
 String column,
 String sequenceName,
 Object[] args,
 int[] types)

Specified by:

insertWithGeneratedKey in interface ISqlTemplate

insertWithGeneratedKey

protected long insertWithGeneratedKey(Connection conn,
 String sql,
 String column,
 String sequenceName,
 Object[] args,
 int[] types)
                               throws SQLException

Throws:

SQLException

isUniqueKeyViolation

public boolean isUniqueKeyViolation(Throwable ex)

Specified by:

isUniqueKeyViolation in interface ISqlTemplate

isForeignKeyViolation

public boolean isForeignKeyViolation(Throwable ex)

Specified by:

isForeignKeyViolation in interface ISqlTemplate

isForeignKeyChildExistsViolation

public boolean isForeignKeyChildExistsViolation(Throwable ex)

Specified by:

isForeignKeyChildExistsViolation in interface ISqlTemplate

Overrides:

isForeignKeyChildExistsViolation in class AbstractSqlTemplate

isDeadlock

public boolean isDeadlock(Throwable ex)

Specified by:

isDeadlock in interface ISqlTemplate

Overrides:

isDeadlock in class AbstractSqlTemplate

isDataTruncationViolation

public boolean isDataTruncationViolation(Throwable ex)

Specified by:

isDataTruncationViolation in interface ISqlTemplate

doesObjectAlreadyExist

public boolean doesObjectAlreadyExist(Throwable ex)

Specified by:

doesObjectAlreadyExist in interface ISqlTemplate

Overrides:

doesObjectAlreadyExist in class AbstractSqlTemplate

doesObjectNotExist

public boolean doesObjectNotExist(Throwable ex)

Specified by:

doesObjectNotExist in interface ISqlTemplate

Overrides:

doesObjectNotExist in class AbstractSqlTemplate

findSQLException

protected SQLException findSQLException(Throwable ex)

getObjectFromResultSet

public <T> T getObjectFromResultSet(ResultSet rs,
 Class<T> clazz)
                             throws SQLException

Throws:

SQLException

setValues

public void setValues(PreparedStatement ps,
 Object[] args,
 int[] argTypes,
 org.springframework.jdbc.support.lob.LobHandler lobHandler)
               throws SQLException

Throws:

SQLException

setTinyIntValue

protected void setTinyIntValue(PreparedStatement ps,
 int i,
 Object arg,
 int argType)
                        throws SQLException

Throws:

SQLException

setDecimalValue

protected void setDecimalValue(PreparedStatement ps,
 int i,
 Object arg,
 int argType)
                        throws SQLException

Throws:

SQLException

setNanOrNull

protected void setNanOrNull(PreparedStatement ps,
 int i,
 Object arg,
 int argType)
                     throws SQLException

Throws:

SQLException

verifyArgType

protected int verifyArgType(Object arg,
 int argType)

setValues

public void setValues(PreparedStatement ps,
 Object[] args)
               throws SQLException

Throws:

SQLException

doSetValue

public void doSetValue(PreparedStatement ps,
 int parameterPosition,
 Object argValue)
                throws SQLException

Set the value for prepared statements specified parameter index using the passed in value. This method can be overridden by sub-classes if needed.

Parameters:

ps - the PreparedStatement

parameterPosition - index of the parameter position

argValue - the value to set

Throws:

SQLException

getUniqueKeyViolationIndexName

public String getUniqueKeyViolationIndexName(Throwable ex)

Specified by:

getUniqueKeyViolationIndexName in interface ISqlTemplate

Overrides:

getUniqueKeyViolationIndexName in class AbstractSqlTemplate