JSP Tutorial - Database Access using a Bean

We're going to get information from the Scott/Tiger schema, which is a simple demo schema provided by Oracle. It contains a couple of tables EMP, containing a list of employees, and DEPT, a list of departments. Each employee belongs to a department.

Lets create our employee list.

Now in our first step we saw how you can add java code directly into a jsp page. We could add all the java code we need into our demo.jsp page. It would work, but it would be big and unwieldy, and if we wished to include the same information on several pages we would have to copy and paste it around.

Not a problem? What if you need to add a new column, or worse, a column is renamed or dropped? You have to find and update every page where your code is used. Let's do it the lazy way so you only have to change it once.

We're going to create a Java class to do the work and just call that whenever we need it. A java class included in a jsp page is referred to as a java bean. It is actually very simple.

There's something we need to do first. We'll need to adjust the project properties so that the class ends up in the right place. While we're there we can make a couple of other changes as well.

Right-click on the Demo project in the navigator view and choose project properties.

Change the default package to "uk.co.patrickhaston.demo" (you can use your own url if you prefer). The reverse order is a convention. The idea is that each package is unique, and using urls is one way of ensuring this.

Click on the J2EE section and change both the J2EE web application name and J2EE web context root to "demo". That makes the url neater. In the Development / Libraries section scroll down until you find "Oracle JDBC" and push it across to the right. We'll need that soon. Click Ok.

Now let's create our class. Choose File / New and click on the General section heading. You should see the option to create a Java Class. You can also find this under the Simple Files section.

Lets call our class "Emp" and accept the defaults. You should have something like this:

package uk.co.patrickhaston.demo;

public class Emp 
{
  public Emp()
  {
  }
}

You may have a different layout of brackets and indents, but I find that aligning the opening a closing brackets makes the code easier to read and debug.

Our class is going to access the database. I've found it easier to put the database connection information in a class of it's own so I can easily point my code at different databases.

Create another class called "Database" and add the following lines:

package uk.co.patrickhaston.demo;

public class Database 
{
  private String url = "jdbc:oracle:thin:localhost:1521:xe";
  private String user = "scott";
  private String password = "tiger";
  
  public Database()
  {
  }


  public String getUrl()
  {
    return url;
  }


  public String getUser()
  {
    return user;
  }


  public String getPassword()
  {
    return password;
  }
  
}

Compile your code to check for typos.

Now lets get back to our Emp class. We're going to add a new method to our class, and we'll call it "showData". Because we want to be able to filter our list of employees by department, we'll add a parameter to the method. Although deptno is a number in the database, I find it easier to stick with String data types in the Java classes. Here's the whole Emp class:

package uk.co.patrickhaston.demo;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import oracle.jdbc.pool.OracleDataSource;

public class Emp 
{
  private Database db = new Database();
  
  public Emp()
  {
  }

  public String showData(String p_deptno)
  {
    String html = "";
    String SQL = "";
    
    // Check to see if a department has been specified.
    // If not, we want to see all departments
    if( p_deptno == null || p_deptno.length() == 0 ) p_deptno = "'%'";
    
    try
    {
      OracleDataSource ods = new OracleDataSource();
      ods.setURL( db.getUrl() );
      ods.setUser( db.getUser() );
      ods.setPassword( db.getPassword() );
      
      Connection connection = ods.getConnection();
      
      Statement statement = connection.createStatement();
      
      SQL = "select empno, ename, job, mgr, hiredate, sal, comm, deptno "
          + "from scott.emp "
          + "where deptno like " + p_deptno;

      ResultSet rs = statement.executeQuery(SQL);
      
      html = "<table>\n"
        + "<tr><th>Emp No</th><th>Employee Name</th>
        + "<th>Job</th><th>Mgr No</th>"
        + "<th>Hire Date</th><th>Salary</th>
        + "<th>Commission</th><th>Dept No</th></tr>\n";
      
      while( rs.next() )
      {
        html = html + "<tr><td>" + rs.getString("empno") + "</td>"
          + "<td>" + rs.getString("ename") + "</td>"
          + "<td>" + rs.getString("job") + "</td>"
          + "<td>" + rs.getString("mgr") + "</td>"
          + "<td>" + rs.getString("hiredate") + "</td>"
          + "<td>" + rs.getString("sal") + "</td>"
          + "<td>" + rs.getString("comm") + "</td>"
          + "<td>" + rs.getString("deptno") + "</td></tr>\n";
      }
      
      html = html + "</table>\n";
    }
    catch (Exception e)
    {
      html = html + "<br>Error: " + e.getMessage() + "<br>SQL: " + SQL;
    }
    
    return html;
  }
}

There are four include statements at the top. That's because of the database calls we need to make.

We've added a reference to our new Database class. It is created when the class is created.

The showData() method returns a String. This will be html code that we can simply display in the jsp page.

We then check the parameter that has been passed in. Here's where a String comes in handy: we can change it to '%' if it's null. Java and sql handle null values differently, java will convert a null string to "null" for you, so you can never use nvl( ... ).

Database calls have to be contained within a try{ } catch { } block, because anything can go wrong (the server could be down, etc.) and the code needs to know what to do if it encounters an error. We'll catch it and return the error message to the browser. In a live application you may wish to do something more elegant, but for debugging it's good to see the real message.

We create a reference to our database using the OracleDataSource class. We get the connection information from our Database class and establish a connection. We create a statement and define the sql.

We then create a recordset from the result of executing the query. Using a while( rs.next() ) test we can loop through the results of the query.

I've appended the results of the query into a table. There are times where it's still good practice to use a table, and this is one of them.

We finish up by returning the html to the calling application.

Compile it to check for bugs.

Let's add it to our page.

Open up the demo.jsp page and add in some lines above the head opening tag. We're going to add a reference to our java class, so you're code should now look like this:

<%@ page contentType="text/html;charset=windows-1252"%>

<jsp:useBean 
  id="emp" 
  scope="request" 
  class="uk.co.patrickhaston.demo.Emp" 
/>


<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; 
		    charset=windows-1252">
    <title>Demo</title>
    <link href="css/demo.css" rel="stylesheet" media="screen">
  </head>
  <body>
    <div class="page_banner">
      <h1>Tutorial</h1>
    </div>
    <div>
      <%=emp.showData("")%>
    </div>
  </body>
</html>

Run it and see what happens. You should get a tabular list of all the employees in the EMP table.