Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Many developers have embraced JSP (Java Server Pages) as their Web application development model. JSP allows HTML to be used as the presentation layer and Java as the processing/programming layer. With support of JavaBeans, it also provides a framework for developing Web pages in terms of components instead of streams of HTML. I have seen a recent rise in the number of questions on the Earthweb Discussion Groups about database connectivity within the JSP model. This article will provide an overview of how database connections would work within JSP and provides some working examples.
Database connectivity seems to be an important piece in most Web applications. We are also seeing an increasing number of applications that treat a database as an abstract "data source"which may or may not be a relational database. Other viable data sources include XML documents, Web sites, and legacy/proprietary systems. Looking at JSP, it is easy to get engulfed with the intermixing of HTML and Java and wondering how databases would fit the model. It should be noted, however, that the JSP page consisting of Java and HTML will actually be translated into a Java servlet code, compiled and executed as a servlet. Therefore, the problem of database (data source in abstract terms) connectivity, boils down to how servlets utilize databases. Most of the issues such as connection pools, session objects to keep state, and request/response model of HTTP are well documented as servlets have matured.
What JSP brings to the table is a cleaner separation of presentation and code and its advocating of the JavaBeans framework. A typical Web page can be divided into distinct components (menu, ad banner, header, footer, etc.), each component can be represented by a Bean and arrangement of the content produced by various Beans on the page can be controlled by the familiar HTML. Since a Bean is an independent Java component, it may make database connections to retrieve or data. The most likely approach would be to use JDBC.
Inserting data
In the following section, we develop a JSP page that uses a Bean to insert some data into a database table. The Bean is called InsertBean and is shown in Listing 1.
Listing 1. InsertBean.java
package DB;import java.sql.*;import java.io.*;public class InsertBean { private String dbURL = “jdbc:odbc:names”; private String dbDriver = “sun.jdbc.odbc.JdbcOdbcDriver”; private Connection dbCon; String Name = null; String Address = null; String Zip = null; public InsertBean() { super(); } public String getName() { return this.Name; } public String getAddress() { return this.Address; } public String getZip() { return this.Zip; } public void setName(String tname) { this.Name = tname; } public void setAddress(String taddress) { this.Address = taddress; } public void setZip(String tzip) { this.Zip = tzip; } public void doInsert() throws ClassNotFoundException, SQLException { Class.forName(dbDriver); dbCon = DriverManager.getConnection(dbURL); Statement s = dbCon.createStatement(); String sql = “INSERT INTO names values (‘” + this.Name; sql = sql + “‘, ‘” + this.Address + “‘, ‘” + this.Zip; sql = sql + “‘)”; int insertResult = s.executeUpdate(sql); dbCon.close(); } } The Bean consists of three properties representing the three fields in the table (name, address, zip). Also, note that the actual JDBC calls are embedded inside the Bean, mostly in the doInsert() method. A more complete design would separate the details of data access from the Bean itself. To demonstrate usage of the Bean, we have two JSP pages. Insert.jsp (Listing 2) is basically an HTML form that allows the user to enter values for three fields: name, address, zip. In fact, it has no JSP components and could have been written as a regular HTML page as well.
Listing 2. insert.jsp
This is the Insert page.
The content of the form is submitted to insert2.jsp (listing 3). This page uses the InsertBean to do the database insertion. As you can see, from the perspective of the JSP programmer, no knowledge of the database or connecting to it is required. The JSP programmer, simply initializes the Bean with the values from the HTML form and then calls the doInsert() method of the Bean. For a real application, error handling should be done as you would do for any database application.
Listing 3. insert2.jsp
<%@ page language="java" %><% insertBean.setName(request.getParameter("tname")); insertBean.setAddress(request.getParameter("taddress")); insertBean.setZip(request.getParameter("tzip")); insertBean.doInsert(); %> You inserted the following record:
Name: <%= insertBean.getName() %>
Address: <%= insertBean.getAddress() %>
Zip: <%= insertBean.getZip() %>
Running the example
I used the JDK 1.2 to compile the Bean. For the JSP hosting, I used JSWDK from Sun (http://www.javasoft.com/jsp). You should consult my previous articles on JSP for details of how to set up and use JSWDK. There are specific directories that you need to create under the JSWDK directory structure. One of these directories will contains the Bean. The other directory will contain the JSP pages. I created the following directory to host the JSP pages (insert.jsp and insert2.jsp):
C:softwarejswdk-1.0-eaexamplesjspdbsample
and the following directory for the Bean:
C:softwarejswdk-1.0-eaexamplesWEB-INFjspbeansDB
After you place the files in the appropriate directories and start the Web server, you can access the HTML form via the following URL: (assuming a local browser)
http://localhost:8080/examples/jsp/dbsample/insert.jsp
Of course, you also need a database. I used MS Access and used the JDBC-ODBC driver inside the Bean to do the insertion. I created a simple table with three text fields (name, address, and zip). After you do the insertion, check your database table and make sure the data was indeed inserted.
Querying the database
As you may expect, the same model can be used to query the database. We have another Bean called QueryBean (listing 4) which performs a simple query on the table based on the "name" field. Since the lookup is limited to the "name" field, the Bean only has getter and setter methods for that field. Unlike the insert operation which returns a status value on success or failure of the insert, the query operation returns some data. Note how we wrap the data with HTML tags for a table. Granted, we should not be mixing presentation tags with the data, but I want to make another point. The data generated from any data source should be self describing and XML provides an excellent medium for that. The data could very easily have been wrapped using XML tags (instead of HTML) and thus the Bean would return an XML document. That document could then have been combined with an XSL stylesheet to produce the final presentation sent to the browser, WAP device, WebTV, etc.
Listing 4. QueryBean.java
package DB;import java.sql.*;import java.io.*;public class QueryBean { private String dbURL = “jdbc:odbc:names”; private String dbDriver = “sun.jdbc.odbc.JdbcOdbcDriver”; private Connection dbCon; String Name = null; public QueryBean() { super(); } public String getName() { return this.Name; } public void setName(String tname) { this.Name = tname; } public String doQuery() throws ClassNotFoundException, SQLException { Class.forName(dbDriver); dbCon = DriverManager.getConnection(dbURL); Statement s = dbCon.createStatement(); String sql = “SELECT * FROM names WHERE name like ‘” + this.Name + “%'”; ResultSet rs = s.executeQuery(sql); String rt = “”; while (rs.next()) { rt = rt + “
” + rs.getString(“name”) + “
“; rt = rt + “
” + rs.getString(“address”) + “
“; rt = rt + “
” + rs.getString(“zip”) + “
“; } dbCon.close(); return rt; } }
Again, we use two JSP pages. The first is query.jsp (listing 5) which provides a simple query field to the user. The content of that field is submitted to query2.jsp (listing 6).
Listing 5. query.jsp
This is the Query page.
Listing 6. query2.jsp
<%@ page language="java" %><% queryBean.setName(request.getParameter("tname")); %> The result of the query is:
| Name | Address | Zip |
|---|
Looking at query2.jsp, see how the HTML table tags are used to set up the table and the header row. Knowing that the Bean is going to return the "inside" of the table, we simply call the
doInsert() |
Conclusion
JSP offers an effective model for developing Web application using Java. In the article we have seen two examples of how a typical JSP page can insert and query data from a relational database. Although the details of JDBC calls can be embedded inside the JSP page, it is much cleaner to take that part of the code and wrap it in a JavaBean. JSP can then call the appropriate Beans. Given this model, we can easily divide a typical HTML page into distinct components and offer customization for each component. General HTML knowledge is used to put the page together using these components, but the details of each component are wrapped inside an independent JavaBean and could be managed via a central management Bean if needed.
About the Author
Piroz Mohseni is president of Bita Technologies, focusing on business improvement through effective usage of technology. His areas of interest include enterprise Java, XML, and ecommerce applications. Contact him at mohseni@bita-tech.com.
