How To Use JSTL SQL in JDeveloper 10g Preview

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.

  1. 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.
  2. 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".

  3. 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..

  4. 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)

  5. 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".

  6. If you need to define a new connection, click "New..". in the dataSource dialog.

  7. 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.)
  8. 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.
  9. 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}
  10. 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 .)
  11. 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.
  12. Drag and drop a "Foreach" tag onto the page. Set the attributes as follows:
    items: ${emp.columnNames}
    var: columnName
  13. Inside of the Foreach tag, insert an "Out" tag and set as follows:
    value: ${columnName}
  14. 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")
  15. 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 source code of your JSP. To access the source code view of your JSP, click on the " Code" tab at the bottom ot the page.
  16. 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>
  17. Once you've pasted into the source, click on the "Design" tab to see how it renders in the visual editor.

  18. 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

  1. 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.
  2. In order to try out the more advanced code in this sample, first create a new JSP.
  3. 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.
  4. The page should render in JDeveloper as:

  5. 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:

 

false ,,,,,,,,,,,,,,,