Introduction
Large data objects such as images and audio are often required to be stored in the database. DB2 Universal Database provides specialized data types for storage large data objects. These data types are called large objects (LOB). In this article, we will travel in the world of LOB. More specifically, we will study LOB from the perspective of Java program developers. You will see how to store and retrieve the LOB in the Java application.
Business scene
Applying what you have learned to a real world is always beneficial. Therefore, in this article, we will simulate a bookstore. We hope to provide a method of retrieving a cover image for website visits. After that, you will see how we store the cover image of the book as blob. In addition, we also want to store a summary of the book in a CLOB data type in the database to search by keywords. The following figure will make you understand the workflow we will have to complete in this article.
figure 1
LOB family of data types
DB2 provides three different large object data types. All of these data types can store up to 2GB of data:
CLOB (Character Large Object) - can contain up to 2GB of character data. BLOB (Binary Large Object) - can contain up to 2GB of binary data. The binary data here can be substantially anything (such as images, audio files, etc.). DBClob (Double-byte Character Large Object) - can contain up to 2GB of double-byte character data. Please note that this data type can only be used when you create a database supports double-byte data.
These data types belong to the SQL3 data type. As you will see later, JDBC 2.0 supports these data types.
Do not move it unless you have to have already, don't move it.
Moving large objects in memory is a very resource operation. Therefore, DB2 provides a function of interacting with the LOB, which can minimize this movement.
Where is the data?
Note that the LOB in DB2 has an important and interesting feature that the value of LOB is not stored in the table of the database. In fact, the descriptor is stored, and the descriptor points to the actual location of the LOB. The real LOB value is stored in the table space, and the table space is some physical storage units.
Locator representing a LOB value
In fact, it is not true LOB data stored in the LOB column, but a pointer to the LOB data. This pointer is referred to as "locator". These so-called locators are used to represent the LOB value. When you retrieve data in a RESULTSET, you will retrieve the locator instead of the actual LOB value represented by these locator. As you will see, you must explicitly point out the LOB value to retrieve. This search is called "Materialization" with database terms.
begin
Let's start DB2 Command Line Processor to do LOB experiments.
First, we create a database called "LOBDB":
DB2 => CREATE DATABASE LOBDB
Use the username DB2Admin and password DB2Admin to connect to the database:
DB2 => Connect to LOBDB User DB2ADMIN Using DB2ADMIN
For the purpose of learning, we will create a pair of tables, store a blob and a clob:
DB2 => Create Table Bookcovers (Bookisbn Varchar (10) Not Null, Bookcover Blob (100K) Not Null, PRIMARY Key (Bookisbn))
DB2 => CREATE TABLE ABSTRACTS (Bookisbn Varchar (10) Not Null, Bookstract Clob (100K) Not NULL, PRIMARY Key (Bookisbn) The above statement is the maximum length of the LOB we need to store. This length can be changed in 1B to 2GB. When the LOB is embodied, the application will assign a size suitable buffer through this maximum length information to accommodate data.
You can use the following suffix to represent the number of bytes of length:
K: Thousand bytes (1,024 bytes) M: megabytes (1,048,576 bytes) G: Gigabits (1,073,741,824 bytes)
In our two tables, the book is distinguished by primary key bookishn. Bookisbn This is the ISBN number indicating the book.
Is it compressed?
When you create a table, you can specify the Compact or Not Compact option. If a Compact option is specified, the space you store will be minimized. However, if you perform an update operation to the LOB column and the operation increases the size of the LOB, performance loss is performed. On the other hand, if you specify the NOT Compact option (default), you can have a longer resilience to your LOB value.
Is it logging?
If you specify the logged option, the updates on the LOB column will be recorded in the system log. Specifies that the logged option provides maximum protection for the stored data. This can rebuild the data by recovering the process to prevent media failure. However, this is at the expense of disk space (not to say to the time paying for the disk). If you don't specify an option, the Logged option is selected by default.
Insert LOB
Inserting blob is very simple through Java. Please see the code sample below:
PreparedStatement PreparedStatement =
Connection.PrepareStatement ("INSERT INTO BOOKCOVERS VALUES (?,?)");
File imagefile = new file ("c: redbookcover.jpg");
InputStream InputStream = New FileInputStream (ImageFile);
PreparedStatement.setstring (1, "0738425826);
PreparedStatement.SetBinaryStream (2, InputStream, (int));
PreparedStatement.executeUpdate ();
The above short code snippet takes out files named RedBookcover.jpg from the C root directory and stores it into the database. Please note how we are associated with an InputStream. This InputStream object is used to fill the value of a preparative statement that exists for the blob column. This code and other code in this article can be found in the project zip file corresponding to this article.
The insertion of Clob is almost exactly the same as the insert of BLOB shown above. You can refer to project files named Clobinsertion.java. You will see a summary of us in the form of CLOB in this file. The summary is grabbed from the text file named RedBookAbstract.txt.
Retrieve LOB
We have inserted some LOBs. How do we retrieve them in Java? This is also a very simple process.
PreparedStatement PreparedStatement =
Connection.Preparestatement ("SELECT BOOKCOVER AUOKCOVERS WHERE BOOKISBN =?"); PreparedStatement.setstring (1, "0738425826");
ResultSet ResultSet = preparedStatement.executeQuery ();
While (resultSet.next ()) {
// Materialization of the blob
BLOB blob = resultSet.getblob (1);
InputStream InputStream = blob.getbinaryStream ();
File FileOutput = New File ("C: CloneDredbookCover.jpg");
FileOutputStream fo = new fileoutputstream (fileoutput);
INT C;
While ((c = intrutstream.read ())! = -1)
Fo.write (c);
fo.close ();
System.out.println ("BLOB RETRIEVED");
In the code segment above, we implemented a prepared statement, and we selected the blob in the previous code segment in the statement. One point to pay attention is that the real blob is only embodied until the line code below:
InputStream InputStream = blob.getbinaryStream ();
We use the input stream to store the retrieved BLOB in a file called CloneDredBookcover.jpg.
Most of the grammar of the CLOB is very similar to the search blob. See Project Document ClobRetrieVal.java.
JDBC API provides a means to make us more interesting operations for Clob.
Check the following code:
Clob Clob = ResultSet.getClob (1);
// Retrieve The First 200 Characters
String substring = clob.getsubstring (1,200);
System.out.println ("First 200 Characters of Clob (Book Abstract):" Substring);
// Find the position of a given string in the clob
Long positionofstring = clob.position ("Tool", 1);
System.out.println ("Position of Substring:" POSITIONOFSTRING);
In the above code, we use the getSubstring method to get part of the stored clob. Such functions can be used in a client application of some of the contents of the summary of the book. The above code also demonstrates the Position method. If a given string is included in our Clob, the method returns a long indicating that this starting position in which the string is searched in the CLOB. If the string is not included in the Clob, -1 will be returned. The Position method provided by the Clob interface does not need to be embodied in the stored data.
The actual specificity of Clob appears as follows:
File fileOutput = New file ("c: clonedredbookstract");
FileOutputStream fo = new fileoutputstream (fileoutput); InputStream is = clob.getaSciistream ();
INT C;
While ((c = is.read ())! = -1)
Fo.write (c);
fo.close ();
In the above code snippet, we store the stored Clob into a new file called CloneDredBookAbstract.txt. We use the getasciistream method in Clob to complete this operation. As in retrieving blob, we first assign the data stream to the InputStream, then read and write the read content to FileoutPutStream.
Conclude
In this article, you see how DB2 UDB provides tools to store large data objects. With JDBC 2.0 API, you can interact with LOB through Java. This article introduces you to the JDBC API usage. We introduce you to an example of a real world - a bookstore needs to store a summary of the cover image and books to introduce its usage. You should now you can use LOB in your own Java application.
Advanced reading
Developing Java Applications Using DB2 Image and Audio Extenders, Part 1Http://www-106.ibm.com/developerWorks/Library/it/it-1001Art29
DB2 Universal Database V8 Application Development - Java Application DevelopmentHttp://www-3.ibm.com/software/data/db2/udb/ad/v8/java/
Using DB2 Version 8 Develop Enterprise Java Applications http://www.ibm.com/developerWorks/cn/dmdd/library/techarticles/0209Hutchison/index.shtml
Advanced Programming for the Java Platform - JDBC Technology.java.sun.com/developer/onlineTraining/Programming/jdcbook/jdbc.html
Thank you
Thank Robert Indrigo to help me review this article.
