Bạn có thể dùng JDBC để truy xuất cơ sở dữ liệu. Với Access, cách đơn giản nhất là bạn dùng cầu nối JdbcOdbc để kết nối. Bạn có thể tham khảo nhiều sách tiếng Việt về vấn đề này hay đọc sơ ở đây:
////** from Java Servlet Programming Copyright © 2001 O'Reilly & Associates
9.2.2. Getting a Connection
The first step in using a JDBC driver to get a database connection involves loading the specific driver class into the application's JVM. This makes the driver available later, when we need it for opening the connection. An easy way to load the driver class is to use the Class.forName() method:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
When the driver is loaded into memory, it registers itself with the java.sql.DriverManager class as an available database driver.
The next step is to ask the DriverManager class to open a connection to a given database, where the database is specified by a specially formatted URL. The method used to open the connection is DriverManager.getConnection() . It returns a class that implements the java.sql.Connection interface:
Connection con =
DriverManager.getConnection("jdbc:odbc:somedb", "user", "passwd");
A JDBC URL identifies an individual database in a driver-specific manner. Different drivers may need different information in the URL to specify the host database. JDBC URLs usually begin with jdbc:subprotocol:subname. For example, the Oracle JDBC-Thin driver uses a URL of the form of jdbc:oracle:thin:@dbhost:port:sid; the JDBC-ODBC bridge uses jdbc:odbc:data- sourcename ;odbcoptions.
During the call to getConnection() , the DriverManager object asks each registered driver if it recognizes the URL. If a driver says yes, the driver manager uses that driver to create the Connection object. Here is a snippet of code a servlet might use to load its database driver with the JDBC-ODBC bridge and create an initial connection:
Connection con = null;
try {
// Load (and therefore register) the JDBC-ODBC Bridge
// Might throw a ClassNotFoundException
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Get a connection to the database
// Might throw an SQLException
con = DriverManager.getConnection("jdbc:odbc:somedb", "user", "passwd");
// The rest of the code goes here.
}
catch (ClassNotFoundException e) {
// Handle an error loading the driver
}
catch (SQLException e) {
// Handle an error getting the connection
}
finally {
// Close the Connection to release the database resources immediately.
try {
if (con != null) con.close();
}
catch (SQLException ignored) { }
}
9.2.3. Executing SQL Queries
To really use a database, we need to have some way to execute queries. The simplest way to execute a query is to use the java.sql.Statement class. Statement objects are never instantiated directly; instead, a program calls the createStatement() method of Connection to obtain a new Statement object:
Statement stmt = con.createStatement();
A query that returns data can be executed using the executeQuery() method of Statement. This method executes the statement and returns a java.sql.ResultSet that encapsulates the retrieved data:
ResultSet rs = stmt.executeQuery("SELECT * FROM CUSTOMERS");
You can think of a ResultSet object as a representation of the query result returned one row at a time. You use the next()method of ResultSet to move from row to row. The ResultSet interface also boasts a multitude of methods designed for retrieving data from the current row. The getString()and getObject()methods are among the most frequently used for retrieving column values:
while(rs.next()) {
String event = rs.getString("event");
Object count = (Integer) rs.getObject("count");
}
You should know that the ResultSet is linked to its parent Statement. Therefore, if a Statement is closed or used to execute another query, any related ResultSet objects are closed automatically.
Example 9-1 shows a very simple servlet that uses the Oracle JDBC driver to perform a simple query, printing names and phone numbers for all employees listed in a database table. We assume that the database contains a table named EMPLOYEES, with at least two fields, NAME and PHONE.
Example 9-1. A JDBC-enabled servlet
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class DBPhoneLookup extends HttpServlet {
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
res.setContentType("text/html");
PrintWriter out = res.getWriter();
try {
// Load (and therefore register) the Oracle Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// Get a Connection to the database
con = DriverManager.getConnection(
"jdbc:oracle:thin:@dbhost:1528:ORCL", "user", "passwd");
// Create a Statement object
stmt = con.createStatement();
// Execute an SQL query, get a ResultSet
rs = stmt.executeQuery("SELECT NAME, PHONE FROM EMPLOYEES");
// Display the result set as a list
out.println("<HTML><HEAD><TITLE>Phonebook</TITLE></HEAD>");
out.println("<BODY>");
out.println("<UL>");
while(rs.next()) {
out.println("<LI>" + rs.getString("name") + " " + rs.getString("phone"));
}
out.println("</UL>");
out.println("</BODY></HTML>");
}
catch(ClassNotFoundException e) {
out.println("Couldn't load database driver: " + e.getMessage());
}
catch(SQLException e) {
out.println("SQLException caught: " + e.getMessage());
}
finally {
// Always close the database connection.
try {
if (con != null) con.close();
}
catch (SQLException ignored) { }
}
}
}
This is about as simple a database servlet as you are likely to see. All DBPhoneLookup does is connect to the database, run a query that retrieves the names and phone numbers of everyone in the employees table, and display the list to the user.
9.2.4. Handling SQL Exceptions
DBPhoneLookup encloses most of its code in a try/catch block. This block catches two exceptions: ClassNotFoundException and SQLException . The former is thrown by the Class.forName() method when the JDBC driver class can not be loaded. The latter is thrown by any JDBC method that has a problem. SQLException objects are just like any other exception type, with the additional feature that they can chain. The SQLException class defines an extra method, getNextException(), that allows the exception to encapsulate additional Exception objects. We didn't bother with this feature in the previous example, but here's how to use it:
catch (SQLException e) {
out.println(e.getMessage());
while((e = e.getNextException()) != null) {
out.println(e.getMessage());
}
}
This code displays the message from the first exception and then loops through all the remaining exceptions, outputting the error message associated with each one. In practice, the first exception will generally include the most relevant information.