- Databases Working Overtime
- Binary Data in an XML World
- Binary Data in a Relational Database
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Binary Data in a Relational Database
Getting binary data into a relational database might seem like a bit of a contradiction—doesn’t everything in a relational database have to be table-based? Yes, but you can put binary data into a special type of table column. This level of clever flexibility has allowed relational database technology to move smoothly into the new multimedia era. A binary large object (BLOB) is a variable-length binary string that can be up to 2GB (2,147,483,648 bytes) in length. There is also a variant for unstructured character data called a character large object (CLOB), which is a variable-length character string that can be up to 2GB in length.
To store our binary data, we’ll be using a BLOB. What does a BLOB look like?
Step 1: Enter the BLOB End-to-End Fashion
Listing 2 shows a complete Java example of BLOB usage. I call this an end-to-end example because it encompasses everything from BLOB creation to data storage to retrieval. I prefer this approach because the code can be copied and pasted for compilation into a file called blobTest.java. This way, you get to see a worked example, and you can poke around in the code to see how it all hangs together.
Listing 2 An end-to-end BLOB example.
import java.sql.*;
import java.io.*;
import javax.sound.sampled.AudioInputStream;
import javax.sound.sampled.AudioFormat;
import javax.sound.sampled.AudioFileFormat;
import javax.sound.sampled.AudioSystem;
public class blobTest
{
public static void main(String[] args)
{
try
{
String url = "jdbc:derby:blobberyblob;create=true";
Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
Connection conn = DriverManager.getConnection(url);
Statement s = conn.createStatement();
s.executeUpdate("CREATE TABLE documents (id INT, pic blob(16M))");
conn.commit();
java.io.File file = new java.io.File("captureFile.wav");
int fileLength = (int) file.length();
java.io.InputStream fin = new java.io.FileInputStream(file);
PreparedStatement ps = conn.prepareStatement(
"INSERT INTO documents VALUES (?, ?)");
ps.setInt(1, 1477);
System.out.println("Set the value of the input parameter");
ps.setBinaryStream(2, fin, fileLength);
ps.execute();
conn.commit();
ResultSet rs = s.executeQuery(
"SELECT pic FROM documents WHERE id = 1477");
while (rs.next())
{
java.sql.Blob ablob = rs.getBlob(1);
java.io.InputStream ip = rs.getBinaryStream(1);
FileOutputStream op = new FileOutputStream("dbsound.wav");
op.write(ablob.getBytes(1, (int)ablob.length()));
System.out.println("BLOB length = " + ablob.length());
op.close();
ip.close();
System.out.print("\n");
}
}
catch (Exception e)
{
System.out.println("Error! "+e);
}
}
}
The only extra thing you’ll need to run the code in Listing 2 is the file referenced in the following line:
java.io.File file = new java.io.File("captureFile.wav");
The file can be obtained as part of the source.zip code download [3] with this article.
***Please link the word source.zip above to the source.zip file for this article.
Now look at the code in Listing 3, which shows the first part of the program.
Listing 3 Creating a BLOB.
String url = "jdbc:derby:blobberyblob;create=true";
Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
Connection conn = DriverManager.getConnection(url);
Statement s = conn.createStatement();
s.executeUpdate("CREATE TABLE documents (id INT, pic blob(16M))");
In Listing 3, the Derby database is created. When you execute the code, you’ll see a new folder appear, called blobberyblob. This contains the database. To see the tables contained within the database, look at the last line of Listing 3. This line indicates that the database contains a table called documents, which in turn contains two columns: id and pic. The second of these columns represents the BLOB or binary data element. Because documents is a table, each row contains an instance of both id and pic. Notice that the size of the pic is 16MB.
Now we know what a BLOB is. How do we push some binary data into the BLOB?
Step 2: Inserting Data into a BLOB
Listing 4 illustrates population of a BLOB field with the contents of a binary file.
Listing 4 Populating a BLOB.
java.io.File file = new java.io.File("captureFile.wav");
int fileLength = (int) file.length();
java.io.InputStream fin = new java.io.FileInputStream(file);
PreparedStatement ps = conn.prepareStatement(
"INSERT INTO documents VALUES (?, ?)");
ps.setInt(1, 1477);
ps.setBinaryStream(2, fin, fileLength);
ps.execute();
The data contained in the file captureFile.wav will be stored in the BLOB. An InputStream instance is created, and then an instance of PreparedStatement called ps. The object ps is used to set the column values of the documents table. An important step in BLOB use is setting up a binary stream, which requires the specification of a source and a data length. Once the code in Listing 4 is executed, the BLOB data should be sitting comfortably in the database.
As the topic of this article is the storage of unstructured or binary data, remember that the data we’ve just stored is in fact recorded audio. To hear the audio, you can launch a media player by double-clicking the file captureFile.wav. The data in this file has now been transferred from the file into the database BLOB.
Once binary data resides inside a database, how can you get it out? Again, this is easy. What I’ve done is simply retrieve the BLOB and create a new file called dbsound.wav.
Step 3: Querying a Database for a BLOB
Listing 5 illustrates the code required to extract the BLOB data and create a new file called dbsound.wav.
Listing 5 Extracting binary data.
ResultSet rs = s.executeQuery(
"SELECT pic FROM documents WHERE id = 1477");
while (rs.next()){
java.sql.Blob ablob = rs.getBlob(1);
java.io.InputStream ip = rs.getBinaryStream(1);
FileOutputStream op = new FileOutputStream("dbsound.wav");
op.write(ablob.getBytes(1, (int)ablob.length()));
System.out.println("BLOB length = " + ablob.length());
op.close();
ip.close();}
The first thing to notice in Listing 5 is the SQL SELECT statement, which uses the same value for the id column (1477) as we used at creation time (refer to Listing 4). Next, we pull the BLOB object from the database and point an instance of InputStream at it. We also create a FileOutputStream object, into which we write the contents of the BLOB. When this completes, we’ll have re-created the original file, except that the contents have been pushed through the database courtesy of our friend the BLOB.
All that remains is to close the files. The binary data has been reconstituted in the form of an audio file.
Step 4: Application Use of the BLOB Data
When you run the code, you’ll notice the appearance of two entities: a folder (as discussed earlier, this is the local Derby database) and an audio file. To use the audio file, just double-click it to launch a media player. When you do this, you should hear the dulcet tones of the author welcoming you to Java Sound!
Step 5: Running the Code
The requirements for running the supplied code [3] are the same as for my earlier articles. [1, 2] Make sure that derby.jar and the compiled class file are both on the CLASSPATH. Then just use the command-line tool java.exe to invoke the program:
java blobTest
The program should run as described earlier.