2005_604_wagner_ppr

eBusiness & Application Development

UUSINGSING O ORACLERACLE P PLL/SQL /SQL ANDAND JDBC JDBC TOTO M MANAGEANAGE F FILEILE UUPLOADSPLOADS W WITHINITHIN T THEHE D DATABASEATABASE - A H - A HANDSANDS--ONON E EXAMPLEXAMPLE

Mary Wagner, Delaware Department of Transportation

WHAT ARE BLOBS? A BLOB is an Oracle data type that can be used to store binary data within an Oracle database. The term BLOB is an abbreviation for Binary Large Object and is a type of internal Large Object (LOB). An internal LOB is stored within the database, whereas an external LOB is stored outside of the database (i.e. BFILE). Many different types of binary files, including spreadsheets, word processor documents, images and other files can be stored within the database, using the BLOB data type.

WHY USE THEM?Storing binary files within the database has many advantages over storing them within a server’s operating system.REMOVES FILE SYSTEM / SERVER DEPENDENCEIf you store binary files inside the database, you no longer have to depend on your server administrator to manage access to the server or files. You don’t have to worry about files getting accidentally deleted or other server security issues.FILES ARE BACKED UP VIA DATABASE BACKUPSIf you store binary files inside the database, then they will be backed up as part of the database when doing your regular database backups and exports. FILES ARE MORE SECUREIf you store binary files inside the database, then security and access to the files can now be managed within the database. There is now more than one level of security (server OS). In other words, a user also needs database access and access to the database object where the files reside in order to access the files.FILES RECOVERABILITY IS HIGHERIf you store binary files inside the database, then they are recoverable just like other data that is stored within the database. For example, when a file is uploaded to the database, it is part of an Oracle transaction and can be recovered, in the event of a media failure, just like any other type of data stored in an Oracle database.

AN EXAMPLE - MY DOCUMENT MANAGEMENT SYSTEM (MYDMS) OVERVIEWmyDMS is a small java web application used for managing and viewing documents (binary files) by topic. The maximum file upload size for this application is 50mb. The application uses JDBC and PL/SQL to upload, view and delete binary files from within an Oracle 9i database. Within the application, topics are used to categorize the uploaded documents. Topics can be

1 Paper #604


added, modified and deleted. The application uses an Oracle 9i database to house the data and BEA Weblogic 8.1 as the Java Application Server. A JDBC connection pool and JNDI datasource are implemented within the Weblogic server instance.

The user interface is written using the Java Model – View – Controller (MVC) architecture. There is a controller and process request servlet that handle the flow and process designated action classes, which are related to each task that can be completed within the application. The action classes call methods in the Data Access Object (DAO) classes. These DAO methods call the PL/SQL procedures that complete all of the related database transactions from within the database. Java Server Pages are used for the various user interface views and call getter methods in the related Value Object (VO) classes, which are set within the executed DAO methods.

FUNCTIONALITY AND RELATED SCREENSScreenshots and brief explanations of the functionality of myDMS are displayed below. The detailed explanations of each phase of developing the application will then follow.

LOGGING INThere are 2 initial users in the myDMS application. To test the application functionality, either of these users can be used to log into the system (Figure 1):

MANAGER – this user can view, upload and delete documents as well as view, edit and delete topics.

USER – this user can view and upload documents and view and add topics, but cannot delete documents or edit or delete topics.

Figure 1 (myDMS - Log In Screen)

VIEW DOCUMENT LISTOnce logged in, a current list of documents is displayed (Figure 2).

Figure 2 (myDMS - View Document List)

2 Paper #604


VIEW A DOCUMENTWhile viewing the document list, the “VIEW” link can be clicked to view the actual binary file representing the document. This will retrieve the BLOB from the database and display it in it’s originally uploaded file type on the screen (Figure 3).

Figure 3 (myDMS – View a Document)

UPLOAD A NEW DOCUMENTA new document can be uploaded by clicking the “UPLOAD NEW DOCUMENT” link, while viewing the document list. After clicking the link, the upload form is displayed and once completed and submitted, the file and related data is saved to the database. The file is saved as a BLOB within the oracle database (Figure 4).

Figure 4 (myDMS – Upload a New Document)

DELETE A DOCUMENTIf the logged in user is the MANAGER user, then the “DELETE” link can be clicked on to delete the related document from the database (Figure 5).

3 Paper #604


Figure 5 (myDMS – Delete a Document)

VIEW TOPIC LISTThe “TOPIC LIST” link can be clicked on to view a list of topics that can be assigned to documents when they are uploaded (Figure 6).

Figure 6 (myDMS - View Topic List)

ADD A NEW TOPICThe “ADD NEW TOPIC” link can be clicked to add a new topic to the database. The new topic will display in the topic dropdown list, which displays on the “UPLOAD NEW DOCUMENT” form (Figure 7).

Figure 7 (myDMS – Add a New Topic)

DELETE A TOPICThe “DELETE” link can be clicked to delete the related topic. The deleted topic will no longer appear in the topic list or in the topic dropdown list on the “UPLOAD NEW DOCUMENT” form (Figure 8).

4 Paper #604


Figure 8 (myDMS – Delete a Topic)

EDIT A TOPICThe “EDIT” link can be clicked to edit the related topic. The updated topic will then be displayed in the topic list and in the topic dropdown list on the “UPLOAD NEW DOCUMENT” form (Figure 9).

Figure 9 (myDMS – Edit a Topic)

MYDMS - PHASE I. CREATING DATABASE TABLESPACES, USERS, TABLES / SETTING UP

TABLESPACES There are 2 tablespaces that house the myDMS data, MYDMS_DATA and MYDMS_BLOB. MYDMS_DATAThe MYDMS_DATA tablespace houses the NON-BLOB application data, including Document Details, Topics and User information. Below are the SQL PLUS commands that can be run to create this tablespace (Figure 10).

5 Paper #604


Figure 10 (MYDMS_DATA tablespace)

--create tablespace CREATE TABLESPACE MYDMS_DATA LOGGING DATAFILE 'C:\ORACLE\ORADATA\MW92\MYDMS_DATA01.DBF' SIZE 10304K REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

MYDMS_BLOBThe MYDMS_BLOB tablespace houses the DOCUMENTS, which has the BLOB column, containing the binary files that are uploaded. Below are the SQL PLUS commands that can be run to create this tablespace (Figure 11).

Figure 11(MYDMS_BLOB tablespace)

--create tablespace CREATE TABLESPACE MYDMS_BLOBLOGGING DATAFILE 'C:\ORACLE\ORADATA\MW92\MYDMS_BLOB01.DBF' SIZE 51264K EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

USERS There are 2 database users that support the myDMS application, the schema owner, MYDMS, and the application user, MYDMS_USER.MYDMSThe MYDMS account is the schema owner for the application. This user houses all of the tables, PL/SQL packages and other database objects that are called by the Java user interface (Figure 12).

Figure 12(MYDMS Schema Account)

--create user CREATE USER MYDMS IDENTIFIED BY MYDMS

6 Paper #604


DEFAULT TABLESPACE MYDMS_DATA TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON MYDMS_BLOB QUOTA UNLIMITED ON MYDMS_DATA;

--grant rights GRANT CONNECT TO MYDMS;GRANT CREATE PROCEDURE TO MYDMS;GRANT CREATE TRIGGER TO MYDMS;

MYDMS_USERThe MYDMS_USER account is the application end user account and is used by the JDBC connection pool. All data access within the application takes place through PL/SQL packages within the MYDMS schema. The execute privilege is granted to MYDMS_USER on the required PL/SQL packages only. The MYDMS_USER account also has a logon trigger that sets the current schema to MYDMS, whenever the MYDMS_USER account connects to the database. There are no synonyms currently being used within the application (Figure 13).

Figure 13(MYDMS_USER Database Account)

--create userCREATE USER MYDMS_USER IDENTIFIED BY MYDMS_USERDEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;GRANT CONNECT TO MYDMS_USER;

--create logon triggerCREATE OR REPLACE TRIGGER MYDMS_USER.TRG_LOGON_SET_SCHEMA AFTER LOGON ON MYDMS_USER.SCHEMABEGINEXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=MYDMS';END TRG_LOGON_SET_SCHEMA;/

NAVIGATION AND SECURITY OBJECTSAfter the tablespaces and users are created, some system related tables and objects need to be created. These objects are used by the application for navigation flow, access and login verification.

TABLES ACTIONS – Houses meta data related to action class and the resulting success and

failure JSP views.

7 Paper #604


USERTYPE – Houses system user types, which are assigned to each user account and used to manage user access to the application actions.

USERS – Houses system user account information USERACCESS – Controls user access to specific application actions

The below script, which is included with the download script and web archive files, will create the required tables.@create_tables_action_and_user.sql

PL/SQL PACKAGE – PKG_MVC PKG_MVC – Houses procedures and functions related to determining the next JSP view,

verifying whether the logged in user has access to the requested action and verifying usernames and passwords when users log into the system. There are 3 procedures – get_action_info, get_password_hash and verify_user. (Figure 14).

Figure 14(PKG_MVC Package)

The below script, which is included with the download script and web archive files, will create the PKG_MVC package.@create_package_mvc.sql

APPLICATION TABLESThere are 3 main application tables, used to store document information and topics. These tables are named DOCUMENTS, TOPICS AND DOCUMENTDETAILS.

DOCUMENTS (THE BLOB TABLE)The documents table houses the binary file (BLOB column) which is saved to the database when the “UPLOAD NEW DOCUMENT” form is submitted. This table is assigned to the MYDMS_BLOB tablespace. The BLOB column within the table is called BINARYFILE. BLOB columns have unique settings that are further discussed below. (Figure 15).

8 Paper #604


Figure 15(DOCUMENTS Table)

INITIALIZATION OF LOB

In order to access the contents of a LOB column, a LOB locator is used by Oracle. If a null value is stored in a LOB column, the locator is not stored in the row, instead the null value is stored in the row. To remedy this, the LOB column is initialized. To accommodate this requirement in myDMS, the default value of the BINARYFILE column is set to EMPTY_BLOB(). This will allow the BLOB column to be initialized upon insertion and then we will be able to return the newly inserted empty blob locator and write the uploaded binary data to the BINARYFILE column when the “UPLOAD NEW DOCUMENT” form is submitted.

If the UPLOAD form within myDMS was not going to always be inserting a BLOB, upon submission, I may want to have null values allowed within the BLOB column. Since this isn’t the case with this application, however, I am initializing the column with every insert.

ENABLE LOB STORAGE IN ROW (INLINE STORAGE)For this example, the default setting of enabling LOB storage in row is being accepted (also known as inline storage). By enabling inline storage, if the BLOB length is less than 4mb, it’s contents will be stored within the table row. If the BLOB length is more than 4 mb, then the contents will be stored in the designated tablespace, however, the LOB locator will still be stored within the table row.

LOB TABLESPACE

For this example, we have created a separate tablespace, called MYDMS_BLOB, to house BLOB contents in each row that are larger than 4mb and therefore, cannot be stored within the table row. By storing the larger BLOBs in their own tablespace, we will be able to keep them separate from other data and also be able to monitor the growth, as it relates to the binary files, themselves.

9 Paper #604


CHUNK SIZE

In this example, we are accepting the default chunk size of 8192 bytes, which is the block size of our database. Our files are all going to be fairly small, nothing over 50mb, so this size should be sufficient.

LOB CACHE

In this example, we are accepting the default of off for the LOB cache. In our example, the same documents are not going to be viewed several times a day by several different useres, but only infrequently, therefore, they do not need to be cached. If you choose to cache your LOB, make sure to adjust the buffer cache settings appropriately.

The below commands can be run in SQL PLUS as the MYDMS user to create the DOCUMENTS table and it’s related objects and to insert sample records.

--create tableCREATE TABLE DOCUMENTS (DOCUMENTID NUMBER(10) NOT NULL, BINARYFILE BLOB DEFAULT EMPTY_BLOB() NOT NULL,DATESTAMP DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT PK_DOCUMENTS_DOCUMENTID PRIMARY KEY(DOCUMENTID)) TABLESPACE MYDMS_BLOB;

--create sequence to populate primary keyCREATE SEQUENCE SEQ_DOCUMENTS_DOCUMENTID;

TOPICS (NO BLOBS)The topics table houses a list of document topics that can be used to categorize a document when uploading it through the application. The below commands can be run in SQL PLUS as the MYDMS user to create the TOPICS table and it’s related objects and to insert sample records (Figure 16).

Figure 16(TOPICS Table)

--create tableCREATE TABLE TOPICS (

10 Paper #604


TOPICID NUMBER(2) NOT NULL, TOPICNAME VARCHAR2(128) NOT NULL,USERID NUMBER(3) NOT NULL,DATESTAMP DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT FK_TOPICS_USERID FOREIGN KEY(USERID) REFERENCES USERS(USERID), CONSTRAINT PK_TOPICS_TOPICID PRIMARY KEY(TOPICID),CONSTRAINT UN_TOPICS_TOPICNAME UNIQUE(TOPICNAME))TABLESPACE MYDMS_DATA;

--create additional indexCREATE INDEX FK_TOPICS_USERID ON TOPICS(USERID); --create sequence to populate primary keyCREATE SEQUENCE SEQ_TOPICS_TOPICID;

--create trigger to automatically update datestamp when record is modifiedCREATE OR REPLACE TRIGGER TRG_TOPICS_DATESTAMPBEFORE UPDATE ON TOPICS FOR EACH ROW BEGIN:new.DATESTAMP := SYSDATE;END TRG_TOPICS_DATESTAMP;/

--insert sample recordsINSERT INTO TOPICS (TOPICID, TOPICNAME, USERID) VALUES (0, 'NONE', 1);INSERT INTO TOPICS (TOPICID, TOPICNAME, USERID) VALUES (SEQ_TOPICS_TOPICID.NEXTVAL, 'DISASTER RECOVERY', 1);INSERT INTO TOPICS (TOPICID, TOPICNAME, USERID) VALUES (SEQ_TOPICS_TOPICID.NEXTVAL, 'STANDARD OPERATING PROCEDURES', 1);INSERT INTO TOPICS (TOPICID, TOPICNAME, USERID) VALUES (SEQ_TOPICS_TOPICID.NEXTVAL, 'SERVICE LEVEL AGREEMENTS', 1);COMMIT;

DOCUMENTDETAILS (NO BLOBS)The documentdetails table houses all of the non-blob data regarding uploaded documents, including a title, description, filename, file size and file content type. The below commands can be run in SQL PLUS as the MYDMS user to create the DOCUMENTDETAILS table and it’s related objects (Figure 17).

11 Paper #604


Figure 17(DOCUMENTDETAILS Table)

--create tableCREATE TABLE DOCUMENTDETAILS (DOCUMENTDETAILID NUMBER(10) NOT NULL, TOPICID NUMBER(2) NOT NULL, DOCUMENTID NUMBER(10) NOT NULL, TITLE VARCHAR2(512) NOT NULL, DESCRIPTION VARCHAR2(1024 byte) NOT NULL, FILENAME VARCHAR2(256) NOT NULL, FILESIZE NUMBER(9) NOT NULL, CONTENTTYPE VARCHAR2(128) NOT NULL, USERID NUMBER(3) NOT NULL, DATESTAMP DATE DEFAULT SYSDATE NOT NULL,CONSTRAINT FK_DOCUMENTDETAILS_DOCUMENTID FOREIGN KEY(DOCUMENTID) REFERENCES DOCUMENTS(DOCUMENTID), CONSTRAINT FK_DOCUMENTDETAILS_TOPICID FOREIGN KEY(TOPICID) REFERENCES TOPICS(TOPICID), CONSTRAINT FK_DOCUMENTDETAILS_USERID FOREIGN KEY(USERID) REFERENCES USERS(USERID), CONSTRAINT PK_DOCUMENTDETAILS_DDID PRIMARY KEY(DOCUMENTDETAILID),CONSTRAINT UN_DOCUMENTDETAILS_FN_TID UNIQUE(FILENAME, TOPICID))TABLESPACE MYDMS_DATA;

--create additional indexesCREATE INDEX FK_DOCUMENTDETAILS_DOCUMENTID ON DOCUMENTDETAILS(DOCUMENTID); CREATE INDEX FK_DOCUMENTDETAILS_TOPICID ON DOCUMENTDETAILS(TOPICID);CREATE INDEX FK_DOCUMENTDETAILS_USERID ON DOCUMENTDETAILS(USERID);

--create sequence to populate primary keyCREATE SEQUENCE SEQ_DOCUMENTDETAILS_DDID;

--create trigger to automatically update datestamp when record is modifiedCREATE OR REPLACE TRIGGER TRG_DOCUMENTDETAILS_DATESTAMPBEFORE UPDATE ON DOCUMENTDETAILS FOR EACH ROW BEGIN

12 Paper #604


:new.DATESTAMP := SYSDATE;END TRG_DOCUMENTDETAILS_DATESTAMP;/

MYDMS - PHASE 2. PL/SQL API DATABASE API – PL/SQL PACKAGE (PKG_DMS)All database related transactions are completed through functions and procedures within a PL/SQL package, called PKG_DMS. This package includes procedures to upload documents, delete documents, view a document listing, add a topic, edit a topic, delete a topic and view a topic listing. It also contains functions and global variables used within these procedures. The MYDMS_USER account is granted execute on this package.

Below are details related to just the Document / BLOB related procedures that are housed within the PKG_DMS package.

VIEW_DOCUMENT_LISTThis procedure returns a ref cursor, which is used by the related Java Data Access Object to display a current list of uploaded documents. The DOCUMENTDETAILS table is queried and the resulting data is stored in the ref cursor and returned as an out parameter. The DOCUMENTS table (and the related BLOB column) are not touched for this query, as the BLOB is not needed to display a listing of documents.

UPLOAD_DOCUMENTThis procedure takes care of inserting an initial record into the DOCUMENTS table, inserting a record into the DOCUMENTDETAILS table, returning the LOB locator to Java for use in streaming the uploaded document into the database and returning an updated document list, via a ref cursor, to the calling Java Data Access Object.

VIEW_DOCUMENTThis procedure is for displaying the BLOB within the client’s browser. The BLOB column is returned through a ref cursor, to the related Java Data Access Object. Java streaming is then used to display the file via the web browser.

DELETE_DOCUMENTThis procedure is for deleting the related uploaded file from the database and returning an updated document list to the related Java Data Access Object for display to the user. Related records from the DOCUMENTS and DOCUMENTDETAILS tables are removed from the database for the related uploaded file.

MYDMS - PHASE 3. CREATE JAVA OBJECTS MODEL – VIEW – CONTROLLER (MVC) ARCHITECTURE myDMS uses the Java MVC architecture--

13 Paper #604


Model - Business / Value Object (VO) (also known as Java Bean) – Value objects are place holders for data that is displayed on JSP views. Value Objects have getter and setter methods. Data Access Objects call the setter methods to store the data that is retrieved from the database and JSP Views call the getter methods to display the stored data within the client’s browser.

View (also known as Java Server Page, JSP) - Views are the web pages that are displayed within the client’s browser. They could also be described as the user interface. These are the screens that the end user interfaces with to submit and retrieve information from the application.

Controller – The controller is a servlet and is responsible for the navigation of the web application. It routes to the application to the appropriate action and then is responsible for forwarding the user to the appropriate success or failure view (JSP).

Actions – Actions are Java classes that are called by the controller to complete specific tasks within the application. They execute the data access and other methods that make up the application. There is an action for each piece of functionality within the application.

Data Access Objects (DAO) – Data Access Objects contain all of the database access methods. The database access methods are responsible for handling any database related transactions, including inserting new records, updating existing records, deleting existing records and selecting a set of records to be displayed within the client’s browser.

Below, I will discuss the Java objects related to the BLOB portion of the myDMS application. Although myDMS allows Topics to be added, edited and deleted, since these are non-BLOB tasks I’m not going to discuss the corresponding MVC objects for them, but only for the MVC objects for the Document / BLOB related tasks.

VALUE OBJECTS (BLOB RELATED ONLY) DOCUMENTVOThis java bean is the main bean for the application and includes the getter and setter methods that are used to get and set data to be displayed on the resulting JSP views (Figure 18).

Figure 18(DOCUMENTVO Value Object / Java Bean)

14 Paper #604


VIEWS – JAVA SERVER PAGES(BLOB RELATED ONLY) VIEWDOCUMENTLIST.JSPThis view displays the current list of documents that have been uploaded to the database, within the client’s browser.

VIEWDOCUMENTSERVLETThis view is really a servlet, but is the designated success view for displaying a queried document (binary file) within the requesting client’s browser.

FORMUPLOADDOCUMENT.JSPThis view displays the form within the client’s browser that is used for uploading a new document into the database.SERVLETS CONTROLLERSERVLETThis servlet controls the flow of the application. All actions (URLs with a .do extension) are routed through this servlet, which then processes the action request and returns the appropriate success or failure JSP view. The

PROCESSREQUESTSERVLETThis servlet is called by the Controller servlet and handles the guts of the requested action class processing. It instantiates the action and then determines whether the action was processed successfully. Once the result is determined, it then returns the appropriate JSP view to the Controller servlet.

ACTION CLASSES (BLOB RELATED ONLY)VIEWDOCUMENTLISTACTIONThis action class calls the related DAO method to display a list of uploaded documents that are currently in the database. At this point, the BLOB is not touched at all, only the table which houses the details behind the uploaded file. This action queries only the DOCUMENTDETAILS table.

VIEWDOCUMENTACTIONThis action class is responsible for displaying an uploaded binary file (BLOB) within the client’s browser. Request attributes for the queried document, including the file name and content type are set by this action. The related DAO method is called to retrieve the BLOB locator and then stream the binary file to the client’s browser. This action queries only the DOCUMENTS table.FORMUPLOADDOCUMENTACTIONThis action class calls the related DAO method to display the form for uploading a new document. There is a drop down list of Topics that is displayed on this form, which is retrieved and set via this action and the related DAO method. This action queries the TOPICS table.

15 Paper #604


SAVEUPLOADDOCUMENTACTIONThis action class calls the related DAO method to save the submitted data from the upload form to the database. The initial BLOB locator is returned to the DAO method and then Java streaming is used to upload the binary data to the database. This action inserts into the DOCUMENTS and DOCUMENTDETAILS tables and then queries the DOCUMENTDETAILS table.

SAVEDELETEDOCUMENTACTIONThis action class calls the related DAO method to delete the related document from the database and then displays an updated document list to the client’s browser. This action deletes from the DOCUMENTS and DOCUMENTSDETAILS tables and queries the DOCUMENTDETAILS table.

DATA ACCESS OBJECTS (BLOB RELATED ONLY)BASEDAOThis data access class includes database connectivity methods used by all DAO objects, including retrieving and releasing connections, closing statements and result sets, committing and rolling back transactions (Figure 19).

Figure 19(BASEDAO Data Access Object)

DOCUMENTDAOThis data access class is the data access object that calls all of the PL/SQL procedures related to uploading, viewing and deleting documents in the database (Figure 20).

Figure 20(DOCUMENTDAO Data Access Object)

MYDMS - PHASE 4. PUTTING IT TOGETHER Now that we have the database objects and the Java objects defined, we can build the application, based on the myDMS BLOB related functionality that we have outlined at the beginning of the paper. The flow is the same for each piece of functionality.

16 Paper #604


An ACTION class is processed which executes a DATA ACCESS OBJECT method within the DocumentDAO class, which in turn executes the related PL/SQL stored procedure within the PKG_DMS package. Data can be passed as out parameters back to the DocumentDAO class, which then can be processed or used to set the DocumentVO object as a request attribute. The resulting Java Server Page view then calls the DocumentVO getter methods to display data to the client’s browser.

Below I will go over the pieces that make up the core DOCUMENT functionality of the myDMS application, including:

Viewing a Document Listing Viewing a Binary Document File Displaying the Upload Form Saving the Upload Form to the Database Deleting an Uploaded File from the Database

The code snippets displayed below are the key pieces that make up each part of each illustrated function, however, the complete working code, including PL/SQL, Java and other files is available for download (see “Where to Get the myDMS scripts, Java files and Web Archive” section at the end of the paper).

VIEWING A DOCUMENT LISTINGThe document listing is a listing of all of the uploaded documents that are in the DOCUMENTDETAILS table. The BLOB column, which is stored in the DOCUMENTS table, is not touched for this query, as we do not need to display the BLOB data at this point. The file size is calculated upon BLOB insertion and then stored as a number within the DOCUMENTDETAILS table, so that it does not have to be calculated every time that the Document List is viewed (Figure 21).

Figure 21(myDMS DOCUMENT LIST hyperlink

ACTION CLASS VIEWDOCUMENTLISTACTIONThis class is processed by the controller and executes the viewDocumentList method within DocumentDAO (Figure 22).

Figure 22(ACTION CLASS --ViewDocumentListAction)

17 Paper #604


DOCUMENTDAO METHOD VIEWDOCUMENTLISTThis method executes the view_document_list PL/SQL procedure within the PKG_DMS package (Figure 23).

Figure 23(DOCUMENTDAO – viewDocumentList Method)

PKG_DMS METHOD VIEW_DOCUMENT_LISTThis procedure returns a ref cursor as an out parameter back to the viewDocumentList method in DocumentDAO. DocumentDAO then processes the result set to create a DocumentVO object and sets the object as a request attribute which the JSP view can then reference for displaying to the client’s browser (Figure 24).

Figure 24(PKG_DMS – view_document_list Procedure)

JSP VIEW VIEWDOCUMENTLIST.JSPThis view retrieves the document list, which was set as a request attribute by the DAO method, and then iterates through the list to display the document listing (Figure 25).

Figure 25(VIEW – viewDocumentList.jsp)

18 Paper #604


VIEWING A BINARY DOCUMENT FILETo view the actual binary file, while viewing the Document List, the view link can be clicked on. The queried document is then displayed within the client’s browser in its original uploaded file content type (Figure 26).

Figure 26(myDMS VIEW DOCUMENT hyperlink

ACTION CLASS VIEWDOCUMENTACTIONThis class is processed by the controller and gets and sets file specific information, including the file name and the file content type as request attributes. These attributes are used by the ViewDocumentServlet to display the file within the client’s browser (Figure 27).

Figure 27(ACTION CLASS --ViewDocumentAction)

DOCUMENTDAO METHOD VIEWDOCUMENTThis method retrieves the BLOB from the database, using the view_document PL/SQL procedure and then uses Java streaming to display the file in the client’s browser. After the length of the file is determined, the java.io.InputStream object is used to read the file into the stream and the java.io.OutputStream is used to write the file to the screen, using the ViewDocumentServlet (Figure 28).

19 Paper #604


Figure 28(DOCUMENTDAO – viewDocument Method)

PKG_DMS METHOD VIEW_DOCUMENTThis procedure returns a ref cursor as an out parameter back to the viewDocument method in DocumentDAO. DocumentDAO then processes the BLOB data using Java streaming (Figure 29).

Figure 29(PKG_DMS – view_document Procedure)

VIEW VIEWDOCUMENTSERVLETThis view requests and then receives the BLOB data and then displays the binary file to the client’s browser, in the designated content type (Figure 30).

20 Paper #604


Figure 30(VIEW – ViewDocumentServlet)

DISPLAYING THE UPLOAD FORMTo begin the file upload process, the upload new document link can be clicked. The upload form is then displayed (Figure 31).

Figure 31(myDMS – UPLOAD NEW DOCUMENT Hyperlink

ACTION CLASS FORMUPLOADDOCUMENTACTIONThis class is processed by the controller and executes the viewTopicList method within TopicDAO. This method returns a list of Topics that are displayed within the Topic dropdown list on the resulting JSP view (upload form) (Figure 32).

Figure 32(ACTION CLASS --FormUploadDocumentAction)

DOCUMENTDAO METHOD VIEWTOPICLISTThis method retrieves a ref cursor from the database by calling the view_topic_list PL/SQL procedure and then sets the TopicVO java bean as a request attribute so that it can be referenced by the resulting JSP view for display on the form as a drop down list (Figure 33).

Figure 33(DOCUMENTDAO – viewTopicList Method)

21 Paper #604


PKG_DMS METHOD VIEW_TOPIC_LISTThis procedure returns a ref cursor as an out parameter back to the viewDocument method in DocumentDAO., which then sets the data in the TopicVO object, using the setter methods. The resulting JSP can then retrieve the data from the TopicVO object, for display, using the getter methods (Figure 34).

Figure 34(PKG_DMS – view_topic_list Procedure)

JSP VIEW FORMUPLOADDOCUMENT.JSPThis view retrieves the topic list from the VO, which was set as a request attribute by the DAO method, and then iterates through the list to display the topic dropdown list with the rest of the form. You will notice that the enctype within the form tags is set to multipart/form-data. This is because when the form is submitted, we will be submitting a file, in addition to regular form text boxes and dropdown fields (Figure 35).

22 Paper #604


Figure 35(VIEW – formUploadDocument.jsp)

SAVING THE UPLOAD FORMOnce the upload document form is filled out, it can then be submitted by clicking the submit now button. This submits the upload to the database (Figure 36).

Figure 36(myDMS – SUBMITTING UPLOAD FORM)

ACTION CLASS SAVEUPLOADDOCUMENTACTIONThis class is processed by the controller and executes the uploadDocument method within DocumentDAO. The FileItem, DiskFileUpload and FileUpload objects within the org.apache.commons.fileupload API are used to process the incoming html form with the attached file and the text and dropdown form fields. The maximum upload size is set to 50mb, using a context parameter set in the web.xml file. An org.apache.commons.fileupload.FileItem object houses the submitted file and an input stream is then created. The file name, file size and content type are then retrieved via methods available through the FileItem object and are set as variables, along with the input stream and the non-file submitted form fields. These designated variables are then submitted to the uploadDocument method in DocumentDAO. (Figure 37).

23 Paper #604


Figure 37(ACTION CLASS --SaveUploadDocumentAction)

DOCUMENTDAO METHOD UPLOADDOCUMENTThis method accepts all of the action submitted file and non-file variables. It then calls the upload_document PL/SQL procedure. An updated file list and the newly inserted BLOB locator are returned back to this method. An output stream is then opened for the returned BLOB locator using the java.io.OutputStream object and the chunk, buffer and length are set. The input stream then reads the binary file from the buffer and the file is uploaded to the database. In this example, since Weblogic 8.1 is being used, the recommended casting of weblogic.jdbc.vendor.oracle.OracleThinBlob is executed in order for the streaming to work with the related JDBC driver. This casting may be different depending on the Java server and the JDBC driver that you are using (Figure 38).

24 Paper #604


Figure 38(DOCUMENTDAO – uploadDocument Method)

PKG_DMS METHOD UPLOAD_DOCUMENTThis procedure first inserts a record into the DOCUMENTS table, with an empty BLOB (BLOB locator). Next it inserts a record into the DOCUMENTDETAILS table, which includes all of the descriptive information about the binary file that is being uploaded. The procedure then returns a ref cursor with an updated document list as well as the BLOB locator, which is used by the DAO method to finish the process of uploading the binary data into the database (Figure 39).

25 Paper #604


Figure 39(PKG_DMS – upload_document Procedure)

JSP VIEW VIEWDOCUMENTLIST.JSPThis view displays the updated document list to the client’s browser, which was set as a request attribute by the DAO method (Figure 40).

26 Paper #604



DELETING AN UPLOADED DOCUMENTDeleting an uploaded document is accomplished by clicking the delete hyperlink, while viewing a document listing. Once the delete hyperlink is clicked, the related database record is permanently removed from the DOCUMENTDETAILS table and then from the DOCUMENTS table (Figure 41).

Figure 41(myDMS DELETE Hyperlink)

ACTION CLASS SAVEDELETEDOCUMENTACTIONThis class is processed by the controller and executes the deleteDocument method within DocumentDAO to delete the related document from the database and to then display an updated document list to the client’s browser. (Figure 42)

Figure 42(ACTION CLASS --SaveDeleteDocumentAction)

DOCUMENTDAO METHOD DELETEDOCUMENT This method executes the delete_document PL/SQL procedure and receives a ref cursor back which is used to set the DocumentVO object with the newly updated document list, which is then displayed on the resulting JSP view. (Figure 43)

27 Paper #604


Figure 43(DOCUMENTDAO – deleteDocument Method)

PKG_DMS METHOD DELETE_DOCUMENTThis procedure deletes the related record from the DOCUMENTDETAILS and DOCUMENTS tables and then returns a ref cursor as an out parameter back to the deleteDocument method in DocumentDAO. DocumentDAO then uses the DocumentVO object to set the newly updated document list as a request attribute, which is then displayed on the resulting JSP view (Figure 44).

Figure 44(PKG_DMS – delete_document Procedure)

JSP VIEW VIEWDOCUMENTLIST.JSPThis view displays the updated document list, which was set as a request attribute by the DAO method (Figure 45).

28 Paper #604



WEB APPLICATION DEPLOYMENT FOR TESTING There are 4 main steps to deploying myDMS in your test environment.

1.) Create a JDBC Connection Pool on your Java server, using the MYDMS_USER as the database user. This procedure varies depending on the Java server that you use.

2.) Create a JNDI Datasource on your Java server, pointing to the Connection Pool set up in step 1.). This procedure varies depending on the Java server that you use.

3.) Reference the JNDI Datasource Name that was set up in step 2.) in the context parameter called "dataSource" within the web.xml file.

4.) Deploy the mydms folder on your Java server as a web application. This procedure varies depending on the Java server that you use.

SUGGESTIONS Be Consistent – When naming tables, PL/SQL packages, procedures, java classes, be

consistent with your naming convention throughout the application.

Take Your Environment into Account – Modify your application and database for your environment. No one setting, size, JDBC driver, etc. will work for every environment, no one knows your environment like you..

Test everything as much as possible. The weirdest things may pop up, just when you think everything is working!

WHERE TO GET THE MYDMS SCRIPTS, JAVA FILES AND WEB ARCHIVE The complete zip file with all of the myDMS scripts, java classes, etc. can be downloaded at https://eclouddesk.com/Client/Document/DownloadFile?uploadID=95

MY CONTACT INFORMATIONMary Wagner, [email protected]. Please contact me if you have any questions or encounter any problems with the download or its contents.

29 Paper #604

mailto:[email protected]

https://eclouddesk.com/Client/Document/DownloadFile?uploadID=95


ADDITIONAL RESOURCES Oracle 9i Application Developer’s Guide – Large Objects (LOBs) Oracle 9i JDBC Developer’s Guide and Reference – Working with LOBs and BFILEs

(Chapter 8) Oracle 9i Supplied PL/SQL Packages and Types Reference – DBMS_LOB (Chapter 23) http://asktom.oracle.com http://java.sun.com http://jakarta.apache.org (tomcat information and downloads) http://struts.apache.org (struts information and downloads) http://e-docs.bea.com/wls/docs81/jdbc/ http://www.jetbrains.com (intellij idea) Expert Oracle One on One – Tom Kyte Effective Oracle By Design – Tom Kyte Mastering Oracle PL/SQL – Connor MacDonald Sun Certified Web Component Developer for J2EE – Sam Dalton, Dan Jepp Professional Java Servlets 2.3 – John Bell, et al

30 Paper #604

2005_604_wagner_ppr

Documents