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 use cases.
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 images)
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 called insertImage
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 with.
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 data service?
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 service
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 archive
- 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.
- Configure the in web.xml
- If using Portlet Factory 6.1.2, open web-inf\bin\deployment\wasce.web.xml. Uncomment the WASCE sample Datasource:
Then open web-inf\bin\deployment\geronimo-web.xml. Uncomment the Dbpool dependency for the sample datasource
and also uncomment the resource ref for the sample datasource.
- If using previous version of Portlet Factory, you will need to create a database in Cloudscape, use the WAS CE Administrative Console to configure a Database pool, and manually add the and elements to wasce.web.xml and geronimo-web.xml.
- Run the consumer model models/samples/db_blob/Blob_Consumer.model