/*
Copyright (c) 2003-2009 ITerative Consulting Pty Ltd. All Rights Reserved.
Redistribution and use in source and binary forms, with or without modification, are permitted
provided that the following conditions are met:
o Redistributions of source code must retain the above copyright notice, this list of conditions and
the following disclaimer.
o Redistributions in binary form must reproduce the above copyright notice, this list of conditions
and the following disclaimer in the documentation and/or other materials provided with the distribution.
o This jcTOOL Helper Class software, whether in binary or source form may not be used within,
or to derive, any other product without the specific prior written permission of the copyright holder
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING,
BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
package GenericDBMS;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.log4j.Logger;
import org.springframework.dao.CleanupFailureDataAccessException;
import org.springframework.dao.ConcurrencyFailureException;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.dao.DataRetrievalFailureException;
import org.springframework.dao.DeadlockLoserDataAccessException;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.dao.PermissionDeniedDataAccessException;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
import org.springframework.transaction.TransactionDefinition;
import Framework.ParameterHolder;
/**
* A collection of utility methods relating to database operations
*
*/
public class DBUtilities {
protected static Logger _log = Logger.getLogger(DBUtilities.class);
/**
* Transform a forte-style SQL string containing parameters prefixed by colon (:) into a Java
* style SQL string contain parameters represented by question marks (?). The names of the
* variables that were referenced are returned in pColList
* @param pSQL - the forte-style SQL string
* @param pCmdType - The type of the SQL command (DB_CV_SELECT, etc)
* @param pColList - An output of the list of variables referenced in the SQL
* @return - a Java-syntax SQL string, ready to pass to the <i>execute</i> method
*/
public static String translateSQL(String pSQL, ParameterHolder pCmdType, ParameterHolder pColList) {
StringBuilder assembler = new StringBuilder(pSQL.length());
int index;
int lastOffset = 0;
ArrayList<String> colList = new ArrayList<String>();
while ((index = pSQL.indexOf(":", lastOffset)) >= 0) {
assembler.append(pSQL.substring(lastOffset, index));
if (isContainedInQuotes(pSQL, index)) {
assembler.append(':');
lastOffset = index + 1;
continue;
}
assembler.append('?');
// ------------------------------------------------------------------------
// Now extract this token. We're at a colon, so skip it and any white
// space. We'll either then find an identifier character (a-z0-9_) or
// a quote. Skip to the end of this and mark this as what we're looking for
// ------------------------------------------------------------------------
lastOffset = index;
while (Character.isWhitespace(pSQL.charAt(++index)));
if (pSQL.charAt(index) == '\'') {
index = pSQL.indexOf('\'', index+1)+1;
}
else if (pSQL.charAt(index) == '"') {
index = pSQL.indexOf('"', index+1)+1;
}
else {
if (index < pSQL.length()){
while (Character.isJavaIdentifierPart(pSQL.charAt(index))) {
index++;
if (index == pSQL.length())
break;
}
}
}
String colName = pSQL.substring(lastOffset+1, index);
colList.add(colName);
lastOffset = index;
}
assembler.append(pSQL.substring(lastOffset));
pColList.setObject(colList);
// -----------------------------------------------
// Determine what the type of the SQL statement is
// -----------------------------------------------
String type = pSQL.trim();
pCmdType.setInt(Constants.DB_CV_OTHER);
if (type.length() > 6) {
type = type.substring(0,6).toLowerCase();
if (type.compareTo("select") == 0)
pCmdType.setInt(Constants.DB_CV_SELECT);
else if (type.compareTo("insert") == 0)
pCmdType.setInt(Constants.DB_CV_INSERT);
else if (type.compareTo("update") == 0)
pCmdType.setInt(Constants.DB_CV_UPDATE);
else if (type.compareTo("delete") == 0)
pCmdType.setInt(Constants.DB_CV_DELETE);
else if (type.compareTo("execut") == 0)
pCmdType.setInt(Constants.DB_CV_EXECUTE);
}
// ---------------------------------------------------------------------
// TF:6/8/07:If the first word of the SQL statement is "execute" then we
// need to surround the call with matched {}
// ---------------------------------------------------------------------
if (assembler.toString().matches("execute\\s+.*")) {
assembler.replace(0, 7, "{call");
assembler.append('}');
}
return assembler.toString();
}
private static boolean isContainedInQuotes(String pSQL, int maximumOffset) {
final int LOOKING_FOR_SINGLE_QUOTE = 1;
final int LOOKING_FOR_DOUBLE_QUOTE = 2;
final int NORMAL = 3;
int state = NORMAL;
for (int i = 0; i < maximumOffset; i++) {
switch (pSQL.charAt(i)) {
case '\'':
switch (state) {
case NORMAL:
// Start of a single quoted string
state = LOOKING_FOR_SINGLE_QUOTE;
break;
case LOOKING_FOR_SINGLE_QUOTE:
// end of a single quoted string
state = NORMAL;
break;
// No need to do anything for looking for double quotes, as they're not special characters here
}
break;
case '"':
switch (state) {
case NORMAL:
// Start of a single quoted string
state = LOOKING_FOR_DOUBLE_QUOTE;
break;
case LOOKING_FOR_DOUBLE_QUOTE:
// end of a single quoted string
state = NORMAL;
break;
// No need to do anything for looking for single quotes, as they're not special characters here
}
break;
case '\\':
// It's an escape character, we need to ignore the next character
i++;
break;
}
}
return state != NORMAL;
// int currentOffset = 0;
// boolean contained = false;
// int quoteCount = 0;
// int index = 0;
//
// while ((index = pSQL.indexOf("'", currentOffset)) >= 0) {
// if (index < maximumOffset) {
// quoteCount++;
// currentOffset = index + 1;
// } else {
// break;
// }
// }
//
// if ((quoteCount % 2) != 0) {
// contained = true;
// }
// return contained;
}
/**
* prepare a statement for execution with the passed connection. This includes
* translating parameters from :name format that forte uses to ? that java uses
* @param pDBSession
* @return
*/
public static PreparedStatement prepare(Connection pDBSession, String pSQL, ParameterHolder pInputDataSet, ParameterHolder pCmdType) {
StringBuilder assembler = new StringBuilder(pSQL.length());
int index;
int lastOffset = 0;
while ((index = pSQL.indexOf(":", lastOffset)) >= 0) {
assembler .append(pSQL.substring(lastOffset, index))
.append( '?');
// ------------------------------------------------------------------------
// Now extract this token. We're at a colon, so skip it and any white
// space. We'll either then find an identifier character (a-z0-9_) or
// a quote. Skip to the end of this and mark this as what we're looking for
// ------------------------------------------------------------------------
lastOffset = index;
while (Character.isWhitespace(pSQL.charAt(++index)));
if (pSQL.charAt(index) == '\'') {
index = pSQL.indexOf('\'', index+1)+1;
}
else if (pSQL.charAt(index) == '"') {
index = pSQL.indexOf('"', index+1)+1;
}
else {
while (Character.isJavaIdentifierPart(pSQL.charAt(index)))
index++;
}
// String colName = pSQL.substring(lastOffset+1, index);
lastOffset = index;
}
assembler.append(pSQL.substring(lastOffset));
_log.debug(assembler.toString());
// -----------------------------------------------
// Determine what the type of the SQL statement is
// -----------------------------------------------
String type = pSQL.trim();
pCmdType.setInt(Constants.DB_CV_OTHER);
if (type.length() > 6) {
type = type.substring(0,6).toLowerCase();
if (type.compareTo("select") == 0)
pCmdType.setInt(Constants.DB_CV_SELECT);
else if (type.compareTo("insert") == 0)
pCmdType.setInt(Constants.DB_CV_INSERT);
else if (type.compareTo("update") == 0)
pCmdType.setInt(Constants.DB_CV_UPDATE);
else if (type.compareTo("delete") == 0)
pCmdType.setInt(Constants.DB_CV_DELETE);
else if (type.compareTo("execut") == 0)
pCmdType.setInt(Constants.DB_CV_EXECUTE);
}
//PreparedStatement result = pDBSession.pre
return null;
}
/**
* Translate the passed exception into the appropriate DataAccessException for the passed
* database type with a generic database type
* @param e The exception that was thrown
* @return
*/
public static DataAccessException translateSQLException(SQLException e){
return new SQLErrorCodeSQLExceptionTranslator().translate("SQL tasks", null, e );
}
/**
* Translate the passed exception into the appropriate DataAccessException for the passed
* database type. If the database type is unknown, the standard translation is applied.
* @param dbType one of the constants represnting the database type
* @param e The exception that was thrown
* @return
*/
public static DataAccessException translateSQLException(int dbType, SQLException e){
switch (dbType) {
case Constants.DB_VT_DB2:
return new SQLErrorCodeSQLExceptionTranslator("DB2").translate("SQL tasks", null, e );
case Constants.DB_VT_INFORMIX:
return new SQLErrorCodeSQLExceptionTranslator("Informix").translate("SQL tasks", null, e );
case Constants.DB_VT_INGRES:
return new SQLErrorCodeSQLExceptionTranslator("PostgreSQL").translate("SQL tasks", null, e );
case Constants.DB_VT_MYSQL:
return new SQLErrorCodeSQLExceptionTranslator("MySQL").translate("SQL tasks", null, e );
case Constants.DB_VT_ORACLE:
return new SQLErrorCodeSQLExceptionTranslator("Oracle").translate("SQL tasks", null, e );
case Constants.DB_VT_SYBASE:
return new SQLErrorCodeSQLExceptionTranslator("Sybase").translate("SQL tasks", null, e );
default:
return new SQLErrorCodeSQLExceptionTranslator().translate("SQL tasks", null, e );
}
}
public static int translateReasonCode(int dbType, SQLException ex){
return translateReasonCode(dbType, translateSQLException(dbType, ex));
}
public static int translateReasonCode(int dbType, DataAccessException ex){
if (ex instanceof CleanupFailureDataAccessException)
return Constants.DB_ER_UNKNOWN;
else if (ex instanceof ConcurrencyFailureException)
return Constants.DB_ER_DEADLOCK;
else if (ex instanceof DataAccessResourceFailureException)
return Constants.DB_ER_UNKNOWN;
else if (ex instanceof DataIntegrityViolationException)
return Constants.DB_ER_CONSTRAINT;
else if (ex instanceof DataRetrievalFailureException)
return Constants.DB_ER_UNKNOWN;
else if (ex instanceof DeadlockLoserDataAccessException)
return Constants.DB_ER_DEADLOCK;
else if (ex instanceof InvalidDataAccessApiUsageException)
return Constants.DB_ER_UNKNOWN;
else if (ex instanceof BadSqlGrammarException)
return Constants.DB_ER_SYNTAXERROR;
else if (ex instanceof PermissionDeniedDataAccessException)
return Constants.DB_ER_DATABASESECURITY;
else
return Constants.DB_ER_UNKNOWN;
}
/**
* Begin a transaction on the current data source with the passed transaction definition
* @param pDataSource
* @param pTransaction
* @deprecated use {@link #TransactionMgr.beginTransaction(String, TransactionDefinition)} instead
*/
public static void beginTransaction(String pDataSource, TransactionDefinition pTransaction) {
TransactionMgr.beginTransaction(pDataSource, pTransaction);
}
/**
* Begin a transaction on the current data source with the passed transaction definition
* @param pDataSource
* @param pTransaction
* @deprecated use {@link #TransactionMgr.beginTransaction(DBConnectionManager, TransactionDefinition)} instead
*/
public static void beginTransaction(DBConnectionManager pDataSource, TransactionDefinition pTransaction) {
TransactionMgr.beginTransaction(pDataSource, pTransaction);
}
/**
* Begin a dependent transaction on the passed data source
* @param pDataSource
* @deprecated use {@link #TransactionMgr.beginTransaction(String)} instead
*/
public static void beginTransaction(String pDataSource) {
TransactionMgr.beginTransaction(pDataSource);
}
/**
* Begin a dependent transaction on the passed data source
* @param pDataSource
* @deprecated use {@link #TransactionMgr.beginTransaction(DBConnectionManager)} instead
*/
public static void beginTransaction(DBConnectionManager pDataSource) {
TransactionMgr.beginTransaction(pDataSource);
}
/**
* Determine if the passed user transaction is currently active or inactive
* @param pDataSource - the DataSource to examine
* @return false if the passed transaction is null, or it is not currently active.
* @deprecated use {@link #TranactionMgr.isActive()} instead
*/
public static boolean isActive(String pDataSource) {
return TransactionMgr.isActive(pDataSource);
}
/**
* Determine if the passed user transaction is currently active or inactive
* @param pDataSource - the DataSource to examine
* @return false if the passed transaction is null, or it is not currently active.
* @deprecated use {@link #TranactionMgr.isActive()} instead
*/
public static boolean isActive(DBConnectionManager pDataSource) {
return TransactionMgr.isActive(pDataSource);
}
/**
* if there are multiple transaction nested, return true, otherwise return false
* @return
* @deprecated use {@link #TranactionMgr.isDependent()} instead
*/
public static boolean isDependent() {
return TransactionMgr.isDependent();
}
/**
* returns the transaction ID for the current transaction
* @return
* @deprecated use {@link #TranactionMgr.getTransactionID()} instead
*/
public static int getTransactionID() {
return TransactionMgr.getTransactionID();
}
/**
* Mark the current transaction for rollback only
*
* @deprecated use {@link #TranactionMgr.setRollback()} instead
*/
public static void setRollback() {
TransactionMgr.setRollback();
}
/**
* Rollback the current transaction. Do not call this method is calling commit() threw an
* exception -- the transaction will have already been rolled back.
* @deprecated use {@link #TranactionMgr.rollbackTransaction()} instead
*/
public static void rollbackTransaction() {
TransactionMgr.rollbackTransaction();
}
/**
* Commits the current transaction.
* @deprecated use {@link #TranactionMgr.commitTransaction()} instead
*/
public static void commitTransaction() {
TransactionMgr.commitTransaction();
}
/**
* The getOuterTransactionDesc() method provides access to the outermost transaction running for the current transaction.
* @return If no transaction is in process, the return value is null.
* @deprecated use {@link #TranactionMgr.getOuterTransactionDesc()} instead
*/
public static TransactionMgr.TransactionData getOuterTransactionDesc() {
return TransactionMgr.getOuterTransactionDesc();
}
public static void main(String[] args) {
ParameterHolder h1 = new ParameterHolder(), h2 = new ParameterHolder();
String result = DBUtilities.translateSQL("blah :fred = \"2008-02-04 13:19:12.000\" or :fred = '2008-02-04 15:00:06' or :'blad' = :\"blah\"", h1, h2);
System.out.println(result);
}
}