How to Connect to MySQL with Java JDBC driver

This article will go over how to connect to MySQL with the Java JDBC driver. The tutorial will go over step-by-step on how to make your first connection with the Java JDBC driver to MySQL and get started.

Installation of MySQL

For starters, you must have MySQL on your machine. To install MySQL, you can easily use the following command on Ubuntu or Debian machines:

sudo apt-get install mysql-server
sudo apt-get install mysql-client

sudo mysql -u root -ppassword

Create a MySQL database

After entering into the mysql command line, you can start by creating a new MySQL database for use:

create database jdbc;
use jdbc;

Now, you’re using the jdbc database. You can create a new user instead of root to use this database.

CREATE USER jdbcuser IDENTIFIED BY 'jdbcpw';

1	CREATE USER jdbcuser IDENTIFIED BY 'jdbcpw';

GRANT USAGE on *.* to jdbcuser@localhost IDENTIFIED BY 'jdbcpw';
GRANT ALL PRIVILEGES ON FEEDBACK.* to jdbcuser@localhost;

1 2	GRANT USAGE on . to jdbcuser@localhost IDENTIFIED BY 'jdbcpw'; GRANT ALL PRIVILEGES ON FEEDBACK.* to jdbcuser@localhost;

We’ve given permissions for the jdbcuser.

Now, we’ll also be creating a sample database so that you can try the MySQL JDBC driver.

CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT,
    USER VARCHAR(30) NOT NULL,
    EMAIL VARCHAR(30),
    PRIMARY KEY(ID));

CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT,

USER VARCHAR(30) NOT NULL,

EMAIL VARCHAR(30),

PRIMARY KEY(ID));

INSERT INTO users VALUES (default, 'huyle333', 'huyle333@bu.edu');

1	INSERT INTO users VALUES (default, 'huyle333', '[email protected]');

Installation of MySQL JDBC driver

You will need to install the MySQL JDBC driver. The latest JDBC driver can be found at: http://dev.mysql.com/downloads/connector/j

Java JDBC Project

We create a Java project and package called com.slothparadise.jdbcproject.

We create a folder named lib inside the project and copy the JDBC driver into this folder so that we can use the driver. Make sure to copy the .jar file from the downloaded JDBC driver into the lib folder. On Eclipse, you can use File, Import, General, File System. Select the lib folder that you had created to import.

Now, we must adjust the classpath. On Eclipse, you can do so by right clicking on the JAR file and selecting Build Path, Add to Build Path.

Sample Java Class project

We will start by creating a new Java class for our project. I will create a class called JDBCExample.

The following prints the data from the MySQL database from the JDBC driver.

package com.slothparadise.jdbcproject;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

// This class prints out contents of the MySQL database using the JDBC driver.
public class JDBCExample {

    private Connection connection = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    // readDatabase does sample statements with the JDBC driver
    public void readDatabase() throws Exception {
        try {
            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost/jdbc?user=jdbcuser&password=jdbcpw");

            // preparedStatements make things easy. We give an example of inserting into the db.
            preparedStatement = connection.prepareStatement("INSERT INTO jdbc.users VALUES (default, ?, ?)");
            preparedStatement.setString(1, "winston");
            preparedStatement.setString(2, "winstonc@bu.edu");
            preparedStatement.executeUpdate();

            // We give an example of selecting specific categories to print.
            preparedStatement = connection.prepareStatement("SELECT user, email FROM jdbc.users");
            resultSet = preparedStatement.executeQuery();
            printResultSet(resultSet);

            // Alternatively, you can use regular statements. We will print the db a second time.
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM jdbc.users");
            printResultSet(resultSet);

        } catch (Exception e) {
            throw e;
        } finally {
            closeConnection();
        }
    }

    // Function to print the contents of the db.
    private void printResultSet(ResultSet resultSet) throws SQLException {
         while (resultSet.next()) {
            String user = resultSet.getString("user");
            String email = resultSet.getString("email");
            System.out.println("User is " + user);
            System.out.println("Email is " + email);
        }
    }

    // You need to close the connections after you're done
    private void closeConnection() {
        try {
           if (resultSet != null) {
               resultSet.close();
           }

           if (statement != null) {
               statement.close();
           }

           if (connection != null) {
               connection.close();
           }
       } catch (Exception e) {

       }
   }

   public static void main(String[] args) throws Exception {
        readDatabase();
   }
}

package com.slothparadise.jdbcproject;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Date;

// This class prints out contents of the MySQL database using the JDBC driver.

public class JDBCExample {

private Connection connection = null;

private Statement statement = null;

private PreparedStatement preparedStatement = null;

private ResultSet resultSet = null;

// readDatabase does sample statements with the JDBC driver

public void readDatabase() throws Exception {

try {

Class.forName("com.mysql.jdbc.Driver");

connection = DriverManager.getConnection("jdbc:mysql://localhost/jdbc?user=jdbcuser&password=jdbcpw");

// preparedStatements make things easy. We give an example of inserting into the db.

preparedStatement = connection.prepareStatement("INSERT INTO jdbc.users VALUES (default, ?, ?)");

preparedStatement.setString(1, "winston");

preparedStatement.setString(2, "[email protected]");

preparedStatement.executeUpdate();

// We give an example of selecting specific categories to print.

preparedStatement = connection.prepareStatement("SELECT user, email FROM jdbc.users");

resultSet = preparedStatement.executeQuery();

printResultSet(resultSet);

// Alternatively, you can use regular statements. We will print the db a second time.

statement = connection.createStatement();

resultSet = statement.executeQuery("SELECT * FROM jdbc.users");

printResultSet(resultSet);

} catch (Exception e) {

throw e;

} finally {

closeConnection();

}

// Function to print the contents of the db.

private void printResultSet(ResultSet resultSet) throws SQLException {

while (resultSet.next()) {

String user = resultSet.getString("user");

String email = resultSet.getString("email");

System.out.println("User is " + user);

System.out.println("Email is " + email);

}

// You need to close the connections after you're done

private void closeConnection() {

try {

if (resultSet != null) {

resultSet.close();

}

if (statement != null) {

statement.close();

}

if (connection != null) {

connection.close();

}

} catch (Exception e) {

}

public static void main(String[] args) throws Exception {

readDatabase();

}

Run the program

When you run the program with this class, it will print out the contents of the MySQL database twice.

August 3, 2015