JDBC 101 - Connect to a SQL database with JDBC

Java database FAQ: How do I connect to a SQL database with Java and JDBC?

If you're interested in connecting your Java applications to standard SQL databases like Oracle, MySQL, SQL Server, and others, the Java JDBC technology is exactly what you need. The combination of Java/JDBC and standard SQL queries creates a simple and powerful database solution. JDBC makes the simple things easy -- without making the complex tasks too difficult either.

JDBC connections

In this first article in our JDBC tutorial series, we'll show you step-by-step how to establish a database connection from your Java programs to a SQL database using JDBC (i.e., creating a "JDBC connection"). In the process we'll show you how to connect to two different databases -- Mini SQL (mSQL), and Interbase -- just so you can see how the code changes when you switch from one database to another.

(I was going to update this tutorial to connect to MySQL and Postgres, but the databases you connect to don't matter much -- that's one of the beauties of JDBC. Once you're connected to the database with JDBC, most databases act the same.)

Obtaining the JDBC driver

Before you start working with JDBC, you'll need a copy of the Java JDK. If you don't have it already, you can get the JDK/SDK for free at Sun's Java web site, or it will also be included with many IDEs that you can use, including Eclipse and NetBeans.

Once you have the JDK, the next thing you need to do is to get the correct JDBC driver for your database. In most cases the JDBC driver will be provided by your database vendor. To make it easier, I've created a page of JDBC drivers for common SQL databases.

Once you have the correct JDBC driver for your database, install it according to the instructions that came with it. Installation instructions will vary somewhat for each vendor.

Establishing a JDBC database connection in two steps

Once you have the correct JDBC driver installed, establishing a JDBC connection from your Java programs to your SQL database is pretty easy.

Regardless of whether you're trying to connect to Oracle, SQL Server, MySQL, Postgres, mSQL, or Interbase (or any other JDBC data source), establishing a connection to an SQL database with Java JDBC is a simple two-step process:

1. Load the JDBC driver.
2. Establish the JDBC connection to your database.

We'll show you two JDBC examples just so you can see how easy it is, and how little the code changes when you migrate from one database server to another.

1) A Java JDBC Connection example

Listing 1 provides the full source code required to establish a JDBC connection to a mSQL database on a server named "www.myserver.com".

//  Establish a connection to a mSQL database using JDBC. 
import java.sql.*; 

class JdbcTest1 { 

  public static void main (String[] args) { 
    try
    {
      // Step 1: Load the JDBC driver. 
      Class.forName("com.imaginary.sql.msql.MsqlDriver"); 
      // Step 2: Establish the connection to the database. 
      String url = "jdbc:msql://www.myserver.com:1114/contact_mgr"; 
      Connection conn = DriverManager.getConnection(url,"user1","password");  
    }
    catch (Exception e)
    {
      System.err.println("Got an exception! "); 
      System.err.println(e.getMessage()); 
    } 
  } 
} 

Listing 1 (above): This source code example shows the two steps required to establish a connection to a Mini SQL (mSQL) database using JDBC.

2) An Interbase JDBC ODBC Connection example

Listing 2 provides the full source code required to establish a JDBC connection to an Interbase database. In this example, we're connecting to a local Interbase server using the JDBC ODBC bridge driver after setting up Interbase as an ODBC datasource on the local system.

//  Establish a connection to an Interbase database using JDBC and ODBC. 
import java.sql.*; 

class JdbcTest1
{
  public static void main (String[] args)
  { 
    try
    { 
      // Step 1: Load the JDBC ODBC driver. 
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
      // Step 2: Establish the connection to the database. 
      String url = "jdbc:odbc:contact_mgr"; 
      Connection conn = DriverManager.getConnection(url,"user1","password");  
    }
    catch (Exception e)
    { 
      System.err.println("Got an exception! "); 
      System.err.println(e.getMessage()); 
    } 
  } 
}

Listing 2 (above): This source code example shows the two steps required to establish a connection to an Interbase database using JDBC.

What's the difference?

The cool thing about using Java to connect to a SQL database is that the difference between the two source code listings is very small. As you can see from the code, there are really only two differences:

1. The name of the JDBC driver.
2. The JDBC URL used to connect to the database.

Everything else in the two source code listings -- except for the comment at the top -- is identical. Here's a slightly more detailed discussion of the two differences:

1.The JDBC Driver

The name of the JDBC driver will be supplied to you by your database vendor. As you can see in the class.forName() statements, these names will vary. In the first case we're using the mSQL-JDBC driver. In the second case we're using the JDBC-ODBC Bridge driver supplied with the Interbase server.

2. The URL

The syntax of the DriverManager.getConnection() method is:

DriverManager.getConnection(String url, String username, String password);

The username and password are the normal names you use to log into your database. The URL you use will again vary with the database you use. In both examples shown, we're establishing a connection to a database named contact_mgr. (We'll use this database for all of our examples in this series of JDBC articles.)

If you stick with standard SQL commands, it can be very easy to switch from one database server to another. In fact, I've heard from several developers who are using mSQL to prototype their software (because it's so inexpensive), and then switching to another commercial vendor when it's time to take their product "live".

For your reference, I just created this page of example JDBC connection strings (JDBC driver string and URL).

Conclusion

As shown, establishing a JDBC connection to an SQL database with Java is a simple, two-step process. The process is nearly identical for all SQL databases, and the only real differences are (a) the JDBC driver name, and (b) the JDBC URL used to connect to the database. Your database vendor will provide this information in their documentation.

Much more related JDBC content

There is a lot of newer Java and JDBC content on my blog, including these JDBC tips:

• How do I connect to a database?
• How do I create and use a PreparedStatement?
• A good example of using try/catch/finally with JDBC


• Using a PreparedStatement with a SELECT statement and LIKE clause
• Sample JDBC PreparedStatement (using SQL UPDATE)
• A sample Java/JDBC program to connect to a database


• The SQLProcessor as a JDBC Facade (a better way to use JDBC)
• A simple Spring JDBC example showing a SELECT and INSERT
• Spring JDBC SELECT statement examples


• Spring JDBC DELETE examples
• How to test your Spring JDBC code