Connecting to a MySQL Database using Connector/J JDBC Driver

Overview
In this tutorial following topics will be covered:

What are Database URLs in JDBC?
Why and how to specify a JDBC Driver name?
How to create a connection to a Database?
An example on how to connect to a MySQL Database?

Note: This tutorial assumes you've correctly setup MySQL database and Connector/J JDBC driver. If not then consult "Installing and Configuring MySQL Database and Connector/J JDBC Driver on Microsoft Windows".

What are Database URLs in JDBC?
URL stands for "Uniform Resource Locator". You will be familiar with HTTP URLs that you normally use to access a web site e.g. http://www.stardeveloper.com. URLs are used to identify a resource using a unique name.

Same goes for database URLs in JDBC. JDBC requires that all database connection strings should be represented by URLs. The URLs used in JDBC have following structure:

jdbc:subprotocol:subname

In HTTP you begin a URL with the protocol name i.e. http:, similarly in JDBC driver URLs, you start the URL with protocol name i.e. jdbc:. Next subprotocol represents the database you want to connect to e.g. mysql, oracle, odbc etc. While subname provides additional information on how and where to connect.

Tip: If you are familiar with ASP/ASP.NET, a database URL in JDBC is quite similar to a connection string used in an ASP environment to connect to a database.

Examples of Database URLs
Following are some examples of JDBC database URLs:

jdbc:odbc:dsn_name;UID=your_uid;PWD=your_pwd - JDBC-ODBC Bridge Driver URL.
jdbc:oracle:thin:@machine_name:port_number:instance_name - Orace Type 4 JDBC Driver.
jdbc:mysql://host_name:port/dbname - MySQL Connector/J JDBC Driver.

Why and how to specify a JDBC Driver name?
Next thing you need to know besides the database URL is the full class name of your JDBC driver e.g. com.mysql.jdbc.Driver in case of MySQL Connector/J JDBC driver. The name of the driver is a requirement and is not optional.

Note: In ASP/ASP.NET both database URL and driver name are part of a single connection string, while Java separates them for greater flexibility.

You can tell JVM about what driver/s to use by using one of the following methods:

To load the the driver/s at JVM startup, specify the driver/s in jdbc.drivers system property like this:
```
java -Djdbc.drivers=com.mysql.jdbc.Driver YourJavaProgram
```
To explicitly load the driver, use Class.forName() method in your code like this:
```
Class.forName("com.mysql.jdbc.Driver").newInstance();
```

Note: In above examples, "com.mysql.jdbc.Driver" is the name of the JDBC driver that you want to load.

The example discussed in this tutorial makes use of the second option discussed above.

How to create a connection to a Database?
To create a connection to a database, you will have to use java.sql.DriverManager's getConnection() method. This method takes as an argument the database URL (that we discussed earlier) you want to connect to. It then internally finds the appropriate driver which has been loaded in the JVM and then delegates the work of creating the connection to that driver.

An example on how to connect to a MySQL Database?
After learning the theory behind connecting to a database, we'll now move on to create a Java program which will connect to a MySQL database running on your local system.

JdbcExample2.java
Create a new Java source file and save it as JdbcExample2.java. Copy/paste following code in it:

package com.stardeveloper.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JdbcExample2 {

  public static void main(String args[]) {
    Connection con = null;

    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      con = DriverManager.getConnection("jdbc:mysql:///test",
        "root", "secret");

      if(!con.isClosed())
        System.out.println("Successfully connected to " +
          "MySQL server using TCP/IP...");

    } catch(Exception e) {
      System.err.println("Exception: " + e.getMessage());
    } finally {
      try {
        if(con != null)
          con.close();
      } catch(SQLException e) {}
    }
  }
}

Explanation
Above Java program tries to connect to "test" database installed on your local MySQL server. "test" database is installed on all MySQL servers by default so that is why I am using this database to connect to, otherwise as far as this tutorial is concerned, it won't be accessing any tables in that database.

On the next page, we will look into this code and learn what it does, step-by-step. We will then execute this code and display the status message on the console.