/**
   * Insert new row into the BUSINESS_ENTITIES table.
   *
   * @param service object holding values to be inserted
   * @param connection JDBC connection
   * @throws java.sql.SQLException
   */
  public static void insert(BusinessService service, Connection connection)
      throws java.sql.SQLException {
    PreparedStatement statement = null;
    Timestamp timeStamp = new Timestamp(System.currentTimeMillis());

    try {
      statement = connection.prepareStatement(insertSQL);
      statement.setString(1, service.getBusinessKey().toString());
      statement.setString(2, service.getServiceKey().toString());
      statement.setTimestamp(3, timeStamp);

      log.info(
          "insert into BUSINESS_SERVICE table:\n\n\t"
              + insertSQL
              + "\n\t BUSINESS_KEY="
              + service.getBusinessKey().toString()
              + "\n\t SERVICE_KEY="
              + service.getServiceKey().toString()
              + "\n\t LAST_UPDATE="
              + timeStamp.getTime()
              + "\n");

      int returnCode = statement.executeUpdate();

      log.info("insert into BUSINESS_SERVICE was successful, return code=" + returnCode);
    } finally {
      try {
        statement.close();
      } catch (Exception e) {
        /* ignored */
      }
    }
  }
  /**
   * Select all rows from the business_service table for a given BusinessKey.
   *
   * <p>
   *
   * @param businessKey BusinessKey
   * @param connection JDBC connection
   * @throws java.sql.SQLException
   */
  public static Vector selectByBusinessKey(String businessKey, Connection connection)
      throws java.sql.SQLException {
    Vector serviceList = new Vector();
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    try {
      // create a statement to query with
      statement = connection.prepareStatement(selectByBusinessKeySQL);
      statement.setString(1, businessKey.toString());

      log.info(
          "select from BUSINESS_SERVICE table:\n\n\t"
              + selectByBusinessKeySQL
              + "\n\t BUSINESS_KEY="
              + businessKey.toString()
              + "\n");

      // execute the statement
      resultSet = statement.executeQuery();

      BusinessService service = null;
      while (resultSet.next()) {
        service = new BusinessService();
        service.setBusinessKey(businessKey);
        service.setServiceKey(resultSet.getString("SERVICE_KEY"));
        serviceList.add(service);
        service = null;
      }

      log.info("select was successful, rows selected=" + serviceList.size());
      return serviceList;
    } finally {
      try {
        resultSet.close();
      } catch (Exception e) {
        /* ignored */
      }
      try {
        statement.close();
      } catch (Exception e) {
        /* ignored */
      }
    }
  }
  /**
   * Select one row from the BUSINESS_SERVICE table.
   *
   * @param serviceKey primary key value
   * @param connection JDBC connection
   * @throws java.sql.SQLException
   */
  public static BusinessService select(String serviceKey, Connection connection)
      throws java.sql.SQLException {
    BusinessService service = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    try {
      statement = connection.prepareStatement(selectSQL);
      statement.setString(1, serviceKey.toString());

      log.info(
          "select from BUSINESS_SERVICE table:\n\n\t"
              + selectSQL
              + "\n\t SERVICE_KEY="
              + serviceKey.toString()
              + "\n");

      resultSet = statement.executeQuery();
      if (resultSet.next()) {
        service = new BusinessService();
        service.setBusinessKey(resultSet.getString("BUSINESS_KEY"));
        service.setServiceKey(serviceKey);
      }

      if (service != null) log.info("select successful, at least one row was found");
      else
        log.info(
            "select executed successfully but no rows were found with SERVICE_KEY="
                + serviceKey.toString());

      return service;
    } finally {
      try {
        resultSet.close();
      } catch (Exception e) {
        /* ignored */
      }
      try {
        statement.close();
      } catch (Exception e) {
        /* ignored */
      }
    }
  }
  // system test-driver
  public static void test(Connection connection) {
    Transaction txn = new Transaction();

    if (connection != null) {
      try {
        String businessKey = UUID.nextID();
        BusinessEntity business = new BusinessEntity();
        business.setBusinessKey(businessKey);
        business.setAuthorizedName("sviens");
        business.setOperator("WebServiceRegistry.com");

        String serviceKey = UUID.nextID();
        BusinessService service = new BusinessService();
        service.setBusinessKey(businessKey);
        service.setServiceKey(serviceKey);

        // begin a new transaction
        txn.begin(connection);

        String authorizedUserID = "sviens";

        // insert a new BusinessEntity
        BusinessEntityTable.insert(business, authorizedUserID, connection);

        // insert a new BusinessService
        BusinessServiceTable.insert(service, connection);

        // insert another new BusinessService
        service.setServiceKey(UUID.nextID());
        BusinessServiceTable.insert(service, connection);

        // insert one more new BusinessService
        service.setServiceKey(UUID.nextID());
        BusinessServiceTable.insert(service, connection);

        // select a BusinessService object
        service = BusinessServiceTable.select(serviceKey, connection);

        // delete a BusinessService object
        BusinessServiceTable.delete(serviceKey, connection);

        // select a BusinessService object
        service = BusinessServiceTable.select(serviceKey, connection);

        // select a Collection BusinessService objects by BusinessKey
        BusinessServiceTable.selectByBusinessKey(businessKey, connection);

        // delete a Collection BusinessService objects by BusinessKey
        BusinessServiceTable.deleteByBusinessKey(businessKey, connection);

        // select a Collection BusinessService objects by BusinessKey
        BusinessServiceTable.selectByBusinessKey(businessKey, connection);

        // commit the transaction
        txn.commit();
      } catch (Exception ex) {
        ex.printStackTrace();
        try {
          txn.rollback();
        } catch (java.sql.SQLException sqlex) {
          sqlex.printStackTrace();
        }
      }
    }
  }