Database Programming With Java + JDBC: Part 1/2 - What is JDBC? (Page 2 of 6 )
The structured query language (SQL) is popularly used for database operations. You can execute SQL statements to perform 2 major types of operations:
1. DDL: Data definition language statements let you do jobs like creating a table.
2. DML: Data manipulation language statements let you do jobs like firing a select query.
JDBC is an API specification to provide access to the database. In order to use it, you need to choose an appropriate JDBC driver. The driver acts as a gateway between your Java program and the database.
Q. Do I have any choice for which JDBC driver to use? The different types of JDBC drivers are shown below:
1. Type 1: JDBC-ODBC bridge driver This provides access to the database using an ODBC (Open Database Connectivity) driver. This is popularly used in development environments when you have an ODBC driver for your database. A JDBC-ODBC driver comes with the standard JDK distribution.
2. Type 2: Native-API partly Java driver This provides access to the database by translating a JDBC call to a native database level API. Typically, it uses native C code to do this. This driver is not a good candidate for applets.
3. Type 3: JDBC-Net pure Java driver This provides access to the database by translating a JDBC call into a Net protocol, which is then translated by the server. Since this is a pure Java driver, it's a good candidate for use in applets.
4. Type 4: Native-protocol pure Java driver This provides access to the database by translating a JDBC call to a database specific call, such as Oracle Call Level Interface (CLI). This is commonly used for server-side Java programming (such as servlets).
Q. Hmmm, that sounds familiar. How's this different from ODBC? Well, the point is not in difference. As you can see from the types of drivers above, Java has leveraged to a great extent from existing APIs like ODBC (Open Database Connectivity), other legacy implementations and even vendor specific implementations. All that wrapped together in an easy to use API specification works like a charm for many developers.
Q. What do I need to know if I want to use JDBC? JDBC provides a high amount of simplicity to developers, however there are some basic things that are, in general, applicable to use it effectively:
Knowledge of SQL.
Access to a database and an appropriate JDBC driver. Most database vendors provide one or more types of JDBC drivers, downloadable via their website.
Familiarity with JDBC -- That's the goal of this series of articles.
Q. How do I use JDBC to talk to a database? Shown below are the steps involved in using JDBC to talk to a database:
Load an appropriate JDBC driver.
Open a connection with the database.
Get a statement object to fire a database call.
Optionally, update the statement with any inputs. For example, you may want to pass the values for an insert clause.
Fire the statement.
If the statement returns any result itís returned in the form of a java.sql.ResultSet object. This lets you traverse the results in a sequential manner.
Cleanup: close the associated objects. At this point, you may either close the database connection or may return it to a connection pool.
Java provides JDBC related APIs via the java.sql package.
As we discussed earlier, the role of a JDBC driver is to act as a gateway between your Java program and the database. It's recommended to use an appropriate JDBC driver from the database vendor. However, you can use the JDBC-ODBC bridge driver bundled with the standard JDK distribution for development purposes only.
Opening a connection with the database requires a connect string, and an optional username and password. Once you have the connection established, it provides you with the necessary resources to access the database.
The statement lets you fire the SQL statements. This is a frequently used object in JDBC programming.
For select queries, the result is provided via a java.sql.ResultSet object. The ResultSet is an interface that provides convenient APIís to extract the returned data (more about this later).
And finally, you should do the clean up by closing the ResultSet, Statement and Connection objects. You may defer closing of certain objects, like the connection, if you are using some sort of object pooling.