|The Java Explorer||Back | TOC | Next|
Overview | Packages | Class Internals | Collections | I-O | Network | Database
The Java database API is modeled as a Facade design pattern, which abstracts away or hides the details of network and database connections, and provides a simple interface to manage a complex entity.
java.sql package contains the classes and interfaces that make up the database API. This package has been around since the first version of Java, but has been augmented considerably in later versions with the inclusion of javax.sql package.
The java database API is a set of interfaces to connect to databases and manipulate the data in the tables as rows of record sets. The core API has implemented these interfaces for the universal ODBC database connectivity. So it is possible to connect to any ODBC-compliant database directly from java code. This ODBC-connectivity comes in the form of a third-party implementation that Java provides as part of its core java database API, fondly called JDBC.
Many third-party implementations exist to connect to databases from different vendors like Oracle, MySQL, and so on. These implementations provide a direct connectivity to their respective databases, and avoid the ODBC-bridge connectivity, thus saving in time and network bandwidth.
To connect to a databse with JDBC, you just follow the steps below. Listing 1 pulls it all together.
Connect to a database
Create a SQL statement
Execute the statement
Retrieve the results
Connecting to a database
Establishing connection to a database is the first step. You connect to the database using a JDBC driver. For the purposes of this example we will use a ODBC-compliant database like MS Access, and use the existing JDBC-ODBC bridge.
Connection involves the loading of the JDBC driver, an implementation of the Driver interface in the java.sql package. Loading the default JDBC-ODBC bridge driver is a simple affair:
Class.forName loads an instance of the Driver class. The driver enables connection to the DBMS. The next step is to make the connection. This is how you do it:
Connection con = DriverManager.getConnection(url, "login", "password");
An instance of the Connection class is obtained from the getConnection method of the DriverManager class. This methods accepts a JDBC URL and optionally DBMS access details. It is one of several overloaded methods. One overloaded version also accepts a Properties object in addition to the URL. The DriverManager class provides the essential services to manage JDBC drivers.
The JDBC specification provides a connection protocol that begins with jdbc: and so the JDBC URL in this case is jdbc:odbc:dsn. The dsn stands for the data source name that you use to connect to a DBMS such as MS Access. The driver that you loaded earlier uses this information to connect to the database.
The Connection object also allows you to query for information regarding the DBMS, called the database meta data.
Armed with the Connection object, we can now go to the next step.
Creating the Statement
The Statement interface refers to the SQL statement or query that you use to manipulate the data in a table. You construct a Statement object from the Connection instance you obtained earlier.
Statement st = con.createStatement();
This line of code only prepares the ground for passing the SQL statement to the DBMS.
Executing the statement
The statement actually passes to the DBMS when you execute it. This is how you do it.
st.executeQuery(sql); where sql is the String that contains your SQL statement. This method returns with a ResultSet object which we shall see under the section 'retrieving the results'.
There is another method in the Statement interface, executeUpdate, which is used to execute DDL statements such as inserting data into a table. This method returns with the number of rows affected as a result of this operation.
Retrieving the results
You fetch the results of your query from the ResultSet object as shown below.
ResultSet rs = st.executeQuery(sql);
ResultSet provides methods to loop over the database records row by row. You may also query the table meta data with this object for information such as number of columns and column type, called the result set meta data.
We will see an example that covers the JDBC basics in one listing.
to be continued...