When retrieving data from a relational database, the SQL builders automatically
transform simple data types -- including VARCHAR, INT, and CHAR -- into
the SQL builder's XML results variable. But complex data types --
including BLOB, CLOB, ARRAY and STRUCT -- often require special handling
and can be potentially large, requiring considerable heap memory. Thus,
these complex types are not automatically transformed. Instead,
the SQL Call and SQL Statement builders allow you to code special callback
methods so that you can transform these data types in a customized fashion.
Conversely, the SQL builders automatically handle type conversion and setting
parameters for simple data types for SQL INSERT and UPDATE statements.
To handle the complex data types, the SQL builders allow you to code
special callback methods and automatically pass you the objects you need
to set the value manually using the java.sql.PreparedStatement.
This article and accompanying sample application demonstrates both of these
For simplicity and easy configuration, this sample was built to use the
embedded Cloudscape database in WAS Community Edition, which ships with
Portlet Factory. If you wish to use another supported application server
or relational database, the approach will be similar.
Before describing the models, we'll start with a description of the sample
app and an overview of the architecture.
|The Sample Application
The sample application, shown in the image to the right, allows for uploading
and viewing images stored in a BLOB column. The application is configured
to use the SAMPLE database and related JNDI name pre-configured with the
version of WAS CE that ships with Portlet Factory.
When the application is initially run, clicking "Create and Populate
Table" will create a table in the database in Cloudscape and populate
it with two sample IBM logo images from the WAR file. Clicking "Drop
Table" will remove the table.
Once a table is created, an input to browse and upload a file is
visible beneath the table. Use this to upload and insert an image
of your own. (For simplicity, this sample only accepts .jpg and .gif
Now that we know what the sample does, let's see how it works.
The sample is written in the recommended provider/consumer pattern. All
interaction with the back end database occurs in the provider model in
models/samples/db_blob/BlobProvider.model. The provider model exposes services
through a Service Definition and Service Operation builders.
The consumer model models/samples/db_blob/Blob/Consumer.model is not aware
of the underlying DBMS data store; it only knows the interfaces to the
service operations. Thus, if you decided to change the underlying
data store in the provider model, the consumer does not have to change.
Now we'll look at how the provider model creates and deletes the table,
transforms the BLOB data, and stores data in a BLOB field.. Then, we'll
see the consumer interacts with these services.
Creating and Dropping the Table
Two SQL Call builders, wrapped by Service Operation builders, handle creating
and dropping the table. There are no inputs or outputs of these operations.
The screenshot below shows the SQL statement used to create the table.
Similarly, the dropTable executes a SQL statement to drop the table.
With the ability to create a table, now we can issue SELECT and INSERT
statements to retrieve and store image files.
Transforming BLOB Data from a SELECT
The "selectImages" Sql Statement builder handles retrieving
records from the database. The SQL statement is a simple select:
To transform the data in certain columns in a customized fashion, the SQL
Statement builder allows you to specify call back methods when it is time
to transform a particular column in a record. This is configured
in the ResultSet Custom Data Transforms section in the builder. As
shown below, choose an appropriate column number. Then, type in a method
that will be called to transform that column for each record. (Note
that you cannot use the chooser to select a method). In this case,
column 2 maps to the BLOB column, so transformImage will be responsible
for handling this column.
So that the SQL builder can pass you the objects you need, yourTransform
Method must have the following signature.
IXml myTransformMethod(String builderName, int columnNumber,
Object columnValue, String elementName, boolean createVerboseXml)
The method must return IXml, which is the data that will be set for
that column in the results variable. The columnValue input contains
the column data from the result set. The elementName contains the
name of the element being transformed. In this case, the element
name is IMAGE.
In this sample application, transformImage does several things:
- Casts the columnValue to a java.sql.Blob
- Gets an InputStream from the BLOB
- Streams the InputStream data to a FileOutputStream
which persists the data as a file in application's html root directory
- Creates an IXml object using passed
in elementName and sets its value to a relative path of the persisted image
- Returns the IXml object, which is put
in the schema typed results variable of the SQL builder.
So now the provider can retrieve image data from a BLOB column, save it
to a servable directory on the app server, and return a relative URL in
the results variable. But how does the consumer model use this data?
After the consumer calls the getImages service, the consumer model uses
an Image builder whose Image Source input is configured to point to the
IMAGE element -- set by the transformImage method -- of the RowLoopVar.
The IMAGE element contains the relative URL returned from transformImage.
Now that we can retrieve and transform images from a BLOB column, let's
see how we can perform the opposite process: upload and store image
data in a BLOB column.
Inserting BLOB Data
Dealing with simple data types such as VARCHAR is easy: SQL Call
creates unique variables for each parameter in your SQL statement. You
set the value and SQL Call will substitute this value when inserting the
data into the database. However, when dealing with complex types
such as BLOB, a bit more work is needed. To flexibly handle these complex
data types, SQL Call allows you to create a special callback method that
sets the value for the parameter -- in this case, the data for the image.
We'll discuss this call back method shortly. First we'll look
at the SQL insert statement and how the provider model exposes a data service
for inserting an image.
The SQL Call builder defines a SQL INSERT statement. In the Parameters
section, notice that column 2 -- the image column -- is configured to call
setImage, which is our callback method that will ultimately handle inserting
the image into the database.
Before looking at the setImage call back method, let's look at how the
provider model exposes a data service for inserting an image.
The provider model needs metadata about the image - its name and file system
path on the application server (You'll see shortly how the file is sent
from the browser and temporarily stored on the application server) --
and a Service Operation so that it can be called by a consumer. Thus
the provider uses a Schema builder to define a schema named imageSchema
that is used to describe the metadata. Notice the schema defines
two elements: name and path.
This schema is then used when defining the input structure in insertImage
Service Operation. As shown below, the Service Operation is configured
to call an action called insertImage, which we'll discuss next. It
also defines an input to the Service Operation and the input is schema
typed to the imageSchema schema.
The "Action to Call" input is configured to call insertImage.
This is implemented in the Method builder of the same name.
The insertImage method does several things:
- Receives a schema typed argument containing
the image metadata (the argument is schema typed to the imageSchema schema)
- Creates a java.io.FileInputStream to
the image with the given path and stores that in a Variable called imageInputStream
(You'll see shortly that the call back method retrieves this variable to
do its work)
- Saves the length of a file to a variable
(You'll see shortly that the call back method needs the length of the file
to do its work)
- Manually invokes the SQL Call builder
To review so far, we have exposed a Service Operation whose argument contains
the image metadata. We've defined a SQL Call builder with a SQL INSERT
statement. The target operation of the Service Operation is insertImage,
which is a method that reads the file path from the metadata, creates an
input stream and saves it to a variable, then invokes the SQL Call builder
to do its work. With this in place, we can now discuss the setImage
call back method.
The callback method can have any name, but it must have the following signature:
void setImage( String builderName, java.sql.PreparedStatement pStatment,
Integer position, String getOrSet)
The PreparedStatement input is used to manually call one of the setter
methods. The position input indicates which parameter you're dealing
In this sample, the setImage call back method does the following:
- Retrieves the image file's InputStream
that was set by the insertImage method.
- Retrieves the length of the file that
was set by the insertImage method
- Calls PreparedStatement.setBinaryStream(),
passing the parameter position, the file's InputStream and the file's length
To summarize, when a consumer calls the insertImage data service and passes
the metadata, an InputStream is created and the SQL Call builder will call
the setImage call back method that will use the PreparedStatement to insert
the stream into the BLOB field.
One last question remains: How does the consumer use the insertImage
The consumer uses a File Upload builder to allow a file to be sent from
the browser and persisted on the application server's disk. The submit
button is configured to call the uploadFile Action List. The uploadFile
Action List calls com.ibm.samples.db_blob_sample.Util.uploadFile() which
does the following:
- Retrieves the file name of the file
uploaded by the File Upload builder. (The File Upload builder automatically
sets it in a request input keyed by the tag name on which the File Upload
builder was placed on the page)
- Creates an IXml variable that conforms
to the imageSchema schema
- Sets this variable as the input to the
provider's insertImage data service
- Invokes the provider's insertImage data
A similar process happens when the the user clicks the "Create and
Populate Table." See the main Action List defined in the consumer
model and the populateTable() method defined in com.ibm.samples.db_blob_sample.Util.
You've just seen how to retrieve and store image data in a BLOB column.
We only had to write a minimal amount of code; the majority of the
code automation is done for you by the SQL builders, saving you considerable
time. The SQL builders were purposefully designed this way to provide
flexibility when working with complex SQL types. We also saw how
to leverage Portlet Factory's other builders -- including the File Upload
builder, Service Definition, and Service Operation -- to rapidly
build the rest of the application.
This same general approach can be used for handling other complex types,
such as CLOB and STRUCT.
For Additional Information
- See "Writing ResultSet Column and
Output Parameter Transform methods" in the Portlet Factory help.
- See "About writing custom set methods
for input parameters" in the Portlet Factory help.
Download and Instructions
- For demonstrative purposes, the sample
application was written specifically to work with WebSphere Community Edition
and its embedded Cloudscape database. Building an application to handle
BLOB data on another supported application server or DBMS will be similar.
- Create a new Portlet Factory project
- Import the zip file as a Portlet Factory
- Note that this zip contains an override.properties
that enables several features for this sample to work. Thus if you
are importing this zip over an existing project, you'll be prompted to
overwrite your current override.properties file.
- Run the consumer model models/samples/db_blob/Blob_Consumer.model