Connect to MySQL with JDBC driver

A JDBC example to show you how to connect to a MySQL database with a JDBC driver.

Tested with:

• Java 8
• MySQL 5.7
• MySQL JDBC driver mysql-connector-java:8.0.16

1. Download MySQL JDBC Driver

Visit https://dev.mysql.com/downloads/connector/j/ to download the latest MySQL JDBC Driver.

2. JDBC Connection

2.1 Make a connection to the MySQL database.

JDBCExample.java

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

public class JDBCExample {

    public static void main(String[] args) {

        // https://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html#package.description
        // auto java.sql.Driver discovery -- no longer need to load a java.sql.Driver class via Class.forName

        // register JDBC driver, optional since java 1.6
        /*try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }*/

        // auto close connection
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://127.0.0.1:3306/test", "root", "password")) {

            if (conn != null) {
                System.out.println("Connected to the database!");
            } else {
                System.out.println("Failed to make connection!");
            }

        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

Test:

# compile
> javac JDBCExample.java

# run
> java JDBCExample

SQL State: 08001
No suitable driver found for jdbc:mysql://127.0.0.1:3306/test

To run it with java command, we need to load the MySQL JDBC driver manually. Assume everything is stored in the c:\test folder, run it again with this -cp option.

> java -cp "c:\test\mysql-connector-java-8.0.16.jar;c:\test" JDBCExample
Connected to the database!

Example to run on Ubuntu/Unix (Reference: How to run Java program in terminal with external library JAR):

You can do :

# 1) javac -cp /path/to/jar/file Myprogram.java
# 2) java -cp .:/path/to/jar/file Myprogram

# So, lets suppose your current working directory in terminal is src/Report/
# 1) javac -cp src/external/myfile.jar Reporter.java
# 2) java -cp .:src/external/myfile.jar Reporter

====
javac -cp "mysql-connector-java-8.0.16.jar" JDBCExample.java
java -cp ".:mysql-connector-java-8.0.16.jar" JDBCExample

3. Maven Project

3.1 The MySQL JDBC driver is available in the Maven central repository.

pom.xml

	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>8.0.16</version>
    </dependency>

3.2 A simple JDBC select example.

JDBCExample2.java

import com.favtuts.jdbc.model.Employee;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCExample2 {

    public static void main(String[] args) {

        System.out.println("MySQL JDBC Connection Testing ~");

        List<Employee> result = new ArrayList<>();

        String SQL_SELECT = "Select * from EMPLOYEE";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://127.0.0.1:3306/test", "root", "password");
             PreparedStatement preparedStatement = conn.prepareStatement(SQL_SELECT)) {

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {

                long id = resultSet.getLong("ID");
                String name = resultSet.getString("NAME");
                BigDecimal salary = resultSet.getBigDecimal("SALARY");
                Timestamp createdDate = resultSet.getTimestamp("CREATED_DATE");

                Employee obj = new Employee();
                obj.setId(id);
                obj.setName(name);
                obj.setSalary(salary);
                // Timestamp -> LocalDateTime
                obj.setCreatedDate(createdDate.toLocalDateTime());

                result.add(obj);

            }
            result.forEach(x -> System.out.println(x));

        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}

Employee.java

import java.math.BigDecimal;
import java.time.LocalDateTime;

public class Employee {

    private Long id;
    private String name;
    private BigDecimal salary;
    private LocalDateTime createdDate;

    //...
}

Table definition.

CREATE TABLE EMPLOYEE
(
    ID INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    SALARY DECIMAL(15, 2) NOT NULL,
    CREATED_DATE DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (ID)
);

Download Source Code

$ git clone https://github.com/favtuts/java-core-tutorials-examples.git

$ cd java-jdbc/mysql

References

• MySQL Database
• MySQL GUI – Workbench
• Java JDBC Tutorials