/** static method testing jdbc framework with a simple select statement using parameters */
public static void testSimpleSelectWithParams() {
try {
SQLExecutor sqlExec = new SQLExecutor(getConnectionPool());
// sqlExec.addParam(new Integer(8));
sqlExec.addParam(8);
sqlExec.addParam(Date.valueOf("2003-05-10"));
SQLResults res = sqlExec.runQueryCloseCon(sqlSelect);
String out = "SQL Results:\n";
for (int row = 0; row < res.getRowCount(); row++)
out +=
res.getLong(row, "TEST_ID")
+ " "
+ res.getString(row, "NOTES")
+ " "
+ res.getDate(row, "TEST_DT")
+ " "
+ res.getDouble(row, "AMOUNT")
+ " "
+ res.getString(row, "CODE")
+ "\n";
System.out.println(out);
} catch (DatabaseException e) {
if (e.isDataIntegrityViolation()) applyDataIntegrityViolationRecovery();
}
}
/**
* INTERNAL: Execute the query building the objects directly from the database result-set.
*
* @exception DatabaseException - an error has occurred on the database
* @return object - the first object found or null if none.
*/
protected Object executeObjectLevelReadQueryFromResultSet() throws DatabaseException {
UnitOfWorkImpl unitOfWork = (UnitOfWorkImpl) getSession();
DatabaseAccessor accessor = (DatabaseAccessor) unitOfWork.getAccessor();
DatabasePlatform platform = accessor.getPlatform();
DatabaseCall call = (DatabaseCall) getCall().clone();
call.setQuery(this);
call.translate(this.translationRow, null, unitOfWork);
Statement statement = null;
ResultSet resultSet = null;
boolean exceptionOccured = false;
try {
accessor.incrementCallCount(unitOfWork);
statement = call.prepareStatement(accessor, this.translationRow, unitOfWork);
resultSet = accessor.executeSelect(call, statement, unitOfWork);
ResultSetMetaData metaData = resultSet.getMetaData();
Vector results = new Vector();
ObjectBuilder builder = this.descriptor.getObjectBuilder();
while (resultSet.next()) {
results.add(
builder.buildWorkingCopyCloneFromResultSet(
this,
this.joinedAttributeManager,
resultSet,
unitOfWork,
accessor,
metaData,
platform));
}
return results;
} catch (SQLException exception) {
exceptionOccured = true;
DatabaseException commException =
accessor.processExceptionForCommError(session, exception, call);
if (commException != null) throw commException;
throw DatabaseException.sqlException(exception, call, accessor, unitOfWork, false);
} finally {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
accessor.releaseStatement(statement, call.getSQLString(), call, unitOfWork);
}
} catch (SQLException exception) {
if (!exceptionOccured) {
// in the case of an external connection pool the connection may be null after the
// statement release
// if it is null we will be unable to check the connection for a comm error and
// therefore must return as if it was not a comm error.
DatabaseException commException =
accessor.processExceptionForCommError(session, exception, call);
if (commException != null) throw commException;
throw DatabaseException.sqlException(exception, call, accessor, session, false);
}
}
}
}
/**
* INTERNAL: Return the value of the field from the row or a value holder on the query to obtain
* the object. Check for batch + aggregation reading.
*/
public Object valueFromRow(
AbstractRecord row,
JoinedAttributeManager joinManager,
ObjectBuildingQuery query,
AbstractSession executionSession)
throws DatabaseException {
Ref ref = (Ref) row.get(getField());
if (ref == null) {
return null;
}
Struct struct;
try {
((DatabaseAccessor) executionSession.getAccessor()).incrementCallCount(executionSession);
java.sql.Connection connection =
((DatabaseAccessor) executionSession.getAccessor()).getConnection();
struct =
(Struct) executionSession.getPlatform().getRefValue(ref, executionSession, connection);
} catch (java.sql.SQLException exception) {
throw DatabaseException.sqlException(exception, executionSession, false);
}
AbstractRecord targetRow =
((ObjectRelationalDataTypeDescriptor) getReferenceDescriptor())
.buildRowFromStructure(struct);
((DatabaseAccessor) executionSession.getAccessor()).decrementCallCount();
return getReferenceDescriptor().getObjectBuilder().buildObject(query, targetRow, joinManager);
}
/** INTERNAL: Build a ADT structure from the row data. */
public Struct buildStructureFromRow(
AbstractRecord row, AbstractSession session, java.sql.Connection connection)
throws DatabaseException {
Struct structure;
boolean reconnected = false;
try {
if (connection == null) {
((DatabaseAccessor) session.getAccessor()).incrementCallCount(session);
reconnected = true;
connection = ((DatabaseAccessor) session.getAccessor()).getConnection();
}
Object[] fields = new Object[getOrderedFields().size()];
for (int index = 0; index < getOrderedFields().size(); index++) {
DatabaseField field = (DatabaseField) getOrderedFields().elementAt(index);
fields[index] = row.get(field);
}
structure =
session.getPlatform().createStruct(getStructureName(), fields, session, connection);
} catch (java.sql.SQLException exception) {
throw DatabaseException.sqlException(exception, session, false);
} finally {
if (reconnected) {
((DatabaseAccessor) session.getAccessor()).decrementCallCount();
}
}
return structure;
}
/**
* INTERNAL: Build and return the appropriate field value for the specified set of nested rows.
* The database better be expecting an ARRAY. It looks like we can ignore inheritance here....
*/
public Object buildFieldValueFromNestedRows(
Vector nestedRows, String structureName, AbstractSession session) throws DatabaseException {
Object[] fields = new Object[nestedRows.size()];
java.sql.Connection connection = ((DatabaseAccessor) session.getAccessor()).getConnection();
boolean reconnected = false;
try {
if (connection == null) {
((DatabaseAccessor) session.getAccessor()).incrementCallCount(session);
reconnected = true;
connection = ((DatabaseAccessor) session.getAccessor()).getConnection();
}
int i = 0;
for (Enumeration stream = nestedRows.elements(); stream.hasMoreElements(); ) {
AbstractRecord nestedRow = (AbstractRecord) stream.nextElement();
fields[i++] = this.buildStructureFromRow(nestedRow, session, connection);
}
return session.getPlatform().createArray(structureName, fields, session, connection);
} catch (java.sql.SQLException exception) {
throw DatabaseException.sqlException(exception, session, false);
} finally {
if (reconnected) {
((DatabaseAccessor) session.getAccessor()).decrementCallCount();
}
}
}
/**
* static method testing jdbc framework with multiple updates and intentional sql exceptions that
* are trapped in appropriate exception handling blocks
*/
public static void testMultipleUpdatesAndTransWithException() {
ConnectionPool conPool = getConnectionPool();
SQLExecutor sqlExec = new SQLExecutor(conPool);
try {
sqlExec.setAutoCommit(false);
sqlExec.addParam(new Integer(7));
sqlExec.runQuery("UPDATE JDBC_TEST SET CODE = 'Z' WHERE TEST_ID = ?");
sqlExec.addParam(new Integer(6));
// integrity constraint violation
sqlExec.runQuery("UPDATE JDBC_TEST SET TEST_ID = NULL WHERE TEST_ID = ?");
sqlExec.commitTrans();
System.out.println("transaction committed");
} catch (DatabaseException e) {
System.out.println("Error code=" + e.getSQLErrorCode() + ", SQLState=" + e.getSQLState());
if (e.isDataIntegrityViolation()) System.out.println("data integrity violation");
else if (e.isBadSQLGrammar()) System.out.println("bad SQL grammar");
else if (e.isNonExistentTableOrViewOrCol()) System.out.println("Non existent table or view");
System.out.println(e.getMessage());
sqlExec.rollbackTrans();
System.out.println("transaction rolled back");
} finally {
sqlExec.closeConnection();
}
}
/**
* static method testing jdbc framework with a simple select statement using parameters and the
* result set's .toString() method
*/
public static void testSimpleSelectWithParamsAndToString() {
try {
driverName = "oracle.jdbc.driver.OracleDriver";
connURL = "jdbc:oracle:thin:@SNOWMASS:1521:WDEV";
username = "******";
password = "******";
ConnectionPool conPool = new ConnectionPool(1, driverName, connURL, username, password);
SQLExecutor sqlExec = new SQLExecutor(conPool);
// sqlExec.addParam(new Integer(8));
sqlExec.addParam(8);
sqlExec.addParam(Date.valueOf("2003-05-10"));
SQLResults res = sqlExec.runQueryCloseCon("SELECT * FROM UTILITY_STATUS");
String out = "SQL Results:\n" + res.toString();
System.out.println(out);
} catch (DatabaseException e) {
if (e.isDataIntegrityViolation()) applyDataIntegrityViolationRecovery();
}
}
private synchronized void init() throws DatabaseException {
status = PwmService.STATUS.OPENING;
LOGGER.debug("opening connection to database " + this.dbConfiguration.getConnectionString());
connection = openDB(dbConfiguration);
for (final DatabaseTable table : DatabaseTable.values()) {
initTable(connection, table, dbConfiguration);
}
status = PwmService.STATUS.OPEN;
try {
put(
DatabaseTable.PWM_META,
KEY_ENGINE_START_PREFIX + instanceID,
PwmConstants.DEFAULT_DATETIME_FORMAT.format(new java.util.Date()));
} catch (DatabaseException e) {
final String errorMsg = "error writing engine start time value: " + e.getMessage();
throw new DatabaseException(new ErrorInformation(PwmError.ERROR_DB_UNAVAILABLE, errorMsg));
}
}
/**
* static method testing jdbc framework with a select for update statement (that locks a record)
* and an update statement that generates a deadlock.
*/
public static void testDeadlockException() {
ConnectionPool conPool = getConnectionPool();
SQLExecutor sqlExec1 = new SQLExecutor(conPool);
try {
// lock record with a select for update statement
sqlExec1.setAutoCommit(false);
sqlExec1.addParam(new Integer(2));
sqlExec1.runQuery("SELECT CODE FROM JDBC_TEST WHERE TEST_ID = ? FOR UPDATE");
System.out.println("Attempting to update a record locked by another connection...");
SQLExecutor sqlExec2 = new SQLExecutor(getConnectionPool());
sqlExec2.setTimeoutInSec(5); // timeout if deadlocked
sqlExec2.addParam(new Integer(2));
sqlExec2.runQueryCloseCon("UPDATE JDBC_TEST SET CODE = 'X' WHERE TEST_ID = ?");
} catch (DatabaseException e) {
System.out.println("Error code=" + e.getSQLErrorCode() + ", " + e.getMessage());
if (e.isRowlockOrTimedOut()) System.out.println("Rowlock exception!");
} finally {
conPool.closeAllConnections();
}
}
/**
* INTERNAL: Build the appropriate field value for the specified set of direct values. The
* database better be expecting an ARRAY.
*/
public Object buildFieldValueFromDirectValues(
Vector directValues, String elementDataTypeName, AbstractSession session)
throws DatabaseException {
Object[] fields = Helper.arrayFromVector(directValues);
try {
((DatabaseAccessor) session.getAccessor()).incrementCallCount(session);
java.sql.Connection connection = ((DatabaseAccessor) session.getAccessor()).getConnection();
return session.getPlatform().createArray(elementDataTypeName, fields, session, connection);
} catch (java.sql.SQLException ex) {
throw DatabaseException.sqlException(ex, session, false);
} finally {
((DatabaseAccessor) session.getAccessor()).decrementCallCount();
}
}
/**
* INTERNAL: Build a row representation from the ADT structure field array. TopLink will then
* build the object from the row.
*/
public AbstractRecord buildRowFromStructure(Struct structure) throws DatabaseException {
Object[] attributes;
try {
attributes = structure.getAttributes();
} catch (java.sql.SQLException exception) {
throw DatabaseException.sqlException(exception);
}
if (attributes != null) {
for (int i = 0; i < attributes.length; i++) {
if (attributes[i] instanceof Array) {
attributes[i] =
ObjectRelationalDataTypeDescriptor.buildArrayObjectFromArray(attributes[i]);
} else if (attributes[i] instanceof Struct) {
attributes[i] =
ObjectRelationalDataTypeDescriptor.buildArrayObjectFromStruct(attributes[i]);
}
}
}
return buildNestedRowFromFieldValue(attributes);
}
/** INTERNAL: Build array of objects for Struct data type. */
public static Object buildArrayObjectFromStruct(Object structure) throws DatabaseException {
Object[] attributes = null;
if (structure == null) {
return structure;
}
try {
attributes = ((Struct) structure).getAttributes();
} catch (java.sql.SQLException exception) {
throw DatabaseException.sqlException(exception);
}
if (attributes == null) {
return null;
} else {
for (int i = 0; i < attributes.length; i++) {
if (attributes[i] instanceof Array) {
attributes[i] = buildArrayObjectFromArray(attributes[i]);
}
if (attributes[i] instanceof Struct) {
attributes[i] = buildArrayObjectFromStruct(attributes[i]);
}
}
}
return attributes;
}
/** INTERNAL: Build array of objects for Array data type. */
public static Object buildArrayObjectFromArray(Object array) throws DatabaseException {
Object[] objects = null;
if (array == null) {
return array;
}
try {
objects = (Object[]) ((Array) array).getArray();
} catch (java.sql.SQLException ex) {
throw DatabaseException.sqlException(ex);
}
if (objects == null) {
return null;
} else {
for (int i = 0; i < objects.length; i++) {
if (objects[i] instanceof Array) {
objects[i] = buildArrayObjectFromArray(objects[i]);
}
if (objects[i] instanceof Struct) {
objects[i] = buildArrayObjectFromStruct(objects[i]);
}
}
}
return objects;
}
public List<HealthRecord> healthCheck() {
if (status == PwmService.STATUS.CLOSED) {
return Collections.emptyList();
}
final List<HealthRecord> returnRecords = new ArrayList<>();
try {
preOperationCheck();
} catch (DatabaseException e) {
lastError = e.getErrorInformation();
returnRecords.add(
new HealthRecord(
HealthStatus.WARN,
HealthTopic.Database,
"Database server is not available: " + e.getErrorInformation().toDebugStr()));
return returnRecords;
}
try {
final Map<String, String> tempMap = new HashMap<>();
tempMap.put("instance", instanceID);
tempMap.put("date", (new java.util.Date()).toString());
this.put(
DatabaseTable.PWM_META, DatabaseAccessorImpl.KEY_TEST, JsonUtil.serializeMap(tempMap));
} catch (PwmException e) {
returnRecords.add(
new HealthRecord(
HealthStatus.WARN,
HealthTopic.Database,
"Error writing to database: " + e.getErrorInformation().toDebugStr()));
return returnRecords;
}
if (lastError != null) {
final TimeDuration errorAge = TimeDuration.fromCurrent(lastError.getDate().getTime());
if (errorAge.isShorterThan(TimeDuration.HOUR)) {
returnRecords.add(
new HealthRecord(
HealthStatus.CAUTION,
HealthTopic.Database,
"Database server was recently unavailable ("
+ errorAge.asLongString(PwmConstants.DEFAULT_LOCALE)
+ " ago at "
+ lastError.getDate().toString()
+ "): "
+ lastError.toDebugStr()));
}
}
if (returnRecords.isEmpty()) {
returnRecords.add(
new HealthRecord(
HealthStatus.GOOD,
HealthTopic.Database,
"Database connection to " + this.dbConfiguration.getConnectionString() + " okay"));
}
return returnRecords;
}
/**
* INTERNAL: Build and return the nested rows from the specified field value. This method allows
* the field value to be an ARRAY containing other structures such as arrays or Struct, or direct
* values.
*/
public static Object buildContainerFromArray(
Array fieldValue, ObjectRelationalDatabaseField arrayField, AbstractSession session)
throws DatabaseException {
if (arrayField.getType() == null) {
return fieldValue;
}
Object[] objects = null;
try {
objects = (Object[]) fieldValue.getArray();
} catch (java.sql.SQLException ex) {
throw DatabaseException.sqlException(ex, session, false);
}
if (objects == null) {
return null;
}
boolean isNestedStructure = false;
ObjectRelationalDataTypeDescriptor ord = null;
DatabaseField nestedType = null;
if (arrayField != null) {
nestedType = arrayField.getNestedTypeField();
if ((nestedType != null) && nestedType.getSqlType() == Types.STRUCT) {
ClassDescriptor descriptor = session.getDescriptor(nestedType.getType());
if ((descriptor != null) && (descriptor.isObjectRelationalDataTypeDescriptor())) {
// this is used to convert non-null objects passed through stored procedures and custom
// SQL to structs
ord = (ObjectRelationalDataTypeDescriptor) descriptor;
}
} else if ((nestedType != null) && (nestedType instanceof ObjectRelationalDatabaseField)) {
isNestedStructure = true;
}
}
// handle ARRAY conversions
ReadObjectQuery query = new ReadObjectQuery();
query.setSession(session);
ContainerPolicy cp = ContainerPolicy.buildPolicyFor(arrayField.getType());
Object container = cp.containerInstance(objects.length);
for (int i = 0; i < objects.length; i++) {
Object arrayValue = objects[i];
if (arrayValue == null) {
return null;
}
if (ord != null) {
AbstractRecord nestedRow = ord.buildRowFromStructure((Struct) arrayValue);
ClassDescriptor descriptor = ord;
if (descriptor.hasInheritance()) {
Class newElementClass =
descriptor.getInheritancePolicy().classFromRow(nestedRow, session);
if (!descriptor.getJavaClass().equals(newElementClass)) {
descriptor = session.getDescriptor(newElementClass);
if (descriptor == null) {
descriptor = ord;
}
}
}
arrayValue = descriptor.getObjectBuilder().buildNewInstance();
descriptor
.getObjectBuilder()
.buildAttributesIntoObject(arrayValue, nestedRow, query, null, false);
} else if (isNestedStructure && (arrayValue instanceof Array)) {
arrayValue =
buildContainerFromArray(
(Array) arrayValue, (ObjectRelationalDatabaseField) nestedType, session);
}
cp.addInto(arrayValue, container, session);
}
return container;
}
