Ejemplo n.º 1
0
  /** 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();
    }
  }
Ejemplo n.º 2
0
 /**
  * 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();
      }
    }
  }
Ejemplo n.º 6
0
  /**
   * 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();
    }
  }
Ejemplo n.º 7
0
  /**
   * 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();
    }
  }
Ejemplo n.º 8
0
  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));
    }
  }
Ejemplo n.º 9
0
  /**
   * 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;
 }
Ejemplo n.º 14
0
  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;
  }