Written by Chris Schalk, Oracle Corporation
February 2004
Introduction
You may be surprised to find out that JDeveloper 10g
actually contains the widely popular
JavaServer Pages Standard Tag Library (JSTL). This means that it is possible
to use JSTL directly out of the box within JDeveloper without requiring you
to download JSTL separately and install in your environment. JDeveloper 10g's
Visual environment also makes working with JSTL a snap.
The following How To shows how to work with the JSTL SQL tags in JDeveloper
10g's visual development environment.
Basic JSTL SQL in JDeveloper
To get started, first make sure to download JDeveloper
10g Preview from OTN. You'll also need access to a database. An Oracle database
will be used in this example, but you could use any SQL92 compliant database
with JDeveloper.
In JDeveloper, create a new Application Workspace. File->New->General->Workspaces->Application
Workspace.
You can name your application: "jstl_sql".
The package prefix can also be "jstl_sql" - (not required)
Set the "Application Template" as "Custom Application [All
Technologies]". This will generate a single project (model), which is
all we need since we will not be creating an MCV based app for this example.
Once your workspace and starter project is generated, create a new JSP.
File->New->Web-Tier->JavaServer Pages->JSP Page.
You can name it "jstl_fun.jsp".
Once Created, feel free to add a banner to the top of the page like "JSTL
SQL FUN". Hint: use the Toolbar to format the text to <h2>.
On the right side, you'll want to select "JSTL SQL" in the Component
Palette. (See red arrow below.)
Now we can start dragging and dropping JSTL SQL tags onto the JSP page..
With JSTL SQL, you can define a new page based "dataSource" by
dragging a "SetDataSource" tag onto your page. (Red
Arrow)
Once it appears on the page, you'll be able to edit it's attributes in the
Property Inspector in the lower right corner.(Blue
Arrow)
When you click "dataSource" in the Property Inspector, you'll
notice that you'll be able to click on a button to either define a new connection
or use an existing connection. Note: JDeveloper manages all of the database
connections centralized "Connection Manager".
If you need to define a new connection, click "New..". in the
dataSource dialog.
Once the database connection information is defined you will need to set
the "var" attribute to "empds". This is the variable name
associated with the datasource:
(Note: By setting the dataSource, we will have already set the user,password,
url etc. As an option, you could set these individually.)
It should be pointed out that defining a dataSource directly in your page
should only be used for prototyping or small non-secure applications.
For production applications you would of course hide the connection information.
This can be done by defining a context parameter in your web.xml. This reference
could be to the JDBC url or to an existing container managed JNDI datasource.
Configuring a JNDI datasource is container specific and beyond the scope of
this article, but referencing the dataSource in your web.xml is easily done
in JDeveloper as well. Here's how:
Open up the web.xml "Settings..." dialog.
Then enter a context parameter for javax.servlet.jsp.jstl.sql.dataSource.
In the value: field your can either enter a JDBC URL or a JNDI datasource
defined in the container. For this example, I'll just use JDBC.
Now that we've covered how to access our our dataSource, let's create a
query. To do this we'll drop a Query tag onto our page. In the property Inspector
set the following attributes: var: emp dataSource: ${empds}
In the body of the query tag which is depicted as a small white rectangle
on the right side of the tag, enter a simple query: "select * from emp".
(Note: Do not include the semicolon ; )
(Note: Tag containership is displayed in the visual editor with a solid rectangle
.)
Now that we've defined our data, we can use JSTL Core tags to display the
data. For this example we'll use the JSTL Core: "Foreach" and "Out"
tags. Change the Component Palette to display the "JSTL Core" tags.
Drag and drop a "Foreach" tag onto the page. Set the attributes
as follows: items: ${emp.columnNames} var: columnName
Inside of the Foreach tag, insert an "Out" tag and set as follows: value: ${columnName}
At this point you should be able to test this out. Run the JSP to see a
list of columns. (Right-click on your JSP in the navigator and select "Run
jstl_fun.jsp")
Now let's add some more code to display the entire contents of the database
table using JSTL and HTML. For this you can copy and past the following code
into the sourcecode of your JSP. To access the source code view of
your JSP, click on the "Code" tab at the bottom ot the page.
Now copy and paste the following: (Note: this can replace your existing
Foreach statement.)
<table border="1" >
<%-- Get the column names for the header of the table --%>
<c:forEach var="columnName" items="${emp.columnNames}">
<th><c:out value="${columnName}"/></th>
</c:forEach>
<%-- Get the value of each column while iterating over rows --%>
<c:forEach var="row" items="${emp.rowsByIndex}">
<tr>
<c:forEach var="column" items="${row}">
<td><c:out value="${column}"/></td>
</c:forEach>
</tr>
</c:forEach>
</table>
Once you've pasted into the source, click on the "Design" tab
to see how it renders in the visual editor.
Save and re-run the JSP. You should only have to click refresh on your browser
to see the results:
More examples of JSTL SQL in JDeveloper
Now that we've stepped through how to query and display data, we can experiment
with more advanced transactions. Fortunately a tutorial on OTN already exists
on how to do this. Check out the original JSTL
SQL sample from OTN.
In order to try out the more advanced code in this sample, first create
a new JSP.
Now copy and paste the sample
code from the older JSTL SQL sample. You'll have to customize the datasource
attributes before you run it. Also your user account will need to have Create
privileges.
The page should render in JDeveloper as:
Right-click and run your new JSP to see it in action!
Summary
As you can see, database development with JSP and JSTL SQL are greatly simplified
in JDeveloper 10g's visual development environment. For further info on JSTL
see:
Bookmark
