Enabling BLOB support with JHeadstart – Uploading/Downloading files to and from a BLOB column

0

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 />&nbsp;

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 />&nbsp;

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 />/&nbsp;

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.
 

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Comments are closed.