SQL Zone is brought to you in partnership with:

Nitin has posted 391 posts at DZone. View Full User Profile

The ABCs of JDBC, Part 1 - Getting Started with JDBC

04.21.2010
| 15317 views |
  • submit to reddit

The Java Database Connectivity (JDBC) API remains the industry standard for database-independent connectivity between Java and a wide range of databases. While modern object-relational frameworks, such as Hibernate and JPA, have simplified data persistence, knowledge of the JDBC API is still an essential part of any Java developer’s tool belt.

DZone has assembled a panel of JDBC experts from the community that will be answering some of your most common (and not-so-common) questions about the JDBC API.  Over the next several months, we will be publishing their responses here on JavaLobby. We encourage you to provide your insights and experiences using JDBC as well!

This week, Daniel Rubio, author of Web Forefront, provides answers to some common (yet essential) JDBC questions.

Read the other parts in DZone's JDBC FAQ series:

 

What is JDBC?

Daniel Rubio: The JDBC API is a standard that allows Java applications to perform operations against permanent storage sources, which include Relational Database Management Systems (RDBMS), spreadsheets, flat files and other tabular data sources.

Since the JDBC API is supported across several permanent storage solutions, this allows Java developers to learn and concentrate on a single API, without worrying about the potential discrepancies of performing operations against different permanent storage solutions.

The JDBC API forms part of Java's Standard Edition.

 

What is a JDBC Driver ?                                                           

Daniel: A JDBC driver is what allows a permanent storage solution to support operations through the JDBC API. Each permanent storage solution generally comes accompanied by a JDBC driver. Though it's possible to write a JDBC driver from scratch, creating a JDBC driver requires a deep understanding of the underlying permanent storage solution, so you're best served using a pre-built JDBC driver.

 

How does the Java Database Connectivity (JDBC) work ?

Daniel: JDBC maps a permanent storage solution's operations under a single API (i.e. the JDBC API). Since each permanent storage solution will have a proprietary way in which to create, read, update and delete data, JDBC maps each of these proprietary calls to a single JDBC API call which performs uniformly irrespective of the permanent storage manufacturer.

You can think of it in terms of crossing a bridge, with one end representing the Java application and another the permanent storage solution. To cross the first half of the bridge, you must learn the JDBC API and incorporate it with a Java application. The cross the second half of the bridge, you must rely on a JDBC driver designed for a particular permanent storage solution. The benefit is that once you know the JDBC API, you're always half-way through communicating with any permanent storage solution that supports JDBC.

 

If I use the JDBC API, do I have to use ODBC ?

Daniel: There is only one circumstance in which JDBC would be used in conjunction with ODBC: A lack of a suitable JDBC driver for a permanent storage solution. Under such circumstances, a permanent storage solution's ODBC driver is used as the underlying mechanism to perform operations through the JDBC API.

It should be noted though, this approach -- often called JDBC-ODBC bridge -- has lost popularity with the growing presence of JDBC drivers provided by manufacturers. As far as the ODBC API itself is concerned, there is no need to use this API while using the JDBC API.

 

Once I have Java's Standard Edition, what else do I need to connect to a database ?

Daniel: In addition to Java's SE which includes the JDBC API, you'll also need to obtain the following:

  •  A JDBC driver (i.e. JAR file) for the particular database version and manufacturer you wish to connect to.
  •  Connection credentials to the permanent storage system (e.g. username, password, location-URL) -- this is provided by your system administrator or DBA.

 

What are the steps required to execute a query in JDBC ?

  • Establish a connection to a permanent storage system
  • Perform the required query using the JDBC API
  • Validate and use the results obtained from using the JDBC API

 

How do I create a database connection ?

Daniel: A connection to a database is made through a combination of JDBC API classes, including configuration parameters specific to a database. There are two ways in which to create a database connection, one involves the DriverManager class and the other the DataSource class. Note that both classes belong to the JDBC API.

The DriverManager class represents the earliest approach to creating a database connection. The following code snippet illustrating this process.

Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "foo", "bar");
Statement stmt = con.createStatement();

 

 

 To create a connection using the DriverManager class you need to call the getConnection() method. In this case, the getConnection() method receives three parameters: a String specifying the connection URL of the database -- in the form "jdbc:<database_type>://<host>:<port>/<name>" -- and two additional Strings specifying the database username and password. In addition, prior to instantiating a connection using the DriverManager class, a call is made to Class.forName to ensure the appropriate JDBC driver class is loaded onto Java's classpath. Once the Connection object is created, it can be used to perform queries against a database.

Another variation to creating a database connection consists of using the DataSource class. The DataSource class is primarily designed to be used in Java EE applications and is a newer approach to using the DriverManager class. Given the needs of Java EE applications, the DataSource class increases code portability, as well as performance when using the JDBC API.

For example, the following code snippet illustrates how to create a database connection using the DataSource class.

InitialContext cxt = new InitialContext();
DataSource ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/mysql");
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();

 

Similar to the DriverManager class, the DataSource class also calls a method named getConnection() in order to create a connection. However, unlike its counterpart, notice the DataSource object doesn't have as many hard-coded connection parameters, but is rather instantiated with the line:

ctx.lookup("java:/comp/env/jdbc/mysql"); 

 

This last line represents a JNDI resource.  

JNDI resources are common in Java EE applications since they allow resources to be configured in a central location, instead of hard-coding them in Java code. In this case, the JNDI resource is configured with the same database connection parameters (i.e. "jdbc:mysql://localhost:3306/test", "foo", "bar"), except its done so in a centralized configuration file. Discussing JNDI resource configuration would go beyond the scope of this question, consult your Java EE web container's documentation for more on JNDI resource configuration.   

Another variation to using the DataSource class consists of forgoing the use of JNDI and instead relying on dependency injection -- a common process in Java applications using frameworks like Spring or Guice. The following code snippet illustrates this sequence.

private DataSource ds = null;
public void setDataSource(DataSource ds) {
this.ds = ds;
}

Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();

   

In this case notice the DataSource object apparently lacks any configuration parameters, however, this is not the case. When an application using this particular code snippet relies on a dependency injection framework, an object is said to be injected with its values. In a similar way to using JNDI, a DataSource's database connection parameters (i.e. "jdbc:mysql://localhost:3306/test", "foo", "bar") are configured in a centralized configuration file. Discussing dependency injection configuration would go beyond the scope of this question, consult your dependency injection framework's documentation for more on DataSource injection configuration.

Finally, another advantage to using the DataSource class over the ConnectionManager class is that the DataSource class is tightly knit to the usage of connection pools. Connection pools are described next.

 

What is Connection?                                                           

Daniel: A Connection is an object capable of performing CRUD operations (e.g. create, read, update, delete) on a permanent storage system.

 

What is Connection pooling?                                                           

Daniel: Connection pooling is the act of re-using Connection objects used to access permanent storage systems. Since creating Connection objects involves time and resources (e.g. establishing a connection through a network), keeping Connection objects in a stand-by state can increase performance in application's using the JDBC API.

The management of Connection pools, which can include pre-spawning Connection objects or limiting the number of Connection objects in a pool, is generally delegated to third party libraries. One of the most popular pooling libraries is Apache's DBCP (http://commons.apache.org/dbcp/). It should be further noted, that if an application is designed to operate on a Java EE web container (e.g. Tomcat) Connection pooling is the recommended approach.

 

What is DriverManager ?

Daniel: DriverManager is a class which allows a connection to be made to a permanent storage system using JDBC. See 'How do I create a database connection' for more details on DriverManager.

 

What is Statement pooling?                                                           

Daniel: Similar to Connection pooling, Statement pooling consists of re-using Statement objects used to query a permanent storage system. Since executing a Statement object involves time and resources (e.g. update record X, update record Y, update record Z), keeping Statement objects in a pre-compiled form can increase performance in application's using the JDBC API.

 

What is DML?                                                           

Daniel: DML stands for Data Manipulation Language (DML) and consists of statements used to manage data. The JDBC API supports a multitude of DML operations. Typical DML operations include SELECT, INSERT, UPDATE and DELETE. DML is not directly tied to JDBC, but rather forms part of a broader set of database terms along with Data Definition Language (DDL), Data Control Language (DCL) and Transaction Control Language (TCL).

 

What are four types of JDBC drivers ?           

JDBC drivers have evolved over time. There are now four types of JDBC drivers and ongoing work on a fifth type.

Type 1 JDBC drivers - The first JDBC drivers. Type 1 drivers operate on the foundations of ODBC, which was an established permanent storage API at the time of JDBC's release. This allowed the JDBC API to be used in application's that needed to connect to permanent storage system's via ODBC. These type of drivers are often called JDBC-ODBC bridges, given their need to operate through ODBC.

Type 2 JDBC drivers - Type 2 drivers convert JDBC calls into native calls belonging to a permanent storage manufacturer's API. Compared to type 1 JDBC drivers, type 2 JDBC drivers represent a step forward because they are linked directly to a permanent storage manufacturer's API, instead of ODBC. This makes the connection process more efficient, albeit less portable since the driver is linked to a permanent storage manufacturer API (e.g. version and operating system). 

Type 3 JDBC drivers - Type 3 drivers operate through a permanent storage system's protocol, unlike type 1 and type 2 drivers which operate by mapping lower level APIs (i.e. ODBC and native). Type 3 JDBC drivers require the presence of a middle-tier. This middle-tier then serves as a broker, converting application calls to a database independent protocol, which is then translated into a database protocol.

Type 4 JDBC drivers - Similar to type 3 JDBC drivers, type 4 drivers also operate through a permanent storage system's protocol, except they don't require a middle-tier to operate, thus resulting in better performance over type 3 JDBC drivers. Connections between an application and a permanent storage system can be made directly (i.e. usually through a network connection). The majority of modern permanent storage solutions offer type 4 JDBC drivers.

Type 5 JDBC drivers (Ongoing work) - Though there is no official word on the advent of type 5 JDBC drivers, there are numerous writings around the web on some of the shortcomings of type 4 JDBC drivers. Some of the issues include, addressing technologies like Object Relational Mapping (ORM) which depend on JDBC and have had explosive growth in recent years, bulk data loading and all-in-one deployment. You can find an interesting read on the possible future of type 5 JDBC drivers from Jesse Davis -- active member of the JDBC Expert Group -- at  Has JDBC kept up with Enterprise requirements.

 

Editor's Note: In the coming installments, we will look at JDBC Result Sets, data types, transactions, and more.

AttachmentSize
rubio.jpg3.91 KB
Published at DZone with permission of its author, Nitin Bharti.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Thomas Eichberger replied on Wed, 2010/04/21 - 4:50pm

Is this article left from 1997???

Andy Jefferson replied on Thu, 2010/04/22 - 12:39pm

The JDBC API is a standard that allows Java applications to perform operations against permanent storage sources, which include Relational Database Management Systems (RDBMS), spreadsheets, flat files and other tabular data sources.

While there may exist JDBC drivers for some spreadsheets, or flat files, those are certainly not the most common way of creating/accessing/updating data in those spreadsheets or flat files programmatically. A custom API is what is used for the vast majority of cases (e.g Apache POI, ODFDOM, etc). JDBC is to all intents and purposes a mechanism for RDBMS, and not much more.

Bas Schulte replied on Thu, 2010/04/22 - 1:42pm in response to: Thomas Eichberger

> Is this article left from 1997???

Those were my exact same thoughts!

Francois Grogor replied on Fri, 2010/04/23 - 3:51am

I thought this article was great for someone wanting a clear understanding of creating connections for a normal stand-alone app VS a connection for J2EE container. If your a seasoned pro then most of this information you would find redundant. For me, it was perfect. Thanks so much

Thomas Eichberger replied on Tue, 2010/05/25 - 12:30am

@Francois: well, if you're new to JDBC, this might help:

http://www.roseindia.net/jdbc/

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.