Skip to content

Accessing Database Servers (JDBC)

It is often the case that a masking algorithm will require access to a large amount of data such as lookup values for masking input data or storing states of the algorithm. To support these use cases, the extensible algorithm framework allows algorithms to access database servers using JDBC connections.

Opening Database Connection

Algorithms access the database by using extensible drivers. Access is achieved by invoking the openJdbcConnection method of the ComponentService object passed to the algorithm's setup method to acquire a Connection (java.sql.Connection) object. The file's location must be specified by an JdbcReference object visible in the object's public fields. This object is passed to the openJdbcConnection method. The value of the JdbcReference may be made configurable using the @JsonProperty annotation. The JdbcReference object requires following fields

  1. jdbcDriverId: The driver Id of the JDBC Driver uploaded using the JDBC Driver API (/jdbc-drivers).
  2. url: The JDBC URL that will be used to connect to the server. Please don't use this to pass the credentials.
  3. credFileReference: A FileReference object that contains the location of the JSON file that stores the credentials. The schema of the file is:
{
  "username": "USERNAME",
  "password": "PASSWORD"
}

The file must be a mount type FileReference object. To see how to create a mount type FileReference object, refer to Accessing Files. The Connection object is kept open throughout the execution of the algorithm unless it is closed by the algorithm itself.

Example Algorithm

public class RedactionDB implements MaskingAlgorithm<String> {
   private String redactionCharacter = null;

   @JsonProperty(value = "jdbc", required = true)
   @JsonPropertyDescription("A reference to a database containing a table redaction_character")
   public JdbcReference jdbc;

   private static final String GET_REDACTION_CHARACTER = "SELECT redact FROM redaction_character LIMIT 1";


   @Override
   public String getName() {
       return "RedactionDB";
   }

   @Override
   public String mask(@Nullable String input) throws MaskingException {
       if (input == null) {
           return null;
       }
       StringBuilder returnVal = new StringBuilder();
       for (int i = 0; i < input.length(); i++) {
           returnVal.append(redactionCharacter);
       }
       return returnVal.toString();
   }

   @Override
   public void setup(@Nonnull ComponentService serviceProvider) {
     try (Connection conn = serviceProvider.openJdbcConnection(jdbc);
          PreparedStatement stmt = conn.prepareStatement(GET_REDACTION_CHARACTER)) {
          ResultSet resultSet = stmt.executeQuery();
          List<String> redactionChars = new ArrayList<>();
          if (resultSet.next()) {
            redactionCharacter = resultSet.getString("redact");
          } else {
            throw new RuntimeException("Couldn't find redaction character");
          }
      } catch (SQLException e) {
          throw new RuntimeException(e);
      }

   }

   @Override
   public void validate() throws ComponentConfigurationException {
       GenericReference.checkRequiredReference(jdbc, "jdbc");
   }
}

Some methods have been omitted for brevity.

This example algorithm is very similar to the StringRedaction class discussed earlier, in that it redacts strings by replacing with a same-length string of the redaction character. This variant reads the character to use for redaction from a table in a database, the connection information for which is specified in the algorithm's configuration. This is all done in the initialize method:

  • The value of the variable "jdbc" is public and marked configurable with @JsonProperty.
  • The jdbc reference is passed to serviceProvider.openJdbcConnection during setup.
  • The redaction character is read from the table redaction_character and stored in the instance variable redactionCharacter for use in the mask method.
  • This class's validate method uses the static method GenericReference.checkRequiredReference provided by the Masking Plugin API to check the jdbc reference for validity.