LOB with Oracle: not easy
Since I've been writing classes accessing Oracle Database the biggest problem I've come accross has always been dealing with BLOBs and CLOBs.
Inserts, updates, deletes, calling stored procedures/functions, no problem with JDBC. In the worst case, google it and in 5 seconds, you find out how to use the API and it usually takes only a few lines of code.
For BLOBs and CLOBs, it's a totally different matter : you can't rely on JDBC API alone to manipulate them.Oracle provides code samples on how to deal with LOBs in Java, but using Oracle's objects is required for writing.
JDBC only code: limited
After a bit of diging, I found out that using JBDC only is possible but is limited to small data (see code sample below).
File myFile = new File("[some file location]");
FileInputStream istreamImage = new FileInputStream();
try {
PreparedStatement ps = conn.prepareStatement("insert into image (location, img) values (?,?)");
try {
ps.setString(1, location);
ps.setBinaryStream(2, istreamImage, (int) myFile.length());
ps.executeUpdate();
}
finally {
ps.close();
}
}
finally {
istreamImage.close();
}
The reason : method setBinaryStream() does not stream file's content ! The whole file is loaded in memory. This might not be an issue for the application you're writing but think about an online archiving service where files are usually bigger than a few kilobytes, that's not acceptable.
JDBC does provide methods to stream content to the database, but they are not supported by Oracle's driver. A runtime exception is thrown if you try to use it.
In the last version of its driver (for 10g), Oracle added new ways to insert CLOBs. Their intend was to make our life easier but I personnaly thing they failed: each way has its specific drawbacks (such as system wide configuration requirements) and won't work in all cases. I want to be able to manipulate LOBs pretty much the same way I use JDBC: independently of the context.
Writing LOB in Oracle: can't use pure JDBC code
Thinking about inserting data in a row and the CLOB content at the same time ?
With Oracle, that's not possible. One has to proceed in two separate steps to have a 100% chance to successfully insert a LOB:
- insert data and a empty lob in an "insert" statement
- select the empty lob with a "select for update" statement and write the content
The two steps insert raises a specific concern: commit management. If writing lob's content fails, we need to rollback both statements. I wrote an Helper class to deal with such problems (article here).
If we can't write generic JDBC code, we can still make the second step easier (avoid managing buffering, instanciating Oracle's object, ...) and avoid bugs by repeating to many lines of code.
Helpers to the rescue !
So, I came up with two abstract helper classes, OracleBLOBHelper and OracleCLOBHelper, and their respesctive adapater classes : OracleBLOBHelperAdapter and OracleCLOBHelperAdapter.
They do use Oracle's driver objects, but at least you don't get to deal with it directly and they work the same way no matter the context. Obviously, they are thread-safe.
This class takes enters your code after you created a statement (either a "select" or a "select for udpate") and executed it. It only deals with the ResultSet you got from the statement.
Common cases
Helper classes provide public static methods to easily perform the most common tasks such as :
- writing an input stream to a BLOB
- reading a BLOB into an output stream
- writing a String to a CLOB
- reading a String from a CLOB
Here is a code sample on how to use one of these methods:
public void easyBinaryWriting(Connection conn) {
File file = new File("[file location]");
PreparedStatement stmt = null;
ResultSet rSet = null;
InputStream inputStream = null;
try {
int id = 12;
String query = "select file_content from uploaded_files where id = ? for update";
conn.prepareStatement(query);
stmt.setInt(1, id);
stmt.executeQuery();
inputStream = new FileInputStream(file);
// write content of file into BLOB in a single line of code
OracleBLOBHelper.writeBinary(inputStream, rSet, false, 1);
}
catch (SQLException e) {
// TODO log SQLException !!
}
catch (IOException e) {
// TODO log SQLException !!
}
finally {
// FIXME handle closing of inputStream, ResultSet and Statement here
}
}
Custom cases: how do helpers really work
Suppose you keep serialized objects in a BLOB or just want to stream an HTML document directly back to the customer who requested it, these are cases where the static methods won't be enough.
Helpers are intended to be used by creating a subclass of either the helper itself or its adapter. Also, an helper instance beeing a stateful object, it is strongly advised to use it for only one operation (otherwise thread-safety can not be guaranted).
They define several public methods which perform specific operations (read, write or more specific ones such as read/write serialized objects from a BLOB).
Each public method requires the user to provide an implementation for the abstract protected method which has the same name as the method if the user intends to use. Since an helper class will usually rarely need to use all operations at the same time, the user will usually subclass the adapter rather than the helper class directly. As shown below, using an anonymous subclass can also be a good idea.
Statement stmt = null;
ResultSet rSet = null;
try {
Connection conn = getConnection();
String query = "select content from test_table where id= 2 for update";
stmt = conn.createStatement();
rSet = stmt.executeQuery(query);
OracleBLOBHelper bHelper = new OracleBLOBHelperAdapter() {
// Overrides protected method for the operation we want to use
protected boolean write(OutputStream outputStream) throws IOException {
// to do here: write some binary data into outputStream
// to do here: return true if writing succeeded, false otherwise
}
};
boolean flag = bHelper.write(rSet, true, 1);
// handle flag are required
}
catch (SQLException e) {
// to do here: log or throw exception
}
finally {
// to do here: close ResultSet
// to do here: close Statement
}
