Details of the JDBC Interface¶
This section describes information specific to the JDBC driver of Denodo
Description of Views and Their Fields¶
It publishes the description of the views and their fields in the column “REMARKS” of the metadata of tables/views and their fields.
Retrieving the Content Type of Blob Values¶
The driver makes available the content type of blob fields. The example below shows how to do it.
ResultSet rs = stmt.executeQuery(...);
...
com.denodo.vdb.jdbcdriver.VDBJDBCBlob blob =
(com.denodo.vdb.jdbcdriver.VDBJDBCBlob) rs.getBlob(index);
String contentType = blob.getContentType();
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 tojava.sql.Struct
objects.Values of type
array
are converted tojava.sql.Array
objects.java.sql.Array
objects are arrays ofStruct
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:
Obtain the name of the subfields of a
Struct
object.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.
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.
*/
}