Development

Accessing the Database

When you're building a dynamic site, the code will typically require access to the database to pull information for the current user. You can expand our earlier example to obtain information about users from the database after they have entered their username and password. Listing 4.1 contains the code for the database servlet.

Listing 4.1: The database servlet.

 package example; import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; import javax.naming.*; import javax.sql.*; public class ViewAccount extends HttpServlet {   DataSource pool;     public void init()      throws ServletException {       try {         Context ic = new InitialContext();           Pool = (DataSource) ic.lookup("java:comp/env/jdbc/test");         if (pool == null)           throw new ServletException(             "jdbc/test is an unknown DataSource");       } catch (NamingException e) {           throw new ServletException(e);       }     }     public void doGet(HttpServletRequest request,                       HttpServletResponse response)       throws IOException, ServletException {       response.setContentType("text/html");       PrintWriter out = response.getWriter();       Connection conn = null;       try {         conn = pool.getConnection();         Statement statement = conn.createStatement();         ResultSet rs = statement.executeQuery(           "SELECT * FROM acc_acc WHERE username = " +           request.getParameter("username") +           " AND password = " +           request.getParameter("password"));         out.println("Full name: " + rs.getString("name"));         out.println("Address: " + rs.getString("address"));         out.println("City: " + rs.getString("city"));         out.println("State: " + rs.getString("state"));         conn.close();          } catch(SQLException e) {            out.println("There has been a database error");          } finally {            if (conn != null)              conn.close();          }   }   public void doPost(HttpServletRequest request,     HttpServletResponse response)     throws ServletException, IOException {     doGet(request, response);   } } 

For a comprehensive discussion on using databases with Resin, see Chapter 11, "Database Connectivity"; we use some of the information from that chapter in this example. Probably the most important part of the database servlet code in Listing 4.1 takes place in the init() method. The code within init() is responsible for creating the connection to the database. For this example, we assume a Java Naming and Directory Interface (JNDI) definition is included within the web.xml or resin.conf file with a key of jdbc/test.

The servlet doesn't really care about the underlying database as long as it successfully obtains a connection. Once the connection has been established, the code will use the username and password passed through the HttpServletRequest object from the HTML form.

The servlet pulls information about the user from a database table called acc_acc, returned in a ResultSet object and displayed to the user through the Response object. Clearly the output generated for users can be uniquely tailored to their needs and personal profile. If the server does not find the username and password in the database, it redirects the user to a registration page.