Advanced Data Types Overview

Advanced data types allow relational databases to handle flexible column values. Columns can store BLOB (binary large objects) containing substantial amounts of raw binary data, or CLOB (character large objects) storing extensive character-based data.

The current ANSI/ISO SQL standard, known as SQL:2003, specifies these data types:

• SQL92 built-in types: Including familiar column types like CHAR, FLOAT, and DATE
• SQL99 additions: Such as:
  • BOOLEAN: True/false values
  • BLOB: Binary large objects
  • CLOB: Character large objects
• SQL:2003 additions: Including XML objects
• User-defined types:
  • Structured types: Custom user types, example:

    CREATE TYPE POINT_2D
    AS (X_COORD FLOAT, Y_COORD FLOAT) NOT FINAL
    

  • DISTINCT types: User types based on built-in types:

    CREATE TYPE CURRENCY_AMOUNT
    AS NUMERIC(12,2) FINAL
    

• Constructed types: New types based on base types:
  • REF(*structured-type*): Persistent pointers to structured type instances in database
  • *base-type* ARRAY[*n*]: Arrays of n basic type elements
• Locators: Logical pointers referencing data on database servers
• Datalink: Type for managing external data sources

Mapping Advanced Data Types

JDBC provides default mappings for SQL:2003 advanced data types:

• BLOB: Blob interface
• CLOB: Clob interface
• NCLOB: NClob interface
• ARRAY: Array interface
• XML: SQLXML interface
• Structured types: Struct interface
• REF(structured type): Ref interface
• ROWID: RowId interface
• DISTINCT: Maps to underlying type (e.g., NUMERIC maps to java.math.BigDecimal)
• DATALINK: java.net.URL object

Using Advanced Data Types

Operations follow standard patterns using ResultSet.get*DataType*, CallableStatement.get*DataType*, PreparedStatement.set*DataType*, and ResultSet.update*DataType* methods. Here's the method mapping table:

Advanced Data Type	get*DataType* Method	set*DataType* Method	update*DataType* Method
BLOB	getBlob	setBlob	updateBlob
CLOB	getClob	setClob	updateClob
NCLOB	getNClob	setNClob	updateNClob
ARRAY	getArray	setArray	updateArray
XML	getSQLXML	setSQLXML	updateSQLXML
Structured type	getObject	setObject	updateObject
REF(structured type)	getRef	setRef	updateRef
ROWID	getRowId	setRowId	updateRowId
DISTINCT	getBigDecimal	setBigDecimal	updateBigDecimal
DATALINK	getURL	setURL	updateURL

For example, retrieving an SQL ARRAY value:

ResultSet resultSet = statement.executeQuery(
    "SELECT GRADES FROM STUDENTS " +
    "WHERE STUDENT_ID = 001144");
resultSet.next();
Array gradeValues = resultSet.getArray("GRADES");

The gradeValues variable holds a logical pointer to the SQL ARRAY object stored in the STUDENTS table.

For storage operations:

Clob documentation = resultSet.getClob("DOCUMENTATION");
PreparedStatement prepStmt =
    connection.prepareStatement(
        "UPDATE PRODUCTS SET DETAILS = ? " +
        "WHERE REVENUE < 500000");
prepStmt.setClob(1, documentation);
prepStmt.executeUpdate();

Working with Large Objects

Blob, Clob, and NClob Java objects enable manipulation without transferring all data from the database server to the client. Implementations may use locators (logical pointers) pointing to database objects, improving performance since these SQL objects can be very large.

Adding Large Object Types to Database

The following excerpt adds a CLOB SQL value to the PRODUCT_DESCRIPTIONS table:

public void insertProductDescription(String productName,
                                     String filePath) throws SQLException {
  String query = "INSERT INTO PRODUCT_DESCRIPTIONS VALUES(?,?)";
  Clob descriptionObject = this.connection.createClob();
  try (PreparedStatement pstmt = this.connection.prepareStatement(query);
       Writer descWriter = descriptionObject.setCharacterStream(1);) {
    String content = this.loadFile(filePath, descWriter);
    if (this.config.dbms.equals("mysql")) {
      descriptionObject.setString(1, content);
    }
    pstmt.setString(1, productName);
    pstmt.setClob(2, descriptionObject);
    pstmt.executeUpdate();
  } catch (SQLException sqlex) {
    printSqlException(sqlex);
  } catch (Exception ex) {
    System.out.println("Unexpected error: " + ex.toString());
  }
}

Retrieving CLOB Values

Method to retrieve CLOB from the database:

public String fetchPartialContent(String productName,
                                  int charCount) throws SQLException {

  String content = null;
  Clob descriptionObject = null;
  String query = "SELECT DESCRIPTION_CONTENT FROM PRODUCT_DESCRIPTIONS WHERE PRODUCT_TITLE = ?";

  try (PreparedStatement pstmt = this.connection.prepareStatement(query)) {
    pstmt.setString(1, productName);
    ResultSet rs = pstmt.executeQuery();
    if (rs.next()) {
      descriptionObject = rs.getClob(1);
    }
    content = descriptionObject.getSubString(1, charCount);
  } catch (SQLException sqlex) {
    printSqlException(sqlex);
  }
  return content;
}

Freeing Large Object Resources

Large object Java objects remain valid during their transaction duration. Applications should call their free method to release resources:

Clob textObject = connection.createClob();
textObject.setString(1, value);
textObject.free();

Working with SQLXML Objects

The Connection interface supports creating SQLXML objects through the createSQLXML method. The created object is initially empty.

Creating SQLXML Objects

Connection conn = DriverManager.getConnection(databaseUrl, properties);
SQLXML xmlObject = conn.createSQLXML();
xmlObject.setString(dataValue);

Retrieving SQLXML Values

Retrieve SQLXML values using getSQLXML method from ResultSet or CallableStatement:

SQLXML xmlVariable = resultSet.getSQLXML(1);

Accessing SQLXML Data

Access internal content using getString, getBinaryStream, getCharacterStream, and getSource methods:

SQLXML xmlObject = resultSet.getSQLXML(1);
String content = xmlObject.getString();

Storing SQLXML Objects

PreparedStatement pstmt = connection.prepareStatement("INSERT INTO metadata " +
                              "(xmlContent, recordId) VALUES (?, ?)");
pstmt.setSQLXML(1, metadataObject);
pstmt.setInt(2, recordIdentifier);

Releasing SQLXML Resources

SQLXML xmlVariable = connection.createSQLXML();
xmlVariable.setString(value);
xmlVariable.free();

Working with Array Objects

Use Connection.createArrayOf to create Array objects:

Connection connection = DriverManager.getConnection(url, props);
String [] regionPostalCodes = { "10022", "02110", "07399" };
Array arrayObject = connection.createArrayOf("VARCHAR", regionPostalCodes);

Retrieving and Accessing Array Values

ResultSet rs = statement.executeQuery(
    "SELECT region_name, postal_codes FROM REGIONS");

while (rs.next()) {
    Array postalArray = rs.getArray("postal_codes");
    String[] codes = (String[])postalArray.getArray();
    for (int i = 0; i < codes.length; i++) {
        if (!PostalValidator.isValid(codes[i])) {
            // Handle invalid postal code
        }
    }
}

Releasing Array Resources

Array arrayObj = connection.createArrayOf("VARCHAR", postalCodes);
// Process array
arrayObj.free();

Working with DISTINCT Data Types

DISTINCT data types behave differently from other advanced SQL types. They map to their underlying SQL type's Java equivalent rather than having a dedicated interface.

Example creation:

CREATE TYPE STATE_CODE AS CHAR(2);

Retrieval uses same methods as underlying type:

String stateCode = resultSet.getString(4);

Working with Structured Objects

SQL structured types are user-defined types similar to Java classes with member fields called attributes.

Example definition:

CREATE TYPE ADDRESS_INFO
(
    STREET_NUMBER INTEGER,
    STREET_NAME VARCHAR(40),
    CITY_NAME VARCHAR(40),
    STATE_CODE CHAR(2),
    POSTAL_CODE CHAR(5)
);

Implementation in JDBC:

String createAddressType =
    "CREATE TYPE ADDRESS_INFO " +
    "(STREET_NUMBER INTEGER, STREET_NAME VARCHAR(40), " +
    "CITY_NAME VARCHAR(40), STATE_CODE CHAR(2), POSTAL_CODE CHAR(5))";
Statement stmt = connection.createStatement();
stmt.executeUpdate(createAddressType);

Working with Custom Type Mapping

Implement the SQLData interface for custom mapping:

public class AddressInfo implements SQLData {
    public int streetNumber;
    public String streetName;
    public String cityName;
    public String stateCode;
    public String postalCode;
    private String sqlTypeName;

    @Override
    public String getSQLTypeName() {
        return sqlTypeName;
    }

    @Override
    public void readSQL(SQLInput stream, String typeName)
        throws SQLException {
        sqlTypeName = typeName;
        streetNumber = stream.readInt();
        streetName = stream.readString();
        cityName = stream.readString();
        stateCode = stream.readString();
        postalCode = stream.readString();
    }

    @Override
    public void writeSQL(SQLOutput stream)
        throws SQLException {
        stream.writeInt(streetNumber);
        stream.writeString(streetName);
        stream.writeString(cityName);
        stream.writeString(stateCode);
        stream.writeString(postalCode);
    }
}

Set up custom mapping in type map:

java.util.Map<String, Class<?>> typeMap = connection.getTypeMap();
typeMap.put("SchemaName.ADDRESS_INFO", AddressInfo.class);
connection.setTypeMap(typeMap);

Working with Datalink Objects

DATALINK values reference external resources via URLs.

Storing External References

public void insertUrlRecord(String description, String url) throws SQLException {
  String query = "INSERT INTO resource_links(description_text,url_reference) VALUES (?,?)";
  try (PreparedStatement pstmt = this.connection.prepareStatement(query)) {
    pstmt.setString(1, description);
    pstmt.setURL(2, new URL(url));
    pstmt.execute();    
  }
}

Retrieving External References

public static void displayTable(Connection conn, Proxy proxy)
  throws SQLException, IOException {
  String query = "SELECT description_text, url_reference FROM resource_links";
  try (Statement stmt = conn.createStatement()) {
    ResultSet rs = stmt.executeQuery(query);
    while (rs.next()) {
      String description = rs.getString(1);
      java.net.URL url = rs.getURL(2);    
      if (url != null) {
        URLConnection urlConnection = url.openConnection(proxy);
        BufferedReader reader =
          new BufferedReader(new InputStreamReader(urlConnection.getInputStream()));
        System.out.println("Resource: " + description);
        String content = null;
        while ((content = reader.readLine()) != null) {
          System.out.println(content);
        }
      }
    }
  }
}

Working with RowId Objects

RowId objects represent addresses of table rows. ROWID values provide fast access to individual rows and serve as unique identifiers.

Retrieving RowId Objects

java.sql.RowId rowIdentifier = resultSet.getRowId(1);

Using RowId Objects

PreparedStatement ps = connection.prepareStatement(
    "INSERT INTO BOOKLIST" +
    "(ROW_IDENTIFIER, AUTHOR, TITLE, ISBN) " +
    "VALUES (?, ?, ?, ?)");
ps.setRowId(1, rowIdentifier);

Working with Stored Procedures

Stored procedures are groups of SQL statements that form logical units for specific tasks.

Paramter Modes

• IN: Passes values to procedure (default)
• OUT: Returns values to caller
• INOUT: Both passes initial value and returns updated value

Creating Procedures in Java DB

Example Java method for stored procedure:

public static void displaySuppliers(ResultSet[] results)
    throws SQLException {

    Connection conn = DriverManager.getConnection("jdbc:default:connection");
    Statement stmt = null;

    String query =
        "SELECT SUPPLIERS.COMPANY_NAME, " +
        "PRODUCTS.PRODUCT_NAME " +
        "FROM SUPPLIERS, PRODUCTS " +
        "WHERE SUPPLIERS.ID = " +
        "PRODUCTS.SUPPLIER_ID " +
        "ORDER BY COMPANY_NAME";

    stmt = conn.createStatement();
    results[0] = stmt.executeQuery(query);
}

Create procedure in Java DB:

CREATE PROCEDURE DISPLAY_SUPPLIERS() 
PARAMETER STYLE JAVA 
LANGUAGE JAVA 
DYNAMIC RESULT SETS 1 
EXTERNAL NAME 
'com.example.StoredProcSample.displaySuppliers'

Calling Stored Procedures

Using CallableStatement:

CallableStatement callableStmt = connection.prepareCall("{call DISPLAY_SUPPLIERS()}");
ResultSet rs = callableStmt.executeQuery();

while (rs.next()) {
    String company = rs.getString("COMPANY_NAME");
    String product = rs.getString("PRODUCT_NAME");
    System.out.println(company + ": " + product);
}

For procedures with parameters:

callableStmt = connection.prepareCall("{call GET_PRODUCT_SUPPLIER(?, ?)}");
callableStmt.setString(1, productNameArg);
callableStmt.registerOutParameter(2, Types.VARCHAR);
callableStmt.executeQuery();

String supplier = callableStmt.getString(2);

For INOUT parameters:

callableStmt = connection.prepareCall("{call UPDATE_PRICE(?,?,?)}");
callableStmt.setString(1, productNameArg);
callableStmt.setFloat(2, maxPercentArg);
callableStmt.registerOutParameter(3, Types.NUMERIC);
callableStmt.setFloat(3, priceArg);
callableStmt.execute();