Monday Apr 04, 2005

Servlet Connection Pooling for Dummies

In a previous entry ("Compiling JSP Files") in this blog I mentioned the cool NetBeans IDE feature which allows you to see the JSP file's translated servlet. Thanks to this feature, you're able to very quickly access a connection pool from a servlet without writing a single line of Java code, because once you've created the JSP file (below) and deployed it, all you've got to do is view the translated servlet, copy the code into a new servlet file, and then deploy it as you normally would (with one additional step, as explained in step 5 below). Accessing a connection pool from a servlet is a more natural scenario than doing so from a JSP file anyway (since you're dealing with data) and so the fact that you're able to see the translated servlet is a pretty helpful facility.
  1. After deploying, as described in the previous blog entry ("Super Cool Connection Pool"), right-click the index.jsp file and choose View Servlet.
  2. Select everything in the file and copy it.
  3. Create a servlet named index_jsp and put it in a package called org.apache.jsp. (Right-click the project node, choose New > Servlet.)
  4. Paste the code you copied over all the default code in the template.
  5. Right-click the Libraries node, choose Add Library, click Manage Libraries, click New Library, name it JASPER, click Add JAR/Folder, browse to enterprise1\\jakarta-tomcat-5.5.7\\common\\lib and select jasper-runtime.jar. Click OK.

Now deploy the servlet and you'll get the same result as below.

NB: There's one disadvantage with this and that is that the servlet is translated from the JSP file by the server. Therefore, the servlet created by the Sun Java System Application Server may not work with the Tomcat Web Server. Similarly, a servlet created by one version of a server may not work with another. Portability...

Update 04/25/05: Finding it hard to establish a connection from your Java source file to your database? Check out this blog entry: NetBeans IDE 4.1: No More Worrying About Database Connections.

Super Cool Connection Pool

While working on the 4.0 documentation some months ago, I put together some documentation on connection pooling with NetBeans IDE 4.0, Tomcat, and MySQL (click here for details). It was a fun little area to work on and it was cool to see how, thanks to the integration of Tomcat's Admin tool with the IDE's Runtime window, everything can be set up very very quickly. So, this morning, 10 minutes ago, I thought to myself: "Let's see if it's just as easy with NetBeans IDE 4.1, the Sun Java System Application Server, and PointBase." (After all, a disadvantage with using MySQL is that you've got to download everything first, while PointBase is -- currently, anyway -- simply bundled with the IDE. Also, PointBase includes two sample databases, one of which is used below.)

So, that was 10 minutes ago. It really went amazingly quickly -- and I didn't have to type any code in anywhere. Everything, from start to finish, was done by using the mouse to select menu items. I'm going to do it again now, and start completely from scratch, by removing my user directory. After that, these are the only steps needed:

  1. Register the Sun Java System Application Server. (Go to Tools > Server Manager.)
  2. Start PointBase database. (Go to Tools > PointBase database > Start Local PointBase database.)
  3. Go to Runtime window, expand Databases node, right-click jdbc:pointbase://localhost:9092/sample node, click connect.
  4. Expand the Tables node, right-click the CUSTOMER_TBL node, choose View Data. (We'll display the NAME and CITY fields in a JSP page.)
  5. Create a new web application. (Go to File > New Project and use all the defaults.)
  6. Right-click the project node, choose New > File/Folder > Sun Resources > JDBC Resource.
  7. Click Next, click "Create New JDBC Connection Pool", click Next and Next again.
  8. Click "Extract from Existing Connection" > select jdbc:pointbase://localhost:9092/sample > click Next and then Finish.
  9. Start the Sun Java System Application Server 8.1. (Go to Runtime window, expand the Servers node, right-click the server.)
  10. Go back to the Projects window, expand the Server Resources node, right-click the "datasource" and "connectionPool" nodes separately and choose Register for each.
That's it. And now put some code in a JSP page (such as the index.jsp page that's created for you when you create the project) to access the JDBC resource:

  1. At the top of the page:
        <%@ taglib prefix="c" uri=""%>
        <%@ taglib prefix="sql" uri=""%>
    Then, right-click the Libraries node, choose Add Library, select JSTL 1.1, click Add Library.

  2. Above the <html> tag:
       <sql:setDataSource var="datasource"
    	url="jdbc:pointbase://localhost:9092/sample" driver="com.pointbase.jdbc.jdbcDataSource"
    	user="pbpublic" password="pbpublic"/>
       <sql:query var="queryresults" dataSource="${datasource}">
  3. In the body of the JSP page:
        <table border=1>
          <c:forEach var="row" items="${queryresults.rows}">
    	     <td><c:out value="${row.NAME}" /></td>
    	     <td><c:out value="${row.CITY}" /></td>
  4. Now right-click the project node in the Projects window, choose Run Project, et voila:


Geertjan Wielenga (@geertjanw) is a Principal Product Manager in the Oracle Developer Tools group living & working in Amsterdam. He is a Java technology enthusiast, evangelist, trainer, speaker, and writer. He blogs here daily.

The focus of this blog is mostly on NetBeans (a development tool primarily for Java programmers), with an occasional reference to NetBeans, and sometimes diverging to topics relating to NetBeans. And then there are days when NetBeans is mentioned, just for a change.


« April 2005 »