John W Baker's Weblog

« NetBeans 6.0 Visual... | Main | Solutions to the... »

Tuesday Nov 13, 2007

Using CLOB datatypes with NetBeans Visual Web Java EE 5 projects

In this entry, I'll explain how to create a simple Visual Web Java EE 5 application that displays CLOB values from an Oracle database in a Table component. Here is the supported Oracle driver information for Visual Web

NetBeans Visual Web components support many types of converters to convert data types to String for displaying values in components (Table, StaticText, Dropdown List, ..).

There are however, these in-the-box converters do not support all types, such as database LOB fields (CLOB, BLOB (not blog), BFILE) . For these, a custom converter is required.

Here is a sample converter that converts a CLOB to a String. To use this converter with a JSF component, a bean property along with an accessor method must be created to instantiate the converter class*.

    private Converter myClob = new ClobConverter();

    public Converter getMyClob() {

        return myClob;

Next, in the JSP source where the component is defined, the converter is set using JSF Expression Language statement to retrieve the value from the property of the bean class, myClob.

 <webuijsf:staticText binding="#{Page1.staticText7}" converter="#{Page1.myClob}" id="staticText7" text="#{currentRow.value['CLOB_COL']}"/>

Next, converter must be registered in the faces-config.xml file:

 <faces-config version="1.2" 

    xmlns="http://java.sun.com/xml/ns/javaee" 

    xmlnssi="http://www.w3.org/2001/XMLSchema-instance" 

    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-facesconfig_1_2.xsd">


    <converter>

        <converter-id>ClobConverter</converter-id>

        <converter-class>

            webapplication8.ClobConverter

        </converter-class>

    </converter>

    <managed-bean>

        <managed-bean-name>SessionBean1</managed-bean-name>

        <managed-bean-class>webapplication8.SessionBean1</managed-bean-class>

        <managed-bean-scope>session</managed-bean-scope>

    </managed-bean>

    <managed-bean>

        <managed-bean-name>Page1</managed-bean-name>

        <managed-bean-class>webapplication8.Page1</managed-bean-class>

        <managed-bean-scope>request</managed-bean-scope>

    </managed-bean>


...


</faces-config>

Next setup the database. You can use the pre-registered Derby connection, travel on TRAVEL.

Select this connection, right-click and choose Connect... then again, right-click then choose Execute Command... and paste the following SQL into the SQL Editor, then click the smaller Green arrow in the toolbar of the SQL Editor. Note the SQL below is for Oracle. I can provide the SQL for other databases at the end of this blog entry.

CREATE TABLE clob_table (

    id NUMBER PRIMARY KEY, 

    text VARCHAR(30),

    clob_col CLOB

);


INSERT INTO clob_Table VALUES (0, 'RECORD 0', 'COMMENT FOR RECORD 0');

INSERT INTO clob_Table VALUES (1, 'RECORD 1', 'COMMENT FOR RECORD 1');

INSERT INTO clob_Table VALUES (2, 'RECORD 2', 'COMMENT FOR RECORD 2');

INSERT INTO clob_Table VALUES (3, 'RECORD 3', 'COMMENT FOR RECORD 3');

Create a new Web Application using the Visual JSF Framework. Then, from the Palette, drag and drop a Table component onto the Designer surface. Next, from the Services tab, expand the travel on TRAVEL connection and drag and drop a CLOB_TABLE onto the Table component (the outline of the component will turn blue)

In order to use this project, modify the setup/sun-resources.xml file for your environment.
Also add the Oracle JDBC driver to the project.

1) Create a new Library in NetBeans (Tools->Libraries) I created a new library named Oracle
add odbc14.jar

2) In the web application, right-click on the project node and select Properties then select Libraries
and add the Oracle library

Next, change from the Design view to the JSP source view and if you haven't already, add the converter option to the StaticText component that is bound to CLOB_COL

    <webuijsf:tableColumn binding="#{Page1.tableColumn6}" headerText="CLOB_COL" id="tableColumn6" sort="CLOB_COL">

       <webuijsf:staticText binding="#{Page1.staticText7}" converter="#{Page1.myClob}" id="staticText7" text="#{currentRow.value['CLOB_COL']}"/>

    </webuijsf:tableColumn>

And make sure the converter is registered in the faces-config as above.

Finally, you can execute the application. Your browser should render the Table component as shown in the screenshot. If the page is blank then in NetBeans, click on the the Output tab at the bottom and then the Glassfish tab. Scroll to the bottom to check the error.

I'll attach a sample project for reference.

Sample project that connects to Oracle.
To resolve project errors, edit the sun-resources.xml and replace the database settings with your Oracle settings.

Posted at 03:18PM Nov 13, 2007 by dashboy in Java | Comments[10] | Permalink

Comments:

Thanks.
but where is the converter source
and sample project?
(not very familiar with NetBeans)
usually CLOBs are used to store
images. probably a table column is
needed to store mime types
and converter needs to display
that.

Posted by 128.249.96.252 on November 14, 2007 at 02:46 AM PST #

Sorry I forgot to upload the project. It's available now. See the link at the bottom of this entry. Hmm... I usually use blobs to store images and clobs to store character data, such as files..

Next time, I'll post an example using blobs.

Posted by 192.18.43.225 on November 15, 2007 at 06:40 PM PST #

Hi,

I've got a problem with NB5.5.1 + Visual Web Pack and Oracle NCLOB data type.

When I try to insert data that's ok, no problem, but when I try to update rows then I receive an error

Number of conflicts while synchronizing: 1 SyncResolver.UPDATE_ROW_CONFLICT row 0 Niepoprawny typ kolumny (wrong column type) - I have tried to use String class, SerialClob class etc. Sometimes I do not receive any error, but data is not saved into database.

Second thing I would like you to ask is: can (and how) we may use database transactions with DataProviders ?

Best regards,

Posted by Matthew on January 22, 2008 at 07:47 AM PST #

Hmm… I don’t really know if the previous problem is so complicated or simply that library is worthless or there is no one interested in explaining how to avoid common pitfalls.
Frankly I am unable to find any interesting materials about it and solutions to common problems. I do believe it suffers from serious limitations and number of bugs.
If you consider using it, I would recommend rethinking that idea. Frankly, if I were to choose framework again, I would not choose that Visual Web Pack (which now is known as framework in NB6) and merely mimics Ms VS .NET. Idea is good, but the rest is far beyond counterpart from Ms (and to support my point of view I have 18 months of working experience with VS2k3 / VS2k5 and 6 months of trying to do the same in VWP).
Thanks for reading, have a good time…

Posted by Matthew on February 05, 2008 at 02:24 AM PST #

Finally, I had some time to update this blog.

For updating records, please see the tutorial - http://www.netbeans.org/kb/60/web/inserts-updates-deletes.html

This should still apply

Thanks for your patience

Posted by John Baker on October 01, 2008 at 11:51 PM PDT #

Dear John;
You said on november 2007, and I quote "Next time, I'll post an example using blobs".
I have been trying to find where this post is. Sorry please, but would you mind give me the link?
I really need to see how to deal with Blob.
Yours.

Posted by Christian Calmen on January 23, 2009 at 05:55 AM PST #

I am using SUN JAVA STUDIO CREATOR 2 and I am facing the following issue.
I ca't display database images on my web page.
I have set up a web application that can connect to Oracle DB 10.
When i select an employee from a drop down list or when i query the database to get an employee information, I want that employee
picture to be displayed, alongside other bimetric information.
At the moment, I can all other information except the picture. Could any one help?
I have already read loads of entries about the subject, but none of them work with Sun Creator.

Posted by Christian Calmen on January 23, 2009 at 06:05 AM PST #

I am using SUN JAVA STUDIO CREATOR 2 and I am facing the following issue.
I can't display database images on my web page.
I have set up a web application that can connect to Oracle DB 10.
When i select an employee from a drop down list or when i query the database to get an employee information, I want that employee
picture to be displayed, alongside other bimetric information.
At the moment, I can all other information except the picture. Could any one help?
I have already read loads of entries about the subject, but none of them work with Sun Creator.

Posted by Christian Calmen on January 23, 2009 at 06:23 AM PST #

Please see the example code in this blog.

The links are in the beginning of the 4th paragraph above:
"Here is a sample converter" links to
http://blogs.sun.com/dashboy/resource/ClobConverter.java

Note that for blobs to work, you'll need to program to the vendor's JDBC API.
For ClobConverter, I'm using Oracle's JDBC API.

Also, a link to the entire application is above, second line from the bottom.
Sample project that connects to Oracle.
http://blogs.sun.com/dashboy/resource/TestClob.zip

Hope this helps

Posted by John Baker on January 23, 2009 at 09:59 AM PST #

Can you write of how to insert a Blob object into a JavaDB using Visual Web Pack Components (An example is something like a picture file from a fileUpload component save into a database).