Handling VARBINARY URI Storage in Java with JDBC PreparedStatement

Storing and retrieving URIs in a database using Java can sometimes be tricky, especially when the URI is stored in a VARBINARY column. Developers often encounter issues when reading such data from the database, resulting in unexpected symbols or deserialization errors. In this article, we will explore how to correctly retrieve and handle VARBINARY-stored URIs using JDBC and Java’s PreparedStatement.

Understanding the Problem

When storing a URI in a VARBINARY column, it can either be stored as a raw UTF-8 encoded string or as a serialized Java object. The retrieval approach depends on the format in which the data was stored. A common mistake developers make is assuming that VARBINARY data can be directly converted to a string without proper encoding or deserialization.

A typical issue occurs when a developer tries to retrieve a VARBINARY column using getString() or getBytes() and encounters unreadable characters such as:

??♣srjava.net.URI?☺x.C?I?♥☺L♠stringt↕Ljava/lang/String;xpt(https://192.168.50.118:18081/1.0.6/tokenx

This usually indicates that the data is stored as a serialized Java object rather than a simple UTF-8 string.

Correctly Storing and Retrieving URIs

Storing URIs as UTF-8 Encoded Strings

The simplest and most recommended way to store URIs in a database is to save them as VARCHAR or TEXT. However, if a VARBINARY column is necessary, ensure the URI is encoded as bytes before storage.

PreparedStatement stmt = connection.prepareStatement("INSERT INTO bank_accounts (acc_balances_uri) VALUES (?)");
String uriString = "https://192.168.50.118:18081/1.0.6/token";
stmt.setBytes(1, uriString.getBytes(StandardCharsets.UTF_8));
stmt.executeUpdate();

Retrieving and Decoding UTF-8 Stored URIs

If the URI was stored as a UTF-8 encoded string in a VARBINARY column, use getBytes() and convert it back to a string:

if (resultSet.getBytes("acc_balances_uri") != null) {
    String uriString = new String(resultSet.getBytes("acc_balances_uri"), StandardCharsets.UTF_8);
    bank.setAccountBalancesBaseUri(URI.create(uriString));
}

Handling Serialized URI Objects

If the URI was stored as a serialized java.net.URI object, it must be deserialized properly when retrieved. Use ObjectOutputStream to store the serialized object and ObjectInputStream to read it back.

Storing a Serialized URI Object

PreparedStatement stmt = connection.prepareStatement("INSERT INTO bank_accounts (acc_balances_uri) VALUES (?)");
ByteArrayOutputStream bos = new ByteArrayOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(bos);
oos.writeObject(URI.create("https://192.168.50.118:18081/1.0.6/token"));
oos.flush();
stmt.setBytes(1, bos.toByteArray());
stmt.executeUpdate();

Retrieving a Serialized URI Object

if (resultSet.getBytes("acc_balances_uri") != null) {
    try (ObjectInputStream ois = new ObjectInputStream(new ByteArrayInputStream(resultSet.getBytes("acc_balances_uri")))) {
        Object obj = ois.readObject();
        if (obj instanceof URI) {
            bank.setAccountBalancesBaseUri((URI) obj);
        }
    } catch (IOException | ClassNotFoundException e) {
        e.printStackTrace();
    }
}

Best Practices

• Use VARCHAR/TEXT Instead of VARBINARY: Whenever possible, store URIs as plain strings to avoid unnecessary complexity.
• Ensure Consistent Encoding: If using VARBINARY, always encode and decode using StandardCharsets.UTF_8.
• Use Serialization Only When Necessary: Avoid serializing objects unless there is a strict requirement.
• Handle Exceptions Properly: When deserializing objects, always handle potential IOException or ClassNotFoundException errors.
• Validate URIs After Retrieval: Always verify that the retrieved data is a valid URI before using it.

Conclusion

Handling VARBINARY-stored URIs in Java requires understanding how the data is stored and retrieved. The best approach is to store URIs as simple strings, but if VARBINARY must be used, ensure consistent encoding and decoding methods. Following these best practices will help you avoid common pitfalls and ensure smooth database interactions with Java and JDBC.