/*
 * Copyright (c) 1998-2001 Caucho Technology -- all rights reserved
 *
 * Caucho Technology permits redistribution, modification and use
 * of this file in source and binary form ("the Software") under the
 * Caucho Developer Source License ("the License").  The following
 * conditions must be met:
 *
 * 1. Each copy or derived work of the Software must preserve the copyright
 *    notice and this notice unmodified.
 *
 * 2. Redistributions of the Software in source or binary form must include 
 *    an unmodified copy of the License, normally in a plain ASCII text
 *
 * 3. The names "Resin" or "Caucho" are trademarks of Caucho Technology and
 *    may not be used to endorse products derived from this software.
 *    "Resin" or "Caucho" may not appear in the names of products derived
 *    from this software.
 *
 * This Software is provided "AS IS," without a warranty of any kind. 
 * ALL EXPRESS OR IMPLIED REPRESENTATIONS AND WARRANTIES, INCLUDING ANY
 * IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE
 * OR NON-INFRINGEMENT, ARE HEREBY EXCLUDED.
 *
 * CAUCHO TECHNOLOGY AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES
 * SUFFERED BY LICENSEE OR ANY THIRD PARTY AS A RESULT OF USING OR
 * DISTRIBUTING SOFTWARE. IN NO EVENT WILL CAUCHO OR ITS LICENSORS BE LIABLE
 * FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL,
 * CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND
 * REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR
 * INABILITY TO USE SOFTWARE, EVEN IF HE HAS BEEN ADVISED OF THE POSSIBILITY
 * OF SUCH DAMAGES.      
 */

package example.servlet.database;

import java.io.*;

import javax.servlet.*;
import javax.servlet.http.*;

// import the JNDI package
import javax.naming.*;

// import the JDBC (Java's SQL) package
import java.sql.*;

// import the JDBC 2.0 package for the DataSource support
import javax.sql.*;

/**
 * Example of a database query from a servlet.
 *
 * <p>Database access has two components: looking up the data source using
 * JNDI and using JDBC calls to retrieve the data.
 *
 * <p>The JNDI lookup finds the DataSource configured in the resin.conf
 * or web.xml.  Since the JNDI lookup only needs to happen once,
 * it's usually put in the init() method.  The database configuration looks
 * something like the following:
 *
 * <code><pre>
 * &lt;resource-ref>
 *   &lt;res-ref-name>jdbc/test</res-ref-name>
 *   &lt;res-type>javax.sql.DataSource</res-type>
 *   &lt;init-param driver-name="com.caucho.jdbc.mysql.Driver"/>
 *   &lt;init-param url="jdbc:mysql_caucho://localhost:3306/test"/>
 *   &lt;init-param max-connections="20"/>
 *   &lt;init-param max-idle-time="30"/>
 * &lt;/resource-ref>
 * </pre></code>
 *
 * Different databasees will have different values for driver-name
 * and url.  The max-connections and max-idle-time are configuration
 * values for Resin's database pooling.  The servlet doesn't need to
 * worry about database pooling; Resin will take care of it automatically.
 * (The APIs used in the example are all from the J2EE standard.)
 *
 * <p>Applications should always use the following pattern for all
 * database access.  The try ... finally pattern is vital to make
 * sure the connection is released to the pool.
 *
 * <code><pre>
 * Connection conn = null;
 * try {
 *   conn = dataSource.getConnection();
 *   ...
 * } finally {
 *   if (conn != null)
 *     conn.close();
 * }
 * </pre></code>
 */
public class QueryServlet extends HttpServlet {
  /**
   * Cached DataSource.   Typically, the servlet will lookup the
   * DataSource using JNDI in the init() method and save it in a
   * servlet variable.  Since DataSource is thread-safe, it can
   * be used safely in a servlet.
   */
  private DataSource dataSource;

  /**
   * Initialize the servlet, caching the JNDI lookup of the DataSource.
   */
  public void init()
    throws ServletException
  {
    try {
      // java:comp/env is the standard location for server objects
      
      // Since Resin's JNDI is specific to a web-app, each
      // virtual host and web-app will have its own JNDI directory.

      // Since most JNDI users will only need to use the following pattern,
      // it's safe to treat it as a cookbook pattern and only learn
      // more details of JNDI if absolutely necessary.
      Context env = (Context) new InitialContext().lookup("java:comp/env");

      dataSource = (DataSource) env.lookup("jdbc/test");
    } catch (NamingException e) {
      throw new ServletException(e);
    }
  }
  
  /**
   * Handles GET requests.  Resin will call the doGet method when
   * the browser sends a GET request.
   *
   * @param request the request object contains the data from
   * the browser's request.
   * @param response the response object contains methods to send
   * data back to the browser.
   */
  public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws IOException, ServletException
  {
    // Tell the browser that the data is HTML
    response.setContentType("text/html");

    PrintWriter out = response.getWriter();
    
    // The Connection object represents a connection to the database.
    Connection conn = null;

    // The following try ... finally block is vital to any use of JDBC
    // _always_ use this pattern with any JDBC connection.
    try {
      // The dataSource will automatically reuse any pooled connection
      conn = dataSource.getConnection();

      // Create a statement object
      Statement stmt = conn.createStatement();
      
      // Here's our query:
      String sql = "SELECT course, teacher FROM servlet_courses";

      // Execute the query
      ResultSet rs = stmt.executeQuery(sql);

      out.println("<table>");
      
      // JDBC clients must call rs.next() before retrieving the next row
      while (rs.next()) {
        // It's important to extract the columns in order.  Some drivers
        // will allow out-of-order values for small results, but require
        // in order values for large results.

        String course = rs.getString(1);
        String teacher = rs.getString(2);

        out.println("<tr><td>" + course + "<td>" + teacher);
      }

      out.println("</table>");

      rs.close();
      stmt.close();
    } catch (SQLException e) {
      throw new ServletException(e);
    } finally {
      // To re-emphasize: it's vital to put the conn.close() in a
      // try ... finally block for any use of JDBC
      try {
        if (conn != null)
          conn.close();
      } catch (SQLException e) {
      }
    }
  }
}

