Enabling BLOB support with JHeadstart – Uploading/Downloading files to and from a BLOB column
JHeadstart 10.1.3 has great support for Uploading and Downloading files up to and from Web Applications – and indirectly from the database. Compared to prior releases, JHeadstart 10.1.3 has a little less flexibility: it only supports File manipulation for ViewObject Attributes of one of the Oracle InterMedia Types (that will be MultiMedia starting with Oracle 11g), such as ORDSYS.ORDDOC. When your database column is of that type, your EntityObject and ViewObject will have attributes of the Java Class that is the perfect counterpart - oracle.ord.im.OrdDocDomain.
However, many databases will have files stored in BLOB columns! And it seems like you are rather stuck with those when it comes to generating your JHeadstart application. I ran into that very same problem today, and was puzzled for some time. At one point I considered migrating all 100.000+ records from their BLOB home into an ORDSYS.ORDDOC column. I then focused on a Middle Tier solution:
- create a Transient Attribute MediaDocument of type OrdDocDomain in my ViewObject
- only use MediaDocument in the Web Application (base all JHeadstart Items on this attribute)
- in the getMediaDocument() method, create and return an OrdDocDomain object based on the BlobDomain attribute
- in the setMediaDocument() method, set the BlobDomain attribute, as well as the filename, mimetype and filesize attributes, from OrdDocDomain
Well, I more or less got it to work – but it was not straightforward. And as Steven Davelaar just informed me: using BlobDomain is disastrous for performance when used with large files, as the BlobDomain does not support streaming the file content); all the more reason for using OrdDocDomain in the middle tier! It turns out there is a much easier solution. It is largely server side and involves the on-the-fly-creation of ORDDOC objects in a SQL Query on the table with the BLOB column holding the files.
Let’s look at an example:
Create a table to store files (of course in reality you already have this table and are stuck with it, otherwise you would create a table with an ORDSYS type based column in it):
<br />create table image<br />( id number(10) not null<br />, filename varchar2(100)<br />, img_size number(10)<br />, mime_type varchar2(100)<br />, creation_date date default sysdate<br />, image blob<br />)<br />
Create a View on top of this table. The table has the Blob column – as well as file name, size and mime type – and the View has an OrdDoc column. They both have a Creation_Date column and an ID column:
create view VW_DOCUMENTS<br />as<br />select id<br />, ORDSYS.ORDDoc( ORDSYS.ORDSource( image, null, null, filename, null, 1 ),<br /> null, mime_type, img_size, null ) document<br />, creation_date <br />from image<br />/<br />
The ADF BC EntityObject Document is created on top of the View VW_DOCUMENTS. It has no knowledge whatsoever about the BLOB column. Now we can easily create a default ViewObject DocumentsView on top of the Entity.
Using JHeadstart, we quickly create a page to view, query, create/upload and update documents. Well, to be honest, we cannot just start creating, inserting and uploading documents: we need an INSTEAD OF trigger to deal with the insert into the View:
create or replace trigger AMIS_DOCUMENTS_IOIT<br />INSTEAD OF INSERT<br />ON VW_DOCUMENTS<br />FOR EACH ROW<br />declare<br /> l_filename varchar2(1000):= :new.filename;<br /> l_id number(10):= :new.id;<br />BEGIN<br /> if l_filename is null<br /> then<br /> l_filename := :new.document.source.srcName;<br /> end if;<br /> if inserting<br /> and <br /> l_id is null<br /> then<br /> select cxs_seq.nextval<br /> into l_id<br /> from dual;<br /> end if;<br /> INSERT INTO IMAGE<br /> (id, filename, img_size, mime_type,image )<br /> VALUES<br /> ( l_id<br /> , l_filename<br /> , :new.document.contentLength<br /> , :new.document.mimeType<br /> , :new.document.source.localData<br /> );<br />END;<br />/ <br />
This one will deal with the insert. For the updates, we need a simpler one:
create or replace trigger AMIS_DOCUMENTS_IOUT<br />INSTEAD OF update<br />ON VW_DOCUMENTS<br />FOR EACH ROW<br />declare<br />BEGIN<br /> update IMAGE<br /> set filename = :new.document.source.srcName<br /> , image = :new.document.source.localdata<br /> , img_size = :new.document.contentLength<br /> , mime_type = :new.document.mimeType<br /> where id = :new.id<br /> ;<br />END;<br />/
Note that a View with Instead Of triggers cannot just use the Refresh After Insert or Update feature (as they rely on the RETURNING clause that is not supported with Instead Of triggers). We have to override the isUseReturningClause() method in the EntityDefImpl, see: http://technology.amis.nl/blog/?p=1447 for the detailed solution for that issue.
- Uploading CSV files into dynamic ADF Faces tables – programmtically creating columns
- Example of XSL-transforming in ADF applications – downloading Excel (CSV) files from ADF Web Applications
- Using a generic JHeadstart Custom Template to support the inputTextHyperlink display type
- ADF Faces – Client Side Column Hide and Seek – Expanding/Collapsing Columns in Table
- JHeadstart Sighting: Steve Muench's Column in Oracle Magazine – Frameworks: Creating Search Pages