How do I access a database from my servlet or JSP?

Alex Chaffee

[See also How can I pool my database connections so I don't ...]

Since JDK 1.1, Java comes with a package called JDBC (Java Database Connectivity). JDBC allows you to write SQL queries as Java Strings, pass them to the database, and get back results that you can parse. To learn how to write JDBC code, check the tutorials on Sun's web site, and read the Javadoc API documentation for package java.sql. To install JDBC on your system, you need to locate a JDBC Driver for your particular database and put it in your classpath. Fortunately, most databases these days ship with a 100% Pure Java driver (also known as a "Type IV" driver), including Oracle, Sybase, Informix, etc. Check the documentation for your database engine for installation instructions.

Since opening a connection to a database can take a relatively long time (upwards of 10 seconds or more), you probably don't want to create the connection in your doGet method. Instead, create the connection in the init() method and save it in an instance variable. Remember to close the connection in your destroy() method.

Alternately, you could use a Connection Pool, which opens several database connections at once, then doles them out to individual threads as needed. This solves a number of problems with the one-connection method outlined above (basically, it's better at dealing with multiple simultaneous requests and with transactions). A good free connection pool implementation is available at Java Exchange (http://www.javaexchange.com/) . Connection pools also ship with many popular application servers, including BEA WebLogic (http://weblogic.beasys.com/) (formerly Tengah). This product is also available separately as jdbcKona (http://www.weblogic.com/docs/classdocs/API_jdbc.html) More information on Connection Pooling is available at the JSP FAQ, Question 13 (http://www.esperanto.org.nz/jsp/jspfaq.html#q13) .

An interesting article on connection pools is at http://webdevelopersjournal.com/columns/connection_pool.html.


From the tomcat-user mailing list, here is some code for storing a JDBC connection in an instance variable:
From: "Koen Dejonghe" <koen_dejonghe@hotmail.com>
To: tomcat-user@jakarta.apache.org
Subject: Re: JDBC and permanent connections
Date: Fri, 19 May 2000 17:20:06 CEST
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

public final class YourClass extends HttpServlet {

Connection con = null;

public void init() throws ServletException {

  String url = getServletContext().getInitParameter("url");
  String uid = getServletContext().getInitParameter("uid");
  String pwd = getServletContext().getInitParameter("pwd");

  try {
    //Register the JDBC driver
    Class.forName("oracle.jdbc.driver.OracleDriver");
  } catch( Exception e ) {
    e.printStackTrace();
  }//end catch

  //Get a connection to the database
  try {
    con = DriverManager.getConnection(url, uid, pwd);
  } catch( Exception e ) {
    e.printStackTrace();
  }//end catch

}//end init()

public void destroy() {
  try {
    //Close the connection to the database
    con.close();
  } catch( Exception e ) {
    e.printStackTrace();
  }
}

public void doGet(HttpServletRequest req,
                  HttpServletResponse res)
throws ServletException, IOException{

  try {
       [...]
       Statement st = con.createStatement();
       [ more JDBC code ]
  }
  catch (SQLException e) {
	[ ... ]
  }
}
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

About | Sitemap | Contact