Connecting to MySQL using JDBC

The JDBC API defines a set of interfaces and classes that all major database providers adhere to in order allow Java developers to seamlessly connect to many Relational Database Management Systems (RDBMS).   All major vendors provide their own JDBC drivers which contain a set of java classes that enables you to connect to that particular database.

What JDBC does is standardize how to do many of the operations like: connect to the database, how to query the database, how to update the database, and how to call stored procedures.

Installation and Preparation of Database Drivers

Before you begin you will need to download and install the appropriate JDBC drivers from the vendor of choice for your database. Below you will find the links to each of the most popular databases available.

Oracle 11g Database Drivers

• Download 11g drivers here

Sybase Database Drivers

• Download Sybase JConnect drivers here

Microsoft SQL Server Database Drivers

• Download Microsoft JDBC drivers here

DB2 Database Drivers

• Download DB2 Universal JDBC drivers here

MySQL Database Drivers

• Download MySQL Connector/J drivers here

PostgreSQL Database Drivers

• Download PostgreSQL JDBC drivers here

MongoDB Database Drivers

• Download MongoDB JDBC drivers from Unity here

Common Database URL Formats

JDBC Basics

In order to use your database and connect via JDBC you will need to do the following five steps:

• Add the JDBC Driver jar files to build path
• Import java.sql.* packages
• Register the Database Driver
• Create the Database Connections
• Close Connections

1. Add the JDBC Driver JAR files in Eclipse

In your development environment, in my case, Eclipse, you will need to ensure that the JDBC Driver is added to the build path. For my example, I am using the MySQL JDBC drivers. In order to add the new jar file into your project build path, click on the package name in Package Explorer and hit Alt + Enter, then click on Java Build Path. Next click on the button that says “Add External JARs…” and locate your newly downloaded JDBC Driver jar files. In my case, since I am using MySQL, I will choose mysql-connector-java-5.1.33-bin.jar.

2. Import Java.SQL Package

Now that we have added the appropriate JDBC driver to our Java build path, you will need to import the java.sql.* classes. The import statements declares Java class(es) to use in the code below the import statement. Once a Java class is declared, then the class name can be used in the code without specifying the package the class belongs to.

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

or use one import statement;

import java.sql.*;

3. Register the Database Driver

Before you can begin using JDBC with your database provider, you must register the driver. You do this using the Class.forName method for JDK-compliant JVMs and DriverManager.registerDriver for Microsoft JVMs.

try {
  Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
  System.out.println("ERROR: Unable to load SQLServer JDBC Driver");
  e.printStackTrace();
  return;
}

4. Create the database connection

Once we have registered our JDBC driver, we need to establish a connection to the database. This is done using the DriverManager.getConnection method.

try {
       connection = DriverManager.getConnection(database_url, username, password);
    } catch (SQLException e) {
      System.out.println("ERROR:  Unable to establish a connection with the database!");
      e.printStackTrace();
      return;
    }

5. Create the JDBC statement

Once we have a connection we can begin to interact with the database. The JDBC createStatement(), prepareCall(), and prepareStatement() methods enable you to send your SQL commands and receive data from our database.

try{
  statement = connection.createStatement();
  result = statement.executeQuery("select employee_id, first_name, last_name from tutorial.employee");

  while (result.next()) {
    String employee_id = result.getString("employee_id");
    String first_name  = result.getString("first_name");
    String last_name  = result.getString("last_name");
    System.out.printf("Employee ID: [%s], %s %s n", employee_id, first_name, last_name);
  }
} catch (SQLException e) {
   System.out.println(e.getMessage());
}

6. Iterate through the ResultSet

A ResultSet contains data records being returned from a previously executed database query. Looking at the Javadocs, a ResultSet maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The ‘next’ method moves the cursor to the next row.

As you can see below, we are looping through all of the records in the ResultSet using the while loop. We are stepping through each record in the ResultSet using result.next().

try{
  statement = connection.createStatement();
  result = statement.executeQuery("select employee_id, first_name, last_name from tutorial.employee");

  while (result.next()) {
    String employee_id = result.getString("employee_id");
    String first_name  = result.getString("first_name");
    String last_name  = result.getString("last_name");
    System.out.printf("Employee ID: [%s], %s %s n", employee_id, first_name, last_name);
  }
} catch (SQLException e) {
   System.out.println(e.getMessage());
}

7. Closing the Connection

As a rule, you must ensure you close database connections and other resources at the end of your program or when they are no longer necessary. Forgetting to do so is generally considered to be poor programming practice and can lead to hard to track problems like memory leaks.

} finally {
  if (connection != null) connection.close();
}

Sample JDBC Program

package com.avaldes.tutorials;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MySqlJDBCExample {

  public static void main(String[] args) throws SQLException {
    String database_url   = "jdbc:mysql://localhost:3306/tutorial";
    String username     = "webuser";
    String password     = "webuser123";
    Connection connection   = null;
    Statement statement   = null;
    ResultSet result      = null;

    try {
      Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
      System.out.println("ERROR: Unable to load SQLServer JDBC Driver");
      e.printStackTrace();
      return;
    }
    System.out.println("MySQL JDBC Driver has been registered...");

    System.out.println("Trying to get a connection to the database...");
    try {
      connection = DriverManager.getConnection(database_url, username, password);
    } catch (SQLException e) {
      System.out.println("ERROR:  Unable to establish a connection with the database!");
      e.printStackTrace();
      return;
    }

    if (connection != null) {
      DatabaseMetaData metadata = connection.getMetaData();
      System.out.println("Connection to the database has been established...");
        System.out.println("JDBC Driver Name : " + metadata.getDriverName());
        System.out.println("JDBC Driver Version : " + metadata.getDriverVersion());
    } else {
      System.out.println("ERROR: Unable to make a database connection!");
    }

    System.out.println("Trying to get a list of all employees in employee table...");
    try {
        statement = connection.createStatement();

          String sql = "select employee_id, first_name, last_name from tutorial.employee";
          result = statement.executeQuery(sql);

          while (result.next()) {
              String employee_id = result.getString("employee_id");
              String first_name  = result.getString("first_name");
              String last_name  = result.getString("last_name");

              System.out.printf("Employee ID: [%s], %s %s n", employee_id, first_name, last_name);
          }
    } catch (SQLException e) {
        System.out.println(e.getMessage());
    } finally {
      System.out.println("Closing all open resources...");
          if (result != null) result.close();
        if (statement != null) statement.close();
        if (connection != null) connection.close();
    }
  }
}

Output

Related Posts

• Connecting to SQL Server using JDBC
• Connecting to Oracle using JDBC
• Connecting to MySQL Server using JDBC
• Connecting to MongoDB Server using JDBC

Please Share Us on Social Media