Extensions to the Standard JDBC API

Obtaining the Names of Elements Inside a Struct (Register)

When using the JDBC driver of Denodo to execute queries that return compound values, take into account the following:

  • Values of type register are converted to java.sql.Struct objects.
  • Values of type array are converted to java.sql.Array objects.
  • java.sql.Array objects are arrays of Struct objects.

The standard JDBC API provides methods to obtain the values inside java.sql.Struct objects (i.e. inside register fields). However, it does not offer any way of obtaining the name of the subfields of a Struct or obtaining these values by the name of the subfield.

This section explains how to, using the Denodo JDBC API:

  1. Obtain the name of the subfields of a Struct object.
  2. Obtain a value of a subfield by its name, instead of its position inside the register.

For example, let us say that you have an application that executes a query that returns a register field whose subfields are last_name and first_name. For each row, the result set returns a Struct object. To obtain the values of each Struct object, the application has to invoke the method Struct.getAttributes(), which returns an array of two values: the last name and the first name. If later, you modify this register to add a subfield (e.g. telephone), the array returned by the method Struct.getAttributes() will have three elements instead of two. In addition, if the first element of the array is now the telephone and not the last name, the application will obtain invalid data.

To avoid this sort of maintainability issues you may want to use the classes of the Denodo JDBC API to obtain the values of a Struct by name and not by its position in the register. This will make your application more robust to changes.

The example below shows how to do this.

Obtaining the name of a value inside a Struct object
 private void executeQueryThatReturnsCompoundValues()
         throws Exception {

     /*
      * The method getConnection() returns a Connection to Virtual
      * DataPort
      */
     Connection connection = getConnection();
     Statement st = connection.createStatement();
     String query = "SELECT * FROM view_with_compound_fields";
     ResultSet rs = st.executeQuery(query);

     /*
      * The classes 'VDBJDBCResultSetMetaData' and 'Field' are part
      * of the Denodo JDBC API. They do not belong to the standard
      * JDBC API.
      */
     VDBJDBCResultSetMetaData metaData =
             (VDBJDBCResultSetMetaData) rs.getMetaData();
     com.denodo.vdb.jdbcdriver.printer.Field[] fields =
             metaData.getFields();
     while (rs.next()) {

         int columnCount = metaData.getColumnCount();
         for (int i = 1; i <= columnCount; i++) {

             Object value = rs.getObject(i);
             if (value != null) {

                 if (metaData.getColumnType(i) == Types.STRUCT) {
                     /*
                      * The JDBC API represents the values of type
                      * 'register' as 'Struct' objects.
                      */

                     /*
                      * The classes 'RegisterVO' and
                      * 'RegisterValueVO' are part of the Denodo JDBC
                      * API. They do not belong to the standard Java
                      * API.
                      */
                     RegisterVO vdpType =
                        ((RegisterVO) fields[i - 1].getVdpType());
                     List<RegisterValueVO> registerSubTypes =
                         vdpType.getElements();
                     Struct struct = (Struct) value;
                     Object[] structValues = struct.getAttributes();
                     String firstName = null, lastName = null;
                     for (int j=0; j <registerSubTypes.size(); j++) {
                         /*
                          * The variable 'registerSubTypes'
                          * contains the names of the names of the
                          * subfields.
                          */

                         String subFieldName =
                             registerSubTypes.get(j).getName();
                         switch (subFieldName) {
                         case "first_name":
                             firstName = (String) structValues[j];
                             break;

                         case "last_name":
                             lastName = (String) structValues[j];
                             break;
                         }
                         /*
                          * ...
                          */
                     }
                 } else if (metaData.getColumnType(i)==Types.ARRAY) {
                     /*
                      * The JDBC API represents the values of type
                      * 'array' as 'Array' objects.
                      */
                     Object[] register =
                         (Object[]) rs.getArray(i).getArray();
                     for (Object o : register) {
                         /*
                          * In the Denodo JDBC API, the content of an
                          * 'Array' is an array of 'Struct' objects.
                          */

                         Struct s = (Struct) o;
                         /*
                          * ...
                          */
                     }
                 } // else ...
             }
         }
     }

     /*
      * Close ResultSet, Statement and Connection.
      */
 }