Useful Tips : How to insert BLOB into an Oracle database using JDBC

It can be quite disturbing to try to insert a BLOB (Binary Large OBject) using JDBC, because most of the time java softwares will handle BLOB, not exactly using their own time, but using Strings.
And if you try let’s say this kind of query :

// blob String :
String blobToInsert = ....;

StringBuilder insertQuery = new StringBuilder();
insertQuery.append("insert into ");
insertQuery.append(" table( table_id, table_blob_field ) ");
insertQuery.append("values ");
insertQuery.append(" ('64', '" + blobToInsert + "' )");

And try to execute it,you will end up having several different kind of errors, once Oracle may tell you that the field is too long, to finally end up telling you a strange type error.

The trick is the following, you can’t use Strings (or StringBuilder/StringBuffer/etc… ) because it won’t define the type of Object you’ll be using. So you’ll have to use a prepare statement (normally used for optimization when you’re using more than once a query) this way :

// blob String :
String blobToInsert = ....;

StringBuilder insertQuery = new StringBuilder();
insertQuery.append("insert into ");
insertQuery.append(" table( table_id, table_blob_field ) ");
insertQuery.append("values ");
insertQuery.append(" ('64', ? )");
PrepareStatement psInsert = con.prepareStatement(insertQuery.toString());

// and then set :
psInsert.setBytes(1, blobToInsert.getBytes());

// and execute the query :
psInsert.execute();

This way you won’t be bothered, because nowadays JDBC connection using Oracle drivers can recognize BLOBs and do the work for you.
Enjoy.